Creating a database schema in SQLAlchemy Core

Table of Contents

Creating Tables

Tables in SQLAlchemy are represented as instances of the Table class. Its constructor takes a table name, metadata, and one or more columns. For example:

from sqlalchemy import MetaData, Table, String, Integer, Column, Text, DateTime, Boolean
from datetime import datetime
metadata = MetaData()
blog = Table('blog', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('published', Boolean(), default=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)

Let’s analyze the code line by line:

Import several classes from sqlalchemy that are used to create the table.
Import datetime class from the datetime module.
Create MetaData object. It contains all information about the database and tables. MetaData instance is used to create or delete tables in the database.
Finally, we create a table schema. Columns are created using the Column instance. The constructor of this class takes the column name and data type. You can also pass additional arguments to indicate constraints and SQL constructs. Here are the most popular constraints:

RestrictionDescriptionprimary_keuBulevo. If the value is True, marks the column as the primary key of the table. To create a compound key, you just need to set each column to True.pullablueBulevo. If False, adds a NOT NULL constraint. The default value is True.defaultDefinesthe default value if it was not passed when the data was inserted. Can be either a scalar value or a callable Python value.optionDefault valuefor the column, which is set if nothing was passed in when the record was updated. May take the same value as default.uppercaseBleft. If True, makes sure the value is unique.ndexBulevo. If True, creates an indexable column. Defaults to False.auto_incrementAddsthe auto_increment parameter to the column. The default value is auto. This means that the value of the primary key will increment each time a new entry is added. If you want to increase the value for each item of the composite key, you must set this parameter to True for all columns of the key. To disable this behavior set it to False

Column Types

The type determines what data the column will be able to store. SQLAlchemy provides an abstraction for a large number of types. However, there are a total of three categories:

Common types
Standard SQL types
Individual developer types

Generic types

The Generic type specifies the types that are supported by most databases. When you use this type, SQLAlchemy picks the most appropriate one when creating a table. For example, in the last example, the published column was defined. Its type is Boolean. This is a common type. For a PostgreSQL database, the type would be boolean. For MySQL, it is SMALLINT because it is not a Boolean. In Python it is represented as a bool(True or False). The following table describes the basic types in SQLAlchemy and associations in Python and SQL. SQLAlchemyPythonSQLBigIntegerintBIGINTBooleanboolBOOLEAN or SMALLINTDatedatetime.dateDATEDateTimedatetime.datetimeDATETIMEIntegerINTEGERFloatfloatFLOAT or REALNumericdecimal.DecimalNUMERICTextstrTEXT You can get these types from sqlalchemy.types or sqlalchemy.

Standard SQL types

The types in this category come from SQL itself. Not many databases support them.

from sqlalchemy import MetaData, Table, Column, Integer, ARRAY
metadata = MetaData()
employee = Table('employees', metadata,
Column('id', Integer(), primary_key=True),
Column('workday', ARRAY(Integer),
)

They can also be accessed from sqlalchemy.types or sqlalchemy. However, standard types are written in uppercase to distinguish them. For example, there is the ARRAY type, which is so far only supported by PostgreSQL.

Producer Types

In the sqlalchemy package, you can find types that are used in specific databases. For example, PostgreSQL has the INET type for storing network data. To use it, you need to import sqlalchemy.dialects.

from sqlalchemy import MetaData, Table, Column, Integer
from sqlalchemy.dialects import postgresql
metadata = MetaData()
comments = Table('comments', metadata,
Column('id', Integer(), primary_key=True),
Column('ipaddress', postgresql.INET,)
)

Relational Relationships

Tables in a database rarely exist by themselves. Most often they are linked to others through special relationships. There are three types of relationships:

One-to-many
One-to-many
Many-to-many

Let’s understand how to define these relationships in SQLAlchemy.

One-to-many relationship

Two tables are linked by a one-to-many relationship if a record in the first table is related to one or more records in the second. In the image below, such a relationship exists between the users table and the posts table. Отношение один-ко-многим To create the relationship, you need to pass a ForeignKey object that contains the column name to the Column constructor function.

from sqlalchemy import MetaData, Table, Column, Integer, String, Text, ForeignKey
metadata = MetaData()
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False,)
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', ForeignKey('users.id'),
)

