Working with Database Metadata

SQLAlchemy Core and ORM were created to allow Python objects to be used like tables and columns in a database. These can be used as database metadata.

Metadata describes data that describes other data. Here, metadata refers to configured tables, columns, constraints, and other object information.

Creating a table object and add it to your metadata

In relational databases, tables are created via queries, but in SQLAlchemy, tables can be created through Python objects. To start with SQLAlchemy Expression Language, you need to create a Table object for the database table you want to use.

>>> from sqlalchemy import MetaData
>>> # An object that will hold the meta information for the tables.
>>> metadata = MetaData()
>>>
>>> from sqlalchemy import Table, Column, Integer, String
>>> user_table = Table(
...     # The name of the table that will be stored in the database.
...     'user_account',
...     metadata,
...
...     # The columns that will go into this table.
...     Column('id', Integer, primary_key=True),
...     Column('name', String(30)),
...     Column('fullname', String),
... )

• You can create database tables using the Table object.

• Columns of the table are implemented using Column.

  • By default, it defines like Column(column name, data type).

• After creating a Table instance, you can know the created column information as follows:

>>> user_table.c.name
Column('name', String(length=30), table=<user_account>)

>>> user_table.c.keys()
['id', 'name', 'fullname']

Declaring Simple Constraints

We saw the Column('id', Integer, primary_key=True) statement in the code that creates the user table above. This declares the id column as the primary key.

The primary key is implicitly declared as a structure in the PrimaryKeyConstraint object. This can be confirmed as follows.

>>> user_table.primary_key
PrimaryKeyConstraint(Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False))

Along with the primary key, foreign keys can also be declared as follows.

>>> from sqlalchemy import ForeignKey
>>> address_table = Table(
...     "address",
...     metadata,
...     Column('id', Integer, primary_key=True),
...     # Declaring Foreign Key as `ForeignKey` object.
...     Column('user_id', ForeignKey('user_account.id'), nullable=False),
...     Column('email_address', String, nullable=False)
... )

• You can declare a foreign key column in the form of ForeignKey('table_name.foreign_key').
  • In this case, you can omit the data type of the Column object. The data type is automatically inferred by locating the column corresponding to the foreign key.
• You can also declare a NOT NULL constraint on a column by passing the nullable=False parameter and value.

Applying to your database

We have declared database tables using SQLAlchemy so far. Now, let's make these declared tables actually get created in the database.

Execute metadata.create_all() as follows.

>>> metadata.create_all(engine)

# The above code creates all tables recorded in the metadata instance.
# As a result, it executes the following queries.

BEGIN (implicit)
PRAGMA main.table_...info("user_account")
...
PRAGMA main.table_...info("address")
...
CREATE TABLE user_account (
    id INTEGER NOT NULL,
    name VARCHAR(30),
    fullname VARCHAR,
    PRIMARY KEY (id)
)
...
CREATE TABLE address (
    id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    email_address VARCHAR NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES user_account (id)
)
...
COMMIT

Defining table metadata the ORM way

We will create the same database structure and use the same constraints as above, but this time we will proceed using the ORM approach.

Setting the registry object.

First of all, create a registry object as follows.

>>> from sqlalchemy.orm import registry
>>> mapper_registry = registry()

The registry object contains a MetaData object.

>>> mapper_registry.metadata
MetaData()

Now we can execute the following code.

>>> Base = mapper_registry.generate_base()

The above process can be simplified using declarative_base as follows.

>>> from sqlalchemy.orm import declarative_base
>>> Base = declarative_base()

Declaring the ORM object

By defining a subclass that inherits from the Base object, you can declare tables in the database using the ORM approach.

>>> from sqlalchemy.orm import relationship
>>> class User(Base):
...     # A name of the table to be used in the database.
...     __tablename__ = 'user_account'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String(30))
...     fullname = Column(String)
...
...     addresses = relationship("Address", back_populates="user")
...
...     def __repr__(self):
...        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

>>> class Address(Base):
...     __tablename__ = 'address'
...
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('user_account.id'))
...
...     user = relationship("User", back_populates="addresses")
...
...     def __repr__(self):
...         return f"Address(id={self.id!r}, email_address={self.email_address!r})"

The User and Address objects include a Table object.

You can check this through the __table__ attribute as follows.

>>> User.__table__
Table('user_account', MetaData(),
    Column('id', Integer(), table=<user_account>, primary_key=True, nullable=False),
    Column('name', String(length=30), table=<user_account>),
    Column('fullname', String(), table=<user_account>), schema=None)

Creating an ORM object

After defining the table, you can create an ORM object as follows.

>>> sandy = User(name="sandy", fullname="Sandy Cheeks")
>>> sandy
User(id=None, name='sandy', fullname='Sandy Cheeks')

Applying to your database

Now, you can apply the tables declared with ORM to the actual database as follows.

>>> mapper_registry.metadata.create_all(engine)
>>> Base.metadata.create_all(engine)

Importing tables from an existing database into an ORM object

Aside from the above methods, there is a way to retrieve tables from the database without declaring them directly.

>>> some_table = Table("some_table", metadata, autoload_with=engine)

BEGIN (implicit)
PRAGMA main.table_...info("some_table")
[raw sql] ()
SELECT sql FROM  (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE name = ? AND type = 'table'
[raw sql] ('some_table',)
PRAGMA main.foreign_key_list("some_table")
...
PRAGMA main.index_list("some_table")
...
ROLLBACK

Now it can be used as follows:

>>> some_table
Table('some_table', MetaData(),
    Column('x', INTEGER(), table=<some_table>),
    Column('y', INTEGER(), table=<some_table>),
    schema=None)