SQLAlchemy — Введение

SQLAlchemy — это популярный инструментарий SQL и Object Relational Mapper . Он написан на Python и предоставляет полную мощность и гибкость SQL для разработчика приложений. Это кроссплатформенное программное обеспечение с открытым исходным кодом, выпущенное по лицензии MIT.

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

Поскольку размер и производительность баз данных SQL начинают иметь значение, они ведут себя не так, как коллекции объектов. С другой стороны, когда абстракция в коллекциях объектов начинает иметь значение, они ведут себя не так, как таблицы и строки. SQLAlchemy стремится учитывать оба эти принципа.

По этой причине он принял шаблон отображения данных (например, Hibernate), а не шаблон активной записи, используемый рядом других ORM . Базы данных и SQL будут рассматриваться с другой стороны, используя SQLAlchemy.

Майкл Байер — оригинальный автор SQLAlchemy. Его первоначальная версия была выпущена в феврале 2006 года. Последняя версия имеет номер 1.2.7, выпущенную совсем недавно, в апреле 2018 года.

Что такое ORM?

ORM (Object Relational Mapping) — это метод программирования для преобразования данных между несовместимыми системами типов в объектно-ориентированных языках программирования. Обычно система типов, используемая в объектно-ориентированном (ОО) языке, таком как Python, содержит нескалярные типы. Они не могут быть выражены как примитивные типы, такие как целые числа и строки. Следовательно, OO-программист должен преобразовывать объекты в скалярных данных для взаимодействия с базой данных. Однако типы данных в большинстве продуктов баз данных, таких как Oracle, MySQL и т. Д., Являются первичными.

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

SQLAlchemy — настройка среды

Давайте обсудим настройки среды, необходимые для использования SQLAlchemy.

Любая версия Python выше 2.7 необходима для установки SQLAlchemy. Самый простой способ установить это с помощью Python Package Manager, pip . Эта утилита входит в стандартную поставку Python.

pip install sqlalchemy

Используя приведенную выше команду, мы можем загрузить последнюю выпущенную версию SQLAlchemy с python.org и установить ее в вашей системе.

В случае дистрибутива Python от Anaconda SQLAlchemy можно установить с терминала conda с помощью следующей команды:

conda install -c anaconda sqlalchemy

Также можно установить SQLAlchemy из исходного кода ниже —

python setup.py install

SQLAlchemy предназначен для работы с реализацией DBAPI, созданной для конкретной базы данных. Он использует систему диалектов для связи с различными типами реализаций DBAPI и баз данных. Все диалекты требуют, чтобы был установлен соответствующий драйвер DBAPI.

Следующие диалекты включены —

• жар-птица
• Microsoft SQL Server
• MySQL
• оракул
• PostgreSQL
• SQLite
• Sybase

Чтобы проверить правильность установки SQLAlchemy и узнать его версию, введите в командной строке Python следующую команду:

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'

SQLAlchemy Core — язык выражений

Ядро SQLAlchemy включает в себя механизм рендеринга SQL, интеграцию DBAPI, интеграцию транзакций и сервисы описания схем . Ядро SQLAlchemy использует язык выражений SQL, который обеспечивает парадигму использования, ориентированную на схему, тогда как SQLAlchemy ORM — это режим использования, ориентированный на домен .

Язык выражений SQL представляет собой систему представления структур и выражений реляционных баз данных с использованием конструкций Python. В нем представлена ​​система представления примитивных конструкций реляционной базы данных непосредственно без мнения, которая отличается от ORM, которая представляет собой высокоуровневую и абстрактную модель использования, которая сама по себе является примером прикладного использования языка выражений.

Язык выражений является одним из основных компонентов SQLAlchemy. Это позволяет программисту определять операторы SQL в коде Python и использовать их непосредственно в более сложных запросах. Язык выражений не зависит от серверной части и охватывает все аспекты исходного SQL. Он ближе к сырому SQL, чем любой другой компонент в SQLAlchemy.

Язык выражений непосредственно представляет примитивные конструкции реляционной базы данных. Поскольку ORM основан на языке Expression, типичное приложение базы данных Python может перекрывать использование обоих. Приложение может использовать только язык выражений, хотя оно должно определить свою собственную систему преобразования концепций приложения в отдельные запросы к базе данных.

Операторы языка выражений будут переведены в соответствующие необработанные запросы SQL с помощью механизма SQLAlchemy. Теперь мы научимся создавать движок и выполнять различные SQL-запросы с его помощью.

SQLAlchemy Core — подключение к базе данных

В предыдущей главе мы обсудили язык выражений в SQLAlchemy. Теперь давайте перейдем к шагам, связанным с подключением к базе данных.

Класс Engine связывает пул и диалект вместе, обеспечивая источник подключения и поведения базы данных. Объект класса Engine создается с помощью функции create_engine () .

Функция create_engine () принимает базу данных в качестве одного аргумента. База данных нигде не должна быть определена. Стандартная форма вызова должна отправлять URL в качестве первого позиционного аргумента, обычно это строка, которая указывает на диалект базы данных и аргументы соединения. Используя приведенный ниже код, мы можем создать базу данных.

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)

Для базы данных MySQL используйте следующую команду —

engine = create_engine("mysql://user:pwd@localhost/college",echo = True)

Чтобы особо упомянуть DB-API, который будет использоваться для соединения, строка URL-адреса принимает вид:

dialect[+driver]://user:password@host/dbname

Например, если вы используете драйвер PyMySQL с MySQL , используйте следующую команду —

mysql+pymysql://<username>:<password>@<host>/<dbname>

Флаг echo — это ярлык для настройки ведения журнала SQLAlchemy, который выполняется с помощью стандартного модуля ведения журналов Python. В последующих главах мы изучим все сгенерированные SQL. Чтобы скрыть подробный вывод, установите для атрибута echo значение None . Другие аргументы функции create_engine () могут быть специфическими для диалекта.

Функция create_engine () возвращает объект Engine . Некоторые важные методы класса Engine —

Sr.No.	Метод и описание
1	подключения () Возвращает объект соединения
2	выполнить () Выполняет конструкцию оператора SQL
3	начать() Возвращает менеджер контекста, доставляющий Соединение с установленной Транзакцией. После успешной операции транзакция фиксируется, иначе она откатывается
4	распоряжаться () Уничтожает пул соединений, используемый Engine
5	Водитель() Название драйвера диалекта, используемого двигателем
6	table_names () Возвращает список всех имен таблиц, доступных в базе данных.
7	сделка() Выполняет данную функцию в пределах транзакции

подключения ()

Возвращает объект соединения

выполнить ()

Выполняет конструкцию оператора SQL

начать()

Возвращает менеджер контекста, доставляющий Соединение с установленной Транзакцией. После успешной операции транзакция фиксируется, иначе она откатывается

распоряжаться ()

Уничтожает пул соединений, используемый Engine

Водитель()

Название драйвера диалекта, используемого двигателем

table_names ()

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

сделка()

Выполняет данную функцию в пределах транзакции

SQLAlchemy Core — Создание таблицы

Давайте теперь обсудим, как использовать функцию создания таблицы.

Язык выражений SQL строит свои выражения для столбцов таблицы. Объект столбца SQLAlchemy представляет столбец в таблице базы данных, который, в свою очередь, представлен объектом таблицы . Метаданные содержат определения таблиц и связанных объектов, таких как индекс, представление, триггеры и т. Д.

Следовательно, объект класса MetaData из метаданных SQLAlchemy представляет собой коллекцию объектов Table и связанных с ними конструкций схемы. Он содержит коллекцию объектов Table, а также необязательную привязку к Engine или Connection.

from sqlalchemy import MetaData
meta = MetaData()

Конструктор класса MetaData может иметь параметры связывания и схемы, которые по умолчанию отсутствуют .

Далее мы определяем все наши таблицы в каталоге метаданных, используя конструкцию Table , которая напоминает обычный оператор SQL CREATE TABLE.

Объект класса Table представляет соответствующую таблицу в базе данных. Конструктор принимает следующие параметры —

название	Название стола
Метаданные	Объект MetaData, который будет содержать эту таблицу
Колонка (ы)	Один или несколько объектов класса столбца

Объект столбца представляет столбец в таблице базы данных . Конструктор принимает имя, тип и другие параметры, такие как primary_key, автоинкремент и другие ограничения.

SQLAlchemy сопоставляет данные Python с наилучшими возможными типами данных столбца, определенными в нем. Некоторые из общих типов данных —

• BigInteger
• логический
• Дата
• DateTime
• терка
• целое число
• числовой
• SmallInteger
• строка
• Текст
• Время

Чтобы создать таблицу студентов в базе данных колледжа, используйте следующий фрагмент —

from sqlalchemy import Table, Column, Integer, String, MetaData
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

Функция create_all () использует объект engine для создания всех определенных объектов таблицы и сохраняет информацию в метаданных.

meta.create_all(engine)

Ниже приведен полный код, который создаст базу данных SQLite college.db с таблицей студентов.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String),
)
meta.create_all(engine)

Поскольку для атрибута echo функции create_engine () установлено значение True , консоль будет отображать фактический запрос SQL для создания таблицы следующим образом:

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

College.db будет создан в текущем рабочем каталоге. Чтобы проверить, создана ли таблица студентов, вы можете открыть базу данных с помощью любого инструмента SQLite GUI, такого как SQLiteStudio .

На рисунке ниже показана таблица учеников, созданная в базе данных.

Ядро SQLAlchemy — выражения SQL

В этой главе мы кратко остановимся на выражениях SQL и их функциях.

Выражения SQL создаются с использованием соответствующих методов относительно объекта целевой таблицы. Например, оператор INSERT создается путем выполнения метода insert () следующим образом:

ins = students.insert()

Результатом описанного выше метода является объект вставки, который можно проверить с помощью функции str () . Приведенный ниже код вставляет детали, такие как идентификатор студента, имя, фамилия.

'INSERT INTO students (id, name, lastname) VALUES (:id, :name, :lastname)'

Можно вставить значение в определенное поле с помощью метода values ​​() для вставки объекта. Код для того же приведен ниже —

>>> ins = users.insert().values(name = 'Karan')
>>> str(ins)
'INSERT INTO users (name) VALUES (:name)'