This code defines a foreign key for the user_id column of the posts table. This means that this column can only contain values from the id column of the users table. Instead of passing the column name as a string, you can pass the Column object directly to the ForeignKey constructor. For example:

from sqlalchemy import MetaData, Table, Column, Integer, String, Text, ForeignKey
metadata = MetaData()
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False,)
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', Integer(), ForeignKey(user.c.id))
)

user.c.id refers to the column id of the users table. The important thing to remember is that the column definition(user.c.id) must go before the column reference(posts.c.user_id).

One-to-one relationship

Two tables have a one-to-one relationship if a record in one table is related to only one record in the other. In the image below, the employees table is linked to employee_details. The former includes public employee records and the latter includes private employee records. Отношение один-к-одному

from sqlalchemy import MetaData, Table, Column, Integer, String, DateTime, ForeignKey
metadata = MetaData()
employees = Table('employees', metadata,
Column('employee_id', Integer(), primary_key=True),
Column('first_name', String(200), nullable=False),
Column('last_name', String(200), nullable=False),
Column('dob', DateTime(), nullable=False),
Column('designation', String(200), nullable=False,)
)
employee_details = Table('employee_details', metadata,
Column('employee_id', ForeignKey('employees.employee_id'), primary_key=True),
Column('sn', String(200), nullable=False),
Column('salary', String(200), nullable=False),
Column('blood_group', String(200), nullable=False),
Column('residential_address', String(200), nullable=False,)
)

To create such a relationship, the same column must act as both primary and foreign key in employee_details.

Many-to-many relationship

Two tables have a many-to-many relationship if the record in the first table is related to one or more tables in the second table. At the same time, the record in the second table is linked to one or more in the first table. An association table is created for such a relationship. In the image below, a many-to-many relationship exists between the posts and tags tables. Отношение многие-ко-многим

from sqlalchemy import MetaData, Table, Column, Integer, String, Text, ForeignKey
metadata = MetaData()
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False,)
)
tags = Table('tags', metadata,
Column('id', Integer(), primary_key=True),
Column('tag', String(200), nullable=False),
Column('tag_slug', String(200), nullable=False,)
)
post_tags = Table('post_tags', metadata,
Column('post_id', ForeignKey('posts.id'),
Column('tag_id', ForeignKey('tags.id')
)

The process of setting up the relationship is almost the same as in SQL. This is because it uses SQLAlchemy Core, which allows you to do almost the same things that are available in SQL.

Constraint at the table level

In the previous sections, we saw how to add constraints and indexes to a column by passing additional arguments to the Column constructor function. Similar to SQL, you can also define constraints with indexes at the table level. The following table lists the basic constraints and classes for creating them: Constraints/indicesClass namePrimaryKeyConstraintExternal keyForeignKeyConstraintUnique keyUniqueConstraintCheckConstraintIndex You can access these classes through sqlalchemy.schema or sqlalchemy. Here are some examples of how to use them:

Adding Primary Key with PrimaryKeyConstraint

parent = Table('parent', metadata,
Column('acc_no', Integer(),
Column('acc_type', Integer(), nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('acc_no', name='acc_no_pk')
)

This creates a primary key for the acc_no column. This code is equivalent to the following:

parent = Table('parent', metadata,
Column('acc_no', Integer(), primary=True),
Column('acc_type', Integer(), nullable=False),
Column('name', String(16), nullable=False,)
)

Primarily PrimaryKeyConstraint is used to create a composite primary key (one that uses multiple columns). For example:

parent = Table('parent', metadata,
Column('acc_no', Integer, nullable=False),
Column('acc_type', Integer, nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('acc_no', 'acc_type', name='uniq_1')
)

This code is equivalent to the following:

parent = Table('parent', metadata,
Column('acc_no', Integer, nullable=False, primary_key=True),
Column('acc_type', Integer, nullable=False, primary_key=True),
Column('name', String(16), nullable=False,)
)

Creating a Foreign Key with the ForeignKeyConstraint

parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16), nullable=False)
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, nullable=False),
Column('name', String(40), nullable=False),
ForeignKeyConstraint(['parent_id'],['parent.id'])
)

Create a foreign key in the parent_it column, which refers to the id column of the parent table. This code is equivalent to the following:

parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16), nullable=False)
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', ForeignKey('parent.id'), nullable=False),
Column('name', String(40), nullable=False,)
)

