Python Data Persistence – SQLAlchemy Core

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")
)