SQL, отраженный в консоли Python, не показывает фактическое значение (в данном случае «Karan»). Вместо этого SQLALchemy генерирует параметр связывания, который отображается в скомпилированной форме оператора.

ins.compile().params
{'name': 'Karan'}

Аналогично, такие методы, как update (), delete () и select () создают выражения UPDATE, DELETE и SELECT соответственно. Мы узнаем о них в следующих главах.

Ядро SQLAlchemy — выполнение выражения

В предыдущей главе мы изучили выражения SQL. В этой главе мы рассмотрим выполнение этих выражений.

Чтобы выполнить результирующие выражения SQL, мы должны получить объект соединения, представляющий активно извлеченный ресурс соединения DBAPI, а затем передать объект выражения, как показано в коде ниже.

conn = engine.connect()

Следующий объект insert () может быть использован для метода execute () —

ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
result = conn.execute(ins)

Консоль показывает результат выполнения выражения SQL, как показано ниже:

INSERT INTO students (name, lastname) VALUES (?, ?)
('Ravi', 'Kapoor')
COMMIT

Ниже приведен весь фрагмент, который показывает выполнение запроса INSERT с использованием основного метода SQLAlchemy.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

ins = students.insert()
ins = students.insert().values(name = 'Ravi', lastname = 'Kapoor')
conn = engine.connect()
result = conn.execute(ins)

Результат можно проверить, открыв базу данных с помощью SQLite Studio, как показано на скриншоте ниже —

Результирующая переменная называется объектом ResultProxy . Он аналогичен объекту курсора DBAPI. Мы можем получить информацию о значениях первичного ключа, которые были сгенерированы из нашего оператора, используя ResultProxy.inserted_primary_key, как показано ниже —

result.inserted_primary_key
[1]

Чтобы выполнить много операций вставки с использованием метода execute many () в DBAPI, мы можем отправить список словарей, каждый из которых содержит отдельный набор параметров для вставки.

conn.execute(students.insert(), [
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

Это отражено в представлении данных таблицы, как показано на следующем рисунке —

Ядро SQLAlchemy — Выбор строк

В этой главе мы обсудим концепцию выбора строк в табличном объекте.

Метод select () табличного объекта позволяет нам создавать выражение SELECT .

s = students.select()

Объект select преобразуется в запрос SELECT с помощью функции str (s), как показано ниже —

'SELECT students.id, students.name, students.lastname FROM students'

Мы можем использовать этот объект select как параметр для метода execute () объекта подключения, как показано в коде ниже —

result = conn.execute(s)

Когда приведенный выше оператор выполняется, оболочка Python отображает следующее эквивалентное выражение SQL —

SELECT students.id, students.name, students.lastname
FROM students

Результирующая переменная является эквивалентом курсора в DBAPI. Теперь мы можем получать записи, используя метод fetchone () .

row = result.fetchone()

Все выбранные строки в таблице могут быть напечатаны с помощью цикла for, как показано ниже —

for row in result:
   print (row)

Полный код для печати всех строк из таблицы студентов показан ниже —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

s = students.select()
conn = engine.connect()
result = conn.execute(s)

for row in result:
   print (row)

Вывод, показанный в оболочке Python, выглядит следующим образом:

(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

Предложение WHERE запроса SELECT может быть применено с помощью Select.where () . Например, если мы хотим отображать строки с идентификатором> 2

s = students.select().where(students.c.id>2)
result = conn.execute(s)

for row in result:
   print (row)

Здесь атрибут c является псевдонимом для столбца . Следующий результат будет отображаться на оболочке —

(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')

Здесь следует отметить, что объект select также можно получить с помощью функции select () в модуле sqlalchemy.sql. Функция select () требует в качестве аргумента табличный объект.

from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)

Ядро SQLAlchemy — Использование текстового SQL

SQLAlchemy позволяет вам просто использовать строки, для тех случаев, когда SQL уже известен и нет сильной необходимости в операторе для поддержки динамических функций. Конструкция text () используется для составления текстового оператора, который в основном передается в базу данных без изменений.

Он создает новое TextClause , представляющее текстовую строку SQL напрямую, как показано в приведенном ниже коде —

from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)

Преимущества text () перед простой строкой:

• Нейтральная поддержка параметров привязки
• варианты исполнения для каждого оператора
• тип поведения столбца результата

Для функции text () требуются параметры Bound в указанном формате двоеточия. Они согласованы независимо от базы данных. Чтобы отправить значения параметров, мы передаем их в метод execute () в качестве дополнительных аргументов.

В следующем примере используются связанные параметры в текстовом SQL —

from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between 😡 and :y")
conn.execute(s, x = 'A', y = 'L').fetchall()

Функция text () создает выражение SQL следующим образом:

select students.name, students.lastname from students where students.name between ? and ?

Значения x = ‘A’ и y = ‘L’ передаются как параметры. Результатом является список строк с именами между ‘A’ и ‘L’ —

[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

Конструкция text () поддерживает предварительно установленные значения привязки с помощью метода TextClause.bindparams (). Параметры также могут быть явно введены следующим образом:

stmt = text("SELECT * FROM students WHERE students.name BETWEEN 😡 AND :y")

stmt = stmt.bindparams(
   bindparam("x", type_= String), 
   bindparam("y", type_= String)
)

result = conn.execute(stmt, {"x": "A", "y": "L"})

The text() function also be produces fragments of SQL within a select() object that 
accepts text() objects as an arguments. The “geometry” of the statement is provided by 
select() construct , and the textual content by text() construct. We can build a statement 
without the need to refer to any pre-established Table metadata. 

from sqlalchemy.sql import select
s = select([text("students.name, students.lastname from students")]).where(text("students.name between 😡 and :y"))
conn.execute(s, x = 'A', y = 'L').fetchall()

Вы также можете использовать функцию and_ () для объединения нескольких условий в предложении WHERE, созданном с помощью функции text ().

from sqlalchemy import and_
from sqlalchemy.sql import select
s = select([text("* from students")]) \
.where(
   and_(
      text("students.name between 😡 and :y"),
      text("students.id>2")
   )
)
conn.execute(s, x = 'A', y = 'L').fetchall()

Выше код выбирает строки с именами между «A» и «L» с id больше 2. Вывод кода приведен ниже —

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]

Ядро SQLAlchemy — Использование псевдонимов

Псевдоним в SQL соответствует «переименованной» версии таблицы или инструкции SELECT, которая появляется каждый раз, когда вы говорите «SELECT * FROM table1 AS a». AS создает новое имя для таблицы. Псевдонимы позволяют ссылаться на любую таблицу или подзапрос с уникальным именем.

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

В SQLAlchemy любую конструкцию Table, select () или другой выбираемый объект можно превратить в псевдоним с помощью метода From Clause.alias () , который создает конструкцию Alias. Функция alias () в модуле sqlalchemy.sql представляет псевдоним, который обычно применяется к любой таблице или подвыбору в выражении SQL с использованием ключевого слова AS.

from sqlalchemy.sql import alias
st = students.alias("a")

Этот псевдоним теперь можно использовать в конструкции select () для ссылки на таблицу студентов —

s = select([st]).where(st.c.id>2)

Это переводит в выражение SQL следующим образом:

SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2

Теперь мы можем выполнить этот SQL-запрос с помощью метода execute () объекта соединения. Полный код выглядит следующим образом —

from sqlalchemy.sql import alias, select
st = students.alias("a")
s = select([st]).where(st.c.id > 2)
conn.execute(s).fetchall()

Когда вышеуказанная строка кода выполняется, она генерирует следующий вывод —

[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar'), (5, 'Priya', 'Rajhans')]

Использование выражения UPDATE

Метод update () для объекта целевой таблицы создает эквивалентное выражение UPDATE SQL.

table.update().where(conditions).values(SET expressions)

Метод values ​​() в результирующем объекте обновления используется для указания условий SET ОБНОВЛЕНИЯ. Если оставить значение None, условия SET определяются по тем параметрам, которые передаются оператору во время выполнения и / или компиляции оператора.

Предложение where является опциональным выражением, описывающим условие WHERE оператора UPDATE.

Следующий фрагмент кода изменяет значение столбца ‘фамилия’ с ‘Ханна’ на ‘Капур’ в таблице учеников —

stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

Объект stmt является объектом обновления, который переводится в:

'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'

Связанный параметр lastname_1 будет заменен при вызове метода execute () . Полный код обновления приведен ниже —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

students = Table(
   'students', 
   meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt=students.update().where(students.c.lastname=='Khanna').values(lastname='Kapoor')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

Приведенный выше код отображает следующий вывод со второй строкой, показывающей эффект операции обновления, как на приведенном скриншоте:

[
   (1, 'Ravi', 'Kapoor'),
   (2, 'Rajiv', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (4, 'Abdul', 'Sattar'),
   (5, 'Priya', 'Rajhans')
]

Обратите внимание, что подобная функциональность также может быть достигнута с помощью функции update () в модуле sqlalchemy.sql.expression, как показано ниже —

from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')

Использование DELETE Expression

В предыдущей главе мы поняли, что делает выражение Update . Следующее выражение, которое мы собираемся выучить, это Delete .

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

stmt = students.delete()

В случае таблицы студентов приведенная выше строка кода создает выражение SQL следующим образом:

'DELETE FROM students'

Тем не менее, это удалит все строки в таблице студентов. Обычно запрос DELETE связан с логическим выражением, указанным в предложении WHERE. Следующий оператор показывает, где параметр —

stmt = students.delete().where(students.c.id > 2)

Результирующее выражение SQL будет иметь связанный параметр, который будет подставляться во время выполнения при выполнении инструкции.

'DELETE FROM students WHERE students.id > :id_1'

Следующий пример кода удалит эти строки из таблицы учеников, имеющие фамилию как ‘Ханна’ —

from sqlalchemy.sql.expression import update
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn = engine.connect()
stmt = students.delete().where(students.c.lastname == 'Khanna')
conn.execute(stmt)
s = students.select()
conn.execute(s).fetchall()

Чтобы проверить результат, обновите представление данных таблицы студентов в SQLiteStudio.

Ядро SQLAlchemy — Использование нескольких таблиц

Одной из важных особенностей РСУБД является установление связи между таблицами. Операции SQL, такие как SELECT, UPDATE и DELETE, могут выполняться над связанными таблицами. В этом разделе описываются эти операции с использованием SQLAlchemy.

Для этого в нашей базе данных SQLite (College.db) создаются две таблицы. Таблица студентов имеет ту же структуру, что и в предыдущем разделе; в то время как таблица адресов имеет столбец st_id, который отображается на столбец идентификатора в таблице студентов с использованием ограничения внешнего ключа.

Следующий код создаст две таблицы в College.db —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo=True)
meta = MetaData()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer, ForeignKey('students.id')), 
   Column('postal_add', String), 
   Column('email_add', String))

meta.create_all(engine)

Приведенный выше код переведет запросы CREATE TABLE для студентов и таблицу адресов, как показано ниже:

CREATE TABLE students (
   id INTEGER NOT NULL,
   name VARCHAR,
   lastname VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE addresses (
   id INTEGER NOT NULL,
   st_id INTEGER,
   postal_add VARCHAR,
   email_add VARCHAR,
   PRIMARY KEY (id),
   FOREIGN KEY(st_id) REFERENCES students (id)
)

На следующих скриншотах приведенный выше код очень четко представлен.

Эти таблицы заполняются данными, выполняя метод insert () для табличных объектов. Чтобы вставить 5 строк в таблицу студентов, вы можете использовать код, приведенный ниже —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()

conn = engine.connect()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

conn.execute(students.insert(), [
   {'name':'Ravi', 'lastname':'Kapoor'},
   {'name':'Rajiv', 'lastname' : 'Khanna'},
   {'name':'Komal','lastname' : 'Bhandari'},
   {'name':'Abdul','lastname' : 'Sattar'},
   {'name':'Priya','lastname' : 'Rajhans'},
])

Строки добавляются в таблицу адресов с помощью следующего кода —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer), 
   Column('postal_add', String), 
   Column('email_add', String)
)

conn.execute(addresses.insert(), [
   {'st_id':1, 'postal_add':'Shivajinagar Pune', 'email_add':'[email protected]'},
   {'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'[email protected]'},
   {'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'[email protected]'},
   {'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'[email protected]'},
   {'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'[email protected]'},
])

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

from sqlalchemy.sql import select
s = select([students, addresses]).where(students.c.id == addresses.c.st_id)
result = conn.execute(s)

for row in result:
   print (row)

Объекты select будут эффективно преобразовываться в следующее выражение SQL, объединяющее две таблицы общего отношения —

SELECT students.id, 
   students.name, 
   students.lastname, 
   addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM students, addresses
WHERE students.id = addresses.st_id

Это приведет к выводу соответствующих данных из обеих таблиц следующим образом:

(1, 'Ravi', 'Kapoor', 1, 1, 'Shivajinagar Pune', '[email protected]')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', '[email protected]')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', '[email protected]')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', '[email protected]')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', '[email protected]')

Использование нескольких обновлений таблиц

В предыдущей главе мы обсудили, как использовать несколько таблиц. Таким образом, мы продолжим шаг дальше и изучим множество обновлений таблиц в этой главе

Используя объект таблицы SQLAlchemy, в предложении WHERE метода update () можно указать более одной таблицы. PostgreSQL и Microsoft SQL Server поддерживают операторы UPDATE, которые ссылаются на несколько таблиц. Это реализует синтаксис «ОБНОВЛЕНИЕ ОТ» , который обновляет одну таблицу за раз. Однако на дополнительные таблицы можно ссылаться в дополнительном предложении «FROM» в предложении WHERE напрямую. Следующие строки кода четко объясняют концепцию обновлений нескольких таблиц .

stmt = students.update().\
values({
   students.c.name:'xyz',
   addresses.c.email_add:'[email protected]'
}).\
where(students.c.id == addresses.c.id)

Объект обновления эквивалентен следующему запросу UPDATE —

UPDATE students 
SET email_add = :addresses_email_add, name = :name 
FROM addresses 
WHERE students.id = addresses.id

Что касается диалекта MySQL, несколько таблиц могут быть встроены в один оператор UPDATE, разделенный запятой, как указано ниже —

stmt = students.update().\
   values(name = 'xyz').\
   where(students.c.id == addresses.c.id)

Следующий код отображает результирующий запрос UPDATE —

'UPDATE students SET name = :name 
FROM addresses 
WHERE students.id = addresses.id'

Однако диалект SQLite не поддерживает критерии для нескольких таблиц в UPDATE и показывает следующую ошибку:

NotImplementedError: This backend does not support multiple-table criteria within UPDATE

Упорядоченные по параметру обновления

В запросе UPDATE необработанного SQL есть предложение SET. Он отображается с помощью конструкции update () с использованием порядка столбцов, указанного в исходном объекте Table. Поэтому определенный оператор UPDATE с конкретными столбцами будет отображаться каждый раз одинаково. Поскольку сами параметры передаются методу Update.values ​​() в качестве ключей словаря Python, другого фиксированного порядка нет.

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

Следующий результат заявления —

UPDATE table1 SET x = y + 10, y = 20

будет иметь другой результат, чем —

UPDATE table1 SET y = 20, x = y + 10

Предложение SET в MySQL оценивается для каждого значения, а не для каждого ряда. Для этого используется preserve_parameter_order . Список из двух кортежей Python задается в качестве аргумента метода Update.values ​​() —

stmt = table1.update(preserve_parameter_order = True).\
   values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])

Объект List похож на словарь за исключением того, что он упорядочен. Это гарантирует, что сначала будет отображаться предложение SET для столбца «y», а затем предложение SET для столбца «x».

Ядро SQLAlchemy — удаление нескольких таблиц

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

На многих диалектах СУБД в предложении WHERE оператора DELETE можно ссылаться более чем на одну таблицу. Для PG и MySQL используется синтаксис «DELETE USING»; а для SQL Server выражение «DELETE FROM» относится к нескольким таблицам. Конструкция SQLAlchemy delete () неявно поддерживает оба этих режима, указав несколько таблиц в предложении WHERE следующим образом:

stmt = users.delete().\
   where(users.c.id == addresses.c.id).\
   where(addresses.c.email_address.startswith('xyz%'))
conn.execute(stmt)

В бэкэнде PostgreSQL полученный SQL из вышеприведенного оператора будет выглядеть как —

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

Если этот метод используется с базой данных, которая не поддерживает это поведение, компилятор вызовет NotImplementedError.

Ядро SQLAlchemy — Использование соединений

В этой главе мы узнаем, как использовать соединения в SQLAlchemy.

Эффект объединения достигается простым размещением двух таблиц либо в предложении столбцов, либо в предложении where конструкции select (). Теперь мы используем методы join () и outerjoin ().

Метод join () возвращает объект соединения из одного объекта таблицы в другой.

join(right, onclause = None, isouter = False, full = False)

Функции параметров, упомянутых в приведенном выше коде, следующие:

• right — правая сторона объединения; это любой объект таблицы

• onclause — выражение SQL, представляющее предложение ON объединения. Если оставить None, он попытается объединить две таблицы на основе отношения внешнего ключа.

• isouter — если True, то рендерится ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, а не СОЕДИНЕНИЕ

• full — если True, рендерит FULL OUTER JOIN вместо LEFT OUTER JOIN

right — правая сторона объединения; это любой объект таблицы

onclause — выражение SQL, представляющее предложение ON объединения. Если оставить None, он попытается объединить две таблицы на основе отношения внешнего ключа.

isouter — если True, то рендерится ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, а не СОЕДИНЕНИЕ

full — если True, рендерит FULL OUTER JOIN вместо LEFT OUTER JOIN

Например, последующее использование метода join () автоматически приведет к объединению на основе внешнего ключа.

>>> print(students.join(addresses))

Это эквивалентно следующему выражению SQL —

students JOIN addresses ON students.id = addresses.st_id

Вы можете явно упомянуть критерии присоединения следующим образом:

j = students.join(addresses, students.c.id == addresses.c.st_id)

Если мы сейчас построим нижеприведенную конструкцию select, используя это соединение как —

stmt = select([students]).select_from(j)

Это приведет к следующему выражению SQL —

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

Если этот оператор выполняется с использованием механизма, представляющего соединение, будут отображаться данные, относящиеся к выбранным столбцам. Полный код выглядит следующим образом —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer,ForeignKey('students.id')), 
   Column('postal_add', String), 
   Column('email_add', String)
)

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

