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 columnnullable
: 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 creatingCravedItem
.
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.
Top comments (0)