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
.defaultDefines
the default value if it was not passed when the data was inserted. Can be either a scalar value or a callable Python value.optionDefault value
for 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_incrementAdds
the 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)