Ниже приведен вывод вышеуказанного кода —

[
   (1, 'Ravi', 'Kapoor'),
   (1, 'Ravi', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (5, 'Priya', 'Rajhans'),
   (2, 'Rajiv', 'Khanna')
]

SQLAlchemy Core — Использование конъюнкций

Соединения — это функции в модуле SQLAlchemy, которые реализуют реляционные операторы, используемые в предложении WHERE выражений SQL. Операторы AND, OR, NOT и т. Д. Используются для формирования составного выражения, объединяющего два отдельных логических выражения. Простой пример использования AND в операторе SELECT следующий:

SELECT * from EMPLOYEE WHERE salary>10000 AND age>30

Функции SQLAlchemy and_ (), or_ () и not_ () соответственно реализуют операторы AND, OR и NOT.

and_ () функция

Создает соединение выражений, соединенных AND. Ниже приведен пример для лучшего понимания —

from sqlalchemy import and_

print(
   and_(
      students.c.name == 'Ravi',
      students.c.id <3
   )
)

Это переводит на —

students.name = :name_1 AND students.id < :id_1

Чтобы использовать and_ () в конструкции select () таблицы учеников, используйте следующую строку кода:

stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))

Будет построено утверждение SELECT следующего характера:

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.name = :name_1 AND students.id < :id_1

Полный код, который отображает вывод вышеуказанного запроса SELECT, выглядит следующим образом:

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

from sqlalchemy import and_, or_
stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))
result = conn.execute(stmt)
print (result.fetchall())

Следующая строка будет выбрана при условии, что таблица студентов заполнена данными, использованными в предыдущем примере —

[(1, 'Ravi', 'Kapoor')]

функция or_ ()

Это производит соединение выражений, соединенных OR. Мы заменим объект stmt в вышеприведенном примере следующим, используя or_ ()

stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3))

Что будет эффективно эквивалентно следующему запросу SELECT —

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.name = :name_1 
OR students.id < :id_1

Как только вы сделаете подстановку и запустите приведенный выше код, результатом будет две строки, попадающие в условие ИЛИ —

[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]

функция asc ()