But the real use of ForeignKeyConstraint comes when defining a compound foreign key (which also involves multiple columns). For example:

parent = Table('parent', metadata,
Column('id', Integer, nullable=False),
Column('sn', Integer, nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('id', 'ssn', name='uniq_1')
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), nullable=False),
Column('parent_id', Integer, nullable=False),
Column('parent_ssn', Integer, nullable=False),
ForeignKeyConstraint(['parent_id','parent_ssn'],['parent.id', 'parent.ssn'])
)

Note that simply passing the ForeignKey object to individual columns won’t work – it will create multiple foreign keys.

Creating Unique Constraint with UniqueConstraint

parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('sn', Integer, nullable=False),
Column('name', String(16), nullable=False),
UniqueConstraint('ssn', name='unique_ssn')
)

Let’s define the uniqueness constraint for the ssn column. The optional keyword name allows you to specify a name for this constraint. This code is equivalent to the following:

parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('sn', Integer, unique=True, nullable=False),
Column('name', String(16), nullable=False,)
)

UniqueConstraint is often used to create a uniqueness constraint on multiple columns. For example:

parent = Table('parent', metadata,
Column('acc_no', Integer, primary_key=True),
Column('acc_type', Integer, nullable=False),
Column('name', String(16), nullable=False),
UniqueConstraint('acc_no', 'acc_type', name='uniq_1')
)

In this example, the uniqueness constraints are set on acc_no and acc_type, which results in the combination of the values of these two columns to always be unique.

Creating a check constraint with CheckConstraint

The CHECK constraint allows you to create a condition that will be triggered when data is inserted or updated. If the check succeeds, the data will be successfully saved in the database. Otherwise, an error will occur. You can add this constraint with CheckConstraint.

employee = Table('employee', metadata,
Column('id', 'Integer(), primary_key=True),
Column('name', String(100), nullable=False),
Column('salary', Integer(), nullable=False),
CheckConstraint('salary < 100000', name='salary_check')
)

Creating indexes with Index

The argument-keyword index also allows you to add an index for individual columns. There is an Index class to work with it:

a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('middle_name', String(100),
Column('last_name', String(100), nullable=False),
Index('idx_col1', 'first_name')
)

In this example, the index is created for the first_name column. This code is equivalent to the following:

a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False, index=True),
Column('middle_name', String(100),
Column('last_name', String(100), nullable=False,)
)

If queries involve searching through a specific set of fields, you can increase performance by using a composite index (that is, an index for multiple columns). This is the main purpose of Index:

a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('middle_name', String(100),
Column('last_name', String(100), nullable=False),
Index('idx_col1', 'first_name', 'last_name')
)

Linking to tables and columns with MetaData

MetaData object contains all information about the database and the tables inside it. It can be used to access table objects using these two attributes: AttributeDescribeblesReturns a dictionary object of the immutabledict type, where the key is the table name and the value is the object with its datasord_taBlesReturns a list of Table objects sorted by external key dependency order. In other words, tables with dependencies are placed before the dependencies themselves. For example, if the table posts has a foreign key that points to the id column of the table users, then the table users will come before posts Here are the two described attributes in action:

