Metadata

Metadata#

Metadata contains information that defines the structure of the database: tables, relationships between tables, and table schemas. Check the more in corresponding page of the official documentation.

import sqlalchemy
from sqlalchemy import MetaData

Load from DB#

Typical case when project has to work with database created for other purposes. In such case you need to load schema from bata base to the alchemy defined by the programs. Officially it called reflecting database objects.

There are two general ways to do this:

  • Extract the schema of the single table by defining the autoload_with=<engine> argument.

  • Extract all data from the schema using the MetaData.reflect(<engine>) method.

Check description and examples provided by official docuemntation.

The following cell creates engine and a few tables in, we would try to put them in metadata created by SQLAlchemy.

create_users = """
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);
"""
create_orders = """
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product_name TEXT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
"""

engine = sqlalchemy.create_engine("sqlite+pysqlite:///:memory:")
with engine.connect() as conn:
    conn.execute(sqlalchemy.text(create_users))
    conn.execute(sqlalchemy.text(create_orders))

Now by using the reflect method of the metadata the database created in the cell before being loaded into the ORM. There is also some code that corresponds to each table columns it has.

metadata_obj = MetaData()
metadata_obj.reflect(bind=engine)
{
    name: [col.name for col in table.columns]
    for name, table in metadata_obj.tables.items()
}

{'orders': ['order_id', 'user_id', 'product_name', 'order_date'],
 'users': ['id', 'name', 'email']}

So there is information about every column of every table that was created using raw SQL loaded into Alchimia metadata.

Create table#

Consider the case when you have defined a SQLAlchemy schema and need to duplicate it in the real database. This section corresponds to this option. For example, with sqlalchemy.Table.create you can create the corresponding table in the provided engine.

The following cell shows how to define an SQLAlchemy table and apply its create method.

meta_data = MetaData()

table = sqlalchemy.Table(
    "example_table",
    meta_data,
    sqlalchemy.Column("id", sqlalchemy.Integer),
    sqlalchemy.Column("some_info", sqlalchemy.String)
)

engine = sqlalchemy.create_engine("sqlite+pysqlite:///:memory:")
table.create(engine)

Now let’s check if the corresponding sqlite really contains a table with the corresponding name and schema.

with engine.connect() as conn:
    ans = conn.execute(
        sqlalchemy.text("PRAGMA table_info(example_table)")
    ).all()
ans

[(0, 'id', 'INTEGER', 0, None, 0), (1, 'some_info', 'VARCHAR', 0, None, 0)]

The result is a bit confusing, but there are lines with names of tables that were specified during creation.

Contents