Создает восходящее предложение ORDER BY. Функция берет столбец для применения функции в качестве параметра.

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))

Оператор реализует следующее выражение SQL —

SELECT students.id, 
   students.name, 
   students.lastname
FROM students 
ORDER BY students.name ASC

Следующий код перечисляет все записи в таблице студентов в порядке возрастания столбца имени —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey, select
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()

students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

from sqlalchemy import asc
stmt = select([students]).order_by(asc(students.c.name))
result = conn.execute(stmt)

for row in result:
   print (row)

Выше код производит следующий вывод —

(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')

функция desc ()

Точно так же функция desc () создает нисходящее предложение ORDER BY следующим образом:

from sqlalchemy import desc
stmt = select([students]).order_by(desc(students.c.lastname))

Эквивалентное выражение SQL —

SELECT students.id, 
   students.name, 
   students.lastname
FROM students 
ORDER BY students.lastname DESC

И вывод для вышеуказанных строк кода —

(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')
(3, 'Komal', 'Bhandari')

между () функцией

Он создает предложение предиката BETWEEN. Обычно это используется для проверки того, находится ли значение определенного столбца в диапазоне. Например, следующий код выбирает строки, для которых столбец идентификатора находится между 2 и 4 —

from sqlalchemy import between
stmt = select([students]).where(between(students.c.id,2,4))
print (stmt)

Результирующее выражение SQL напоминает —

SELECT students.id, 
   students.name, 
   students.lastname
FROM students
WHERE students.id 
BETWEEN :id_1 AND :id_2

и результат выглядит следующим образом —

(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')

Ядро SQLAlchemy — Использование функций

Некоторые важные функции, используемые в SQLAlchemy, обсуждаются в этой главе.

Стандартный SQL рекомендовал много функций, которые реализованы большинством диалектов. Они возвращают одно значение на основе переданных ему аргументов. Некоторые функции SQL принимают столбцы в качестве аргументов, а некоторые являются общими. Ключевое слово func в API SQLAlchemy используется для генерации этих функций .

В SQL now () является универсальной функцией. Следующие операторы отображают функцию now () с использованием func —

from sqlalchemy.sql import func
result = conn.execute(select([func.now()]))
print (result.fetchone())

Пример результата приведенного выше кода может быть таким, как показано ниже —

(datetime.datetime(2018, 6, 16, 6, 4, 40),)

С другой стороны, функция count (), которая возвращает количество строк, выбранных из таблицы, визуализируется следующим использованием func —

from sqlalchemy.sql import func
result = conn.execute(select([func.count(students.c.id)]))
print (result.fetchone())

Из приведенного выше кода будет считаться количество строк в таблице студентов.

Некоторые встроенные функции SQL демонстрируются с использованием таблицы Employee со следующими данными:

Я БЫ	название	Метки
1	Камаль	56
2	Fernandez	85
3	Сунил	62
4	Bhaskar	76

Функция max () реализована следующим использованием func из SQLAlchemy, в результате чего будет получено 85 баллов —

from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())

Аналогично, функция min (), которая возвращает 56, минимальные оценки, будет отображаться с помощью следующего кода —

from sqlalchemy.sql import func
result = conn.execute(select([func.min(employee.c.marks)]))
print (result.fetchone())

Таким образом, функция AVG () также может быть реализована с использованием приведенного ниже кода:

from sqlalchemy.sql import func
result = conn.execute(select([func.avg(employee.c.marks)]))
print (result.fetchone())

Functions are normally used in the columns clause of a select statement. 
They can also be given label as well as a type. A label to function allows the result 
to be targeted in a result row based on a string name, and a type is required when 
you need result-set processing to occur.from sqlalchemy.sql import func

result = conn.execute(select([func.max(students.c.lastname).label('Name')]))

print (result.fetchone())

Ядро SQLAlchemy — Использование операций над множествами

В последней главе мы узнали о различных функциях, таких как max (), min (), count () и т. Д., Здесь мы узнаем об операциях над множествами и их использовании.

Операции над множествами, такие как UNION и INTERSECT, поддерживаются стандартным SQL и большей частью его диалекта. SQLAlchemy реализует их с помощью следующих функций —

союз ()

Объединяя результаты двух или более операторов SELECT, UNION удаляет дубликаты из набора результатов. Количество столбцов и тип данных должны быть одинаковыми в обеих таблицах.

Функция union () возвращает объект CompoundSelect из нескольких таблиц. Следующий пример демонстрирует его использование —

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer), 
   Column('postal_add', String), 
   Column('email_add', String)
)

u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))

result = conn.execute(u)
result.fetchall()

Конструкция объединения переводится в следующее выражение SQL —

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

Из нашей таблицы адресов следующие строки представляют операцию объединения —

[
   (1, 1, 'Shivajinagar Pune', '[email protected]'),
   (2, 1, 'ChurchGate Mumbai', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]'),
   (4, 5, 'MG Road Bangaluru', '[email protected]')
]

union_all ()

Операция UNION ALL не может удалить дубликаты и не может отсортировать данные в наборе результатов. Например, в приведенном выше запросе UNION заменяется на UNION ALL, чтобы увидеть эффект.

u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))

Соответствующее выражение SQL выглядит следующим образом:

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?

Кроме_()

Предложение / оператор SQL EXCEPT используется для объединения двух операторов SELECT и возврата строк из первого оператора SELECT, которые не возвращаются вторым оператором SELECT. Функция exc_ () генерирует выражение SELECT с предложением EXCEPT.

В следующем примере функция exc_ () возвращает только те записи из таблицы адресов, которые имеют «gmail.com» в поле email_add, но исключают те, которые имеют «Pune» как часть поля postal_add.

u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

Результатом приведенного выше кода является следующее выражение SQL —

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

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

[(2, 1, 'ChurchGate Mumbai', '[email protected]'),
   (3, 3, 'Jubilee Hills Hyderabad', '[email protected]')]

пересекаются ()

Используя оператор INTERSECT, SQL отображает общие строки из обоих операторов SELECT. Функция intersect () реализует это поведение.

В следующих примерах две конструкции SELECT являются параметрами для функции intersect (). Одна возвращает строки, содержащие «gmail.com», как часть столбца email_add, а другая возвращает строки, содержащие «Pune» как часть столбца postal_add. Результатом будут общие строки из обоих наборов результатов.

u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))

По сути, это эквивалентно следующему оператору SQL —

SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id, 
   addresses.st_id, 
   addresses.postal_add, 
   addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?

Два связанных параметра «% gmail.com» и «% Pune» генерируют одну строку из исходных данных в таблице адресов, как показано ниже —

[(1, 1, 'Shivajinagar Pune', '[email protected]')]

SQLAlchemy ORM — объявление сопоставления

Основной целью API Object Relational Mapper в SQLAlchemy является облегчение связывания пользовательских классов Python с таблицами базы данных, а объектов этих классов — со строками в соответствующих таблицах. Изменения в состояниях объектов и строк синхронно сопоставляются друг с другом. SQLAlchemy позволяет выражать запросы к базе данных в терминах пользовательских классов и их определенных отношений.

ORM построен поверх языка выражений SQL. Это высокий уровень и абстрактная схема использования. На самом деле ORM — это прикладное использование языка выражений.

Хотя успешное приложение может быть создано с использованием исключительно объектно-реляционного сопоставления, иногда приложение, созданное с использованием ORM, может использовать язык выражений непосредственно там, где требуются определенные взаимодействия с базой данных.

Объявить картирование

Прежде всего, вызывается функция create_engine () для настройки объекта механизма, который впоследствии используется для выполнения операций SQL. Функция имеет два аргумента: один — имя базы данных, а другой — параметр эха, если для параметра установлено значение True, будет создан журнал активности. Если он не существует, база данных будет создана. В следующем примере создается база данных SQLite.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)

Engine устанавливает реальное соединение DBAPI с базой данных при вызове метода, подобного Engine.execute () или Engine.connect (). Затем он используется для испускания SQLORM, который не использует Engine напрямую; вместо этого он используется за кадром ORM.

В случае ORM процесс конфигурации начинается с описания таблиц базы данных, а затем с определения классов, которые будут сопоставлены с этими таблицами. В SQLAlchemy эти две задачи выполняются вместе. Это делается с помощью декларативной системы; созданные классы включают директивы для описания фактической таблицы базы данных, в которую они отображаются.

Базовый класс хранит каталог классов и сопоставленных таблиц в декларативной системе. Это называется декларативным базовым классом. Обычно будет только один экземпляр этой базы в обычно импортируемом модуле. Функция Declarative_base () используется для создания базового класса. Эта функция определена в модуле sqlalchemy.ext.declarative.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

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

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

Класс в Declarative должен иметь атрибут __tablename__ и хотя бы один столбец, который является частью первичного ключа. Декларативный заменяет все объекты Column специальными средствами доступа Python, известными как дескрипторы . Этот процесс известен как инструментарий, который предоставляет средства для обращения к таблице в контексте SQL и позволяет сохранять и загружать значения столбцов из базы данных.

Этот сопоставленный класс, как и обычный класс Python, имеет атрибуты и методы согласно требованию.

Информация о классе в декларативной системе, называется метаданными таблицы. SQLAlchemy использует объект Table для представления этой информации для конкретной таблицы, созданной в декларативном. Объект Table создается в соответствии со спецификациями и связывается с классом путем создания объекта Mapper. Этот объект сопоставления не используется напрямую, но используется внутри как интерфейс между отображаемым классом и таблицей.

Каждый объект Table является членом большой коллекции, известной как MetaData, и этот объект доступен с помощью атрибута .metadata декларативного базового класса. Метод MetaData.create_all () передает наш движок в качестве источника подключения к базе данных. Для всех таблиц, которые еще не были созданы, он выдает операторы CREATE TABLE в базу данных.

Base.metadata.create_all(engine)

Полный скрипт для создания базы данных и таблицы, а также для отображения класса Python приведен ниже —

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key=True)

   name = Column(String)
   address = Column(String)
   email = Column(String)
Base.metadata.create_all(engine)

При запуске консоль Python выдает эхо после выполнения выражения SQL —

CREATE TABLE customers (
   id INTEGER NOT NULL,
   name VARCHAR,
   address VARCHAR,
   email VARCHAR,
   PRIMARY KEY (id)
)

