Sqlalchemy 简明教程
SQLAlchemy - Introduction
SQLAlchemy 是一个流行的 SQL 工具包和 Object Relational Mapper 。它用 Python 编写，为应用程序开发人员提供了 SQL 的全部功能和灵活性。它是一个 open source 和 cross-platform software ，在 MIT 许可证下发布。
SQLAlchemy 以其对象关系映射器 (ORM) 而闻名，可以使用它，将类映射到数据库，从而允许对象模型和数据库模式从一开始就以一种干净解耦的方式开发。
随着 SQL 数据库的规模和性能开始变得重要，它们的行为不像对象集合。另一方面，随着对象集合中的抽象开始变得重要，它们的行为不像表和行。SQLAlchemy 旨在适应这两个原则。
出于这个原因，它采用了 data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs 。使用 SQLAlchemy，将从不同的角度看待数据库和 SQL。
Michael Bayer 是 SQLAlchemy 的原始作者。它的初始版本发布于 2006 年 2 月。最新版本编号为 1.2.7，最近于 2018 年 4 月发布。
What is ORM?
ORM（对象关系映射）是一种编程技术，用于在面向对象编程语言的不兼容类型系统之间转换数据。通常，在面向对象 (OO) 语言（如 Python）中使用的类型系统包含非标量类型。这些不能表示为整数和字符串之类的基元类型。因此，OO 编程器必须将标量数据中的对象转换为与后端数据库交互。然而，在大多数数据库产品（如 Oracle、MySQL 等）中的数据类型是主要的。
在 ORM 系统中，每个类都映射到底层数据库中的一个表。ORM 负责处理这些问题，让你专注于对系统逻辑进行编程，而不再需要自己编写乏味的数据库接口代码。
SQLAlchemy - Environment setup
让我们讨论使用 SQLAlchemy 所需的环境设置。
必须使用高于 2.7 的任何 Python 版本才能安装 SQLAlchemy。最简单的安装方法是使用 Python 包管理器 pip 。此实用程序与 Python 的标准发行版捆绑在一起。
pip install sqlalchemy使用上述命令，我们可以从 python.org 下载 SQLAlchemy 的 latest released version ，并将其安装到系统中。
对于 Python 的 conda 发行版，可以使用 conda terminal 通过以下命令安装 SQLAlchemy：
conda install -c anaconda sqlalchemy也可以从下列源代码安装 SQLAlchemy：
python setup.py installSQLAlchemy 被设计为使用专为某个特定数据库而构建的 DBAPI 实现来运行。它使用方言系统与各种类型的 DBAPI 实现和数据库通信。所有方言都要求已安装相应的 DBAPI 驱动程序。
以下是包含的方言：
-
Firebird
-
Microsoft SQL Server
-
MySQL
-
Oracle
-
PostgreSQL
-
SQLite
-
Sybase
要检查 SQLAlchemy 是否正确安装以及了解其版本，请在 Python 提示符中输入以下命令：
>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'SQLAlchemy Core – Expression Language
SQLAlchemy 核心包括 SQL rendering engine, DBAPI integration, transaction integration 和 schema description services 。SQLAlchemy 核心使用 SQL 表达式语言，提供 schema-centric usage 范式，而 SQLAlchemy ORM 则是 domain-centric mode of usage 。
SQL 表达式语言呈现了一个使用 Python 构造来表示关系数据库结构和表达式的系统。它呈现了一个系统来直接表示关系数据库的原始构造，这不同于 ORM，ORM 呈现了一个高级的、抽象化的用法模式，它本身就是对表达式语言应用用法的示例。
表达式语言是 SQLAlchemy 的核心组件之一。它允许程序员在 Python 代码中指定 SQL 语句，并直接将其用于更加复杂的查询。表达式语言不依赖于后端，并且全面涵盖了原始 SQL 的各个方面。它比 SQLAlchemy 中的任何其他组件都更接近原始 SQL。
表达式语言直接表示关系数据库的原始构造。由于 ORM 基于表达式语言之上，因此典型的 Python 数据库应用程序可能重叠使用两者的用法。该应用程序可以单独使用表达式语言，尽管它必须定义自己的系统，将应用程序概念转换为各个数据库查询。
表达式语言的语句将由 SQLAlchemy 引擎转换为相应的原始 SQL 查询。现在，我们将学习如何创建引擎并借助于它执行各种 SQL 查询。
SQLAlchemy Core - Connecting to Database
在上一章中，我们讨论了 SQLAlchemy 中的表达式语言。现在，让我们继续执行连接到数据库的步骤。
引擎类连接一个 Pool and Dialect together 来提供数据库 connectivity and behavior 的源。使用 create_engine() 函数对引擎类的一个对象进行实例化。
create_engine() 函数将数据库作为单个参数。数据库不需要在任何地方进行定义。标准调用形式必须将 URL 作为第一个位置参数进行发送，它通常是一个表示数据库方言和连接参数的字符串。使用下面提供的代码，我们可以创建一个数据库。
>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///college.db', echo = True)对于 MySQL database ，使用以下命令−
engine = create_engine("mysql://user:pwd@localhost/college",echo = True)为了具体指定要用于连接的 DB-API ， URL string 需要采用以下形式−
dialect[+driver]://user:password@host/dbname例如，如果你正在使用 PyMySQL driver with MySQL ，使用以下命令 −
mysql+pymysql://<username>:<password>@<host>/<dbname>echo flag 是设置 SQLAlchemy 日志记录的一个快捷方式，它是通过 Python 的标准日志记录模块来完成的。在后续章节中，我们将学习所有生成的 SQL。若要隐藏冗余输出，请将 echo 属性设置为 None 。create_engine() 函数的其他参数可能是方言专有的。
create_engine() 函数返回一个 Engine object 。引擎类的某些重要方法是 −
Sr.No. |
Method & Description |
1 |
connect() Returns connection object |
2 |
execute() 执行 SQL 语句构造 |
3 |
begin() 返回一个上下文管理器，提供一个已建立事务的连接。在操作成功时，事务被提交，否则会回滚 |
4 |
dispose() 处理引擎所用的连接池 |
5 |
driver() 引擎正在使用的方言的驱动程序名称 |
6 |
table_names() 返回数据库中所有可用的表名的列表 |
7 |
transaction() 在事务边界内执行已提供函数 |
SQLAlchemy Core - Creating Table
现在，让我们讨论如何使用创建表函数。
SQL 表达式语言针对表列构建其表达式。SQLAlchemy Column 对象表示数据库表中的 column ，数据库表反过来表示为 Tableobject 。Metadata 包含表定义和关联的对象，例如索引、视图、触发器等。
因此，SQLAlchemy Metadata 中 MetaData 类的对象是 Table 对象及它们关联的架构构造的集合。它包含一组 Table 对象，以及与引擎或连接可选绑定。
from sqlalchemy import MetaData
meta = MetaData()MetaData 类的构造函数可以具有默认情况下为 None 的 bind 和 schema 参数。
接下来，我们在上述元数据目录中使用 the Table construct 定义所有表，该对象类似于常规的 SQL CREATE TABLE 语句。
Table 类的对象表示数据库中某个对应的表。它的构造函数接受以下参数：
Name |
Name of the table |
Metadata |
包含此表的对象 |
Column(s) |
一个或多个列类的对象 |
Column 对象表示 database table 中的 column 。构造函数接受名称、类型和其他参数，例如 primary_key、autoincrement 及其他约束。
SQLAlchemy 将 Python 数据匹配到其中定义的最佳通用列数据类型。其中一些通用数据类型如下：
-
BigInteger
-
Boolean
-
Date
-
DateTime
-
Float
-
Integer
-
Numeric
-
SmallInteger
-
String
-
Text
-
Time
要在 college 数据库中创建一个 students table ，请使用以下代码段：
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() 函数使用此引擎对象创建所有定义的表对象，并将信息存储在元数据中。
meta.create_all(engine)下面给出了完成的代码，它将在其中创建一张 students 表，生成 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)由于 create_engine() 函数的 echo 属性设置为 True ，因此控制台将显示表的实际 SQL 查询，如下所示：
CREATE TABLE students (
id INTEGER NOT NULL,
name VARCHAR,
lastname VARCHAR,
PRIMARY KEY (id)
)college.db 将在当前工作目录中创建。要检查 students 表是否创建，你可以使用任何 SQLite GUI 工具（如 SQLiteStudio ）来打开该数据库。
下图显示了在数据库中创建的 students 表：
SQLAlchemy Core - SQL Expressions
在本章中，我们将简要关注 SQL 表达式及其函数。
SQL 表达式是使用相对于目标表对象的对应方法构建的。例如，可以通过执行 insert() 方法来创建 INSERT 语句，如下所示：
ins = students.insert()上述方法的结果是一个可以利用 str() 函数验证的插入对象。以下代码插入详细信息，如学生 id、名字、姓氏。
'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)'Python 控制台上回显的 SQL 不会显示实际值（本例中为“Karan”）。SQLAlchemy 会生成一个绑定参数，它在语句的已编译表单中可见。
ins.compile().params
{'name': 'Karan'}同样， update(), delete() 和 select() 等方法分别创建 UPDATE、DELETE 和 SELECT 表达式。后面的章节中将会学习这些表达式的相关内容。
SQLAlchemy Core - Executing Expression
在上一章中，我们了解了 SQL 表达式。本章中，我们将会了解如何执行这些表达式。
要执行所得的 SQL 表达式，必须首先 obtain a connection object representing an actively checked out DBAPI connection resource 并 feed the expression object ，如下面的代码所示。
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以下是显示使用 SQLAlchemy 核心技术执行 INSERT 查询的完整代码段：
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 object 。这类似于 DBAPI 游标对象。我们可以使用 ResultProxy.inserted_primary_key 获取语句中生成的主键值的信息，如下所示：
result.inserted_primary_key
[1]要使用 DBAPI 的 execute many() 方法发出多个插入操作，可以发送一个列表，其中每个列表包含一组要插入的不同参数。
conn.execute(students.insert(), [
{'name':'Rajiv', 'lastname' : 'Khanna'},
{'name':'Komal','lastname' : 'Bhandari'},
{'name':'Abdul','lastname' : 'Sattar'},
{'name':'Priya','lastname' : 'Rajhans'},
])此操作反映在表的数据视图中，如图所示：
SQLAlchemy Core - Selecting Rows
在本章中，我们将讨论表对象中选择行的概念。
表对象的 select() 方法使我们能够 construct SELECT expression 。
s = students.select()select 对象转换成 SELECT query by str(s) function ，如下所示：
'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() method 提取记录。
row = result.fetchone()在下表中，所有选定的行都可以通过 for loop 打印出来，如下所示：
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 shell 中显示的输出如下：
(1, 'Ravi', 'Kapoor')
(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')SELECT 查询的 WHERE 子句可通过使用 Select.where() 应用。例如，如果要显示 id >2 的行
s = students.select().where(students.c.id>2)
result = conn.execute(s)
for row in result:
print (row)这里 c attribute is an alias for column 。将在 shell 上显示以下输出：
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')
(5, 'Priya', 'Rajhans')这里，我们需要注意，select 对象也可以在 sqlalchemy.sql 模块中通过 select() 函数获取。select() 函数需要 table 对象作为参数。
from sqlalchemy.sql import select
s = select([users])
result = conn.execute(s)SQLAlchemy Core - Using Textual SQL
对于已知 SQL 的情况且无需语句支持动态特征的情况，SQLAlchemy 允许你仅使用字符串。text() 构造被用于编写传递到数据库的大致没有修改的文本语句。
它构造一个新的 TextClause ，表示直接作为文本 SQL 字符串，如下面的代码所示：
from sqlalchemy import text
t = text("SELECT * FROM students")
result = connection.execute(t)text() 相较于简单字符串提供的优势包括：
-
后端中立支持绑定参数
-
per-statement execution options
-
result-column typing behaviour
text() 函数需要名结肠号格式的绑定参数。它们与数据库后端无关。要向参数发送值，我们以附加参数的形式将它们传递到 execute() 方法中。
以下示例在文本 SQL 中使用了绑定参数：
from sqlalchemy.sql import text
s = text("select students.name, students.lastname from students where students.name between :x 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 :x 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 :x 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 :x and :y"),
text("students.id>2")
)
)
conn.execute(s, x = 'A', y = 'L').fetchall()上面的代码提取了 id 大于 2 的姓名介于“A”和“L”之间的行。代码的输出如下 -
[(3, 'Komal', 'Bhandari'), (4, 'Abdul', 'Sattar')]SQLAlchemy Core - Using Aliases
SQL 中的别名对应于表的“重命名”版本或 SELECT 语句，该语句在您输入“SELECT * FROM table1 AS a”时随时发生。AS 为表创建了一个新名称。别名允许通过唯一名称引用任何表或子查询。
在表的场合，这允许在“FROM”子句中多次命名相同的表。它为该语句表示的列提供了一个父名称，允许相对于该名称对列进行引用。
在 SQLAlchemy 中，可以使用 From Clause.alias() 方法将任何 Table 型、select() 型或其他可选择对象转换为别名，该方法生成 Alias 型。sqlalchemy.sql 模块中的 alias() 函数表示别名，该别名通常使用 AS 关键字应用于 SQL 语句中的任何表或子查询。
from sqlalchemy.sql import alias
st = students.alias("a")现在可以在 select() 型中使用此别名来引用 students 表 -
s = select([st]).where(st.c.id>2)这转换成的 SQL 表达式如下 -
SELECT a.id, a.name, a.lastname FROM students AS a WHERE a.id > 2现在我们可以使用连接对象的 execute() 方法来执行此 SQL 查询。完整的代码如下 -
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')]Using UPDATE Expression
目标表对象上的 update() 方法构建了等效的 UPDATE SQL 表达式。
table.update().where(conditions).values(SET expressions)结果更新对象上的 values() 方法用于指定 UPDATE 的 SET 条件。如果保留为 None，则 SET 条件将根据在语句执行和/或编译期间传递给该语句的参数确定。
where 子句是一个可选表达式，它描述了 UPDATE 语句的 WHERE 条件。
以下代码片段将 students 表中的“lastname”列的值从“Khanna”更改为“Kapoor” -
stmt = students.update().where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')stmt 对象是一个更新对象，该对象转换为 -
'UPDATE students SET lastname = :lastname WHERE students.lastname = :lastname_1'当调用 execute() 方法时，边界参数 lastname_1 将被替换。完整的更新代码如下 -
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')
]
请注意，还可以使用 sqlalchemy.sql.expression 模块中的 update() 函数来实现类似的功能，如下所示 -
from sqlalchemy.sql.expression import update
stmt = update(students).where(students.c.lastname == 'Khanna').values(lastname = 'Kapoor')Using DELETE Expression
在上一章节中，我们已经了解了 Update 表达式的作用。我们接下来要学习的表达是 Delete 。
删除操作可以通过对目标表对象运行 delete() 方法来实现，如下面的语句中所示：
stmt = students.delete()对于学生表，上面的代码行构造如下 SQL 表达式：
'DELETE FROM students'但是，这将删除学生表中的所有行。通常，DELETE 查询与 WHERE 子句指定的逻辑表达式相关联。以下语句显示 where 参数：
stmt = students.delete().where(students.c.id > 2)结果 SQL 表达式将有一个绑定参数，该参数将在语句执行时在运行时替换。
'DELETE FROM students WHERE students.id > :id_1'以下代码示例将从学生表中删除那些姓为“Khanna”的行：
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 Core - Using Multiple Tables
RDBMS 的重要特性之一是在表之间建立关系。SQL 运算（例如 SELECT、UPDATE 和 DELETE）可以在相关表上执行。本节使用 SQLAlchemy 描述了这些运算。
为此，在我们的 SQLite 数据库 (college.db) 中创建两个表。学生表具有与前一节中给出的相同的结构；而地址表具有使用外键约束映射到 id column in students table 的 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() method 填充表中的数据。要在学生表中插入 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'},
])Rows 在地址表中使用以下代码添加：
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':'ravi@gmail.com'},
{'st_id':1, 'postal_add':'ChurchGate Mumbai', 'email_add':'kapoor@gmail.com'},
{'st_id':3, 'postal_add':'Jubilee Hills Hyderabad', 'email_add':'komal@gmail.com'},
{'st_id':5, 'postal_add':'MG Road Bangaluru', 'email_add':'as@yahoo.com'},
{'st_id':2, 'postal_add':'Cannought Place new Delhi', 'email_add':'admin@khanna.com'},
])请注意，地址表中的 st_id 列引用学生表中的 id 列。我们现在可以使用此关系从这两个表中提取数据。我们希望从与地址表中 st_id 相对应的学生表中提取 name 和 lastname 。
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)选择对象将有效地转换成以下 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', 'ravi@gmail.com')
(1, 'Ravi', 'Kapoor', 2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')
(3, 'Komal', 'Bhandari', 3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')
(5, 'Priya', 'Rajhans', 4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
(2, 'Rajiv', 'Khanna', 5, 2, 'Cannought Place new Delhi', 'admin@khanna.com')Using Multiple Table Updates
在上一章中，我们讨论了如何使用多张表。因此，我们在本章中进一步学习 multiple table updates 。
使用 SQLAlchemy 的表对象，可以在 update() 方法的 WHERE 子句中指定多个表。PostgreSQL 和 Microsoft SQL Server 支持引用多张表的 UPDATE 语句。这实现了 “UPDATE FROM” 语法，该语法一次更新一张表。但是，可以在 WHERE 子句的附加“FROM”子句中直接引用其他表。以下代码行清楚地解释了 multiple table updates 的概念。
stmt = students.update().\
values({
students.c.name:'xyz',
addresses.c.email_add:'abc@xyz.com'
}).\
where(students.c.id == addresses.c.id)update 对象等效于以下 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 UPDATEParameter-Ordered Updates
原始 SQL 的 UPDATE 查询具有 SET 子句。它由 Update.values() 构造使用原始表对象中给出的列顺序渲染。因此，具有特定列的特定 UPDATE 语句每次都会被渲染相同。由于参数本身作为 Python 词典键传递给 Update.values() 方法，因此没有其他固定的排序可用。
在某些情况下，SET 子句中呈现的参数顺序很重要。在 MySQL 中，提供列值更新是基于其他列值的更新。
以下语句的结果：
UPDATE table1 SET x = y + 10, y = 20将具有与以下不同的结果：
UPDATE table1 SET y = 20, x = y + 10MySQL 中的 SET 子句以按值方式进行评估，而不是以按行方式进行评估。为此，使用了 preserve_parameter_order 。Python 2 元组列表作为 Update.values() 方法的参数给出：
stmt = table1.update(preserve_parameter_order = True).\
values([(table1.c.y, 20), (table1.c.x, table1.c.y + 10)])列表对象类似于词典，除了它是按顺序排列的。这确保了 “y” 列的 SET 子句将首先渲染，然后是 “x” 列的 SET 子句。
SQLAlchemy Core - Multiple Table Deletes
在本章中，我们将研究类似于使用多表更新功能的多表删除表达式。
在许多 DBMS 方言中，DELETE 语句的 WHERE 子句中可以引用多个表格。对于 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 Core - Using Joins
在本章中，我们将学习如何在 SQLAlchemy 中使用连接。
连接的效果只需将两个表放在 select() 构造的 columns clause 或 where clause 中即可实现。现在，我们使用 join() 和 outerjoin() 方法。
join() 方法从一个表对象返回一个到另一个表对象的连接对象。
join(right, onclause = None, isouter = False, full = False)上面代码中提到的参数的功能如下：
-
right - 连接的右侧；这是任何表对象
-
onclause - 表示连接的 ON 子句的 SQL 表达式。如果保留为 None，它将尝试基于外键关系连接两个表
-
isouter − 如果为 True，将呈现一个 LEFT OUTER JOIN，而不是 JOIN
-
full − 如果为 True，将呈现一个 FULL OUTER JOIN，而不是 LEFT OUTER JOIN
例如，join() 方法的以下用法将自动导致基于外键的 join。
>>> print(students.join(addresses))这等效于以下 SQL 表达式 −
students JOIN addresses ON students.id = addresses.st_id您可以显式提及 join 条件，如下所示 −
j = students.join(addresses, students.c.id == addresses.c.st_id)如果我们现在使用此 join 构建以下选择构建，则为 −
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 - Using Conjunctions
联接是 SQLAlchemy 模块中的函数，它实现了 SQL 表达式的 WHERE 子句中使用的关系运算符。运算符 AND、OR、NOT 等用于形式一个复合表达式，它将两个单独的逻辑表达式相结合。在 SELECT 语句中使用 AND 的一个简单示例如下 −
SELECT * from EMPLOYEE WHERE salary>10000 AND age>30SQLAlchemy 函数 and_(), or_() and not_() 分别实现 AND、OR 和 NOT 运算符。
and_() function
它生成通过 AND 连接的表达式的联接。下面给出了一个示例以便更好地理解 −
from sqlalchemy import and_
print(
and_(
students.c.name == 'Ravi',
students.c.id <3
)
)这转化为 −
students.name = :name_1 AND students.id < :id_1要在 students 表上的 select() 构造中使用 and_()，请使用以下代码行 −
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())假设用前一个示例中使用的数据填充 students 表，则将选择以下行 −
[(1, 'Ravi', 'Kapoor')]or_() function
它生成通过 OR 连接的表达式的联接。我们将使用 or_() 替换以上示例中的 stmt 对象
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一旦你进行了替换并运行上述代码，结果将是符合 OR 条件的两行 −
[(1, 'Ravi', 'Kapoor'),
(2, 'Rajiv', 'Khanna')]asc() function
它生成一个 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以下代码列出了 students 表中的所有记录，按 name 列升序排序 −
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() function
类似地，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() function
它生成一个 BETWEEN 谓词子句。这通常用来验证特定列的值是否介于某个范围内。例如，以下代码选择 id 列介于 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 Core - Using Functions
本章讨论了 SQLAlchemy 中使用的一些重要函数。
标准 SQL 推荐了许多大多数方言都实现的函数。它们根据传递给它的参数返回单个值。一些 SQL 函数将列作为参数，而另一些则是通用的。 Thefunc keyword in SQLAlchemy API is used to generate these functions 。
在 SQL 中，now() 是一个通用函数。以下语句使用 func 渲染 now() 函数 −
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())从上面的代码中，将获取 students 表中行数的计数。
使用包含以下数据的 Employee 表演示了一些内置 SQL 函数 −
ID |
Name |
Marks |
1 |
Kamal |
56 |
2 |
Fernandez |
85 |
3 |
Sunil |
62 |
4 |
Bhaskar |
76 |
max() 函数通过以下使用 SQLAlchemy 中的 func 来实现，它将产生 85，即获得的总最高分 −
from sqlalchemy.sql import func
result = conn.execute(select([func.max(employee.c.marks)]))
print (result.fetchone())类似地，将返回 56，即最低分数的 min() 函数将通过以下代码渲染 −
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 Core - Using Set Operations
在上一个章节中，我们学习了各种函数，例如 max()、min()、count() 等，在这里，我们将学习集合运算及其用法。
UNION 和 INTERSECT 等集合运算由标准 SQL 及其大多数方言支持。SQLAlchemy 在以下函数的帮助下实现它们 −
union()
在组合两个或多个 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', 'ravi@gmail.com'),
(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
(3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com'),
(4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
]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 ?except_()
SQL EXCEPT 子句/运算符用于组合两个 SELECT 语句，并返回第一个 SELECT 语句中未通过第二个 SELECT 语句返回的行。except_() 函数生成带有 EXCEPT 子句的 SELECT 表达式。
在以下示例中，except_() 函数仅从地址表中返回那些 email_add 字段中具有“gmail.com”但排除 postal_add 字段中有“Pune”的记录。
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', 'kapoor@gmail.com'),
(3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')]intersect()
使用 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', 'ravi@gmail.com')]SQLAlchemy ORM - Declaring Mapping
SQLAlchemy 的对象关系映射器 API 的主要目标是促进将用户定义的 Python 类与数据库表相关联，并将这些类的对象与相应表中的行相关联。对象的 state 和行的 state 的变化彼此同步匹配。SQLAlchemy 能够根据用户定义的类及其定义的关系来表达数据库查询。
ORM 基于 SQL 表达式语言构建。它是一种高级抽象的用法模式。事实上，ORM 是表达式语言的一个应用用法。
虽然可以单独使用对象关系映射器构建成功的应用程序，但有时使用 ORM 构建的应用程序可能在需要特定数据库交互时直接使用表达式语言。
Declare Mapping
首先，调用 create_engine() 函数来设置引擎对象，随后将其用于执行 SQL 操作。该函数有两个参数，一个是数据库名称，另一个是回显参数，当设置为 True 时将生成活动日志。如果数据库不存在，将创建该数据库。在以下示例中，创建了 SQLite 数据库。
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)当调用 Engine.execute() 或 Engine.connect() 等方法时，引擎将建立与数据库的真实 DBAPI 连接。然后用它来 发 射 SQLORM，它不直接使用引擎；而是由 ORM 在后台使用。
在 ORM 的情况下，配置过程首先描述数据库表，然后定义将映射到这些表的类。在 SQLAlchemy 中，这两个任务是同时执行的。这是通过使用声明系统完成的；创建的类包括描述实际数据库表的指令，这些表被映射到。
基本类存储 Declarative 系统中类的目录和映射表。这称为声明基本类。在通常导入的模块中通常只有一个此基类的实例。declarative_base() 函数用于创建基本类。此函数在 sqlalchemy.ext.declarative 模块中定义。
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()声明基本类后，可以基于它定义任意数量的映射类。以下代码定义了 Customer 的类。它包含要映射到的表，以及其中的列的名称和数据类型。
class Customers(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key = True)
name = Column(String)
address = Column(String)
email = Column(String)在声明性中的类必须具有 tablename 属性，以及至少一个 Column 作为主键的一部分。声明性用称为 descriptors 的特殊 Python 访问器替换所有 Column 对象。此过程称为仪器化，它提供了在 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)
)如果我们使用 SQLiteStudio 图形工具打开 Sales.db，它将在其中显示具有上述结构的客户表。
SQLAlchemy ORM - Creating Session
为了与数据库进行交互，我们需要获得其句柄。会话对象是数据库的句柄。使用 sessionmaker() 定义会话类 - 一个可配置的会话工厂方法，它绑定到前面创建的引擎对象。
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)然后使用其默认构造函数设置会话对象，如下所示 −
session = Session()下面列出会话类的一些经常需要的方法 −
Sr.No. |
Method & Description |
1 |
begin() 在此会话上开始事务 |
2 |
add() 在会话中放置一个对象。它的状态将在下一次刷新操作中保存在数据库中 |
3 |
add_all() 向会话添加对象集合 |
4 |
commit() 刷新所有项以及正在进行的事务 |
5 |
delete() 将事务标记为已删除 |
6 |
execute() executes a SQL expression |
7 |
expire() 将实例的属性标记为已过期 |
8 |
flush() 将所有对象改动刷新到数据库 |
9 |
invalidate() 使用连接失效来关闭会话 |
10 |
rollback() 回滚当前正在进行的事务 |
11 |
close() 通过清除所有项并结束正在进行的事务来关闭当前会话 |
SQLAlchemy ORM - Adding Objects
在 SQLAlchemy ORM 的前面章节中，我们了解了如何声明映射和创建会话。在本章中，我们将了解如何向表中添加对象。
我们已经声明映射到 customers 表的 Customer 类。我们必须声明此类的对象，并通过会话对象的 add() 方法将其持久性地添加到表中。
c1 = Sales(name = 'Ravi Kumar', address = 'Station Road Nanded', email = 'ravi@gmail.com')
session.add(c1)请注意，此事务在使用 commit() 方法刷新之前是挂起的。
session.commit()以下是向 customers 表中添加记录的完整脚本 -
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 = 'ravi@gmail.com')
session.add(c1)
session.commit()要添加多条记录，我们可以使用会话类的 add_all() 方法。
session.add_all([
Customers(name = 'Komal Pande', address = 'Koti, Hyderabad', email = 'komal@gmail.com'),
Customers(name = 'Rajender Nath', address = 'Sector 40, Gurgaon', email = 'nath@gmail.com'),
Customers(name = 'S.M.Krishna', address = 'Budhwar Peth, Pune', email = 'smk@gmail.com')]
)
session.commit()SQLiteStudio 表视图显示记录永久添加到 customers 表中。下图显示了结果 -
SQLAlchemy ORM - Using Query
SQLAlchemy ORM 生成的所有 SELECT 语句都是由 Query 对象构建的。它提供了一个生成接口，因此连续调用会返回一个新的 Query 对象，该对象是前一个对象的副本，并带有与其关联的其他 criteria 和 options。
Query 对象最初是使用 Session 的 query() 方法生成的，如下所示 -
q = session.query(mapped class)以下语句也相当于上述给出的语句 -
q = Query(mappedClass, session)此查询对象具有 all() 方法，该方法以对象列表的形式返回结果集。如果我们对 customers 表执行它 -
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 循环遍历，如下所示，以获取底层 customers 表中的所有记录。以下是显示 Customers 表中所有记录的完整代码 −
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: ravi@gmail.com
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com
Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.comQuery 对象还具有以下有用的方法 −
Sr.No. |
Method & Description |
1 |
add_columns() 向要返回的结果列列表添加一个或多个列表达式。 |
2 |
add_entity() 向要返回的结果列列表中添加一个映射实体。 |
3 |
count() 返回此查询将返回的行数。 |
4 |
delete() 执行批量删除查询。从数据库中删除与此查询匹配的行。 |
5 |
distinct() 向查询应用 DISTINCT 子句并返回新生成查询。 |
6 |
filter() 使用 SQL 表达式将给定的筛选条件应用于此查询的副本。 |
7 |
first() 返回此查询的第一结果，如果结果不包含任何行，则返回 None。 |
8 |
get() 返回基于给定主键标识符的实例，该标识符提供对所属会话的身份映射的直接访问。 |
9 |
group_by() 向查询应用一个或多个 GROUP BY 标准并返回新生成查询。 |
10 |
join() 针对此查询对象的条件创建 SQL JOIN，并生成性地应用，返回新生成查询。 |
11 |
one() 返回一个结果或引发异常。 |
12 |
order_by() 向查询应用一个或多个 ORDER BY 标准，并返回新生成查询。 |
13 |
update() 执行批量更新查询并更新数据库中与此查询匹配的行。 |
SQLAlchemy ORM - Updating Objects
在本章中，我们将了解如何使用所需的值修改或更新表。
要修改任何对象的某个属性的数据，我们必须向其分配一个新值并提交更改以使更改保持持久性。
让我们从主键标识符为 2 的 Customers 表中获取一个对象。我们可以按如下方式使用会话的 get() 方法 −
x = session.query(Customers).get(2)我们可以通过下面给出的代码来显示所选对象的内容 -
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)从我们的客户表中，应当显示以下输出 -
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com现在我们需要通过分配新的值（如下所示）来更新地址字段 -
x.address = 'Banjara Hills Secunderabad'
session.commit()更改将持久地反映在数据库中。现在，我们通过如下使用 first() method 来获取表中第一行的相应对象：
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: ravi@gmail.com现在，更改名称属性，并使用下面的代码显示内容：
x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)上述代码的输出为：
Name: Ravi Shrivastava Address: Station Road Nanded Email: ravi@gmail.com尽管显示了更改，但未提交。你可以通过在下面的代码中使用 rollback() method 来保留早期的持久位置。
session.rollback()
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)将显示第一条记录的原始内容。
对于批量更新，我们要使用查询对象的 update() 方法。让我们尝试并给每一行（ID = 2 除外）的名称加上前缀“Mr.”。相应的 update() 语句如下：
session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)The update() method requires two parameters as follows −
-
键的值词典，其中键是要更新的属性，值是属性的新内容。
-
synchronize_session 属性，它提到了在会话中更新属性的策略。有效值为：false（表示不同步会话）、fetch（在更新之前执行选择查询，以查找与更新查询匹配的对象）和 evaluate（在会话中评估对象上的条件）。
表中四行中的三行名前会加上前缀“Mr.”。但是，不会提交更改，因此也不会反映在 SQLiteStudio 的表视图中。只有在提交会话时才会刷新。
SQLAlchemy ORM - Applying Filter
在本章中，我们将讨论如何应用过滤器以及某些过滤器操作及其代码。
查询对象表示的结果集可以通过使用 filter() 方法来满足某些条件。filter 方法的一般用法如下：
session.query(class).filter(criteria)在以下示例中，通过条件 (ID>2) 过滤了客户表上的 SELECT 查询获得的结果集：
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，因此只会显示 ID 列 >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: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.comSQLAlchemy ORM - Filter Operators
现在，我们将学习带有相应代码和输出的过滤操作。
Equals
常用的运算符是 ==，它应用一个用于检查相等性的条件。
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: komal@gmail.comNot Equals
用于不等于运算符的运算符是 !=，它提供不等于条件。
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: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.comLike
like() 方法本身为 SELECT 表达式中的 WHERE 从句生成 LIKE 条件。
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: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.comIN
此运算符检查列值是否属于列表中的项目集合。它由 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)此处，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 IN (?, ?)以上代码的输出如下 −
ID: 1 Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.comAND
此连词由 putting multiple commas separated criteria in the filter or using and_() method 生成，如下所示：
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: nath@gmail.comOR
此连词由 or_() method 实现。
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: ravi@gmail.com
ID: 3 Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
ID: 4 Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.comReturning List and Scalars
有许多查询对象的方法能够立刻发出 SQL 并返回包含已加载数据库结果的值。
以下是返回列表和标量的简要概述 −
all()
它返回一个列表。以下是用于 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 customersfirst()
它应用一个限制，并以标量形式返回第一个结果。
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。
one()
此命令完全提取所有行，并且如果没有正好一个对象标识或结果中存在组合行，它会引发错误。
session.query(Customers).one()发现多行 −
MultipleResultsFound: Multiple rows were found for one()未发现行 −
NoResultFound: No row was found for one()one() 方法对于那些期望区分处理“未找到项”和“找到多项”的系统非常有用。
scalar()
它调用 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 - Textual SQL
早些时候使用 text() 函数的文本 SQL 已从 SQLAlchemy 的核心表达式语言的角度进行了说明。现在我们将从 ORM 的角度对此进行讨论。
可以通过指定对 text() 结构的使用灵活地将文字字符串与查询对象一起使用。大多数适用的方法都会接受它。例如，filter() 和 order_by()。
在下面给出的示例中，filter() 方法将字符串“id<3”转换成 WHERE id<3
from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
print(cust.name)生成的原始 SQL 表达式显示了 filter 向 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从 Customers 表中的示例数据，将选择两行并将 name 列打印如下 −
Ravi Kumar
Komal Pande要使用基于字符串的 SQL 指定绑定参数，请使用冒号，并且要指定值，请使用 params() 方法。
cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()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
WHERE id = ?要使用完全基于字符串的语句, 可以将表示完整语句的 text() 构造传给 from_statement()。
session.query(Customers).from_statement(text("SELECT * FROM customers")).all()以上代码的结果将是一个基本的 SELECT 语句，如下所示：
SELECT * FROM customers很明显，将选择 customers 表中的所有记录。
text() 结构允许我们将其文本 SQL 与核心或 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()即使 SQLite 引擎执行 text() 方法中显示的所有列生成的以下表达式，也将选择所有行的 id 和 name 列：
SELECT name, id, name, address, email FROM customersSQLAlchemy ORM - Building Relationship
本会话描述了如何创建另一个表，该表与我们数据库中已存在的表相关。 customers 表包含客户的主数据。我们现在需要创建一个 invoices 表，该表可能拥有属于某个客户的任意数量的发票。这是一个一对多关系的案例。
使用声明式，我们按照下面给出的方式定义此表及其映射类 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)这会向 SQLite 引擎发送一个 CREATE TABLE 查询，如下所示：
CREATE TABLE invoices (
id INTEGER NOT NULL,
custid INTEGER,
invno INTEGER,
amount INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(custid) REFERENCES customers (id)
)我们可以借助 SQLiteStudio 工具检查是否已在 sales.db 中创建新表。
Invoices 类对 custid 属性应用 ForeignKey 结构。此指令表明此列中的值应被限制为 customers 表中 id 列中的现有值。这是关系数据库的核心特性，并且是将不连接的表集合转换成为具有丰富的重叠关系的“粘合剂”。
另一个称为 relationship() 的指令告诉 ORM 应使用属性 Invoice.customer 将 Invoice 类链接到 Customer 类。relationship() 使用两个表之间的外键关系来确定此链接的性质，并确定该关系是一对多的关系。
另一个 relationship() 指令会被放置在 Customer 映射类的 Customer.invoices 属性下。relationship.back_populates 参数被分配为引用互补的属性名称，以便每个 relationship() 能够就以相反的方式表达的相同关系做出明智的决策。一方是 Invoices.customer 引用 Invoices 实例，另一方是 Customer.invoices 引用 Customers 实例列表。
该关系函数是 SQLAlchemy ORM 包的 Relationship API 的一部分。它提供两个映射类之间的关系。这对应于父-子或关联表关系。
以下是发现的基本关系模式−
One To Many
一对多的关系是指通过子表上的外键来引用父表的关系。relationship() 然后指定在父表上，作为引用子表中一堆项的集合。relationship.back_populates 参数用于在一对多关系中建立双向关系，其中“反向”侧是多对一关系。
Working with Related Objects
在本章中，我们将重点关注 SQLAlchemy ORM 中的相关对象。
现在，当我们创建一个客户对象时，Python 列表的形式将出现一个空白的账单集合。
c1 = Customer(name = "Gopal Krishna", address = "Bank Street Hydarebad", email = "gk@gmail.com")c1.invoices 的 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', 'gk@gmail.com')
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 10, 15000)
INSERT INTO invoices (custid, invno, amount) VALUES (?, ?, ?)
(2, 14, 3850)让我们现在在 SQLiteStudio 表视图中查看客户表和发票表的内容 -
您可通过使用下列命令，在构造函数中为发票提供映射的特性，来构造客户对象 -
c2 = [
Customer(
name = "Govind Pant",
address = "Gulmandi Aurangabad",
email = "gpant@gmail.com",
invoices = [Invoice(invno = 3, amount = 10000),
Invoice(invno = 4, amount = 5000)]
)
]或一组待使用 add_all() 函数（如以下所示）添加的对象 -
rows = [
Customer(
name = "Govind Kala",
address = "Gulmandi Aurangabad",
email = "kala@gmail.com",
invoices = [Invoice(invno = 7, amount = 12000), Invoice(invno = 8, amount = 18500)]),
Customer(
name = "Abdul Rahman",
address = "Rohtak",
email = "abdulr@gmail.com",
invoices = [Invoice(invno = 9, amount = 15000),
Invoice(invno = 11, amount = 6000)
])
]
session.add_all(rows)
session.commit()SQLAlchemy ORM - Working with Joins
现在我们有两个表，我们将了解如何同时对这两个表创建查询。要构建客户和发票之间的简单隐式连接，我们可以使用 Query.filter() 将它们的相关列等同起来。在下面，我们使用此方法一次性加载客户和发票实体 -
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))SQLAlchemy 发出的 SQL 表达式如下 -
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 8500Query.join() 知道如何在这些表之间进行连接，因为它们之间只有一个外键。如果没有外键，或者更多外键，当使用以下任一形式时，Query.join() 能够得到更好的效果 -
query.join(Invoice, id == Address.custid) |
explicit condition |
query.join(Customer.invoices) |
从左到右指定关系 |
query.join(Invoice, Customer.invoices) |
same, with explicit target |
query.join('invoices') |
same, using a string |
同样，outerjoin() 函数可用于实现左外连接。
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一旦我们有了自己的语句，它就会表现得像 Table 结构。语句中的列可以通过一个叫做 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 2Common Relationship Operators
在本教程中，我们将讨论基于关系构建的运算符。
eq()
上面的运算符是多对一的"equals"比较。此运算符的代码行如下所示：
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()
此运算符是多对一的"not equals"比较。此运算符的代码行如下所示：
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()
此运算符用于一对多的集合，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()
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 = ?)has()
此运算符用于标量引用，如下所示：
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 - Eager Loading
渴望加载减少了查询的数量。SQLAlchemy 提供了渴望加载函数，是通过查询选项调用的，这些选项对查询提供了附加说明。这些选项通过 Query.options() 方法来确定如何加载各种属性。
Subquery Load
我们想要 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',)要访问来自两个表的 data，我们可以使用下面的程序：
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 gpant@gmail.com
Invoice no : 3, Amount : 10000
Invoice no : 4, Amount : 5000Joined Load
另一个函数称为 orm.joinedload()。这会发出一个 LEFT OUTER JOIN。在一步中加载了 Lead 对象以及相关对象或集合。
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 实例。这是因为查询对返回的实体应用了一种基于对象标识的“唯一化”策略。连接渴望加载可以在不影响查询结果的情况下应用。
subqueryload() 更适合加载相关集合，而 joinedload() 更适合多对一关系。
SQLAlchemy ORM - Deleting Related Objects
对单个表执行删除操作很容易。你所要做的就是从会话中删除映射类的对象并提交该操作。但是，对多个相关表的删除操作有点棘手。
在我们的 sales.db 数据库中，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")我们设置了一个会话，并使用以下程序通过主键 ID 查询它来获取一个 Customer 对象：
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
x = session.query(Customer).get(2)在我们的示例表中，x.name 恰好是“Gopal Krishna”。让我们从会话中删除这个 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但是，x 的相关 Invoice 对象仍然存在。可以通过以下代码进行验证：
session.query(Invoice).filter(Invoice.invno.in_([10,14])).count()此处，10 和 14 是属于客户 Gopal Krishna 的发票号码。上述查询的结果为 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 类，并添加包含级联配置的 addresses 关系。
relationship 函数中的级联属性是一个级联规则的分隔列表，该列表确定会话操作应如何从父级级联到子级。默认情况下，它是错误的，这意味着它是“保存-更新，合并”。
可用的级联如下 −
-
save-update
-
merge
-
expunge
-
delete
-
delete-orphan
-
refresh-expire
通常使用的选项是“全部、删除孤立项”，以表示在所有情况下相关对象都应与父对象一起执行，并在取消关联时将其删除。
因此，重新声明的 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 名称的 Customer，并查看其关联的 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()使用上述脚本发出的以下 SQL，计数现在为 0 −
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)
0Many to Many Relationships
两个表之间的 Many to Many relationship 通过添加一个关联表来实现，关联表有两个外键 - 一个来自每个表的主键。此外，映射到两个表的类具有一个属性，该属性具有一个集合对象的其他关联表被指定为 relationship() 函数的辅助属性。
为此，我们将在 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 属性。关联功能的次要属性被分配为链接作为其值。
相似地，Employee 类具有与 Department 类相关的 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)
)我们可以通过使用 SQLiteStudio 打开 mycollege.db 来检查这一点，如下所示：
接下来，我们创建 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 对象添加到 Department 对象的 Employees 集合中。相似地，我们可以将 Department 对象添加到 Employee 对象的 department 集合属性中。
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()在 Python 控制台中会发出以下 SQL 语句：
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: TonySQLAlchemy - Dialects
SQLAlchemy 使用方言系统与各种类型的数据库进行通信。每个数据库都有一个相应的 DBAPI 包装器。所有方言都要求安装合适的 DBAPI 驱动程序。
以下方言包含在 SQLAlchemy API 中 −
-
Firebird
-
Microsoft SQL Server
-
MySQL
-
Oracle
-
PostgreSQL
-
SQL
-
Sybase
一个基于 URL 的 Engine 对象是由 create_engine() 函数产生的。这些 URL 可以包括用户名、密码、主机名和数据库名称。可能存在用于附加配置的可选关键字参数。在某些情况下，文件路径是可以接受的，而在另一些情况下，“数据源名称”会替换“主机”和“数据库”部分。数据库 URL 的典型形式如下 −
dialect+driver://username:password@host:port/databasePostgreSQL
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
Oracle 方言使用 cx_oracle 作为默认 DBAPI，如下所示 −
engine = create_engine('oracle://scott:tiger@127.0.0.1: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 的“file”部分是数据库的文件名。对于相对文件路径，这需要三个斜杠，如下所示 −
engine = create_engine('sqlite:///foo.db')对于绝对文件路径，三个斜杠后面是绝对路径，如下所示 −
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')要使用 SQLite:memory:database，请指定一个空 URL，如下所示 −
engine = create_engine('sqlite://')