from sqlalchemy import create_engine, MetaData, Table, Integer, String, Column, Text, DateTime, Boolean, ForeignKey
metadata = MetaData()
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False,)
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', Integer(), ForeignKey('users.id'),
)
for t in metadata.tables:
print(metadata.tables[t])
print('-------------')
for t in metadata.sorted_tables:
print(t.name)

Expected output:

users
posts
-------------
users
posts

Once you have access to the Table instance, you can access any details about the columns:

print(posts.columns) # return the list of columns
print(posts.c) # as well as post.columns
print(posts.foreign_keys) # returns a set containing the table's foreign keys
print(posts.primary_key) # return the primary key of the table
print(posts.metadata) # get MetaData object from table
print(posts.columns.post_title.name) # returns column name
print(posts.columns.post_title.type) # returns column type

Expected Output:

ImmutableColumnCollection(posts.id, posts.post_title, posts.post_slug, posts.content, ImmutableColumnCollection(posts.id, posts.post_title, posts.post_slug, posts.content, posts.user_id)
ImmutableColumnCollection(posts.id, posts.post_title, posts.post_slug, posts.content, posts.user_id)
{ForeignKey('users.id')}
PrimaryKeyConstraint(Column('id', Integer(), table=, primary_key=True, nullable=False))
MetaData()
post_title
VARCHAR(200)

Creating tables

To create tables stored in a MetaData instance, call the MetaData.create_all() method with the Engine object.

metadata.create_all(engine)

This method creates tables only if they do not exist in the database. This means that it can be called safely multiple times. It’s also worth noting that calling the method after defining the schema will not change it. You have to use a migration tool called Alembic to do this. You can delete all tables using MetaData.drop_all(). Next, we will work with a database for an e-commerce application. It includes 4 tables:

customers – stores all information about customers. It has the following columns:
- id – primary key
- first_name – customer name
- last_name – customer last name
- username – unique name of the customer
- email – unique email address
- address – address
- town – city
- created_on – date and time when the account was created
- updated_on – date and time the account was updated
items – stores information about items. Columns:
- id – primary key
- name – name
- cost_price – product cost price
- selling_price – selling price
- quantity – quantity of goods in stock
orders – information about consumer purchases. Columns:
- id – primary key
- customer_id – external key, pointing to the id column of the table customers
- date_placed – date and time of placing the order
- date_shipped – date and time of order shipment
order_lines – details of each item in the order. Columns:
- id – primary key
- order_id – external key, specifying the id of the table orders
- item_id – the external key indicating the id of the items table
- quantity – quantity of products in the order

Создание таблиц ER database model And here is all the code to create these tables:

from sqlalchemy import create_engine, MetaData, Table, Integer, String, 
Column, DateTime, ForeignKey, Numeric, CheckConstraint
from datetime import datetime
metadata = MetaData()
engine = create_engine("postgresql+psycopg2://postgres:[email protected]/sqlalchemy_tuts")
customers = Table('customers', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('last_name', String(100), nullable=False),
Column('username', String(50), nullable=False),
Column('email', String(200), nullable=False),
Column('address', String(200), nullable=False),
Column('town', String(50), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
items = Table('items', metadata,
Column('id', Integer(), primary_key=True),
Column('name', String(200), nullable=False),
Column('cost_price', Numeric(10, 2), nullable=False),
Column('selling_price', Numeric(10, 2), nullable=False),
Column('quantity', Integer(), nullable=False),
CheckConstraint('quantity > 0', name='quantity_check')
)
orders = Table('orders', metadata,
Column('id', Integer(), primary_key=True),
Column('customer_id', ForeignKey('customers.id'),
Column('date_placed', DateTime(), default=datetime.now),
Column('date_shipped', DateTime())
)
order_lines = Table('order_lines', metadata,
Column('id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.id'),
Column('item_id', ForeignKey('items.id')),
Column('quantity', Integer())
)
metadata.create_all(engine)

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.