Если мы откроем Sales.db с помощью графического инструмента SQLiteStudio, в нем будет показана таблица клиентов с вышеупомянутой структурой.

SQLAlchemy ORM — Создание сеанса

Чтобы взаимодействовать с базой данных, нам нужно получить ее дескриптор. Объект сеанса является дескриптором базы данных. Класс сеанса определяется с помощью sessionmaker () — настраиваемого метода фабрики сеансов, который привязан к объекту механизма, созданному ранее.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)

Затем объект сеанса устанавливается с помощью конструктора по умолчанию следующим образом:

session = Session()

Некоторые из часто используемых методов класса сессии перечислены ниже —

Sr.No.	Метод и описание
1	начать() начинает транзакцию в этом сеансе
2	добавлять() помещает объект в сеанс. Его состояние сохраняется в базе данных при следующей операции сброса
3	добавить все() добавляет коллекцию объектов в сессию
4	совершить () сбрасывает все элементы и любую транзакцию в процессе
5	удалять() помечает транзакцию как удаленную
6	выполнить () выполняет выражение SQL
7	истекать () помечает атрибуты экземпляра как устаревшие
8	промывать() сбрасывает все изменения объекта в базу данных
9	недействительными () закрывает сеанс, используя недействительное соединение
10	откат () откатывает текущую транзакцию в процессе
11	близко() Закрытие текущего сеанса путем очистки всех элементов и прекращения любой транзакции в процессе выполнения

начать()

начинает транзакцию в этом сеансе

добавлять()

помещает объект в сеанс. Его состояние сохраняется в базе данных при следующей операции сброса

добавить все()

добавляет коллекцию объектов в сессию

совершить ()

сбрасывает все элементы и любую транзакцию в процессе

удалять()

помечает транзакцию как удаленную

выполнить ()

выполняет выражение SQL

истекать ()

помечает атрибуты экземпляра как устаревшие

промывать()

сбрасывает все изменения объекта в базу данных

недействительными ()

закрывает сеанс, используя недействительное соединение

откат ()

откатывает текущую транзакцию в процессе

близко()

Закрытие текущего сеанса путем очистки всех элементов и прекращения любой транзакции в процессе выполнения

SQLAlchemy ORM — Добавление объектов

В предыдущих главах SQLAlchemy ORM мы узнали, как объявлять сопоставления и создавать сеансы. В этой главе мы узнаем, как добавлять объекты в таблицу.

Мы объявили класс Customer, который был сопоставлен с таблицей клиентов. Мы должны объявить объект этого класса и постоянно добавлять его в таблицу методом add () объекта сеанса.

c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')
session.add(c1)

Обратите внимание, что эта транзакция ожидает, пока она не будет сброшена с использованием метода commit ().

session.commit()

Ниже приведен полный скрипт для добавления записи в таблицу клиентов:

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key=True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

c1 = Customers(name = 'Ravi Kumar', address = 'Station Road Nanded', email = '[email protected]')

session.add(c1)
session.commit()

Чтобы добавить несколько записей, мы можем использовать метод add_all () класса сеанса.

session.add_all([
   Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = '[email protected]'), 
   Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = '[email protected]'), 
   Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = '[email protected]')]
)

session.commit()

Табличное представление SQLiteStudio показывает, что записи постоянно добавляются в таблицу клиентов. Следующее изображение показывает результат —

SQLAlchemy ORM — Использование запросов

Все операторы SELECT, сгенерированные SQLAlchemy ORM, создаются объектом Query. Он обеспечивает генеративный интерфейс, поэтому последовательные вызовы возвращают новый объект Query, копию первого с дополнительными критериями и параметрами, связанными с ним.

Объекты запросов изначально создаются с использованием метода query () Session следующим образом:

q = session.query(mapped class)

Следующее утверждение также эквивалентно приведенному выше утверждению —

q = Query(mappedClass, session)

Объект запроса имеет метод all (), который возвращает набор результатов в виде списка объектов. Если мы выполним это на столе наших клиентов —

result = session.query(Customers).all()

Это утверждение фактически эквивалентно следующему выражению SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers

Объект результата можно просмотреть с помощью цикла For, как показано ниже, чтобы получить все записи в базовой таблице клиентов. Вот полный код для отображения всех записей в таблице «Клиенты» —

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key =  True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)

Консоль Python показывает список записей, как показано ниже —

Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]
Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

Объект Query также имеет следующие полезные методы:

Sr.No.	Метод и описание
1	add_columns () Он добавляет одно или несколько выражений столбцов в список возвращаемых столбцов результатов.
2	add_entity () Он добавляет сопоставленную сущность в список возвращаемых столбцов результатов.
3	кол — () Он возвращает количество строк, которые мог бы вернуть этот запрос.
4	удалять() Он выполняет массовый запрос на удаление. Удаляет строки, соответствующие этому запросу, из базы данных.
5	различны () Он применяет предложение DISTINCT к запросу и возвращает вновь полученный Запрос.
6	фильтр() Он применяет данный критерий фильтрации к копии этого запроса, используя выражения SQL.
7	первый() Возвращает первый результат этого запроса или None, если результат не содержит строки.
8	получить() Он возвращает экземпляр на основе заданного идентификатора первичного ключа, предоставляя прямой доступ к карте идентификации сеанса-владельца.
9	группа по() Он применяет один или несколько критериев GROUP BY к запросу и возвращает вновь полученный Запрос
10	присоединиться() Он создает SQL JOIN по критерию этого объекта Query и применяет его генеративно, возвращая вновь полученный Query.
11	один() Он возвращает ровно один результат или вызывает исключение.
12	Сортировать по() Он применяет один или несколько критериев ORDER BY к запросу и возвращает вновь полученный Запрос.
13	Обновить() Он выполняет массовый запрос на обновление и обновляет строки, соответствующие этому запросу, в базе данных.

add_columns ()

Он добавляет одно или несколько выражений столбцов в список возвращаемых столбцов результатов.

add_entity ()

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

кол — ()

Он возвращает количество строк, которые мог бы вернуть этот запрос.

удалять()

Он выполняет массовый запрос на удаление. Удаляет строки, соответствующие этому запросу, из базы данных.

различны ()

Он применяет предложение DISTINCT к запросу и возвращает вновь полученный Запрос.

фильтр()

Он применяет данный критерий фильтрации к копии этого запроса, используя выражения SQL.

первый()

Возвращает первый результат этого запроса или None, если результат не содержит строки.

получить()

Он возвращает экземпляр на основе заданного идентификатора первичного ключа, предоставляя прямой доступ к карте идентификации сеанса-владельца.

группа по()

Он применяет один или несколько критериев GROUP BY к запросу и возвращает вновь полученный Запрос

присоединиться()

Он создает SQL JOIN по критерию этого объекта Query и применяет его генеративно, возвращая вновь полученный Query.

один()

Он возвращает ровно один результат или вызывает исключение.

Сортировать по()

Он применяет один или несколько критериев ORDER BY к запросу и возвращает вновь полученный Запрос.

Обновить()

Он выполняет массовый запрос на обновление и обновляет строки, соответствующие этому запросу, в базе данных.

SQLAlchemy ORM — Обновление объектов

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

Чтобы изменить данные определенного атрибута любого объекта, мы должны присвоить ему новое значение и зафиксировать изменения, чтобы сделать это изменение постоянным.

Давайте извлечем объект из таблицы, чей идентификатор первичного ключа, в нашей таблице Customers с ID = 2. Мы можем использовать метод get () сессии следующим образом:

x = session.query(Customers).get(2)

Мы можем отобразить содержимое выбранного объекта с помощью приведенного ниже кода —

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Из таблицы наших клиентов должен отображаться следующий результат:

Name: Komal Pande Address: Koti, Hyderabad Email: [email protected]

Теперь нам нужно обновить поле Address, назначив новое значение, как указано ниже —

x.address = 'Banjara Hills Secunderabad'
session.commit()

Изменение будет постоянно отражаться в базе данных. Теперь мы извлекаем объект, соответствующий первой строке таблицы, используя метод first () следующим образом:

x = session.query(Customers).first()

Это выполнит следующее выражение SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
LIMIT ? OFFSET ?

Связанные параметры будут LIMIT = 1 и OFFSET = 0 соответственно, что означает, что будет выбран первый ряд.

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Теперь вывод для вышеуказанного кода, отображающего первую строку, выглядит следующим образом:

Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]

Теперь измените атрибут name и отобразите содержимое, используя приведенный ниже код —

x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Вывод вышеуказанного кода —

Name: Ravi Shrivastava Address: Station Road Nanded Email: [email protected]

Даже если изменение отображается, оно не зафиксировано. Вы можете сохранить прежнюю постоянную позицию, используя метод rollback () с кодом ниже.

session.rollback()

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Оригинальное содержание первой записи будет отображаться.

Для массовых обновлений мы будем использовать метод update () объекта Query. Давайте попробуем дать префикс «мистер» имя в каждой строке (кроме ID = 2). Соответствующий оператор update () выглядит следующим образом:

session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)

Метод update () требует два параметра следующим образом:

• Словарь значений ключей с ключом, являющимся атрибутом, подлежащим обновлению, и значением, являющимся новым содержимым атрибута.

• Атрибут synchronize_session с описанием стратегии обновления атрибутов в сеансе. Допустимые значения: false: для отсутствия синхронизации сеанса fetch: выполняет запрос на выборку перед обновлением, чтобы найти объекты, которые соответствуют запросу на обновление; и оценивать: оценивать критерии по объектам в сеансе.

Словарь значений ключей с ключом, являющимся атрибутом, подлежащим обновлению, и значением, являющимся новым содержимым атрибута.

Атрибут synchronize_session с описанием стратегии обновления атрибутов в сеансе. Допустимые значения: false: для отсутствия синхронизации сеанса fetch: выполняет запрос на выборку перед обновлением, чтобы найти объекты, которые соответствуют запросу на обновление; и оценивать: оценивать критерии по объектам в сеансе.

Три из четырех строк в таблице будут иметь имя с префиксом «Мистер» Однако изменения не зафиксированы и, следовательно, не будут отражены в табличном представлении SQLiteStudio. Он будет обновляться только тогда, когда мы фиксируем сессию.

SQLAlchemy ORM — применение фильтра

В этой главе мы обсудим, как применить фильтр, а также некоторые операции фильтра вместе с их кодами.

