Описание баз данных с помощью метаданных¶

Доступ к таблицам и столбцам¶

Объект MetaData содержит все конструкции схемы, которые мы с ним связали. Он поддерживает несколько методов доступа к этим объектам таблиц, например, аксессор sorted_tables, который возвращает список каждого объекта Table в порядке зависимости от внешнего ключа (то есть, перед каждой таблицей указываются все таблицы, на которые она ссылается):

>>> for t in metadata_obj.sorted_tables:
...     print(t.name)
user
user_preference
invoice
invoice_item

В большинстве случаев отдельные объекты Table были явно объявлены, и к этим объектам обычно обращаются непосредственно как к переменным на уровне модуля в приложении. Как только объект Table определен, он имеет полный набор аксессоров, которые позволяют проверить его свойства. Учитывая следующее определение Table:

employees = Table(
    "employees",
    metadata_obj,
    Column("employee_id", Integer, primary_key=True),
    Column("employee_name", String(60), nullable=False),
    Column("employee_dept", Integer, ForeignKey("departments.department_id")),
)

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

# access the column "employee_id":
employees.columns.employee_id

# or just
employees.c.employee_id

# via string
employees.c["employee_id"]

# iterate through all columns
for c in employees.c:
    print(c)

# get the table's primary key columns
for primary_key in employees.primary_key:
    print(primary_key)

# get the table's foreign key objects:
for fkey in employees.foreign_keys:
    print(fkey)

# access the table's MetaData:
employees.metadata

# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind

# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys

# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key

# access a column's table:
employees.c.employee_id.table is employees

# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table

Создание и удаление таблиц базы данных¶

После того, как вы определили некоторые объекты Table, предполагая, что вы работаете с совершенно новой базой данных, вы можете захотеть выпустить операторы CREATE для этих таблиц и связанных с ними конструкций (кстати, вполне возможно, что вы не захотите этого делать, если у вас уже есть какая-то предпочтительная методология, например, инструменты, включенные в вашу базу данных, или существующая система сценариев - если это так, смело пропускайте этот раздел - SQLAlchemy не требует, чтобы она использовалась для создания ваших таблиц).

Обычным способом создания CREATE является использование create_all() на объекте MetaData. Этот метод выдает запросы, которые сначала проверяют существование каждой отдельной таблицы и, если она не найдена, выдают утверждения CREATE:

engine = create_engine("sqlite:///:memory:")

metadata_obj = MetaData()

user = Table(
    "user",
    metadata_obj,
    Column("user_id", Integer, primary_key=True),
    Column("user_name", String(16), nullable=False),
    Column("email_address", String(60), key="email"),
    Column("nickname", String(50), nullable=False),
)

user_prefs = Table(
    "user_prefs",
    metadata_obj,
    Column("pref_id", Integer, primary_key=True),
    Column("user_id", Integer, ForeignKey("user.user_id"), nullable=False),
    Column("pref_name", String(40), nullable=False),
    Column("pref_value", String(100)),
)

sqlmetadata_obj.create_all(engine)
PRAGMA table_info(user){}
CREATE TABLE user(
        user_id INTEGER NOT NULL PRIMARY KEY,
        user_name VARCHAR(16) NOT NULL,
        email_address VARCHAR(60),
        nickname VARCHAR(50) NOT NULL
)
PRAGMA table_info(user_prefs){}
CREATE TABLE user_prefs(
        pref_id INTEGER NOT NULL PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES user(user_id),
        pref_name VARCHAR(40) NOT NULL,
        pref_value VARCHAR(100)
)

create_all() создает ограничения внешнего ключа между таблицами обычно в строке определения самой таблицы, и по этой причине он также генерирует таблицы в порядке их зависимости. Существуют опции, позволяющие изменить это поведение так, чтобы вместо него использовалось ALTER TABLE.

Отбрасывание всех таблиц аналогично достигается с помощью метода drop_all(). Этот метод действует прямо противоположно методу create_all() - сначала проверяется наличие каждой таблицы, и таблицы удаляются в обратном порядке зависимости.

