How to Create a Database
We'll cover the following...
Creating a database with SQLAlchemy is really easy. SQLAlchemy uses a Declarative method for creating databases. We will write some code to generate the database and then explain how the code works. If you want a way to view your SQLite database, I would recommend the SQLite Manager plugin for Firefox. Here’s some code to create our database tables:
Press + to interact
# table_def.pyfrom sqlalchemy import create_engine, ForeignKeyfrom sqlalchemy import Column, Date, Integer, Stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import relationship, backrefengine = create_engine('sqlite:///mymusic.db', echo=True)Base = declarative_base()class Artist(Base):""""""__tablename__ = "artists"id = Column(Integer, primary_key=True)name = Column(String)class Album(Base):""""""__tablename__ = "albums"id = Column(Integer, primary_key=True)title = Column(String)release_date = Column(Date)publisher = Column(String)media_type = Column(String)artist_id = Column(Integer, ForeignKey("artists.id"))artist = relationship("Artist", backref=backref("albums", order_by=id))# create tablesBase.metadata.create_all(engine)
If you run this code, then you should see something similar to the following output:
Press + to interact
2014-04-03 09:43:57,541 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_12014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_12014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("artists")2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("albums")2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.EngineCREATE TABLE artists (id INTEGER NOT NULL,name VARCHAR,PRIMARY KEY (id))2014-04-03 09:43:57,551 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,661 INFO sqlalchemy.engine.base.Engine COMMIT2014-04-03 09:43:57,661 INFO sqlalchemy.engine.base.EngineCREATE TABLE albums (id INTEGER NOT NULL,title VARCHAR,release_date DATE,publisher VARCHAR,media_type VARCHAR,artist_id INTEGER,PRIMARY KEY (id),FOREIGN KEY(artist_id) REFERENCES artists (id))2014-04-03 09:43:57,661 INFO sqlalchemy.engine.base.Engine ()2014-04-03 09:43:57,741 INFO sqlalchemy.engine.base.Engine COMMIT
Why did this happen? Because when we created the engine ...