Python Data Persistence – SQLAlchemy Core
We have to use the same process (as used in ORM) to connect to the database i.e. using create-engine () function that returns Engine object.
from sqlalchemy import create_engine
engine = create_engine('sqlite:///mydb.sqlite',
echo=True)
If you plan to use any other database dialect, ensure that you install its respective DB-API module.
engine = create_engine('mysql+pymydsql://root@ localhost/mydb')
In order to create tables in this database, first, we have to set up a MetaData object which stores table information and other scheme-related information.
from sqlalchemy import MetaData meta=MetaData( )
The Table class in sqlalchemy module needs this metadata object as one of the arguments to its constructor.
TableName=Table ("name", meta, Columnl, Column2, ...)
As we have used before, the Column object represents a column in the database table and needs its name, data type, and constraints (if any) to be specified.
Example
from sqlalchemy import create_engine, MetaData,
Table, Column, Integer, String
engine = create_eng'ine ('sqlite:///mydb. sqlite1 ,
echo=True)
meta=MetaData()
Products = Table('Products', meta,
Column('ProductID', Integer, primary_key=True),
Column('name', String), Column('Price', Integer), ) meta.create_all(engine)
The create_all ( ) function emits equivalent SQL query as follow:
Example
CREATE TABLE "Products" (
"ProductID" INTEGER NOT NULL, name VARCHAR,
"Price" INTEGER,
PRIMARY KEY ("ProductID")
)