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
We'll update the codebase structure by creating a new folder within
db folder called
items.py file within
models folder. We'll be working with
main.py file in this guide.
We've two tables to rewrite and Core API provides classes which would help in their construct. Core API provides
Column classes for this cause.
# ---- 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 ), )
An instance of
MetaData class was instantiated and stored on variable
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.
CravedItem table both share similar
Table class used to define the structure of a table. In the construct of
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.
We've successfully define the construct of
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.
# ... 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
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_craveditem_table_with_core function uses the individual creation format.
.create() method is called on
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.
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_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
Itemtable first before creating
When dropping tables, we can also have it done individually or run mass table drop. Same concept with creation applies here too.
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)
Import above functions and utilize them to drop existing
CravedItem tables in the database.
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.