Resultset, представленный объектом Query, может быть подвергнут определенным критериям с помощью метода filter (). Общее использование метода фильтра следующее:

session.query(class).filter(criteria)

В следующем примере набор результатов, полученный с помощью запроса SELECT в таблице «Клиенты», фильтруется по условию (ID> 2) —

result = session.query(Customers).filter(Customers.id>2)

Это утверждение будет переводиться в следующее выражение SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ?

Поскольку связанный параметр (?) Задан как 2, будут отображаться только те строки с идентификатором столбца> 2. Полный код приведен ниже —

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).filter(Customers.id>2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Вывод, отображаемый в консоли Python, выглядит следующим образом:

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

SQLAlchemy ORM — операторы фильтров

Теперь мы изучим операции фильтрации с соответствующими кодами и выводом.

Равно

Обычно используется оператор ==, и он применяет критерии для проверки равенства.

result = session.query(Customers).filter(Customers.id == 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

SQLAlchemy отправит следующее выражение SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?

Выход для вышеуказанного кода выглядит следующим образом:

ID: 2 Name: Komal Pande Address: Banjara Hills Secunderabad Email: [email protected]

Не равно

Оператор, используемый для не равно, — это! =, И он предоставляет критерии не равно.

result = session.query(Customers).filter(Customers.id! = 2)

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Полученное выражение SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id != ?

Вывод для приведенных выше строк кода выглядит следующим образом:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

подобно

Сам метод like () создает критерии LIKE для предложения WHERE в выражении SELECT.

result = session.query(Customers).filter(Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Выше SQLAlchemy код эквивалентен следующему выражению SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.name LIKE ?

И вывод для вышеуказанного кода —

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

В

Этот оператор проверяет, принадлежит ли значение столбца к коллекции элементов в списке. Это обеспечивается методом in_ ().

result = session.query(Customers).filter(Customers.id.in_([1,3]))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Здесь выражение SQL, вычисляемое механизмом SQLite, будет выглядеть следующим образом:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id IN (?, ?)

Выход для вышеуказанного кода выглядит следующим образом:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

А ТАКЖЕ

Это соединение создается путем помещения нескольких критериев, разделенных запятыми, в фильтре или использования метода and_ (), как указано ниже —

result = session.query(Customers).filter(Customers.id>2, Customers.name.like('Ra%'))
for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

from sqlalchemy import and_
result = session.query(Customers).filter(and_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

Оба вышеуказанных подхода приводят к похожему выражению SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ? AND customers.name LIKE ?

Выход для вышеуказанных строк кода —

ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]

ИЛИ ЖЕ

Это соединение реализуется методом or_ () .

from sqlalchemy import or_
result = session.query(Customers).filter(or_(Customers.id>2, Customers.name.like('Ra%')))

for row in result:
   print ("ID:", row.id, "Name: ",row.name, "Address:",row.address, "Email:",row.email)

В результате движок SQLite получает следующее эквивалентное выражение SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id > ? OR customers.name LIKE ?

Выход для вышеуказанного кода выглядит следующим образом:

ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: [email protected]
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: [email protected]
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: [email protected]

Возвращение списка и скаляры

Существует несколько методов объекта Query, которые немедленно выдают SQL и возвращают значение, содержащее результаты загруженной базы данных.

Вот краткое изложение списка возврата и скаляров —

все()

Возвращает список. Ниже приведена строка кода для функции all ().

session.query(Customers).all()

Консоль Python отображает следующее выражение SQL:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers

первый()

Он применяет ограничение в единицу и возвращает первый результат в виде скаляра.

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
LIMIT ? OFFSET ?

Связанные параметры для LIMIT — 1, а для OFFSET — 0.

один()

Эта команда полностью выбирает все строки, и, если в результате нет ни одного идентификатора объекта или составной строки, возникает ошибка.

session.query(Customers).one()

С несколькими найденными рядами —

MultipleResultsFound: Multiple rows were found for one()

Строки не найдены —

NoResultFound: No row was found for one()

Метод one () полезен для систем, которые по-разному будут обрабатывать «элементы не найдены» и «несколько элементов найдены».

скаляр ()

Он вызывает метод one () и в случае успеха возвращает первый столбец строки следующим образом:

session.query(Customers).filter(Customers.id == 3).scalar()

Это генерирует следующую инструкцию SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?

SQLAlchemy ORM — текстовый SQL

Ранее текстовый SQL с использованием функции text () был объяснен с точки зрения основного языка выражений SQLAlchemy. Теперь мы обсудим это с точки зрения ORM.

Литеральные строки могут гибко использоваться с объектом Query, определяя их использование с помощью конструкции text (). Большинство применимых методов принимают это. Например, filter () и order_by ().

В приведенном ниже примере метод filter () переводит строку «id <3» в идентификатор WHERE <3

from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
   print(cust.name)

Сгенерированное необработанное выражение SQL показывает преобразование фильтра в предложение WHERE с помощью приведенного ниже кода:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE id<3

Из наших примеров данных в таблице «Клиенты» будут выбраны две строки и столбец с именем будет напечатан следующим образом:

Ravi Kumar
Komal Pande

Чтобы указать параметры связывания с SQL на основе строк, используйте двоеточие, а для указания значений используйте метод params ().

cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()

Эффективный SQL, отображаемый на консоли Python, будет таким, как указано ниже —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE id = ?

Чтобы использовать полностью строковый оператор, в метод from_statement () можно передать конструкцию text (), представляющую полный оператор.

session.query(Customers).from_statement(text("SELECT * FROM customers")).all()

Результат приведенного выше кода будет основным оператором SELECT, как показано ниже:

SELECT * FROM customers

Очевидно, что все записи в таблице клиентов будут выбраны.

Конструкция text () позволяет позиционно связать его текстовый SQL с выражениями столбца Core или ORM. Мы можем достичь этого, передавая выражения столбцов в качестве позиционных аргументов в метод TextClause.columns ().

stmt = text("SELECT name, id, name, address, email FROM customers")
stmt = stmt.columns(Customers.id, Customers.name)
session.query(Customers.id, Customers.name).from_statement(stmt).all()

Столбцы id и name всех строк будут выбраны, даже если механизм SQLite выполняет следующее выражение, сгенерированное приведенным выше кодом, и отображает все столбцы в методе text () —

SELECT name, id, name, address, email FROM customers

SQLAlchemy ORM — построение отношений

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

Используя декларативное, мы определяем эту таблицу вместе с отображаемым классом Invoices, как показано ниже —

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

class Invoice(Base):
   __tablename__ = 'invoices'
   
   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")

Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")
Base.metadata.create_all(engine)

Это отправит запрос CREATE TABLE на движок SQLite, как показано ниже:

CREATE TABLE invoices (
   id INTEGER NOT NULL,
   custid INTEGER,
   invno INTEGER,
   amount INTEGER,
   PRIMARY KEY (id),
   FOREIGN KEY(custid) REFERENCES customers (id)
)

Мы можем проверить, что новая таблица создается в sales.db с помощью инструмента SQLiteStudio.

Класс Invoices применяет конструкцию ForeignKey к атрибуту custid. Эта директива указывает, что значения в этом столбце должны быть ограничены значениями, указанными в столбце id в таблице клиентов. Это основная особенность реляционных баз данных, и это «клей», который преобразует несвязанный набор таблиц, чтобы иметь богатые перекрывающиеся отношения.

Вторая директива, известная как отношением (), сообщает ORM, что класс Invoice должен быть связан с классом Customer с помощью атрибута Invoice.customer. Отношение () использует отношения внешнего ключа между этими двумя таблицами, чтобы определить природу этой связи, определяя, что это много к одной.

Дополнительная директива отношения () помещается в сопоставленный класс Customer под атрибутом Customer.invoices. Параметр relations.back_populate назначается для ссылки на имена дополнительных атрибутов, так что каждая связь () может принимать интеллектуальное решение относительно той же самой связи, которая выражена в обратном порядке. С одной стороны, Invoices.customer ссылается на экземпляр Invoices, а с другой стороны, Customer.invoices ссылается на список экземпляров Customer.

Функция отношений является частью API отношений пакета ORM SQLAlchemy. Он обеспечивает связь между двумя сопоставленными классами. Это соответствует родительскому или дочернему табличному отношению.

Ниже приведены основные шаблоны отношений —

Один ко многим

Отношение «один ко многим» относится к родителю с помощью внешнего ключа на дочерней таблице. Затем для родителя указывается отношение () как ссылка на коллекцию элементов, представленных дочерним элементом. Параметр relations.back_poplates используется для установления двунаправленного отношения «один-ко-многим», где «обратная» сторона — это «многие к одному».

Много к одному

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

Один к одному

Отношения «один к одному», по сути, являются двусторонними отношениями по своей природе. Флаг списка использования указывает размещение скалярного атрибута вместо коллекции на стороне «многие» отношения. Чтобы преобразовать отношения один-ко-многим в тип отношения один-к-одному, установите для параметра uselist значение false.

Много ко многим

Отношение «многие ко многим» устанавливается путем добавления таблицы ассоциации, связанной с двумя классами, путем определения атрибутов с их внешними ключами. На это указывает вторичный аргумент отношения (). Обычно в таблице используется объект MetaData, связанный с декларативным базовым классом, так что директивы ForeignKey могут находить удаленные таблицы, с которыми можно связываться. Параметр relations.back_poplates для каждой взаимосвязи () устанавливает двунаправленную связь. Обе стороны отношений содержат коллекцию.

Работа со связанными объектами

В этой главе мы сосредоточимся на связанных объектах в SQLAlchemy ORM.

Теперь, когда мы создаем объект Customer, будет представлена ​​пустая коллекция счетов в форме списка Python.

c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "[email protected]")

Атрибут invoices в c1.invoices будет пустым списком. Мы можем назначить элементы в списке как —

c1.invoices = [Invoice(invno = 10, amount = 15000), Invoice(invno = 14, amount = 3850)]

Давайте передадим этот объект в базу данных, используя объект Session следующим образом:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(c1)
session.commit()

Это автоматически сгенерирует запросы INSERT для таблиц клиентов и счетов —

INSERT INTO customers (name, address, email) VALUES (?, ?, ?) 
('Gopal Krishna', 'Bank Street Hydarebad', '[email protected]')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)

Давайте теперь посмотрим на содержимое таблицы клиентов и таблицы счетов в табличном представлении SQLiteStudio —

Вы можете создать объект Customer, предоставив сопоставленный атрибут счетов в самом конструкторе, используя команду ниже —

c2 = [
   Customer(
      name = "Govind Pant", 
      address = "Gulmandi Aurangabad",
      email = "[email protected]",
      invoices = [Invoice(invno = 3, amount = 10000), 
      Invoice(invno = 4, amount = 5000)]
   )
]

Или список объектов, которые будут добавлены с использованием функции add_all () объекта сеанса, как показано ниже —

rows = [
   Customer(
      name = "Govind Kala", 
      address = "Gulmandi Aurangabad", 
      email = "[email protected]", 
      invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),

   Customer(
      name = "Abdul Rahman", 
      address = "Rohtak", 
      email = "[email protected]",
      invoices = [Invoice(invno = 9, amount = 15000), 
      Invoice(invno = 11, amount = 6000)
   ])
]

session.add_all(rows)
session.commit()

SQLAlchemy ORM — Работа с объединениями

Теперь, когда у нас есть две таблицы, мы увидим, как создавать запросы для обеих таблиц одновременно. Чтобы создать простое неявное соединение между Customer и Invoice, мы можем использовать Query.filter (), чтобы приравнять их связанные столбцы. Ниже мы сразу загружаем объекты Customer и Invoice, используя этот метод —

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for c, i in session.query(Customer, Invoice).filter(Customer.id == Invoice.custid).all():
   print ("ID: {} Name: {} Invoice No: {} Amount: {}".format(c.id,c.name, i.invno, i.amount))

Выражение SQL, генерируемое SQLAlchemy, выглядит следующим образом:

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM customers, invoices
WHERE customers.id = invoices.custid

И результат приведенных выше строк кода выглядит следующим образом:

ID: 2 Name: Gopal Krishna Invoice No: 10 Amount: 15000
ID: 2 Name: Gopal Krishna Invoice No: 14 Amount: 3850
ID: 3 Name: Govind Pant Invoice No: 3 Amount: 10000
ID: 3 Name: Govind Pant Invoice No: 4 Amount: 5000
ID: 4 Name: Govind Kala Invoice No: 7 Amount: 12000
ID: 4 Name: Govind Kala Invoice No: 8 Amount: 8500
ID: 5 Name: Abdul Rahman Invoice No: 9 Amount: 15000
ID: 5 Name: Abdul Rahman Invoice No: 11 Amount: 6000

Фактический синтаксис SQL JOIN легко достигается с помощью метода Query.join () следующим образом:

session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()

Выражение SQL для объединения будет отображаться на консоли —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers JOIN invoices ON customers.id = invoices.custid
WHERE invoices.amount = ?

Мы можем перебрать результат, используя цикл for —

result = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500)
for row in result:
   for inv in row.invoices:
      print (row.id, row.name, inv.invno, inv.amount)

