DDL on SQLAlchemy Core API
Tags: beginners, python, sql, tutorial
Published: 2022-10-30 05:39:41

Image credit [Tobias Hämmer](https://pixabay.com/users/dertobisturmjagd-7096811/)

Previously we saw how to work with [database definition language using raw](https://dev.to/spaceofmiah/database-interaction-with-sqlalchemy-raw-ddl-tour-ie8) 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](https://github.com/spaceofmiah/practical-route-to-alchemy) 

## Codebase Structure Update

We'll update [the codebase structure](https://dev.to/spaceofmiah/sqlalchemy-with-docker-the-structure-8hl) 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`
```python

# ---- 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](https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.Column.__eq__) 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`
```python
# ...          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`
```python
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`
```python
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](https://dev.to/spaceofmiah/database-interaction-with-sqlalchemy-raw-ddl-tour-ie8) 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.

:coffee:
- A lot is accomplished with a cup of [coffee](https://www.buymeacoffee.com/spaceofmiah)

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