Создание схемы базы данных в SQLAlchemy Core

Создание таблиц

Таблицы в SQLAlchemy представлены в виде экземпляров класса Table. Его конструктор принимает название таблицы, метаданные и одну или несколько колонок. Например:

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)
)

Разберем код построчно:

1. Импортируем несколько классов из sqlalchemy, которые используются для создания таблицы.
2. Импортируем класс datetime из модуля datetime.
3. Создаем объекта MetaData. Он содержит всю информацию о базе данных и таблицах. Экземпляр MetaData используется для создания или удаления таблиц в базе данных.
4. Наконец, создается схема таблицы. Колонки создаются с помощью экземпляра Column. Конструктор этого класса принимает название колонки и тип данных. Также можно передать дополнительные аргументы для обозначения ограничений (constraints) и конструкций SQL. Вот самые популярные ограничения:

Типы колонок

Тип определяет то, какие данные колонка сможет хранить. SQLAlchemy предоставляет абстракцию для большого количества типов. Однако всего есть три категории:

1. Общие типы
2. Стандартные SQL-типы
3. Типы отдельных разработчиков

Общие типы

Тип Generic указывает на те типы, которые поддерживаются большинством баз данных. При использовании такого типа SQLAlchemy подбирает наиболее подходящий при создании таблицы. Например, в прошлом примере была определена колонка published. Ее тип — Boolean. Это общий тип. Для базы данных PostgreSQL тип будет boolean. А для MySQL — SMALLINT, потому что там нет Boolean. В Python же этот тип данных представлен типом bool (True или False).

Следующая таблица описывает основные типы в SQLAlchemy и ассоциации в Python и SQL.

Получить эти типы можно из sqlalchemy.types или sqlalchemy.

Стандартные типы SQL

Типы в этой категории происходят из самого SQL. Их поддерживает небольшое количество баз данных.

from sqlalchemy import MetaData, Table, Column, Integer, ARRAY

metadata = MetaData()

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

Доступ к ним можно также получить из sqlalchemy.types или sqlalchemy. Однако для разделения стандартные типы записаны в верхнем регистре. Например, есть тип ARRAY, который пока поддерживается только PostgreSQL.

Типы производителей

В пакете sqlalchemy можно найти типы, которые используются в конкретных базах данных. Например, в PostgreSQL есть тип INET для хранения сетевых данных. Для его использования нужно импортировать 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),
)

Реляционные отношения (связи)

Таблицы в базе данных редко существуют сами по себе. Чаще всего они связаны с другими через специальные отношения. Существует три типа отношений:

• Один-к-одному
• Один-ко-многим
• Многие-ко-многим

Разберемся, как определять эти отношения в SQLAlchemy.

Отношение один-ко-многим

Две таблицы связаны отношением один-ко-многим, если запись в первой таблице связана с одной или несколькими записями второй. На изображении ниже такая связь существует между таблицей users и posts.

Для создания отношения нужно передать объект ForeignKey, в котором содержится название колонки в функцию-конструктор Column.

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")),
)

В этом коде определяется внешний ключ для колонки user_id таблицы posts. Это значит, что эта колонка может содержать только значения из колонки id таблицы users.

Вместо того чтобы передавать название колонки в качестве строки, можно передать объект Column прямо в конструктор ForeignKey. Например:

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 ссылается на колонку id таблицы users. Важно лишь запомнить, что определение колонки (user.c.id) должно идти до ссылки на нее (posts.c.user_id).

Отношение один-к-одному

Две таблицы имеют связь один-к-одному, если запись в одной таблице связана только с одной записью в другой. На изображении ниже таблица employees связана с employee_details. Первая включает публичные записи о сотрудниках, а вторая — частные.

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('ssn', String(200), nullable=False),
    Column('salary', String(200), nullable=False),
    Column('blood_group', String(200), nullable=False),
    Column('residential_address', String(200), nullable=False),    
)

Для создания такой связи одна и та же колонка должна выступать одновременно основным и внешним ключом в employee_details.

Отношение многие-ко-многим

Две таблицы имеют связь многие-ко-многим, если запись в первой таблице связана с одной или несколькими таблицами во второй. Вместе с тем, запись во второй таблице связана с одной или несколькими в первой. Для таких отношений создается таблица ассоциаций. На изображении ниже отношение многие-ко-многим существует между таблицами posts и tags.

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'))
)

Процесс установки отношений почти не отличается от такового в SQL. Все потому что используется SQLAlchemy Core, который позволяет делать почти то же, что доступно в SQL.

Ограничения (constraint) на уровне таблицы

В прошлых разделах мы рассмотрели, как добавлять ограничения и индексы для колонки, передавая дополнительные аргументы в функцию-конструктор Column. По аналогии с SQL можно также определять ограничения с индексами и на уровне таблицы. В следующей таблице перечислены основные constraint и классы для их создания:

Получить доступ к этим классам можно через sqlalchemy.schema или sqlalchemy. Вот некоторые примеры использования:

Добавления Primary Key с помощью 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')
)

Здесь создается первичный ключ для колонки acc_no. Такой код эквивалентен следующему:

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