При 8500 в качестве параметра связывания отображается следующий вывод:

4 Govind Kala 8 8500  

Query.join () знает, как соединить эти таблицы, потому что между ними есть только один внешний ключ. Если не было внешних ключей или было больше внешних ключей, Query.join () работает лучше, когда используется одна из следующих форм:

query.join (Счет, id == Address.custid)	явное условие
query.join (Customer.invoices)	указать отношения слева направо
query.join (Invoice, Customer.invoices)	то же самое, с явной целью
query.join ( ‘счета’)	то же самое, используя строку

Точно так же функция externaljoin () доступна для достижения внешнего левого соединения.

query.outerjoin(Customer.invoices)

Метод subquery () создает выражение SQL, представляющее инструкцию SELECT, встроенную в псевдоним.

from sqlalchemy.sql import func

stmt = session.query(
   Invoice.custid, func.count('*').label('invoice_count')
).group_by(Invoice.custid).subquery()

Объект stmt будет содержать инструкцию SQL, как показано ниже:

SELECT invoices.custid, count(:count_1) AS invoice_count FROM invoices GROUP BY invoices.custid

Когда у нас есть наше утверждение, оно ведет себя как конструкция таблицы. Столбцы в выражении доступны через атрибут с именем c, как показано в приведенном ниже коде —

for u, count in session.query(Customer, stmt.c.invoice_count).outerjoin(stmt, Customer.id == stmt.c.custid).order_by(Customer.id):
   print(u.name, count)

Вышеуказанный цикл for отображает количество счетов по именам следующим образом:

Arjun Pandit None
Gopal Krishna 2
Govind Pant 2
Govind Kala 2
Abdul Rahman 2

Общие операторы отношений

В этой главе мы обсудим операторы, основанные на отношениях.

__eq __ ()

Вышеприведенный оператор представляет собой сравнение «равных». Строка кода для этого оператора, как показано ниже —

s = session.query(Customer).filter(Invoice.invno.__eq__(12))

Эквивалентный SQL-запрос для приведенной выше строки кода —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.invno = ?

__ne __ ()

Этот оператор представляет собой сравнение «не равно». Строка кода для этого оператора, как показано ниже —

s = session.query(Customer).filter(Invoice.custid.__ne__(2))

Эквивалентный SQL-запрос для приведенной выше строки кода приведен ниже —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers, invoices
WHERE invoices.custid != ?

содержит()

Этот оператор используется для коллекций «один ко многим», и ниже приведен код для метода contains () —

s = session.query(Invoice).filter(Invoice.invno.contains([3,4,5]))

Эквивалентный SQL-запрос для приведенной выше строки кода —

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE (invoices.invno LIKE '%' + ? || '%')

любой()

Оператор any () используется для коллекций, как показано ниже —

s = session.query(Customer).filter(Customer.invoices.any(Invoice.invno==11))

Эквивалентный SQL-запрос для приведенной выше строки кода показан ниже —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE EXISTS (
   SELECT 1
   FROM invoices
   WHERE customers.id = invoices.custid 
   AND invoices.invno = ?)

есть ()

Этот оператор используется для скалярных ссылок следующим образом:

s = session.query(Invoice).filter(Invoice.customer.has(name = 'Arjun Pandit'))

Эквивалентный SQL-запрос для приведенной выше строки кода —

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount
FROM invoices
WHERE EXISTS (
   SELECT 1
   FROM customers
   WHERE customers.id = invoices.custid 
   AND customers.name = ?)

SQLAlchemy ORM — энергичная загрузка

Стремительная загрузка уменьшает количество запросов. SQLAlchemy предлагает активные функции загрузки, вызываемые через параметры запроса, которые дают дополнительные инструкции для запроса. Эти параметры определяют, как загружать различные атрибуты с помощью метода Query.options ().

Загрузка подзапроса

Мы хотим, чтобы Customer.invoices загружались с нетерпением. Опция orm.subqueryload () дает второй оператор SELECT, который полностью загружает коллекции, связанные с только что загруженными результатами. Имя «подзапрос» приводит к тому, что оператор SELECT создается непосредственно через повторно используемый запрос и внедряется как подзапрос в SELECT для связанной таблицы.

from sqlalchemy.orm import subqueryload
c1 = session.query(Customer).options(subqueryload(Customer.invoices)).filter_by(name = 'Govind Pant').one()

Это приводит к следующим двум выражениям SQL —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.name = ?
('Govind Pant',)

SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount 
AS invoices_amount, anon_1.customers_id 
AS anon_1_customers_id
FROM (
   SELECT customers.id 
   AS customers_id
   FROM customers
   WHERE customers.name = ?) 
   
AS anon_1 
JOIN invoices 
ON anon_1.customers_id = invoices.custid 
ORDER BY anon_1.customers_id, invoices.id 2018-06-25 18:24:47,479 
INFO sqlalchemy.engine.base.Engine ('Govind Pant',)

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

print (c1.name, c1.address, c1.email)

for x in c1.invoices:
   print ("Invoice no : {}, Amount : {}".format(x.invno, x.amount))

Вывод вышеуказанной программы следующий:

Govind Pant Gulmandi Aurangabad [email protected]
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000

Объединенная нагрузка

Другая функция называется orm.joinedload (). Это испускает левое внешнее соединение. Ведущий объект, а также связанный объект или коллекция загружаются за один шаг.

from sqlalchemy.orm import joinedload
c1 = session.query(Customer).options(joinedload(Customer.invoices)).filter_by(name='Govind Pant').one()

Это выдает следующее выражение, дающее тот же результат, что и выше —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email, invoices_1.id 
AS invoices_1_id, invoices_1.custid 
AS invoices_1_custid, invoices_1.invno 
AS invoices_1_invno, invoices_1.amount 
AS invoices_1_amount

FROM customers 
LEFT OUTER JOIN invoices 
AS invoices_1 
ON customers.id = invoices_1.custid

WHERE customers.name = ? ORDER BY invoices_1.id
('Govind Pant',)

В результате OUTER JOIN появилось две строки, но он возвращает один экземпляр Customer. Это связано с тем, что Query применяет «уникальную» стратегию, основанную на идентичности объекта, к возвращаемым объектам. Присоединенная готовая загрузка может быть применена, не затрагивая результаты запроса.

Функция subqueryload () больше подходит для загрузки связанных коллекций, в то время как joinload () лучше подходит для отношений «многие к одному».

SQLAlchemy ORM — удаление связанных объектов

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

В нашей базе данных sales.db классы Customer и Invoice сопоставляются с таблицей customer и invoice с типом взаимосвязи один-ко-многим. Мы постараемся удалить объект Customer и увидим результат.

В качестве краткой справки ниже приведены определения классов Customer и Invoice —

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship
class Customer(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)
   
class Invoice(Base):
   __tablename__ = 'invoices'

   id = Column(Integer, primary_key = True)
   custid = Column(Integer, ForeignKey('customers.id'))
   invno = Column(Integer)
   amount = Column(Integer)
   customer = relationship("Customer", back_populates = "invoices")
   
Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")

Мы настраиваем сеанс и получаем объект Customer, запрашивая его с помощью основного идентификатора, используя следующую программу:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)

В нашей таблице примеров x.name — это «Гопал Кришна». Давайте удалим этот x из сеанса и посчитаем вхождение этого имени.

session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()

Полученное выражение SQL вернет 0.

SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',) 0

