Preface
The main objective of the Object Relational Mapper API of SQLAlchemy is to facilitate associating user-defined Python classes with database tables, and objects of those classes with rows in their corresponding tables. Changes in states of objects and rows are synchronously matched with each other. SQLAlchemy enables expressing database queries in terms of user defined classes and their defined relationships.
The ORM is constructed on top of the SQL Expression Language. It is a high level and abstracted pattern of usage. In fact, ORM is an applied usage of the Expression Language.
Although a successful application may be constructed using the Object Relational Mapper exclusively, sometimes an application constructed with the ORM may use the Expression Language directly where specific database interactions are required.
Declaring Mapping
First of all, create_engine() function is called to set up an Engine object which is subsequently used to perform SQL operations. The function has two arguments, one is the name of database and other is an echo parameter when set to True will generate the activity log. If it doesn’t exist, the database will be created:
- from sqlalchemy import create_engine
- db_string = "postgresql://postgres:password@localhost/testdb"
- engine = create_engine(db_string, echo = True)
In case of ORM, the configurational process starts by describing the database tables and then by defining classes which will be mapped to those tables. In SQLAlchemy, these two tasks are performed together. This is done by using Declarative system; the classes created include directives to describe the actual database table they are mapped to.
A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the declarative base class. There will be usually just one instance of this base in a commonly imported module. The declarative_base() function is used to create base class. This function is defined in sqlalchemy.ext.declarative module:
- from sqlalchemy.ext.declarative import declarative_base
- Base = declarative_base()
- class Customers(Base):
- __tablename__ = 'customers'
- id = Column(Integer, primary_key = True)
- name = Column(String)
- address = Column(String)
- email = Column(String)
This mapped class like a normal Python class has attributes and methods as per the requirement.
The information about class in Declarative system, is called as table metadata. SQLAlchemy uses Table object to represent this information for a specific table created by Declarative. The Table object is created according to the specifications, and is associated with the class by constructing a Mapper object. This mapper object is not directly used but is used internally as interface between mapped class and table.
Each Table object is a member of larger collection known as MetaData and this object is available using the .metadata attribute of declarative base class. The MetaData.create_all() method is, passing in our Engine as a source of database connectivity. For all tables that haven’t been created yet, it issues CREATE TABLE statements to the database:
- Base.metadata.create_all(engine)
- demo00.py
- from sqlalchemy import Column, Integer, String
- from sqlalchemy import create_engine
- from sqlalchemy.ext.declarative import declarative_base
- db_string = "postgresql://postgres:password@localhost/testdb"
- engine = create_engine(db_string, echo = True)
- Base = declarative_base()
- class Customers(Base):
- __tablename__ = 'customers'
- id = Column(Integer, primary_key=True)
- name = Column(String)
- address = Column(String)
- email = Column(String)
- Base.metadata.create_all(engine)
- CREATE TABLE customers (
- id INTEGER NOT NULL,
- name VARCHAR,
- address VARCHAR,
- email VARCHAR,
- PRIMARY KEY (id)
- )
- testdb=# \d+ customers
- Table "public.customers"
- Column | Type | Modifiers | Storage | Stats target | Description
- ---------+-------------------+--------------------------------------------------------+----------+--------------+-------------
- id | integer | not null default nextval('customers_id_seq'::regclass) | plain | |
- name | character varying | | extended | |
- address | character varying | | extended | |
- email | character varying | | extended | |
- Indexes:
- "customers_pkey" PRIMARY KEY, btree (id)
- Has OIDs: no
In order to interact with the database, we need to obtain its handle. A session object is the handle to database. Session class is defined using sessionmaker() – a configurable session factory method which is bound to the engine object created earlier:
- from sqlalchemy.orm.session import sessionmaker
- session = sessionmaker(bind = engine)
Supplement
* SQLAlchemy doc - Engine Configuration
* SQLAlchemy doc - Declarative API
* SQLAlchemy doc - Mapping Table Columns
* SQLAlchemy doc - Using the Session
沒有留言:
張貼留言