DDL on SQLAlchemy Core API

Image credit Tobias Hämmer

Previously we saw how to work with database definition language using raw SQL queries on text function provided by SQLAlchemy. In this guide however, we'll see how to simplify the process using SQLAlchemy Core API.

This is a series where I document discoveries in my Journey to SQLAlchemy. If you haven't seen my previous posts, check them out - links are somewhere at the top and bottom of this page.

Codebase is on github

Codebase Structure Update

We'll update the codebase structure by creating a new folder within db folder called models. Create __init__.py and items.py file within models folder. We'll be working with items.py and main.py file in this guide.

Rewrite Tables with Core API

We've two tables to rewrite and Core API provides classes which would help in their construct. Core API provides MetaData, Table, Column classes for this cause.

db/models/items.py

# ---- Import External Resources
# -------------------------------

import datetime
from sqlalchemy import (
    Table,      Column,     String, 
    Integer,    MetaData,   DateTime,
    ForeignKey, Boolean,    Sequence,
)
from db.core.initializer import engine

# ---- End of Imports
# -------------------


# ---- Construct Tables
# ---------------------

table_meta = MetaData()


Item = Table(
    'item',
    table_meta,
    Column(
        'id', 
        Integer, 
        Sequence('item_id'), 
        primary_key=True
    ),
    Column('category', String(200)),
    Column('name', String(250), nullable=False),
    Column(
        'date_tracked', 
        DateTime, 
        default=datetime.datetime.now
    ),
)


CravedItem = Table(
    'craveditem',
    table_meta,
    Column(
        'id', 
        Integer, 
        Sequence('craveditem_id'), 
        primary_key=True
    ),
    Column('item_id', ForeignKey('item.id')),
    Column('is_satisfied', Boolean(), default=False),
    Column(
        'date_tracked', 
        DateTime, 
        default=datetime.datetime.now
    ),
)

Construct Explanation

An instance of MetaData class was instantiated and stored on variable table_meta. MetaData acts as a container for related database tables and their associated schema construct. An application can have multiple MetaData instances ( in the case of a multi-tenant application where tables belongs to different schema ) but most application will do fine with just one.

When using Core API, table construct must specify a MetaData, explicitly declare Columns and table name. Item and CravedItem table both share similar MetaData instance.

SQLAlchemy provide Table class used to define the structure of a table. In the construct of Item and CravedItem from above snippet, the first argument passed to Table class is the name of the tables respectively.

Table column is defined using the Column class. Parameters passed to Column class are much and we've only used a few. The first parameter passed to all Column call is the column name, followed by the data types e.g Integer, String, Boolean, DateTime. String data type takes a numeric value specifying total allowable characters for the given column. Some named parameters used are:

• default: set default value for column

• nullable: defines either or not a column would accommodate no (None/Null) value.

• primary_key: Set the column which would be used as the unique reference for entries in the table.

Create & Drop Operations With SQLAlchemy Core

Creating Tables

We've successfully define the construct of Item & CravedItem tables in our python code. To send this table definition to the database for creation, we can either have them created individually or run mass creation.

db/models/items.py

# ...          other codebase above

def create_all_tables():
    """Creates all tables that share same metadata"""
    table_meta.create_all(engine)

def create_item_table_with_core():
    """Creates item table"""
    Item.create(engine, checkfirst=True)

def create_craveditem_table_with_core():
    """Creates craved item table"""
    CravedItem.create(engine, checkfirst=True)

create_all_tables function uses table_meta. This is same MetaData instance shared by Item & CravedItem table. It uses .create_all() method to create all attached tables. .create_all() method takes the database engine which the creation will be executed against. By default .create_all() will check if the tables to be created exists and ignore if it exists. So it's safe to run the create_all_tables multiple times.

create_item_table_with_core and create_craveditem_table_with_core function uses the individual creation format. .create() method is called on Item & CravedItem which are Table class instances. .create() method also takes a database engine to identify the database to execute the create statement against.

Additionally we included an optional checkfirst argument and set it to True just to ensure that we don't attempt to create an already existing table.

main.py

from db.scripts.queries import show_all_tables
from db.models.items import (
    create_item_table_with_core,
    create_craveditem_table_with_core,
    create_all_tables,
)

if __name__ == "__main__":
    create_item_table_with_core()
    create_craveditem_table_with_core()
    show_all_tables()

When the above is executed using python main.py, we'll should've our tables created. create_item_table_with_core() and create_craveditem_table_with_core() can both be replaced with create_all_tables() to achieve same result.

WATCHOUT : When using individual table creation, ensure to create all independent tables ( tables without ForeignKey ) first before attempting to create dependent ones. In our case we'll create Item table first before creating CravedItem.

Dropping Tables

When dropping tables, we can also have it done individually or run mass table drop. Same concept with creation applies here too.

db/models/items.py

def drop_all_tables():
    """Drop all tables"""
    table_meta.drop_all(engine)

def drop_item():
    """Drops Item table"""
    Item.drop(engine)

def drop_craveditem():
    """Drops CravedItem table"""
    CravedItem.drop(engine)

Exercise

Import above functions and utilize them to drop existing Item & CravedItem tables in the database.

Conclusion

Unlike the raw approach to DDL in SQLAlchemy from previous guide, DDL Operation with SQLAlchemy Core API doesn't warrant that one be familiar with how to write SQL queries. The only requirement here is, understanding your database tables definitions. With the helper classes from Core API, the construct is pythonic and less error prone.

Thank you for being AWESOME

✍️

• Encountered any bugs, typos or concept misinterpretation from this guide ? Let me know in the comment section below.

❤️

• Hit the LIKE button and FOLLOW me so you're notified on future discoveries.

☕

• A lot is accomplished with a cup of coffee

Thank you for your time and for staying with me on this one.