Python Data Persistence - ORM - Table Object and Mapped Class

Python Data Persistence – ORM – Table Object and Mapped Class

The next step is to describe the database tables and define the mapping classes. An object of a metaclass, called Declarative Base class that stores a catalog of user-defined classes and mapped tables is first obtained. This Declarative Base class is defined in sqlalchemy. ext.declarative sub-module.

>>> from sqlalchemy.ext.declarative import 
declarative_base
>>> base=declarative_base( )

Use this ‘base’ class to define mapped classes in terms of it. We define the Products class and map it to the Products table in the database. Its table name property defines this mapping. Other attributes are column names in the table.

Example

#myclasses.py
from sqlalchemy.ext.declarative import declarative_ 
base
from sqlalchemy import Column, Integer, String base=declarative_base( ) 
class Product(Base):
tablename = 'Products'

ProductID = Column(Integer, primary_key=True) 
name = Column(String) 
price = Column(Integer)

The column is a SQL Alchemy schema object that represents column in the database table. Its constructor defines name, data type, and constraint parameters. The Column data type can be any of the following generic data types that specify the type in which Python data can be read, written, and stored. SQLAlchemy will choose the best database column type available on the target database when issuing a CREATE TABLE statement.

Biglnteger
Boolean
Date
DateTime
Float
Integer
Numeric
Smalllnteger
String
Text
Time

Even though this class defines mapping, it’s a normal Python class, in which there may be other ordinary attributes and methods as may be required by the application.

The Table object is created as per the specifications in the class and is associated with the class by constructing a Mapper object which remains behind the scene and we normally don’t need to deal with it directly.

The Table object created in the Declarative system is a member of the MetaData attribute of the declarative base class. The create_all ( ) method is called on metadata, passing in our Engine as a source of database connectivity. It will emit CREATE TABLE statements to the database for all tables that don’t yet exist.

base.metadata.create_all(engine)

Complete process explained above is stored as a script (addproducts.py) in the root folder of our virtual environment.

Example

from sqlalchemy import Column, Integer, String 
from sqlalchemy.ext.declarative import declarative_ base
from sqlalchemy import create_engine
from myclasses import Product, base
engine = create_engine('sqlite:///mydb.sqlite',echo=True)
base.metadata.create_all(engine)

We run this script from the command prompt (from within our virtual environment of course). The command window will show, apart from other logging information, the equivalent CREATE TABLE statement emitted by SQLALchemy. (figure 9.1)

(SQLAlchemyEnv) E:\SQLAlchemyEnv>python class-table-mapping . py
PRAGMA table_info("Products")
( )
CREATE TABLE "Products" (
"ProductID" INTEGER NOT NULL, 
name VARCHAR, 
price INTEGER,
PRIMARY KEY ("ProductID")
)
( )
COMMIT

Python Data Persistence – ORM – Table Object and Mapped Class

Related