Создание и удаление отдельных таблиц может быть выполнено с помощью методов create() и drop() из Table. Эти методы по умолчанию выдают CREATE или DROP независимо от наличия таблицы:

engine = create_engine("sqlite:///:memory:")

metadata_obj = MetaData()

employees = Table(
    "employees",
    metadata_obj,
    Column("employee_id", Integer, primary_key=True),
    Column("employee_name", String(60), nullable=False, key="name"),
    Column("employee_dept", Integer, ForeignKey("departments.department_id")),
)
sqlemployees.create(engine)
CREATE TABLE employees(
employee_id SERIAL NOT NULL PRIMARY KEY,
employee_name VARCHAR(60) NOT NULL,
employee_dept INTEGER REFERENCES departments(department_id)
)
{}

drop() метод:

sqlemployees.drop(engine)
DROP TABLE employees
{}

Чтобы включить логику «сначала проверить, существует ли таблица», добавьте аргумент checkfirst=True к create() или drop():

employees.create(engine, checkfirst=True)
employees.drop(engine, checkfirst=False)

Изменение объектов базы данных с помощью миграции¶

Хотя SQLAlchemy напрямую поддерживает создание операторов CREATE и DROP для схемных конструкций, возможность изменения этих конструкций, обычно с помощью оператора ALTER, а также других конструкций, специфичных для баз данных, находится вне сферы применения SQLAlchemy. Хотя достаточно легко создавать операторы ALTER и подобные им вручную, например, передавая конструкцию text() в Connection.execute() или используя конструкцию DDL, общепринятой практикой является автоматизация обслуживания схем баз данных по отношению к коду приложений с помощью инструментов миграции схем.

Проект SQLAlchemy предлагает для этой цели инструмент миграции Alembic. Alembic имеет высоконастраиваемую среду и минималистичную схему использования, поддерживает такие возможности, как транзакционный DDL, автоматическое создание «кандидатов» на миграцию, «автономный» режим, в котором создаются SQL-скрипты, и поддержку разрешения ветвлений.

Alembic заменяет проект SQLAlchemy-Migrate, который является оригинальным инструментом миграции для SQLAlchemy и теперь считается наследием.

Указание имени схемы¶

Большинство баз данных поддерживают концепцию нескольких «схем» - пространств имен, которые ссылаются на альтернативные наборы таблиц и других конструкций. Геометрия «схемы» на стороне сервера принимает множество форм, включая имена «схем», находящихся в области действия конкретной базы данных (например, схемы PostgreSQL), именованные базы данных-сестры (например, доступ MySQL / MariaDB к другим базам данных на том же сервере), а также другие концепции, такие как таблицы, принадлежащие другим именам пользователей (Oracle, SQL Server) или даже имена, которые ссылаются на альтернативные файлы базы данных (SQLite ATTACH) или удаленные серверы (Oracle DBLINK с синонимами).

Все вышеперечисленные подходы (в основном) объединяет то, что существует способ обращения к этому альтернативному набору таблиц с помощью строкового имени. SQLAlchemy называет это имя имя схемы. В SQLAlchemy это не что иное, как строковое имя, которое ассоциируется с объектом Table, а затем преобразуется в SQL-запросы в соответствии с целевой базой данных таким образом, что таблица упоминается в своей удаленной «схеме», каким бы механизмом она ни была в целевой базе данных.

Имя «схемы» может быть связано непосредственно с Table с помощью аргумента Table.schema; при использовании ORM с конфигурацией declarative table параметр передается с помощью словаря параметров __table_args__.

Имя «схемы» также может быть связано с объектом MetaData, где оно будет автоматически действовать для всех объектов Table, связанных с этим MetaData, которые не задают своего имени. Наконец, SQLAlchemy также поддерживает «динамическую» систему имен схем, которая часто используется в многопользовательских приложениях, где один набор метаданных Table может ссылаться на динамически конфигурируемый набор имен схем на основе каждого соединения или каждого запроса.

Самый простой пример - аргумент Table.schema использует объект Core Table следующим образом:

metadata_obj = MetaData()

financial_info = Table(
    "financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("value", String(100), nullable=False),
    schema="remote_banks",
)