Преимущественно PrimaryKeyConstraint используется для создания составного основного ключа (такого ключа, который использует несколько колонок). Например:

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')
)

Такой код эквивалентен следующему:

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),   
)

Создание Foreign Key с помощью 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'])
)

Создаем внешний ключ в колонке parent_it, которая ссылается на колонку id таблицы parent. Такой код эквивалентен следующему:

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),   
)

Но реальную пользу ForeignKeyConstraint приносит при определении составного внешнего ключа (который также задействует несколько колонок). Например:

parent = Table('parent', metadata,
    Column('id', Integer, nullable=False),
    Column('ssn', 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'])
)

Обратите внимание на то, что просто передать объект ForeignKey в отдельные колонки не получится — это приведет к созданию нескольких внешних ключей.

Создание Unique Constraint с помощью UniqueConstraint

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

Определим ограничение уникальности для колонки ssn. Необязательное ключевое слово name позволяет задать имя для этого constraint. Такой код эквивалентен следующему:

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

UniqueConstraint часто используется для создания ограничения уникальности на нескольких колонках. Например:

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')
)

В этом примере ограничения уникальности устанавливаются на acc_no и acc_type, в результате чего комбинация значений этих двух колонок всегда должна быть уникальной.

Создание ограничения проверки с помощью CheckConstraint

Ограничение CHECK позволяет создать условие, которое будет срабатывать при вставке или обновлении данных. Если проверка пройдет успешно, данные успешно сохранятся в базе данных. В противном случае возникнет ошибка.

Добавить это ограничение можно с помощью 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')
)

Создание индексов с помощью Index

Аргумент-ключевое слово index также позволяет добавлять индекс для отдельных колонок. Для работы с ним есть класс 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')  
)

В этом примере индекс создается для колонки first_name. Такой код эквивалентен следующему:

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),    
)

Если запросы включают поиск по определенному набору полей, то увеличить производительность можно с помощью составного индекса (то есть, индекса для нескольких колонок). В этом основное назначение 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')  
)

Связь с таблицами и колонками с помощью MetaData

Объект MetaData содержит всю информацию о базе данных и таблицах внутри нее. С его помощью можно получать доступ к объектам таблицы, используя такие два атрибута:

Вот два описанных атрибута в действии:

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)

Ожидаемый вывод:

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

После получения доступа к экземпляру Table можно получать доступ к любым деталям о колонках:

print(posts.columns)         # вернуть список колонок
print(posts.c)               # как и post.columns
print(posts.foreign_keys)    # возвращает множество, содержащий внешние ключи таблицы
print(posts.primary_key)     # возвращает первичный ключ таблицы
print(posts.metadata)        # получим объект MetaData из таблицы
print(posts.columns.post_title.name)     # возвращает название колонки
print(posts.columns.post_title.type)     # возвращает тип колонки

Ожидаемый вывод:

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=<posts>, primary_key=True, nullable=False))
MetaData()
post_title
VARCHAR(200)

Создание таблиц

Для создания таблиц, хранящихся в экземпляре MetaData, вызовите метод MetaData.create_all() с объектом Engine.

metadata.create_all(engine)

Этот метод создает таблицы только в том случае, если они не существуют в базе данных. Это значит, что его можно вызвать безопасно несколько раз. Также стоит отметить, что вызов метода после определения схемы не изменит ее. Для этого нужно использовать инструмент миграции под названием Alembic.

Удалить все таблицы можно с помощью MetaData.drop_all().

В дальнейшем будем работать с базой данных для приложения в сфере электронной коммерции. Она включает 4 таблицы:

• customers — хранит всю информацию о потребителях. Имеет следующие колонки:
  • id — первичный ключ
  • first_name — имя покупателя
  • last_name — фамилия покупателя
  • username — уникальное имя покупателя
  • email — уникальный адрес электронной почты
  • address — адрес
  • town — город
  • created_on — дата и время создания аккаунта
  • updated_on — дата и время обновления аккаунта
• items — хранит информацию о товарах. Колонки:
  • id — первичный ключ
  • name — название
  • cost_price — себестоимость товара
  • selling_price — цена продажи
  • quantity — количество товаров в наличии
• orders — информация о покупках потребителей. Колонки:
  • id — первичный ключ
  • customer_id — внешний ключ, указывающий на колонку id таблицы customers
  • date_placed — дата и время размещения заказа
  • date_shipped — дата и время отгрузки заказа
• order_lines — подробности каждого товара в заказе. Колонки:
  • id — первичный ключ
  • order_id — внешний ключ, указывающий на id таблицы orders
  • item_id — внешний ключ, указывающий на id таблицы items
  • quantity — количество товаров в заказе

А вот и весь код для создания этих таблиц:

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:1111@localhost/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)

Базу данных sqlalchemy_tuts мы создали в предыдущем уроке: https://pythonru.com/biblioteki/ustanovka-i-podklyuchenie-sqlalchemy-k-baze-dannyh

В следующем материале рассмотрим, как выполнять CRUD-операции в базе данных с помощью SQL.