Домой Edit me on GitHub

2019-03-15

Каналы передачи данных | Сетевое программирование | Базы данных | Основы Веб-программирования

Метаданные (metadata)

Для описания структуры базы данных используют 3 основных класса:

А также типы полей описанные в модуле 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.MetaData. Получить список таблиц можно при помощи атрибута sqlalchemy.schema.MetaData.tables.

../../../../_images/sqlalchemy_schema.png

Базовые объекты пакета sqlalchemy.schema

Объекты Table и Column уникальны по сравнению со всеми остальными объектами из пакета для работы со схемами, так как они используют двойное наследование от объектов из пакетов sqlalchemy.schema и sqlalchemy.sql.expression, работая не только как конструкции уровня обработки схем, но также и как синтаксические единицы языка для создания выражений SQL. Это отношение проиллюстрировано на sqlalchemy_table_crossover.

../../../../_images/table-column-crossover_ru.png

Двойная жизнь объектов Table и Column

Table

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)

SQL выражения

Объект класса sqlalchemy.schema.Table является частью механизма SQL выражений в sqlalchemy и содержит в себе множество вспомогательных методов для построения SQL запросов:

 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']}]

Полный пример

2.sqlalchemy/2.metadata.py
  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"
#
Previous: Соединение (engine) Next: SQL выражения