SQL, который отображается с использованием этого Table, например, оператор SELECT ниже, будет явно квалифицировать имя таблицы financial_info с именем схемы remote_banks:

>>> print(select(financial_info))
SELECT remote_banks.financial_info.id, remote_banks.financial_info.value
FROM remote_banks.financial_info

Когда объект Table объявляется с явным именем схемы, он хранится во внутреннем пространстве имен MetaData, используя комбинацию имени схемы и таблицы. Мы можем просмотреть его в коллекции MetaData.tables путем поиска по ключу 'remote_banks.financial_info':

>>> metadata_obj.tables["remote_banks.financial_info"]
Table('financial_info', MetaData(),
Column('id', Integer(), table=<financial_info>, primary_key=True, nullable=False),
Column('value', String(length=100), table=<financial_info>, nullable=False),
schema='remote_banks')

Это точечное имя также должно использоваться при ссылке на таблицу для использования с объектами ForeignKey или ForeignKeyConstraint, даже если ссылающаяся таблица также находится в той же схеме:

customer = Table(
    "customer",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("financial_info_id", ForeignKey("remote_banks.financial_info.id")),
    schema="remote_banks",
)

Аргумент Table.schema также может использоваться в некоторых диалектах для указания пути к конкретной таблице с несколькими маркерами (например, точечными). Это особенно важно для таких баз данных, как Microsoft SQL Server, где часто встречаются точечные маркеры «база данных/владелец». Токены могут быть сразу помещены непосредственно в имя, например:

schema = "dbo.scott"

metadata_obj = MetaData(schema="remote_banks")

financial_info = Table(
    "financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("value", String(100), nullable=False),
)

metadata_obj.tables["remote_banks.financial_info"]

# either will work:

refers_to_financial_info = Table(
    "refers_to_financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("fiid", ForeignKey("financial_info.id")),
)


# or

refers_to_financial_info = Table(
    "refers_to_financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("fiid", ForeignKey("remote_banks.financial_info.id")),
)

from sqlalchemy import BLANK_SCHEMA

metadata_obj = MetaData(schema="remote_banks")

financial_info = Table(
    "financial_info",
    metadata_obj,
    Column("id", Integer, primary_key=True),
    Column("value", String(100), nullable=False),
    schema=BLANK_SCHEMA,  # will not use "remote_banks"
)

from sqlalchemy import event
from sqlalchemy import create_engine

engine = create_engine("oracle+cx_oracle://scott:tiger@tsn_name")


@event.listens_for(engine, "connect", insert=True)
def set_current_schema(dbapi_connection, connection_record):
    cursor_obj = dbapi_connection.cursor()
    cursor_obj.execute("ALTER SESSION SET CURRENT_SCHEMA=%s" % schema_name)
    cursor_obj.close()

Параметры, специфичные для бэкенда¶

Table поддерживает опции, специфичные для конкретной базы данных. Например, MySQL имеет различные типы бэкенда таблиц, включая «MyISAM» и «InnoDB». Это можно выразить с помощью Table, используя mysql_engine:

addresses = Table(
    "engine_email_addresses",
    metadata_obj,
    Column("address_id", Integer, primary_key=True),
    Column("remote_user_id", Integer, ForeignKey(users.c.user_id)),
    Column("email_address", String(20)),
    mysql_engine="InnoDB",
)

Другие бэкенды также могут поддерживать опции на уровне таблиц - они будут описаны в отдельных разделах документации для каждого диалекта.

Колонка, таблица, API метаданных¶

attribute sqlalchemy.schema.sqlalchemy.schema.sqlalchemy.schema.BLANK_SCHEMA

Символ, указывающий, что Table или Sequence должен иметь „None“ для своей схемы, даже если родительский MetaData указал схему.

См.также

MetaData.schema

Table.schema

Sequence.schema

Добавлено в версии 1.0.14.

attribute sqlalchemy.schema.sqlalchemy.schema.sqlalchemy.schema.RETAIN_SCHEMA

Символ, указывающий, что объект Table, Sequence или в некоторых случаях ForeignKey, в ситуациях, когда объект копируется для операции Table.to_metadata(), должен сохранить имя схемы, которое он уже имеет.