См.также
Для описания структуры базы данных используют 3 основных класса:
sqlalchemy.schema.Table
- таблицаsqlalchemy.schema.Column
- поле таблицыsqlalchemy.schema.MetaData
- список таблицА также типы полей описанные в модуле sqlalchemy.types
:
1 2 3 4 5 6 7 8 9 10 | from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String
metadata = MetaData()
user_table = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String)
) |
Создание таблиц таким образом описывают структуру базы данных независимо от
объектно-реляционного отображения.
Объект sqlalchemy.schema.Table
представляет имя и другие атрибуты
текущей таблицы. Его коллекция объектов Column
представляет информацию об
именах и типах для определенных столбцов таблицы.
Дополнительно в описание схемы базы данных можно включить внешние ключи, индексы, последовательности и т.д.:
sqlalchemy.schema.ForeignKey
- внешние ключиsqlalchemy.schema.Index
- индексыsqlalchemy.schema.Sequence
- последовательностиВся информация о таблицах базы данных складывается в объект класса
sqlalchemy.schema.MetaData
. Получить список таблиц можно при помощи
атрибута sqlalchemy.schema.MetaData.tables
.
Объекты Table
и Column
уникальны по сравнению со всеми остальными
объектами из пакета для работы со схемами, так как они используют двойное
наследование от объектов из пакетов sqlalchemy.schema
и
sqlalchemy.sql.expression
, работая не только как конструкции уровня
обработки схем, но также и как синтаксические единицы языка для создания
выражений SQL. Это отношение проиллюстрировано на
sqlalchemy_table_crossover
.
1 2 3 4 5 | >>> user_table
Table('user', MetaData(bind=None),
Column('id', Integer(), table=<user>, primary_key=True, nullable=False),
Column('name', String(), table=<user>),
Column('fullname', String(), table=<user>), schema=None) |
1 2 | >>> user_table.name
'user' |
Поля таблицы хранятся в списке sqlalchemy.schema.Table.columns
или его
более коротком варианте sqlalchemy.schema.Table.c
.
1 2 3 4 5 6 7 8 9 10 | >>> user_table.c
<sqlalchemy.sql.expression.ImmutableColumnCollection object at 0x7fee7d18c450>
>>> print(user_table.c)
['user.id', 'user.name', 'user.fullname']
>>> user_table.c.id
Column('id', Integer(), table=<user>, primary_key=True, nullable=False)
>>> user_table.c.name
Column('name', String(), table=<user>)
>>> user_table.c.fullname
Column('fullname', String(), table=<user>) |
Сами поля тоже содержат информацию о себе, например в атрибутах name
и
type
.
1 2 3 4 5 6 7 8 9 10 11 12 13 | >>> user_table.c.id
Column('id', Integer(), table=<user>, primary_key=True, nullable=False)
>>> user_table.c.id.name
'id'
>>> user_table.c.id.type
Integer()
>>>
>>> user_table.c.name
Column('name', String(), table=<user>)
>>> user_table.c.name.name
'name'
>>> user_table.c.name.type
String() |
Первичные ключи таблицы можно получить при помощи атрибута
sqlalchemy.schema.Table.primary_key
1 2 3 4 5 6 | >>> user_table.primary_key
PrimaryKeyConstraint(Column('id', Integer(), table=<user>, primary_key=True, nullable=False))
>>> print(user_table.primary_key.columns)
['user.id']
>>> user_table.primary_key.columns.id
Column('id', Integer(), table=<user>, primary_key=True, nullable=False) |
Объект класса sqlalchemy.schema.Table
является частью механизма SQL
выражений в sqlalchemy
и содержит в себе множество вспомогательных
методов для построения SQL запросов:
sqlalchemy.schema.Table.select()
sqlalchemy.schema.Table.delete()
sqlalchemy.schema.Table.insert()
sqlalchemy.schema.Table.update()
sqlalchemy.schema.Table.join()
sqlalchemy.schema.Table.outerjoin()
1 2 3 4 5 6 7 8 9 10 11 12 | >>> print(user_table.select())
SELECT "user".id, "user".name, "user".fullname
FROM "user"
>>> print(user_table.delete())
DELETE FROM "user"
>>> print(user_table.insert())
INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname)
>>> print(user_table.update())
UPDATE "user" SET id=:id, name=:name, fullname=:fullname |
Все таблицы из списка sqlalchemy.schema.MetaData
можно создать при
помощи метода sqlalchemy.schema.MetaData.create_all()
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | >>> from sqlalchemy import create_engine
>>> engine = create_engine("sqlite://")
>>> metadata.create_all(engine)
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]:
CREATE TABLE user (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
[SQL]: ()
[SQL]: COMMIT |
Для создания, удаления одной таблицы необходимо использовать методы класса
sqlalchemy.schema.Table
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | >>> user_table.drop(engine)
[SQL]:
DROP TABLE user
[SQL]: ()
[SQL]: COMMIT
>>> user_table.create(engine)
[SQL]:
CREATE TABLE user (
id INTEGER NOT NULL,
name VARCHAR,
fullname VARCHAR,
PRIMARY KEY (id)
)
[SQL]: ()
[SQL]: COMMIT |
Типы полей описаны в модуле sqlalchemy.types
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | >>> from sqlalchemy import String, Numeric, DateTime, Enum
>>> fancy_table = Table('fancy', metadata,
... Column('key', String(50), primary_key=True),
... Column('timestamp', DateTime),
... Column('amount', Numeric(10, 2)),
... Column('type', Enum('a', 'b', 'c'))
... )
>>> fancy_table.create(engine)
[SQL]:
CREATE TABLE fancy (
"key" VARCHAR(50) NOT NULL,
timestamp DATETIME,
amount NUMERIC(10, 2),
type VARCHAR(1),
PRIMARY KEY ("key"),
CHECK (type IN ('a', 'b', 'c'))
)
[SQL]: ()
[SQL]: COMMIT |
Индексы создаются при помощи параметра index
в классе
sqlalchemy.schema.Column
или при помощи объекта класса
sqlalchemy.schema.Index
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | >>> meta = MetaData()
>>> mytable = Table('mytable', meta,
... # an indexed column, with index "ix_mytable_col1"
... Column('col1', Integer, index=True),
...
... # a uniquely indexed column with index "ix_mytable_col2"
... Column('col2', Integer, index=True, unique=True),
...
... Column('col3', Integer),
... Column('col4', Integer),
...
... Column('col5', Integer),
... Column('col6', Integer),
... )
>>> from sqlalchemy import Index
>>> Index('idx_col34', mytable.c.col3, mytable.c.col4)
Index('idx_col34', Column('col3', Integer(), table=<mytable>), Column('col4', Integer(), table=<mytable>))
>>> Index('myindex', mytable.c.col5, mytable.c.col6, unique=True)
Index('myindex', Column('col5', Integer(), table=<mytable>), Column('col6', Integer(), table=<mytable>), unique=True)
>>> mytable.create(engine)
[SQL]:
CREATE TABLE mytable (
col1 INTEGER,
col2 INTEGER,
col3 INTEGER,
col4 INTEGER,
col5 INTEGER,
col6 INTEGER
)
[SQL]: ()
[SQL]: COMMIT
[SQL]: CREATE UNIQUE INDEX myindex ON mytable (col5, col6)
[SQL]: ()
[SQL]: COMMIT
[SQL]: CREATE INDEX idx_col34 ON mytable (col3, col4)
[SQL]: ()
[SQL]: COMMIT
[SQL]: CREATE INDEX ix_mytable_col1 ON mytable (col1)
[SQL]: ()
[SQL]: COMMIT
[SQL]: CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2)
[SQL]: ()
[SQL]: COMMIT |
Внешние ключи обычно используют как ссылки на первичные ключи. Для описания
внешнего ключа в схеме нужно использовать класс
sqlalchemy.schema.ForeignKey
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | >>> from sqlalchemy import ForeignKey
>>> addresses_table = Table('address', metadata,
... Column('id', Integer, primary_key=True),
... Column('email_address', String(100), nullable=False),
... Column('user_id', Integer, ForeignKey('user.id'))
... )
>>> addresses_table.create(engine)
[SQL]:
CREATE TABLE address (
id INTEGER NOT NULL,
email_address VARCHAR(100) NOT NULL,
user_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id)
)
[SQL]: ()
[SQL]: COMMIT |
sqlalchemy.schema.ForeignKey
это более краткая запись следующей
конструкции sqlalchemy.schema.ForeignKeyConstraint
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | >>> from sqlalchemy import Unicode, UnicodeText, DateTime
>>> from sqlalchemy import ForeignKeyConstraint
>>> story_table = Table('story', metadata,
... Column('story_id', Integer, primary_key=True),
... Column('version_id', Integer, primary_key=True),
... Column('headline', Unicode(100), nullable=False),
... Column('body', UnicodeText)
... )
>>> published_table = Table('published', metadata,
... Column('pub_id', Integer, primary_key=True),
... Column('pub_timestamp', DateTime, nullable=False),
... Column('story_id', Integer),
... Column('version_id', Integer),
... ForeignKeyConstraint(
... ['story_id', 'version_id'],
... ['story.story_id', 'story.version_id'])
... )
>>> metadata.create_all(engine)
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA table_info("fancy")
[SQL]: ()
[SQL]: PRAGMA table_info("story")
[SQL]: ()
[SQL]: PRAGMA table_info("published")
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]:
CREATE TABLE story (
story_id INTEGER NOT NULL,
version_id INTEGER NOT NULL,
headline VARCHAR(100) NOT NULL,
body TEXT,
PRIMARY KEY (story_id, version_id)
)
[SQL]: ()
[SQL]: COMMIT
[SQL]:
CREATE TABLE published (
pub_id INTEGER NOT NULL,
pub_timestamp DATETIME NOT NULL,
story_id INTEGER,
version_id INTEGER,
PRIMARY KEY (pub_id),
FOREIGN KEY(story_id, version_id) REFERENCES story (story_id, version_id)
)
[SQL]: ()
[SQL]: COMMIT |
См.также
В информатике отражение или рефлексия (холоним интроспекции, англ. reflection) означает процесс, во время которого программа может отслеживать и модифицировать собственную структуру и поведение во время выполнения.
В SQLAlchemy рефлексия означает автоматическую загрузку схемы таблицы из
уже существующей базы данных. Реализуется через параметр autoload
в
конструкторе класса sqlalchemy.schema.Table.autoload
.
1 2 3 4 5 6 7 8 9 10 11 12 | >>> metadata2 = MetaData()
>>> user_reflected = Table('user', metadata2, autoload=True, autoload_with=engine)
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("user")
[SQL]: ()
[SQL]: PRAGMA index_list("user")
[SQL]: ()
>>> print(user_reflected.c)
['user.id', 'user.name', 'user.fullname'] |
Для отражения всех таблиц существует метод
sqlalchemy.schema.MetaData.reflect()
.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | >>> meta = MetaData()
>>> meta.reflect(bind=engine)
[SQL]: SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name
[SQL]: ()
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("address")
[SQL]: ()
[SQL]: PRAGMA table_info("user")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("user")
[SQL]: ()
[SQL]: PRAGMA index_list("user")
[SQL]: ()
[SQL]: PRAGMA index_list("address")
[SQL]: ()
[SQL]: PRAGMA table_info("fancy")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("fancy")
[SQL]: ()
[SQL]: PRAGMA index_list("fancy")
[SQL]: ()
[SQL]: PRAGMA table_info("mytable")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("mytable")
[SQL]: ()
[SQL]: PRAGMA index_list("mytable")
[SQL]: ()
[SQL]: PRAGMA index_info("ix_mytable_col2")
[SQL]: ()
[SQL]: PRAGMA index_info("ix_mytable_col1")
[SQL]: ()
[SQL]: PRAGMA index_info("idx_col34")
[SQL]: ()
[SQL]: PRAGMA index_info("myindex")
[SQL]: ()
[SQL]: PRAGMA table_info("published")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("published")
[SQL]: ()
[SQL]: PRAGMA table_info("story")
[SQL]: ()
[SQL]: PRAGMA foreign_key_list("story")
[SQL]: ()
[SQL]: PRAGMA index_list("story")
[SQL]: ()
[SQL]: PRAGMA index_list("published")
[SQL]: ()
>>> new_user_table = meta.tables['user']
>>> new_fancy_table = meta.tables['fancy'] |
Интроспекция (англ. type introspection) в программировании — возможность в
некоторых объектно-ориентированных языках определить тип и структуру объекта во
время выполнения программы. В SQLAlchemy возможность анализа схемы базы
данных. Для анализа используется функция sqlalchemy.inspection.inspect()
.
1 2 3 4 5 6 7 8 | >>> from sqlalchemy import inspect
>>> inspector = inspect(engine)
>>>
>>> inspector.get_table_names()
[SQL]: SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name
[SQL]: ()
[u'address', u'fancy', u'mytable', u'published', u'story', u'user'] |
1 2 3 4 5 | >>> inspector.get_columns('address')
[SQL]: PRAGMA table_info("address")
[SQL]: ()
[{'primary_key': 1, 'nullable': False, 'default': None, 'autoincrement': True, 'type': INTEGER(), 'name': u'id'}, {'primary_key': 0, 'nullable': False, 'default': None, 'autoincrement': True, 'type': VARCHAR(length=100), 'name': u'email_address'}, {'primary_key': 0, 'nullable': True, 'default': None, 'autoincrement': True, 'type': INTEGER(), 'name': u'user_id'}] |
1 2 3 4 5 | >>> inspector.get_foreign_keys('address')
[SQL]: PRAGMA foreign_key_list("address")
[SQL]: ()
[{'referred_table': u'user', 'referred_columns': [u'id'], 'referred_schema': None, 'name': None, 'constrained_columns': [u'user_id']}] |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | # ## title:: Schema and MetaData
# The structure of a relational schema is represented in Python
# using MetaData, Table, and other objects.
from sqlalchemy import MetaData
from sqlalchemy import Table, Column
from sqlalchemy import Integer, String
metadata = MetaData()
user_table = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('fullname', String)
)
# Table provides a single point of information regarding
# the structure of a table in a schema.
user_table.name
# The .c. attribute of Table is an associative array
# of Column objects, keyed on name.
user_table.c.name
# It's a bit like a Python dictionary but not totally.
print(user_table.c)
# Column itself has information about each Column, such as
# name and type
user_table.c.name.name
user_table.c.name.type
# Table has other information available, such as the collection
# of columns which comprise the table's primary key.
user_table.primary_key
# The Table object is at the core of the SQL expression
# system - this is a quick preview of that.
print(user_table.select())
# Table and MetaData objects can be used to generate a schema
# in a database.
from sqlalchemy import create_engine
engine = create_engine("sqlite://")
metadata.create_all(engine)
# Types are represented using objects such as String, Integer,
# DateTime. These objects can be specified as "class keywords",
# or can be instantiated with arguments.
from sqlalchemy import String, Numeric, DateTime, Enum
fancy_table = Table('fancy', metadata,
Column('key', String(50), primary_key=True),
Column('timestamp', DateTime),
Column('amount', Numeric(10, 2)),
Column('type', Enum('a', 'b', 'c'))
)
fancy_table.create(engine)
# table metadata also allows for constraints and indexes.
# ForeignKey is used to link one column to a remote primary
# key.
from sqlalchemy import ForeignKey
addresses_table = Table('address', metadata,
Column('id', Integer, primary_key=True),
Column('email_address', String(100), nullable=False),
Column('user_id', Integer, ForeignKey('user.id'))
)
addresses_table.create(engine)
# ForeignKey is a shortcut for ForeignKeyConstraint,
# which should be used for composite references.
from sqlalchemy import Unicode, UnicodeText, DateTime
from sqlalchemy import ForeignKeyConstraint
story_table = Table('story', metadata,
Column('story_id', Integer, primary_key=True),
Column('version_id', Integer, primary_key=True),
Column('headline', Unicode(100), nullable=False),
Column('body', UnicodeText)
)
published_table = Table('published', metadata,
Column('pub_id', Integer, primary_key=True),
Column('pub_timestamp', DateTime, nullable=False),
Column('story_id', Integer),
Column('version_id', Integer),
ForeignKeyConstraint(
['story_id', 'version_id'],
['story.story_id', 'story.version_id'])
)
# create_all() by default checks for tables existing already
metadata.create_all(engine)
# ## title:: Exercises
# 1. Write a Table construct corresponding to this CREATE TABLE
# statement.
#
# CREATE TABLE network (
# network_id INTEGER PRIMARY KEY,
# name VARCHAR(100) NOT NULL,
# created_at DATETIME NOT NULL,
# owner_id INTEGER,
# FOREIGN KEY owner_id REFERENCES user(id)
# )
#
# 2. Then emit metadata.create_all(), which will
# emit CREATE TABLE for this table (it will skip
# those that already exist).
#
# The necessary types are imported here:
# ## title:: Reflection
# 'reflection' refers to loading Table objects based on
# reading from an existing database.
metadata2 = MetaData()
user_reflected = Table('user', metadata2, autoload=True, autoload_with=engine)
print(user_reflected.c)
# Information about a database at a more specific level is available
# using the Inspector object.
from sqlalchemy import inspect
inspector = inspect(engine)
# the inspector provides things like table names:
inspector.get_table_names()
# column information
inspector.get_columns('address')
# constraints
inspector.get_foreign_keys('address')
# ## title:: Exercises
#
# 1. Using 'metadata2', reflect the "network" table in the same way
# we just did 'user', then display the columns (or bonus, display
# just the column names)
#
# 2. Using "inspector", print a list of all table names that
# include a column called "story_id"
# |