Однако связанные объекты Invoice для x все еще там. Это можно проверить с помощью следующего кода —

session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

Здесь 10 и 14 — номера счетов, принадлежащих клиенту Гопалу Кришне. Результатом вышеупомянутого запроса является 2, что означает, что связанные объекты не были удалены.

SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14) 2

Это потому, что SQLAlchemy не предполагает удаления каскада; мы должны дать команду, чтобы удалить его.

Чтобы изменить поведение, мы настраиваем параметры каскада в отношении User.addresses. Давайте закроем текущую сессию, воспользуемся новой Declarative_base () и повторно объявим класс User, добавив в отношения адресов, включая конфигурацию каскада.

Атрибут cascade в функции отношений представляет собой разделенный запятыми список правил каскада, который определяет, как операции «Сеанс» должны быть «каскадными» от родителя к потомку. По умолчанию это False, что означает «save-update, merge».

Доступны следующие каскады:

• копи-обновление
• сливаться
• вычеркивать
• удалять
• удалить-сирота
• обновить устареть

Часто используемой опцией является «all, delete-orphan», чтобы указать, что связанные объекты должны следовать вместе с родительским объектом во всех случаях, и удаляться при деассоциировании.

Следовательно, повторно объявленный класс Customer показан ниже —

class Customer(Base): 
   __tablename__ = 'customers'
   
   id = Column(Integer, primary_key = True) 
   name = Column(String) 
   address = Column(String) 
   email = Column(String) 
   invoices = relationship(
      "Invoice", 
      order_by = Invoice.id, 
      back_populates = "customer",
      cascade = "all, 
      delete, delete-orphan" 
   )

Давайте удалим Клиента с именем Gopal Krishna, используя приведенную ниже программу, и посмотрим количество связанных объектов Invoice —

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
x = session.query(Customer).get(2)
session.delete(x)
session.query(Customer).filter_by(name = 'Gopal Krishna').count()
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()

Счетчик теперь равен 0 с последующим SQL, выдаваемым вышеприведенным скриптом —

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
WHERE customers.id = ?
(2,)
SELECT invoices.id 
AS invoices_id, invoices.custid 
AS invoices_custid, invoices.invno 
AS invoices_invno, invoices.amount
AS invoices_amount
FROM invoices
WHERE ? = invoices.custid 
ORDER BY invoices.id (2,)
DELETE FROM invoices 
WHERE invoices.id = ? ((1,), (2,))
DELETE FROM customers 
WHERE customers.id = ? (2,)
SELECT count(*) 
AS count_1
FROM (
   SELECT customers.id 
   AS customers_id, customers.name 
   AS customers_name, customers.address 
   AS customers_address, customers.email 
   AS customers_email
   FROM customers
   WHERE customers.name = ?) 
AS anon_1('Gopal Krishna',)
SELECT count(*) 
AS count_1
FROM (
   SELECT invoices.id 
   AS invoices_id, invoices.custid 
   AS invoices_custid, invoices.invno 
   AS invoices_invno, invoices.amount 
   AS invoices_amount
   FROM invoices
   WHERE invoices.invno IN (?, ?)) 
AS anon_1(10, 14)
0

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

Соотношение «многие ко многим» между двумя таблицами достигается путем добавления таблицы ассоциации так, чтобы у нее было два внешних ключа — по одному от первичного ключа каждой таблицы. Кроме того, классы, отображаемые на две таблицы, имеют атрибут с набором объектов других таблиц ассоциаций, назначенный в качестве вторичного атрибута функции Relations ().

Для этого мы создадим базу данных SQLite (mycollege.db) с двумя таблицами — отдел и сотрудник. Здесь мы предполагаем, что сотрудник является частью более чем одного отдела, а в отделе работает более одного сотрудника. Это составляет отношение многих ко многим.

Определение классов сотрудников и отделов, сопоставленных с таблицей отделов и сотрудников, выглядит следующим образом:

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///mycollege.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy.orm import relationship

class Department(Base):
   __tablename__ = 'department'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   employees = relationship('Employee', secondary = 'link')
   
class Employee(Base):
   __tablename__ = 'employee'
   id = Column(Integer, primary_key = True)
   name = Column(String)
   departments = relationship(Department,secondary='link')

Теперь мы определим класс Link. Он связан с таблицей ссылок и содержит атрибуты Department_id и employee_id, соответственно ссылающиеся на первичные ключи таблицы отделов и сотрудников.

class Link(Base):
   __tablename__ = 'link'
   department_id = Column(
      Integer, 
      ForeignKey('department.id'), 
      primary_key = True)

employee_id = Column(
   Integer, 
   ForeignKey('employee.id'), 
   primary_key = True)

Здесь мы должны отметить, что у класса Department есть атрибут сотрудников, связанный с классом Employee. Вторичному атрибуту функции отношения назначается ссылка в качестве значения.

Аналогично, класс Employee имеет атрибут отделов, связанный с классом Department. Вторичному атрибуту функции отношения назначается ссылка в качестве значения.

Все эти три таблицы создаются при выполнении следующего оператора:

Base.metadata.create_all(engine)

Консоль Python генерирует следующие запросы CREATE TABLE —

CREATE TABLE department (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE employee (
   id INTEGER NOT NULL,
   name VARCHAR,
   PRIMARY KEY (id)
)

CREATE TABLE link (
   department_id INTEGER NOT NULL,
   employee_id INTEGER NOT NULL,
   PRIMARY KEY (department_id, employee_id),
   FOREIGN KEY(department_id) REFERENCES department (id),
   FOREIGN KEY(employee_id) REFERENCES employee (id)
)

Мы можем проверить это, открыв mycollege.db с помощью SQLiteStudio, как показано на скриншотах ниже:

Затем мы создаем три объекта класса Department и три объекта класса Employee, как показано ниже:

d1 = Department(name = "Accounts")
d2 = Department(name = "Sales")
d3 = Department(name = "Marketing")

e1 = Employee(name = "John")
e2 = Employee(name = "Tony")
e3 = Employee(name = "Graham")

Каждая таблица имеет атрибут коллекции, имеющий метод append (). Мы можем добавить объекты Employee в коллекцию Employees объекта Department. Точно так же мы можем добавить объекты Department в атрибут коллекции департаментов объектов Employee.

e1.departments.append(d1)
e2.departments.append(d3)
d1.employees.append(e3)
d2.employees.append(e2)
d3.employees.append(e1)
e3.departments.append(d2)

Все, что нам нужно сделать сейчас, это настроить объект сеанса, добавить все объекты к нему и зафиксировать изменения, как показано ниже —

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
session.add(e1)
session.add(e2)
session.add(d1)
session.add(d2)
session.add(d3)
session.add(e3)
session.commit()

Следующие операторы SQL будут генерироваться на консоли Python —

INSERT INTO department (name) VALUES (?) ('Accounts',)
INSERT INTO department (name) VALUES (?) ('Sales',)
INSERT INTO department (name) VALUES (?) ('Marketing',)
INSERT INTO employee (name) VALUES (?) ('John',)
INSERT INTO employee (name) VALUES (?) ('Graham',)
INSERT INTO employee (name) VALUES (?) ('Tony',)
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

Чтобы проверить влияние вышеуказанных операций, используйте SQLiteStudio и просматривайте данные в таблицах отделов, сотрудников и ссылок —

Чтобы отобразить данные, выполните следующую инструкцию запроса —

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()

for x in session.query( Department, Employee).filter(Link.department_id == Department.id, 
   Link.employee_id == Employee.id).order_by(Link.department_id).all():
   print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))

Согласно данным, приведенным в нашем примере, вывод будет отображаться как показано ниже:

Department: Accounts Name: John
Department: Accounts Name: Graham
Department: Sales Name: Graham
Department: Sales Name: Tony
Department: Marketing Name: John
Department: Marketing Name: Tony

SQLAlchemy — Диалекты

SQLAlchemy использует систему диалектов для связи с различными типами баз данных. Каждая база данных имеет соответствующую оболочку DBAPI. Все диалекты требуют, чтобы был установлен соответствующий драйвер DBAPI.

Следующие диалекты включены в SQLAlchemy API —

• жар-птица
• Microsoft SQL Server
• MySQL
• оракул
• PostgreSQL
• SQL
• Sybase

Объект Engine, основанный на URL, создается функцией create_engine (). Эти URL могут включать имя пользователя, пароль, имя хоста и имя базы данных. Могут быть необязательные ключевые аргументы для дополнительной настройки. В некоторых случаях допускается путь к файлу, а в других «имя источника данных» заменяет части «хост» и «база данных». Типичная форма URL базы данных выглядит следующим образом:

dialect+driver://username:password@host:port/database

PostgreSQL

Диалект PostgreSQL использует psycopg2 в качестве DBAPI по умолчанию. pg8000 также доступен в качестве замены Python, как показано ниже:

# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

MySQL

Диалект MySQL использует mysql-python в качестве DBAPI по умолчанию. Есть много доступных MySQL DBAPI, таких как MySQL-connector-python:

# default
engine = create_engine('mysql://scott:tiger@localhost/foo')

# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

оракул

Диалект Oracle использует cx_oracle в качестве DBAPI по умолчанию следующим образом:

engine = create_engine('oracle://scott:[email protected]:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

Microsoft SQL Server

Диалект SQL Server использует pyodbc в качестве DBAPI по умолчанию. Pymssql также доступна.

# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

SQLite

SQLite подключается к файловым базам данных, используя встроенный модуль Python sqlite3 по умолчанию. Поскольку SQLite подключается к локальным файлам, формат URL немного отличается. Часть файла «URL» — это имя файла базы данных. Для относительного пути к файлу для этого требуется три слеша, как показано ниже —

engine = create_engine('sqlite:///foo.db')

А для абсолютного пути к файлу за тремя косыми чертами следует абсолютный путь, как показано ниже —

engine = create_engine('sqlite:///C:\\path\\to\\foo.db')

Чтобы использовать SQLite: memory: database, укажите пустой URL, как показано ниже —

engine = create_engine('sqlite://')

Заключение

В первой части этого руководства мы узнали, как использовать язык выражений для выполнения операторов SQL. Язык выражений встраивает конструкции SQL в код Python. Во второй части мы обсудили возможность сопоставления объектных отношений в SQLAlchemy. ORM API отображает таблицы SQL с помощью классов Python.