OverIQ-中文系列教程-四-
OverIQ 中文系列教程（四）
SQLAlchemy 教程
SqlAlchemy 简介
最后更新于 2020 年 7 月 27 日
SQLAlchemy 实际上是一个用 Python 处理关系数据库的框架。它是由迈克·拜尔在 2005 年创建的。SQLAlchemy 允许您使用来自不同供应商的数据库，如 MySQL、MS-SQL、PostgreSQL、Oracle、SQLite 和许多其他数据库。
为什么要使用 SQLAlchemy
SQLAlchemy 最令人兴奋的特性是它的 ORM。ORM 或对象关系映射器允许我们使用面向对象的代码而不是编写 SQL 查询来处理数据库。我们从使用像 SQLAlchemy 这样的框架中获得的另一个巨大好处是，无论我们使用哪个数据库，我们的底层代码都将保持不变。这使得在不重写应用代码的情况下从一个数据库迁移到另一个数据库变得很容易。
SQLAlchemy 还有一个名为 SQLAlchemy Core 的组件。核心只是对传统 SQL 的平滑抽象。核心提供了一种 SQL 表达式语言，它允许我们使用 Python 结构生成 SQL 语句。与围绕模型和对象的 ORM 不同，SQL 表达式围绕表、列、索引等(就像普通的旧 SQL 一样)。SQL 表达式语言非常类似于 SQL，但它是标准化的，因此您可以在许多不同的数据库中使用它。您可以独立或一起使用 SQLAlchemy ORM 和 SQLAlchemy Core，这取决于您想要完成的任务。在幕后，SQLAlchemy ORM 使用了 SQLAlchemy Core。
那么应该使用 SQLAlchemy Core 还是 ORM 呢？
拥有 ORM 的全部意义在于使使用数据库变得容易。在这个过程中，它还增加了一些额外的开销。但是，对于大多数应用来说，这种开销并不大，除非您正在处理大量数据。对于大多数项目来说，SQLAlchemy ORM 就足够了，但是如果您正在编写一个处理大量数据的应用(比如在数据库仓库中)，或者您希望对查询有更多的控制，或者您是一个 SQL 纯粹主义者，并且更喜欢使用 core 直接处理行和列。
谁使用 SQLAlchemy
- 视频网站
- Fedora 项目
- 收纳盒
- OpenStack
还有更多。
先决条件:
为了完成本教程，您应该对 Python 和 SQL 有一个基本的了解。关于 Python 的快速复习，请查看我们的 Python 教程。对于 SQL，在网上搜索。
注:本教程源代码可在https://github.com/overiq/sqlalchemy获得。
安装 SQLAlchemy 并连接到数据库
原文：https://overiq.com/sqlalchemy-101/installing-sqlalchemy-and-connecting-to-database/
最后更新于 2020 年 7 月 27 日
SQLAlchemy 可以与 Python 2.7 或更高版本一起使用。在本教程中，我们使用的是 Python 3.5。但是，您可以自由使用 Python 3 的任何版本。
安装 SQLAlchemy
要安装 SQLAlchemy，请键入以下内容:
pip install sqlalchemy
要验证安装是否成功，请在 Python shell 中输入以下内容。
>>>
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.2.2'
>>>
安装 DBAPI
默认情况下，SQLAlchemy 仅适用于 SQLite 数据库，不需要任何额外的驱动程序。要使用其他数据库，您必须安装特定于该数据库的 DBAPI 投诉驱动程序。
什么是 DBAPI？
DBAPI 是一种标准，它鼓励使用相同的 API 来处理各种各样的数据库。下表列出了一些数据库及其 DBAPI 投诉驱动因素。
| 数据库ˌ资料库 | DBAPI 驱动程序 |
|---|---|
| 关系型数据库 | PyMySQL 、 MySQL-Connector 、 CyMySQL 、 MySQL-Python (默认) |
| 一种数据库系统 | psycopg2 (默认) pg8000 ， |
| 搜寻配置不当的 | PyODBC (默认) pymssql |
| 神谕 | CX-甲骨文(默认) |
| 火鸟 | fdb (默认值)，kintersbdb |
本教程中的所有示例都是针对 PostgreSQL 进行测试的，但是您可以使用任何想要的数据库。要为 PostgreSQL 安装 psycopg2 DBAPI，请键入以下命令:
pip install psycopg2
创建引擎
连接数据库的第一步是创建一个Engine对象。Engine对象是我们如何与数据库交互。它由两个组件组成:方言和连接池。
SQLAlchemy 方言
据说 SQL 是访问数据库的标准语言。事实上，不同数据库的 SQL 语法是不同的。数据库供应商很少坚持标准，更喜欢添加自己的扭曲并转向语言。例如，如果我们使用 Firebird，那么从employees表中选择前 5 行的id和name的 SQL 查询将是:
select first 10 id, name from employees
对 MySQL 数据库的等效查询是:
select id, name from employees limit 10
为了处理这些差异，我们需要方言。方言定义了数据库的行为。换句话说，它处理诸如生成 SQL 语句、执行、结果集处理等事情。一旦安装了合适的驱动程序，方言就会为我们处理所有这些差异，这样我们就可以专注于编写应用。
SQLAlchemy 连接池
连接池是一种在内存中缓存连接的标准方式，以便它们可以被重用。每次应用想要与数据库对话时创建一个新的连接既昂贵又耗时。连接池如果实现正确，可以显著提高性能。
在连接池中，每当应用需要与数据库对话时，它都会从池中获取连接。执行所需的查询后，应用释放连接并将其推回到池中。如果所有连接都在使用，则会创建一个新连接并将其添加到池中。
要创建引擎(即Engine对象)，我们使用sqlalchemy包的create_engine()功能。最简单的，它接受连接字符串。连接字符串提供了有关数据源的信息。其一般格式如下:
dialect+driver://username:password@host:port/database
dialect是指数据库的名称，如mysql、postgresql、mssql、oracle等。driver指的是你正在使用的 DBAPI。driver是可选的，如果未指定，将使用默认驱动程序(假设已经安装)。username和password是登录数据库服务器的凭证。host是数据库服务器的位置。port是可选的数据库端口，database是要连接的数据库的名称。
下面是为一些流行的数据库创建引擎的代码:
from sqlalchemy import create_engine
# Connecting to MySQL server at localhost using PyMySQL DBAPI
engine = create_engine("mysql+pymysql://root:pass@localhost/mydb")
# Connecting to MySQL server at 23.92.23.113 using mysql-python DBAPI
engine = create_engine("mysql+mysqldb://root:pass@23.92.23.113/mydb")
# Connecting to PostgreSQL server at localhost using psycopg2 DBAPI
engine = create_engine("postgresql+psycopg2://root:pass@localhost/mydb")
# Connecting to Oracle server at localhost using cx-Oracle DBAPI
engine = create_engine("oracle+cx_oracle://root:pass@localhost/mydb")
# Connecting to MSSQL server at localhost using PyODBC DBAPI
engine = create_engine("oracle+pyodbc://root:pass@localhost/mydb")
SQLite 数据库的连接字符串格式略有不同。因为 SQLite 是一个基于文件的数据库，所以我们不在连接字符串中指定用户名、密码、主机和端口。下面是为 SQLite 数据库创建引擎的代码。
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sqlite3.db') # using relative path
engine = create_engine('sqlite:////path/to/sqlite3.db') # using absolute path
连接到数据库
请注意，创建引擎不会立即建立与数据库的连接。为了获得连接，我们使用Engine对象的connect()方法，该方法返回类型为Connection的对象。
engine.connect() # connect to the database
下面是完整的代码:
from sqlalchemy import create_engine
engine = create_engine("postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts")
engine.connect()
print(engine)
如果您尝试运行上述代码，您将会得到以下错误:
OperationalError: (psycopg2.OperationalError) FATAL: database "sqlalchemy_tuts" does not exist (Background on this error at: http://sqlalche.me/e/e3q8)
问题是我们试图连接到一个不存在的数据库。要创建到 PostgreSQL 服务器的数据库登录并执行以下命令:
$ sudo -su postgres psql
postgres=#
postgres=# create database sqlalchemy_tuts;
CREATE DATABASE
postgres=#
postgres=# \q
再次运行该脚本，这次您应该会得到以下输出:
Engine(postgres+psycopg2://postgres:***@localhost/sqlalchemy_tuts)
一些额外的论点
下表列出了一些可以传递给create_engine()函数的附加关键字参数。
| 争吵 | 描述 |
|---|---|
echo |
一个布尔参数如果设置为True，引擎会将当前正在执行的所有 SQL 记录到标准输出中。默认设置为False。 |
pool_size |
它指定连接池中要保留的连接数。它的默认值是 5。 |
max_overflow |
它指定了超出pool_size设置可以打开的连接数，默认设置为 10。 |
encoding |
它指定 SQLAlchemy 要使用的编码。默认设置为utf-8。请注意，它不控制数据库的编码方案。 |
isolation_level |
要使用的隔离级别。此设置控制事务如何与其他事务隔离。不同的数据库支持不同的隔离级别。要了解有关隔离级别的更多信息，请查看数据库文档。 |
这里有一个脚本，它在创建引擎时使用了一些额外的关键字参数。
from sqlalchemy import create_engine
engine = create_engine(
"postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts",
echo=True, pool_size=6, max_overflow=10, encoding='latin1'
)
engine.connect()
print(engine)
运行脚本，您将获得如下输出:
2018-07-07 18:38:44,468 INFO sqlalchemy.engine.base.Engine select version()
2018-07-07 18:38:44,469 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,472 INFO sqlalchemy.engine.base.Engine select current_schema()
2018-07-07 18:38:44,473 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,476 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-07-07 18:38:44,477 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,479 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-07-07 18:38:44,480 INFO sqlalchemy.engine.base.Engine {}
2018-07-07 18:38:44,484 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2018-07-07 18:38:44,484 INFO sqlalchemy.engine.base.Engine {}
Engine(postgres+psycopg2://postgres:***@localhost/sqlalchemy_tuts)
在 SQLAlchemy 核心中定义模式
原文：https://overiq.com/sqlalchemy-101/defining-schema-in-sqlalchemy-core/
最后更新于 2020 年 7 月 27 日
创建表格
SQLAlchemy 中的表被表示为Table类的一个实例。Table构造函数接受表名、元数据和一个或多个列作为参数。这里有一个例子:
from sqlalchemy import MetaData, Table, String, Column, Text, DateTime, Boolean
from datetime import datetime
metadata = MetaData()
blog = Table('blog', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('published', Boolean(), default=False),
Column('created_on', DateTime(), default=datetime.now)
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
让我们一行一行地浏览代码:
- 在第 1 行中，我们从
sqlalchemy包中导入了几个类，我们将使用它们来定义表。 - 在第 2 行，我们正在从
datetime包导入datetime类。 - 在第 4 行，我们正在创建一个
MetaData对象。MetaData对象保存关于数据库及其包含的表的所有信息。我们使用MetaData实例在数据库中创建或删除表。 - 在第 6-14 行，我们定义了表模式。使用
Column实例创建表格的列。Column构造函数接受名称和类型。我们还可以向它传递额外的参数来定义约束和 SQL 结构。下表列出了一些常用的 SQL 约束。
| 限制 | 描述 |
|---|---|
primary_key |
当设置为True时，布尔参数将该列标记为表的主列。要创建复合主键，请在该键涉及的每一列上将primary_key设置为True。 |
nullable |
当设置为False时，布尔参数在创建列时添加NOT NULL约束。其默认值为True。 |
default |
它指定在插入新行时未指定列值的情况下使用的默认值。它可以采用标量值或 Python 可调用值。 |
onupdate |
如果在更新行时没有为列提供值，它将指定列的默认值。就像default关键字参数一样，它可以采用标量值或 Python 可调用。 |
unique |
如果设置为True，布尔参数确保列中的值必须是唯一的。 |
index |
如果设置为True，将创建一个索引列。其默认值为False。 |
autoincrement |
它将auto increment选项添加到列中。它的默认值是auto，这意味着它会在每次添加新记录时自动增加主键。如果您想要自动递增复合主键中涉及的所有列，请在每个列上将此参数设置为True。要禁用自动递增计数器，将其设置为False。 |
列类型
类型决定了一列可以接受的数据类型。SQLAlchemy 为各种类型提供了抽象。广义地说，有三类类型。
- 泛型类型
- 标准类型
- 供应商特定类型
泛型类型
泛型类型是指主要数据库后端支持的通用类型。当我们使用泛型类型时，SQLAlchemy 在创建表时使用数据库后端的最佳可用类型。例如，在前面的片段中，我们已经将published列定义为Boolean。Boolean是一个通用类型。如果我们对 PostgreSQL 数据库运行代码，那么 SQLAlchemy 将使用 PostgreSQL 提供的boolean类型。另一方面，如果我们对 MySQL 运行代码，那么 SQLAlchemy 将使用SMALLINT类型，因为 MySQL 不支持Boolean类型。然而，在 Python 代码中，Boolean泛型类型使用bool类型(True或False)来表示。
下表列出了 SQLAlchemy 提供的一些泛型列类型及其在 Python 和 SQL 中的关联类型。
| sqllcemy(SQL 语法) | 计算机编程语言 | 结构化查询语言 |
|---|---|---|
BigInteger |
int |
BIGINT |
Boolean |
bool |
BOOLEAN或SMALLINT |
Date |
datetime.date |
DATE |
DateTime |
datetime.datetime |
DATETIME |
Integer |
int |
INTEGER |
Float |
float |
FLOAT或REAL |
Numeric |
decimal.Decimal |
NUMERIC |
Text |
str |
TEXT |
我们可以从sqlalchemy.types或sqlalchemy包中访问泛型类型。
标准类型
此类别中定义的类型直接来自于 SQL 标准。极少数数据库后端支持这些类型。与泛型不同，SQL 标准类型不能保证在所有数据库后端都有效。
就像泛型类型一样，您可以从sqlalchemy.types或sqlalchemy包访问这些类型。但是，为了将它们与泛型类型区分开来，标准类型的名称都是用大写字母书写的。例如，SQL 标准定义了一个类型为Array的列。但是目前只有 PostgreSQL 支持这种类型。
from sqlalchemy import ARRAY
employee = Table('employees', metadata,
Column('id', Integer(), primary_key=True),
Column('workday', ARRAY(Integer)),
)
供应商特定类型
此类别定义了特定于数据库后端的类型。我们可以从sqlalchemy.dialects包中访问供应商特定的类型。例如，PostgreSQL 提供了一种INET类型来存储网络地址。要使用它，我们首先必须从sqlalchemy.dialects包装中进口。
from sqlalchemy.dialects import postgresql
comments = Table('comments', metadata,
Column('id', Integer(), primary_key=True),
Column('ipaddress', postgresql.INET),
)
定义关系
数据库表很少单独存在。大多数情况下，它们通过各种关系与一个或多个表相连。表之间主要存在三种类型的关系:
- 一对一的关系
- 一对多关系
- 多对多关系
让我们看看如何在 SQLAlchemy 中定义这些关系。
一对多关系
如果第一个表中的一行与第二个表中的一行或多行相关，则两个表通过一对多关系相关。下图中，users表和posts表之间存在一对多的关系。
要创建一对多关系，将包含引用列名称的ForeignKey对象传递给Column构造函数。
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False),
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', ForeignKey("users.id")),
)
在上面的代码中，我们在posts表的user_id列上定义了一个外键。这意味着user_id列只能包含来自users表的id列的值。
我们也可以将Column对象直接传递给ForeignKey构造函数，而不是将列名作为字符串传递。例如:
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False),
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', Integer(), ForeignKey(user.c.id)),
)
user.c.id指的是users表的id列。这样做时，请记住引用列(user.c.id)的定义必须在引用列(posts.c.user_id)之前。
一对一的关系
如果第一个表中的一行只与第二个表中的一行相关，则两个表通过一对一的关系相关。下图中，employees表和employee_details表之间存在一一对应的关系。employees表包含公开的员工记录，而employee_details表包含私人记录。
employees = Table('employees', metadata,
Column('employee_id', Integer(), primary_key=True),
Column('first_name', String(200), nullable=False),
Column('last_name', String(200), nullable=False),
Column('dob', DateTime(), nullable=False),
Column('designation', String(200), nullable=False),
)
employee_details = Table('employee_details', metadata,
Column('employee_id', ForeignKey('employees.employee_id'), primary_key=True, ),
Column('ssn', String(200), nullable=False),
Column('salary', String(200), nullable=False),
Column('blood_group', String(200), nullable=False),
Column('residential_address', String(200), nullable=False),
)
为了建立一对一的关系，我们在employee_details表的同一列上定义了主键和外键。
多对多关系
如果第一个表中的一行与第二个表中的一行或多行相关，则两个表通过多对多关系相关。除此之外，第二个表中的一行可以与第一个表中的一个或多个表相关。为了定义多对多关系，我们使用关联表。下图中，posts和tags表之间存在多对多关系。
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
)
tags = Table('tags', metadata,
Column('id', Integer(), primary_key=True),
Column('tag', String(200), nullable=False),
Column('tag_slug', String(200), nullable=False),
)
post_tags = Table('post_tags', metadata,
Column('post_id', ForeignKey('posts.id')),
Column('tag_id', ForeignKey('tags.id'))
)
您可能已经注意到，定义关系的过程几乎与我们在 SQL 中定义关系的方式相同。这是因为我们使用的是 SQLAlchemy 核心，而核心让你可以像在 SQL 中一样做事情。
在表级别定义约束
在前面几节中，我们已经看到了如何通过向Column构造函数传递额外的参数来向列添加约束和索引。事实证明，就像在 SQL 中一样，我们可以在表级别定义约束和索引。下表列出了一些常见的约束以及创建这些约束的类的名称:
| 约束/索引 | 类别名 |
|---|---|
| 主键约束 | PrimaryKeyConstraint |
| 外键约束 | ForeignKeyConstraint |
| 唯一约束 | UniqueConstraint |
| 检查约束 | CheckConstraint |
| 索引 | Index |
我们可以从sqlalchemy.schema或sqlalchemy包访问这些类。下面是一些如何使用它们的例子:
使用主键约束训练添加主键约束
parent = Table('parent', metadata,
Column('acc_no', Integer()),
Column('acc_type', Integer(), nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('acc_no', name='acc_no_pk')
)
这里我们在acc_no列上创建一个主键。上述代码相当于以下代码:
parent = Table('parent', metadata,
Column('acc_no', Integer(), primary=True),
Column('acc_type', Integer(), nullable=False),
Column('name', String(16), nullable=False),
)
PrimaryKeyConstraint主要用于定义复合主键(跨越多列的主键)。例如:
parent = Table('parent', metadata,
Column('acc_no', Integer, nullable=False),
Column('acc_type', Integer, nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('acc_no', 'acc_type', name='uniq_1')
)
该代码相当于以下内容:
parent = Table('parent', metadata,
Column('acc_no', Integer, nullable=False, primary_key=True),
Column('acc_type', Integer, nullable=False, primary_key=True),
Column('name', String(16), nullable=False),
)
使用外键约束创建外键
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16), nullable=False)
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, nullable=False),
Column('name', String(40), nullable=False),
ForeignKeyConstraint(['parent_id'],['parent.id'])
)
这里我们在引用parent表的id列的parent_id列上创建一个外键。上述代码相当于以下代码:
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(16), nullable=False)
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', ForeignKey('parent.id'), nullable=False),
Column('name', String(40), nullable=False),
)
当您想要定义复合外键(一个由多个列组成的外键)时，ForeignKeyConstraint的真正效用就发挥出来了。例如:
parent = Table('parent', metadata,
Column('id', Integer, nullable=False),
Column('ssn', Integer, nullable=False),
Column('name', String(16), nullable=False),
PrimaryKeyConstraint('id', 'ssn', name='uniq_1')
)
child = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), nullable=False),
Column('parent_id', Integer, nullable=False),
Column('parent_ssn', Integer, nullable=False),
ForeignKeyConstraint(['parent_id','parent_ssn'],['parent.id', 'parent.ssn'])
)
请注意，将ForeignKey对象传递给单个列不会创建复合外键，相反，它会创建单独的外键。
使用唯一约束创建唯一约束
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('ssn', Integer, nullable=False),
Column('name', String(16), nullable=False),
UniqueConstraint('ssn', name='unique_ssn')
)
这里我们定义了ssn列的唯一约束。可选的name关键字参数用于为唯一约束提供名称。上述代码相当于以下代码:
parent = Table('parent', metadata,
Column('id', Integer, primary_key=True),
Column('ssn', Integer, unique=True, nullable=False),
Column('name', String(16), nullable=False),
)
UniqueConstraint常用于定义包含多列的唯一约束。例如:
parent = Table('parent', metadata,
Column('acc_no', Integer, primary_key=True),
Column('acc_type', Integer, nullable=False),
Column('name', String(16), nullable=False),
UniqueConstraint('acc_no', 'acc_type', name='uniq_1')
)
在这里，我定义了对acc_no和acc_type的唯一约束，因此，acc_no和acc_type放在一起一定是唯一的。
使用检查约束创建检查约束
一个CHECK约束允许我们定义一个在插入或更新数据时将被评估的条件。如果条件评估为真，则将数据保存到数据库中。否则，将引发错误。
我们可以使用CheckConstraint构造添加CHECK约束。
employee = Table('employee', metadata,
Column('id', Integer(), primary_key=True),
Column('name', String(100), nullable=False),
Column('salary', Integer(), nullable=False),
CheckConstraint('salary < 100000', name='salary_check')
)
使用索引创建索引
我们在本课前面学习的index关键字参数允许我们在每列的基础上添加索引。定义索引的另一种方法是使用Index构造。例如:
a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('middle_name', String(100)),
Column('last_name', String(100), nullable=False),
Index('idx_col1', 'first_name')
)
这里我们在first_name列上创建一个索引。该代码相当于以下内容:
a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False, index=True),
Column('middle_name', String(100)),
Column('last_name', String(100), nullable=False),
)
如果您的查询涉及搜索一组特定的字段，那么您可以通过创建复合索引(即多列索引)来提高性能，这是Index的主要目的。这里有一个例子:
a_table = Table('a_table', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('middle_name', String(100)),
Column('last_name', String(100), nullable=False),
Index('idx_col1', 'first_name', 'last_name')
)
从元数据访问表和列
回想一下MetaData对象保存了关于数据库及其包含的表的所有信息。我们可以使用以下两个属性来访问表对象。
| 属性 | 描述 |
|---|---|
tables |
返回一个名为immutabledict的字典型对象，以表名为键，以对应的Table对象为值。 |
sorted_tables |
返回按外键依赖关系排序的Table对象列表。换句话说，具有依赖关系的表被放在实际依赖关系的前面。例如，如果posts表有一个外键引用了users表的id列，那么users表首先被放置，然后是posts表。 |
以下脚本显示了这两个属性的作用:
from sqlalchemy import create_engine, MetaData, Table, Integer, String, Column, Text, DateTime, Boolean, ForeignKey
metadata = MetaData()
user = Table('users', metadata,
Column('id', Integer(), primary_key=True),
Column('user', String(200), nullable=False),
)
posts = Table('posts', metadata,
Column('id', Integer(), primary_key=True),
Column('post_title', String(200), nullable=False),
Column('post_slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('user_id', Integer(), ForeignKey("users.id")),
)
for t in metadata.tables:
print(metadata.tables[t])
print('-------------')
for t in metadata.sorted_tables:
print(t.name) # print table name
预期输出:
posts
users
-------------
users
posts
一旦我们访问了Table实例，我们就可以很容易地访问关于列的任何相关细节，如下所示:
print(posts.columns) # return a list of columns
print(posts.c) # same as posts.columns
print(posts.foreign_keys) # returns a set containing foreign keys on the table
print(posts.primary_key) # returns the primary key of the table
print(posts.metadata) # get the MetaData object from the table
print(posts.columns.post_title.name) # returns the name of the column
print(posts.columns.post_title.type) # returns the type of the column
预期输出:
['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']
['posts.id', 'posts.post_title', 'posts.post_slug', 'posts.content', 'posts.user_id']
{ForeignKey('users.id')}
PrimaryKeyConstraint(Column('id', Integer(), table=<posts>, primary_key=True, nullable=False))
MetaData(bind=None)
post_title
VARCHAR(200)
创建表格
要创建存储在MetaData实例中的表，请使用Engine对象调用MetaData.create_all()方法。
metadata.create_all(engine)
create_all()方法只在数据库中不存在表的情况下创建表。也就是说可以多次安全调用create_all()。请注意，在更改表定义后调用create_all()方法不会更改表模式。为此，我们可以使用名为 Alembic 的数据库迁移工具。我们将在下一课中了解更多。
我们也可以使用MetaData.drop_all()方法删除数据库中的所有表。
在本教程中，我们将使用电子商务应用的数据库。数据库由以下 4 个表组成:
-
customers表存储了客户的所有信息。它由以下几列组成:-
id-主键 -
first_name-客户的名字 -
last_name-客户的姓氏 -
username-唯一的用户名 -
email-独特的电子邮件 -
address-客户地址 -
town-客户城镇名称 -
created_on-账户创建的日期和时间 -
updated_on-账户上次更新的日期和时间
-
-
items表存储产品信息。它由以下几列组成:id-主键name-项目名称cost_price-项目成本价selling_price-物品售价quantity-库存物品数量
-
orders存储顾客所下订单的信息。它由以下几列组成:id-主键customer_id-customers表中id列的外键date_placed-下订单的日期和时间date_shipped-订单发货的日期和时间
-
order_lines存储每个订单中项目的详细信息。它由以下几列组成:id-主键order_id-orders表中id列的外键item_id-items表中id列的外键quantity-订购的项目数量
下面是数据库的 ER 图。
下面是创建这些表的完整代码。
from sqlalchemy import create_engine, MetaData, Table, Integer, String, \
Column, DateTime, ForeignKey, Numeric, CheckConstraint
from datetime import datetime
metadata = MetaData()
engine = create_engine("postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts")
customers = Table('customers', metadata,
Column('id', Integer(), primary_key=True),
Column('first_name', String(100), nullable=False),
Column('last_name', String(100), nullable=False),
Column('username', String(50), nullable=False),
Column('email', String(200), nullable=False),
Column('address', String(200), nullable=False),
Column('town', String(50), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
items = Table('items', metadata,
Column('id', Integer(), primary_key=True),
Column('name', String(200), nullable=False),
Column('cost_price', Numeric(10, 2), nullable=False),
Column('selling_price', Numeric(10, 2), nullable=False),
Column('quantity', Integer(), nullable=False),
CheckConstraint('quantity > 0', name='quantity_check')
)
orders = Table('orders', metadata,
Column('id', Integer(), primary_key=True),
Column('customer_id', ForeignKey('customers.id')),
Column('date_placed', DateTime(), default=datetime.now),
Column('date_shipped', DateTime())
)
order_lines = Table('order_lines', metadata,
Column('id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.id')),
Column('item_id', ForeignKey('items.id')),
Column('quantity', Integer())
)
metadata.create_all(engine)
在下一课中，我们将学习如何使用 SQL 表达式语言对数据库执行 CRUD 操作。
使用 SQLAlchemy 核心的 CRUD
原文：https://overiq.com/sqlalchemy-101/crud-using-sqlalchemy-core/
最后更新于 2020 年 7 月 27 日
SQL 表达式语言
SQL 表达式语言是一种使用 Python 编写 SQL 语句的后端中立方式。
插入记录
有几种方法可以将记录插入数据库。最基本的方法是使用Table实例的insert()方法，并将列的值作为关键字参数传递给values()方法。
ins = customers.insert().values(
first_name = 'John',
last_name = 'Green',
username = 'johngreen',
email = 'johngreen@mail.com',
address = '164 Hidden Valley Road',
town = 'Norfolk'
)
要查看该代码将生成的 SQL，请键入以下内容:
str(ins)
预期输出:
'INSERT INTO customers (first_name, last_name, username, email, created_on, updated_on)
VALUES (:first_name, :last_name, :username, :email, :created_on, :updated_on)'
请注意，VALUES子句包含绑定参数(即形式为:name的参数)，而不是传递给values()方法的值。
当对数据库运行查询时，方言将用实际值替换绑定参数。方言也将逃避价值观，以减轻 SQL 注入的风险。
我们可以通过编译 insert 语句来查看将替换绑定参数的值。
ins.compile().params
预期输出:
{'address': '164 Hidden Valley Road',
'created_on': None,
'email': 'johngreen@mail.com',
'first_name': 'John',
'last_name': 'Green',
'town': 'Norfolk',
'updated_on': None,
'username': 'johngreen'}
我们已经创建了 insert 语句，但是还没有将其发送到数据库。为此，调用Connection对象的execute()方法。
conn = engine.connect()
conn
r = conn.execute(ins)
r
预期输出:
<sqlalchemy.engine.base.Connection object at 0x7fa82a7d53c8>
<sqlalchemy.engine.result.ResultProxy object at 0x7fa828096da0>
上述代码在customers表中插入了以下记录。
execute()方法返回类型为ResultProxy的对象。ResultProxy提供了几个属性，其中一个叫做inserted_primary_key，它返回刚刚插入的记录的主键。
r.inserted_primary_key
type(r.inserted_primary_key)
预期输出:
[1]
list
创建 insert 语句的另一种方法是从sqlalchemy包中使用独立的insert()函数。
from sqlalchemy import insert
ins = insert(customers).values(
first_name = 'Katherine',
last_name = 'Wilson',
username = 'katwilson',
email = 'katwilson@gmail.com',
address = '4685 West Side Avenue',
town = 'Peterbrugh'
)
r = conn.execute(ins)
r.inserted_primary_key
预期输出:
[2]
多重插入
我们也可以将值传递给execute()方法，而不是将值作为关键字参数传递给values()方法。
ins = insert(customers)
r = conn.execute(ins,
first_name = "Tim",
last_name = "Snyder",
username = "timsnyder",
email = "timsnyder@mail.com",
address = '1611 Sundown Lane',
town = 'Langdale'
)
r.inserted_primary_key
预期输出:
[4]
execute()方法非常灵活，因为它允许我们通过传递一个字典列表来插入多行，每个字典代表一个要插入的行。
r = conn.execute(ins, [
{
"first_name": "John",
"last_name": "Lara",
"username": "johnlara",
"email":"johnlara@mail.com",
"address": "3073 Derek Drive",
"town": "Norfolk"
},
{
"first_name": "Sarah",
"last_name": "Tomlin",
"username": "sarahtomlin",
"email":"sarahtomlin@mail.com",
"address": "3572 Poplar Avenue",
"town": "Norfolk"
},
{
"first_name": "Pablo",
"last_name": "Gibson",
"username": "pablogibson",
"email":"pablogibson@mail.com",
"address": "3494 Murry Street",
"town": "Peterbrugh"
},
{
"first_name": "Pablo",
"last_name": "Lewis",
"username": "pablolewis",
"email":"pablolewis@mail.com",
"address": "3282 Jerry Toth Drive",
"town": "Peterbrugh"
},
])
r.rowcount
预期输出:
4
在进入下一部分之前，让我们在items、orders和order_lines表中添加一些记录。
items_list = [
{
"name":"Chair",
"cost_price": 9.21,
"selling_price": 10.81,
"quantity": 5
},
{
"name":"Pen",
"cost_price": 3.45,
"selling_price": 4.51,
"quantity": 3
},
{
"name":"Headphone",
"cost_price": 15.52,
"selling_price": 16.81,
"quantity": 50
},
{
"name":"Travel Bag",
"cost_price": 20.1,
"selling_price": 24.21,
"quantity": 50
},
{
"name":"Keyboard",
"cost_price": 20.12,
"selling_price": 22.11,
"quantity": 50
},
{
"name":"Monitor",
"cost_price": 200.14,
"selling_price": 212.89,
"quantity": 50
},
{
"name":"Watch",
"cost_price": 100.58,
"selling_price": 104.41,
"quantity": 50
},
{
"name":"Water Bottle",
"cost_price": 20.89,
"selling_price": 25.00,
"quantity": 50
},
]
order_list = [
{
"customer_id": 1
},
{
"customer_id": 1
}
]
order_line_list = [
{
"order_id": 1,
"item_id": 1,
"quantity": 5
},
{
"order_id": 1,
"item_id": 2,
"quantity": 2
},
{
"order_id": 1,
"item_id": 3,
"quantity": 1
},
{
"order_id": 2,
"item_id": 1,
"quantity": 5
},
{
"order_id": 2,
"item_id": 2,
"quantity": 5
},
]
r = conn.execute(insert(items), items_list)
r.rowcount
r = conn.execute(insert(orders), order_list)
r.rowcount
r = conn.execute(insert(order_lines), order_line_list)
r.rowcount
预期输出:
8
2
5
选择记录
要选择记录，我们使用Table对象的select()方法。
s = customers.select()
str(s)
预期输出:
'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \nFROM customers'
如您所见，该查询无论如何都是不合格的，因此，它将返回customers表中的所有行。
创建SELECT查询的另一种方法是使用独立的select()功能。它接受从中检索数据的表或列的列表。
from sqlalchemy import select
s = select([customers])
str(s)
预期输出:
'SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on \nFROM customers'
像往常一样，要将查询发送到数据库，我们使用execute()方法:
r = conn.execute(s)
r.fetchall()
预期输出:
[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444)),
(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 7, 8, 20, 37, 57, 407023), datetime.datetime(2018, 7, 8, 20, 37, 57, 407053)),
(17, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 7, 8, 22, 19, 55, 721864), datetime.datetime(2018, 7, 8, 22, 19, 55, 721895)),
(18, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559344), datetime.datetime(2018, 7, 8, 22, 20, 11, 559380)),
(19, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559397), datetime.datetime(2018, 7, 8, 22, 20, 11, 559411)),
(20, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559424), datetime.datetime(2018, 7, 8, 22, 20, 11, 559437)),
(21, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559450), datetime.datetime(2018, 7, 8, 22, 20, 11, 559464))]
ResultProxy对象的fetchall()方法返回查询匹配的所有记录。一旦结果集用尽，对fetchall()的后续调用将返回一个空列表。
r.fetchall()
预期输出:
[]
>>>
fetchall()方法一次将所有结果加载到内存中。因此，它在大复位集上不是很有效。或者，您可以使用 for 循环一次迭代一个结果集。
rs = conn.execute(s)
for row in rs:
print(row)
预期输出:
(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444))
(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 7, 8, 20, 37, 57, 407023), datetime.datetime(2018, 7, 8, 20, 37, 57, 407053))
(17, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 7, 8, 22, 19, 55, 721864), datetime.datetime(2018, 7, 8, 22, 19, 55, 721895))
(18, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559344), datetime.datetime(2018, 7, 8, 22, 20, 11, 559380))
(19, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559397), datetime.datetime(2018, 7, 8, 22, 20, 11, 559411))
(20, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559424), datetime.datetime(2018, 7, 8, 22, 20, 11, 559437))
(21, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559450), datetime.datetime(2018, 7, 8, 22, 20, 11, 559464))
这里列出了ResultProxy对象的一些常用方法和属性。
| 方法/属性 | 描述 |
|---|---|
fetchone() |
从结果集中获取下一行。如果结果集已经用尽，对fetchone()的后续调用将返回None。 |
fetchmany(size=None) |
从结果集中获取指定数量的行。如果结果集已经用尽，对fetchmany()的后续调用将返回None。 |
fetchall() |
从结果集中获取所有行。如果结果集已经用尽，对fetchall()的后续调用将返回None。 |
first() |
从结果集中获取第一行并关闭连接。这意味着在调用first()方法后，我们不能访问结果集中的任何其他行，直到我们再次将查询发送到数据库(使用execute()方法)。 |
rowcount |
返回结果集中的行数。 |
keys() |
返回从中检索数据的列列表。 |
scalar() |
从第一行获取第一列并关闭连接。如果结果集为空，则返回None。 |
下面的 shell 会话演示了我们刚才讨论的方法和属性。
s = select([customers])
fetchone()
r = conn.execute(s)
r.fetchone()
r.fetchone()
预期输出:
(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444))
(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 7, 8, 20, 37, 57, 407023), datetime.datetime(2018, 7, 8, 20, 37, 57, 407053))
fetchmany()
r = conn.execute(s)
r.fetchmany(3)
r.fetchmany(5)
预期输出:
[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444)),
(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 7, 8, 20, 37, 57, 407023), datetime.datetime(2018, 7, 8, 20, 37, 57, 407053)),
(17, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 7, 8, 22, 19, 55, 721864), datetime.datetime(2018, 7, 8, 22, 19, 55, 721895))]
[(18, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559344), datetime.datetime(2018, 7, 8, 22, 20, 11, 559380)),
(19, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559397), datetime.datetime(2018, 7, 8, 22, 20, 11, 559411)),
(20, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559424), datetime.datetime(2018, 7, 8, 22, 20, 11, 559437)),
(21, 'Pablo', 'Lewis', 'pablolewis', 'pablolewis@mail.com', '3282 Jerry Toth Drive', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559450), datetime.datetime(2018, 7, 8, 22, 20, 11, 559464))]
第一次()
r = conn.execute(s)
r.first()
r.first() # this will result in an error
预期输出:
(4, 'Jon', 'Green', 'jongreen', 'jongreen@gmail.com', datetime.datetime(2018, 6, 22, 10, 3), datetime.datetime(2018, 6, 22, 10, 3))
...
ResourceClosedError: This result object is closed.
行数
r = conn.execute(s)
r.rowcount
预期输出:
7
按键()
r.keys()
预期输出:
['id',
'first_name',
'last_name',
'username',
'email',
'address',
'town',
'created_on',
'updated_on']
>>>
标量()
r.scalar()
预期输出:
4
需要注意的是，通过方法fetchxxx()和first()返回的行不是元组或字典，而是类型为RowProxy的对象，它允许我们使用列名、索引位置或Column实例来访问行中的数据。例如:
r = conn.execute(s)
row = r.fetchone()
row
type(row)
row['id'], row['first_name'] # access column data via column name
row[0], row[1] # access column data via column index position
row[customers.c.id], row[customers.c.first_name] # access column data via Column object
row.id, row.first_name # access column data via attribute
预期输出:
(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444))
sqlalchemy.engine.result.RowProxy
(1, 'John')
(1, 'John')
(1, 'John')
(1, 'John')
要从多个表中访问数据，只需将逗号分隔的Table实例列表传递给select()函数。
select([tableOne, tableTwo])
这段代码将返回两个表中的行的笛卡尔乘积。我们将在本章后面学习如何创建一个 SQL JOIN。
过滤记录
为了过滤记录，我们使用where()方法。它接受了一个条件，并在SELECT声明中增加了一个WHERE条款。
s = select([items]).where(
items.c.cost_price > 20
)
str(s)
r = conn.execute(s)
r.fetchall()
此查询将返回成本价大于 20 的所有项目。
预期输出:
'SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity \nFROM items \nWHERE items.cost_price > :cost_price_1'
[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
我们可以通过简单链接where()方法来指定附加条件。
s = select([items]).\
where(items.c.cost_price + items.c.selling_price > 50).\
where(items.c.quantity > 10)
print(s)
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.cost_price + items.selling_price > :param_1 AND items.quantity > :quantity_1
如你所见，当我们使用where()方法时，这些条件被叠加在一起。
那么我们如何在我们的SELECT声明中指定OR或NOT条件呢？
事实证明，除了链接where()方法，还有两种方法可以组合条件:
- 按位运算符。
- 连词。
让我们从第一个开始。
按位运算符
按位运算符&、|和~允许我们分别用 SQL AND、OR和NOT运算符连接条件。
前面的查询可以使用按位运算符进行编码，如下所示:
s = select([items]).\
where(
(items.c.cost_price + items.c.selling_price > 50) &
(items.c.quantity > 10)
)
请注意，条件是用括号包装的，这是因为按位运算符的优先级大于+和>运算符。
下面是更多的例子:
例:1
s = select([items]).\
where(
(items.c.cost_price > 200 ) |
(items.c.quantity < 5)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.cost_price > :cost_price_1 OR items.quantity < :quantity_1
[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]
例:2
s = select([items]).\
where(
~(items.c.quantity == 50)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity != :quantity_1
[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]
例:3
s = select([items]).\
where(
~(items.c.quantity == 50) &
(items.c.cost_price < 20)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity != :quantity_1 AND items.cost_price < :cost_price_1
[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3)]
连词
连接条件的另一种方式是使用连接功能，即and_()、or_()和not_()。这是在 SQLAlchemy 中定义条件的首选方式。
以下是一些例子:
例 1:
s = select([items]).\
where(
and_(
items.c.quantity >= 50,
items.c.cost_price < 100,
)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1
[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
例 2:
s = select([items]).\
where(
or_(
items.c.quantity >= 50,
items.c.cost_price < 100,
)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity >= :quantity_1 OR items.cost_price < :cost_price_1
[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
例 3:
s = select([items]).\
where(
and_(
items.c.quantity >= 50,
items.c.cost_price < 100,
not_(
items.c.name == 'Headphone'
),
)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity >= :quantity_1 AND items.cost_price < :cost_price_1 AND items.name != :name_1
[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
其他常见的比较运算符
下面的清单演示了如何在 SQLAlchemy 中定义条件时使用其他一些比较运算符。
为空
s = select([orders]).where(
orders.c.date_shipped == None
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped
FROM orders
WHERE orders.date_shipped IS NULL
[(1, 1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), None),
(2, 1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), None)]
不为空
s = select([orders]).where(
orders.c.date_shipped != None
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT orders.id, orders.customer_id, orders.date_placed, orders.date_shipped
FROM orders
WHERE orders.date_shipped IS NOT NULL
[]
在…里
s = select([customers]).where(
customers.c.first_name.in_(["Sarah", "John"])
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on
FROM customers
WHERE customers.first_name IN (:first_name_1, :first_name_2)
[(1, 'John', 'Green', 'johngreen', 'johngreen@mail.com', '164 Hidden Valley Road', 'Norfolk', datetime.datetime(2018, 7, 8, 19, 6, 13, 844404), datetime.datetime(2018, 7, 8, 19, 6, 13, 844444)),
(18, 'John', 'Lara', 'johnlara', 'johnlara@mail.com', '3073 Derek Drive', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559344), datetime.datetime(2018, 7, 8, 22, 20, 11, 559380)),
(19, 'Sarah', 'Tomlin', 'sarahtomlin', 'sarahtomlin@mail.com', '3572 Poplar Avenue', 'Norfolk', datetime.datetime(2018, 7, 8, 22, 20, 11, 559397), datetime.datetime(2018, 7, 8, 22, 20, 11, 559411))
不在
s = select([customers]).where(
customers.c.first_name.notin_(["Sarah", "John"])
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on
FROM customers
WHERE customers.first_name NOT IN (:first_name_1, :first_name_2)
[(2, 'Katherine', 'Wilson', 'katwilson', 'katwilson@gmail.com', '4685 West Side Avenue', 'Peterbrugh', datetime.datetime(2018, 7, 8, 20, 37, 57, 407023), datetime.datetime(2018, 7, 8, 20, 37, 57, 407053)),
(17, 'Tim', 'Snyder', 'timsnyder', 'timsnyder@mail.com', '1611 Sundown Lane', 'Langdale', datetime.datetime(2018, 7, 8, 22, 19, 55, 721864), datetime.datetime(2018, 7, 8, 22, 19, 55, 721895)),
(20, 'Pablo', 'Gibson', 'pablogibson', 'pablogibson@mail.com', '3494 Murry Street', 'Peterbrugh', datetime.datetime(2018, 7, 8, 22, 20, 11, 559424), datetime.datetime(2018, 7, 8, 22, 20, 11, 559437)),
在...之间
s = select([items]).where(
items.c.cost_price.between(10, 20)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.cost_price BETWEEN :cost_price_1 AND :cost_price_2
[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]
不在中间
s = select([items]).where(
not_(items.c.cost_price.between(10, 20))
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.cost_price NOT BETWEEN :cost_price_1 AND :cost_price_2
[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
喜欢
s = select([items]).where(
items.c.name.like("Wa%")
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.name LIKE :name_1
[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
like()方法执行区分大小写的匹配。对于不区分大小写的匹配，使用ilike()。
s = select([items]).where(
items.c.name.ilike("wa%")
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE lower(items.name) LIKE lower(:name_1)
[(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
不喜欢
s = select([items]).where(
not_(items.c.name.like("wa%"))
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.name NOT LIKE :name_1
[(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50)]
排序结果
order_by()方法将ORDER BY子句添加到SELECT语句中。它接受一列或多列作为排序依据。对于order_by()子句中列出的每一列，您可以指定行是按升序排序(使用asc())还是按降序排序(使用desc())。如果没有指定的行按升序排序。例如:
s = select([items]).where(
items.c.quantity > 10
).order_by(items.c.cost_price)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity > :quantity_1 ORDER BY items.cost_price
[(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50)]
该查询返回按cost_price升序排序的行。它相当于:
from sqlalchemy import asc
s = select([items]).where(
items.c.quantity > 10
).order_by(asc(items.c.cost_price))
print(s)
rs = conn.execute(s)
rs.fetchall()
使用desc()功能对结果进行降序排序。例如:
from sqlalchemy import desc
s = select([items]).where(
items.c.quantity > 10
).order_by(desc(items.c.cost_price))
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.quantity > :quantity_1 ORDER BY items.cost_price DESC
[(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]
下面是另一个例子，它根据两列对行进行排序，首先按quantity(升序)，然后按cost_price(降序)。
s = select([items]).order_by(
items.c.quantity,
desc(items.c.cost_price)
)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items ORDER BY items.quantity, items.cost_price DESC
[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5),
(6, 'Monitor', Decimal('200.14'), Decimal('212.89'), 50),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50),
(8, 'Water Bottle', Decimal('20.89'), Decimal('25.00'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50),
(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(3, 'Headphone', Decimal('15.52'), Decimal('16.81'), 50)]
限制结果
limit()方法将LIMIT子句添加到SELECT语句中。它接受一个整数，该整数指示要返回的行数。例如:
s = select([items]).order_by(
items.c.quantity
).limit(2)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items ORDER BY items.quantity
LIMIT :param_1
[(2, 'Pen', Decimal('3.45'), Decimal('4.51'), 3),
(1, 'Chair', Decimal('9.21'), Decimal('10.81'), 5)]
使用offset()方法指定LIMIT子句的偏移量(即起始位置)。
s = select([items]).order_by(
items.c.quantity
).limit(2).offset(2)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items ORDER BY items.quantity
LIMIT :param_1 OFFSET :param_2
[(4, 'Travel Bag', Decimal('20.10'), Decimal('24.21'), 50),
(5, 'Keyboard', Decimal('20.12'), Decimal('22.11'), 50)]
限制列
到目前为止，我们创建的SELECT语句已经从表的所有列中返回了数据。我们可以通过将字段名称作为列表传递给select()函数来限制查询返回的字段数量。例如:
s = select([items.c.name, items.c.quantity]).where(
items.c.quantity == 50
)
print(s)
rs = conn.execute(s)
str(rs.keys())
rs.fetchall()
预期输出:
SELECT items.name, items.quantity
FROM items
WHERE items.quantity = :quantity_1
['name', 'quantity']
[('Headphone', 50),
('Travel Bag', 50),
('Keyboard', 50),
('Monitor', 50),
('Watch', 50),
('Water Bottle', 50)]
该查询仅返回来自items表的name和quantity列的数据。
就像在 SQL 中一样，在将行发送到输出之前，我们可以对检索到的行执行简单的计算。例如:
s = select([
items.c.name,
items.c.quantity,
items.c.selling_price * 5
]).where(
items.c.quantity == 50
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS anon_1
FROM items
WHERE items.quantity = :quantity_1
['name', 'quantity', 'anon_1']
[('Headphone', 50, Decimal('84.05')),
('Travel Bag', 50, Decimal('121.05')),
('Keyboard', 50, Decimal('110.55')),
('Monitor', 50, Decimal('1064.45')),
('Watch', 50, Decimal('522.05')),
('Water Bottle', 50, Decimal('125.00'))]
注意items.c.selling_price * 5不是实际的列，因此生成了一个名为anon_1的匿名名称来显示查询结果(第 5 行)。
我们可以使用label()方法给列或表达式分配一个标签，该方法通过在SELECT语句中添加一个AS子条款来工作。
s = select([
items.c.name,
items.c.quantity,
(items.c.selling_price * 5).label('price')
]).where(
items.c.quantity == 50
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT items.name, items.quantity, items.selling_price * :selling_price_1 AS price
FROM items
WHERE items.quantity = :quantity_1
['name', 'quantity', 'price']
[('Headphone', 50, Decimal('84.05')),
('Travel Bag', 50, Decimal('121.05')),
('Keyboard', 50, Decimal('110.55')),
('Monitor', 50, Decimal('1064.45')),
('Watch', 50, Decimal('522.05')),
('Water Bottle', 50, Decimal('125.00'))]
访问内置函数
要访问数据库提供的内置函数，我们使用func对象。下面的列表显示了如何使用 PostgreSQL 数据库中的日期/时间、数学和字符串函数。
from sqlalchemy.sql import func
c = [
## date/time functions ##
func.timeofday(),
func.localtime(),
func.current_timestamp(),
func.date_part("month", func.now()),
func.now(),
## mathematical functions ##
func.pow(4,2),
func.sqrt(441),
func.pi(),
func.floor(func.pi()),
func.ceil(func.pi()),
## string functions ##
func.lower("ABC"),
func.upper("abc"),
func.length("abc"),
func.trim(" ab c "),
func.chr(65),
]
s = select(c)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
['timeofday_1',
'localtime_1',
'current_timestamp_1',
'date_part_1',
'now_1',
'pow_1',
'sqrt_1',
'pi_1',
'floor_1',
'ceil_1',
'lower_1',
'upper_1',
'length_1',
'trim_1',
'chr_1']
[('Mon Jul 09 00:03:29.638802 2018 IST', datetime.time(23, 6, 54, 773833), datetime.datetime(2018, 7, 8, 23, 6, 54, 773833, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 7.0, datetime.datetime(2018, 7, 8, 23, 6, 54, 773833, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), 16.0, 21.0, 3.14159265358979, 3.0, 4.0, 'abc', 'ABC', 3, 'ab c', 'A')]
您还可以通过func对象访问聚合函数。
from sqlalchemy.sql import func
c = [
func.sum(items.c.quantity),
func.avg(items.c.quantity),
func.max(items.c.quantity),
func.min(items.c.quantity),
func.count(customers.c.id),
]
s = select(c)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT sum(items.quantity) AS sum_1, avg(items.quantity) AS avg_1, max(items.quantity) AS max_1, min(items.quantity) AS min_1, count(customers.id) AS count_1
FROM items, customers
['sum_1', 'avg_1', 'max_1', 'min_1', 'count_1']
[(1848, Decimal('38.5000000000000000'), 50, 3, 48)]
分组结果
通过GROUP BY子句对结果进行分组。它通常与聚合函数结合使用。我们使用group_by()方法在select声明中添加GROUP BY条款。它接受一列或多列，并根据列中的值对行进行分组。例如:
from sqlalchemy.sql import func
c = [
func.count("*").label('count'),
customers.c.town
]
s = select(c).group_by(customers.c.town)
print(s)
conn.execute(s).fetchall()
预期输出:
SELECT count(:count_1) AS count, customers.town
FROM customers GROUP BY customers.town
[(1, 'Langdale'), (2, 'Peterbrugh'), (3, 'Norfolk')]
该查询返回每个城镇的客户数量。
为了根据聚合函数返回的值过滤出结果，我们使用having()方法，该方法将HAVING子句添加到SELECT语句中。就像where()条款一样，它接受一个条件。
from sqlalchemy.sql import func
c = [
func.count("*").label('count'),
customers.c.town
]
s = select(c).group_by(customers.c.town).having(func.count("*") > 2)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT count(:count_1) AS count, customers.town
FROM customers GROUP BY customers.town
HAVING count(:count_2) > :count_3
['count', 'town']
[(3, 'Norfolk')]
连接
Table实例提供了以下两种方法来创建连接:
join()-创建内部连接outerjoin()-创建外部连接(具体为LEFT OUTER JOIN)
内部联接只返回符合联接条件的行，而外部联接返回符合联接条件的行以及一些附加行。
这两种方法都接受一个Table实例，根据外键关系计算出连接条件，并返回一个JOIN构造。
>>>
>>> print(customers.join(orders))
customers JOIN orders ON customers.id = orders.customer_id
>>>
>>>
如果这些方法不能正确计算连接条件，或者您想指定一个替代条件，您可以通过手动传递连接条件作为第二个参数来实现。
>>>
>>> print(customers.join(items,
... customers.c.address.like(customers.c.first_name + '%')
... )
... )
customers JOIN items ON customers.address LIKE customers.first_name || :first_name_1
>>>
>>>
当我们在select()函数中指定表或列列表时，SQLAlchemy 会自动将这些表放在FROM子句中。然而，当我们使用连接时，我们确切地知道我们想要在FROM子句中的表，所以我们使用select_from()方法。然而，如果我们愿意，我们也可以在不涉及连接的查询中使用select_from()。例如:
s = select([
customers.c.id,
customers.c.first_name
]).select_from(
customers
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT customers.id, customers.first_name
FROM customers
['id', 'first_name']
[(1, 'John'),
(2, 'Katherine'),
(17, 'Tim'),
(18, 'John'),
(19, 'Sarah'),
(20, 'Pablo')]
让我们利用这些知识找到所有按客户名称John Green下的订单。
s = select([
orders.c.id,
orders.c.date_placed
]).select_from(
orders.join(customers)
).where(
and_(
customers.c.first_name == "John",
customers.c.last_name == "Green",
)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT orders.id, orders.date_placed
FROM orders JOIN customers ON customers.id = orders.customer_id
WHERE customers.first_name = :first_name_1 AND customers.last_name = :last_name_1
['id', 'date_placed']
[(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526)),
(2, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549))]
前面的查询只返回顺序id和date_placed。知道每个订单的项目和数量不是更好吗？
为了获得每个订单中的项目和项目数量，我们必须创建总共 3 个连接，一直到items表。
s = select([
orders.c.id.label('order_id'),
orders.c.date_placed,
order_lines.c.quantity,
items.c.name,
]).select_from(
orders.join(customers).join(order_lines).join(items)
).where(
and_(
customers.c.first_name == "John",
customers.c.last_name == "Green",
)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
orders.id AS order_id,
orders.date_placed,
order_lines.quantity,
items.name
FROM
orders
JOIN customers ON customers.id = orders.customer_id
JOIN order_lines ON orders.id = order_lines.order_id
JOIN items ON items.id = order_lines.item_id
WHERE
customers.first_name = : first_name_1
AND customers.last_name = : last_name_1
['order_id', 'date_placed', 'quantity', 'name']
[(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 5, 'Chair'),
(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 2, 'Pen'),
(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 1, 'Headphone'),
(2, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), 5, 'Chair'),
(2, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), 5, 'Pen')]
最后，这里有一个如何定义外部联接的示例。
s = select([
customers.c.first_name,
orders.c.id,
]).select_from(
customers.outerjoin(orders)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
customers.first_name,
orders.id
FROM
customers
LEFT OUTER JOIN orders ON customers.id = orders.customer_id
['first_name', 'id']
[('John', 1),
('John', 2),
('Pablo', None),
('Tim', None),
('John', None),
('Katherine', None),
('Sarah', None)]
我们传递给outerjoin()方法的Table实例位于外部连接的右侧。因此，上面的查询将返回来自customers表(左表)的所有行，并且仅从orders表(右表)返回满足连接条件的行。
如果您想要order表中的所有行，但只想要orders表中满足连接条件的行，请调用outerjoin()，如下所示:
s = select([
customers.c.first_name,
orders.c.id,
]).select_from(
orders.outerjoin(customers)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
customers.first_name,
orders.id
FROM
orders
LEFT OUTER JOIN customers ON customers.id = orders.customer_id
['first_name', 'id']
[('John', 1), ('John', 2)]
您也可以通过将full=True传递给outerjoin()方法来创建FULL OUTER JOIN。例如:
s = select([
customers.c.first_name,
orders.c.id,
]).select_from(
orders.outerjoin(customers, full=True)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
customers.first_name,
orders.id
FROM
orders
FULL OUTER JOIN customers ON customers.id = orders.customer_id
['first_name', 'id']
[('John', 1),
('John', 2),
('Pablo', None),
('Tim', None),
('John', None),
('Katherine', None),
('Sarah', None)]
更新记录
使用update()功能更新记录。例如，以下查询将Water Bottle的selling_price和quantity分别更新为30和60。
from sqlalchemy import update
s = update(items).where(
items.c.name == 'Water Bottle'
).values(
selling_price = 30,
quantity = 60,
)
print(s)
rs = conn.execute(s)
rs.rowcount # count of rows updated
预期输出:
UPDATE items
SET selling_price =: selling_price,
quantity =: quantity
WHERE
items. NAME = : name_1
1
删除记录
要删除记录，我们使用delete()功能。
from sqlalchemy import delete
s = delete(customers).where(
customers.c.username.like('pablo%')
)
print(s)
rs = conn.execute(s)
rs.rowcount
预期输出:
DELETE
FROM
customers
WHERE
customers.username LIKE : username_1
1
该查询将删除所有用户名以pablo开头的客户。
处理重复项
为了处理结果集中的重复行，我们使用DISTINCT选项。我们可以使用distinct()方法将DISTINCT选项添加到SELECT语句中。例如:
# without DISTINCT
s = select([customers.c.town]).where(customers.c.id < 10)
print(s)
rs = conn.execute(s)
rs.fetchall()
# with DISTINCT
s = select([customers.c.town]).where(customers.c.id < 10).distinct()
print(s)
rs = conn.execute(s)
rs.fetchall()
预期输出:
SELECT customers.town
FROM customers
WHERE customers.id < :id_1
[('Norfolk',), ('Peterbrugh',), ('Langdale',), ('Norfolk',), ('Norfolk',)]
SELECT DISTINCT customers.town
FROM customers
WHERE customers.id < :id_1
[('Peterbrugh',), ('Langdale',), ('Norfolk',)]
下面是另一个例子，它使用带有count()聚合函数的distinct()选项，并计算customers表中不同的城镇数量。
s = select([
func.count(distinct(customers.c.town)),
func.count(customers.c.town)
])
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
COUNT (DISTINCT customers.town) AS count_1,
COUNT (customers.town) AS count_2
FROM
customers
['count_1', 'count_2']
[(3, 5)]
铸造
将数据从一种类型转换为另一种类型是一种常见的操作，通过sqlalchemy包中的cast()功能完成。
from sqlalchemy import cast, Date
s = select([
cast(func.pi(), Integer),
cast(func.pi(), Numeric(10,2)),
cast("2010-12-01", DateTime),
cast("2010-12-01", Date),
])
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
CAST (pi() AS INTEGER) AS anon_1,
CAST (pi() AS NUMERIC(10, 2)) AS anon_2,
CAST (:param_1 AS DATETIME) AS anon_3,
CAST (:param_2 AS DATE) AS anon_4
['anon_1', 'anon_2', 'anon_3', 'anon_4']
[(3, Decimal('3.14'), datetime.datetime(2010, 12, 1, 0, 0), datetime.date(2010, 12, 1))]
联盟
SQL 的UNION运算符允许我们组合多个SELECT语句的结果集。要将UNION运算符添加到我们的SELECT语句中，我们使用union()函数。
u = union(
select([items.c.id, items.c.name]).where(items.c.name.like("Wa%")),
select([items.c.id, items.c.name]).where(items.c.name.like("%e%")),
).order_by(desc("id"))
print(items.c.name)
print(u)
rs = conn.execute(u)
print(rs.keys())
rs.fetchall()
预期输出:
SELECT items.id, items.name
FROM items
WHERE items.name LIKE :name_1 UNION SELECT items.id, items.name
FROM items
WHERE items.name LIKE :name_2 ORDER BY id DESC
['id', 'name']
[(8, 'Water Bottle'),
(7, 'Watch'),
(5, 'Keyboard'),
(4, 'Travel Bag'),
(3, 'Headphone'),
(2, 'Pen')]
默认情况下，union()从结果集中删除所有重复的行。如果您想保留副本，请使用union_all()。
from sqlalchemy import union_all
s = union_all(
select([items.c.id, items.c.name]).where(items.c.name.like("Wa%")),
select([items.c.id, items.c.name]).where(items.c.name.like("%e%")),
).order_by(desc("id"))
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT items.id, items.name
FROM items
WHERE items.name LIKE :name_1 UNION ALL SELECT items.id, items.name
FROM items
WHERE items.name LIKE :name_2 ORDER BY id DESC
['id', 'name']
[(8, 'Water Bottle'),
(8, 'Water Bottle'),
(7, 'Watch'),
(5, 'Keyboard'),
(4, 'Travel Bag'),
(3, 'Headphone'),
(2, 'Pen')]
创建子查询
我们还可以使用子查询从多个表中访问数据。
以下查询返回第一个订单中John Green订购的物品的 id 和名称:
s = select([items.c.id, items.c.name]).where(
items.c.id.in_(
select([order_lines.c.item_id]).select_from(customers.join(orders).join(order_lines)).where(
and_(
customers.c.first_name == 'John',
customers.c.last_name == 'Green',
orders.c.id == 1
)
)
)
)
print(s)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
SELECT
items.ID,
items. NAME
FROM
items
WHERE
items.ID IN (
SELECT
order_lines.item_id
FROM
customers
JOIN orders ON customers.ID = orders.customer_id
JOIN order_lines ON orders.ID = order_lines.order_id
WHERE
customers.first_name = : first_name_1
AND customers.last_name = : last_name_1
AND orders.ID = : id_1
)
['id', 'name']
[(3, 'Headphone'), (1, 'Chair'), (2, 'Pen')]
该查询也可以使用 JOINs 编写，如下所示:
s = select([items.c.id, items.c.name]).select_from(customers.join(orders).join(order_lines).join(items)).where(
and_(
customers.c.first_name == 'John',
customers.c.last_name == 'Green',
orders.c.id == 1
)
)
rs = conn.execute(s)
rs.keys()
rs.fetchall()
预期输出:
['id', 'name']
[(1, 'Chair'), (2, 'Pen'), (3, 'Headphone')]
原始查询
SQLAlchemy 还为您提供了使用text()函数执行原始 SQL 的灵活性。例如，以下SELECT语句返回所有订单，以及John Green订购的项目。
from sqlalchemy.sql import text
s = text(
"""
SELECT
orders.id as "Order ID", orders.date_placed, items.id, items.name
FROM
customers
INNER JOIN orders ON customers.id = orders.customer_id
INNER JOIN order_lines ON order_lines.order_id = orders.id
INNER JOIN items ON items.id= order_lines.item_id
where customers.first_name = :first_name and customers.last_name = :last_name
"""
)
print(s)
rs = conn.execute(s, first_name="John", last_name='Green')
rs.fetchall()
预期输出:
SELECT
orders.id as "Order ID", orders.date_placed, items.id, items.name
FROM
customers
INNER JOIN orders ON customers.id = orders.customer_id
INNER JOIN order_lines ON order_lines.order_id = orders.id
INNER JOIN items ON items.id= order_lines.item_id
where customers.first_name = :first_name and customers.last_name = :last_name
[(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 1, 'Chair'),
(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 2, 'Pen'),
(1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), 3, 'Headphone'),
(2, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), 1, 'Chair'),
(2, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), 2, 'Pen')]
注意SELECT语句包含两个绑定参数:first_name和last_name。这些参数的值通过execute()方法传递。
text()功能也可以嵌入到select()构造中。例如:
s = select([items]).where(
text("items.name like 'Wa%'")
).order_by(text("items.id desc"))
print(s)
rs = conn.execute(s)
rs.fetchall()
预期输出:
SELECT items.id, items.name, items.cost_price, items.selling_price, items.quantity
FROM items
WHERE items.name like 'Wa%' ORDER BY items.id desc
[(8, 'Water Bottle', Decimal('20.89'), Decimal('30.00'), 60),
(7, 'Watch', Decimal('100.58'), Decimal('104.41'), 50)]
执行原始 SQL 的另一种方法是直接传递给execute()方法。例如:
rs = conn.execute("select * from orders;")
rs.fetchall()
预期输出:
[(1, 1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175526), None),
(2, 1, datetime.datetime(2018, 7, 8, 22, 36, 20, 175549), None)]
处理
事务是一种执行一组 SQL 语句的方式，以便成功执行所有语句或根本不执行任何语句。如果事务中涉及的任何语句失败，那么数据库将返回到事务启动前的状态。
我们目前在数据库中有两个订单。为了完成订单，我们需要执行以下两个操作:
- 从
items表中减去订购项目的数量 - 更新
date_shipped列以包含日期时间值。
这两个动作必须作为一个单元来执行，以确保表中的数据是正确的。
Connection对象提供了一个begin()方法，该方法启动事务并返回一个类型为Transaction的对象。Transaction对象依次提供rollback()和commit()方法，分别回滚和提交事务。
在下面的列表中，我们定义了接受order_id作为参数的dispatch_order()方法，并使用事务执行上述操作。
from sqlalchemy.exc import IntegrityError
def dispatch_order(order_id):
# check whether order_id is valid or not
r = conn.execute(select([func.count("*")]).where(orders.c.id == order_id))
if not r.scalar():
raise ValueError("Invalid order id: {}".format(order_id))
# fetch items in the order
s = select([order_lines.c.item_id, order_lines.c.quantity]).where(
order_lines.c.order_id == order_id
)
rs = conn.execute(s)
ordered_items_list = rs.fetchall()
# start transaction
t = conn.begin()
try:
for i in ordered_items_list:
u = update(items).where(
items.c.id == i.item_id
).values(quantity = items.c.quantity - i.quantity)
rs = conn.execute(u)
u = update(orders).where(orders.c.id == order_id).values(date_shipped=datetime.now())
rs = conn.execute(u)
t.commit()
print("Transaction completed.")
except IntegrityError as e:
print(e)
t.rollback()
print("Transaction failed.")
我们的第一个订单是 5 把椅子，2 支笔和 1 个耳机。用1的订单 id 调用dispatch_order()函数，将返回如下输出:
dispatch_order(1)
预期输出:
Transaction completed.
此时，items和order_lines表应该是这样的:
我们的下一个订单是 5 把椅子和 4 支笔，但是我们现在只有 5 把椅子和 1 支笔库存！
让我们试着运行dispatch_order()二阶，看看会发生什么。
dispatch_order(2)
预期输出:
(psycopg2.IntegrityError) new row for relation "items" violates check constraint "quantity_check"
DETAIL: Failing row contains (1, Chair, 9.21, 10.81, -4).
[SQL: 'UPDATE items SET quantity=(items.quantity - %(quantity_1)s) WHERE items.id = %(id_1)s'] [parameters: {'quantity_1': 5, 'id_1': 1}] (Background on this error at: http://sqlalche.me/e/gkpj)
Transaction failed.
不出所料，我们的发货失败了，因为我们的库存中没有足够的笔，并且因为我们正在使用事务，所以我们的数据库恢复到了事务开始之前的状态。
在 SQLAlchemy ORM 中定义模式
原文：https://overiq.com/sqlalchemy-101/defining-schema-in-sqlalchemy-orm/
最后更新于 2020 年 7 月 27 日
SQL 语法形式
SQLAlchemy ORM(对象关系映射器)是一种使用 Python 类定义表和表之间关系的方法。它还提供了一个使用面向对象代码而不是编写 SQL 来查询和操作数据库的系统。与 SQLAlchemy Core 不同，后者关注表、行和列；ORM 关注的是对象和模型。
ORM 建立在 SQLAlchemy 核心之上，所以到目前为止您所学到的一切仍然适用。
正如您将看到的，ORM 允许您提高工作效率，但是它也增加了查询的额外开销。然而，对于大多数应用来说，它的好处远远超过性能损失。
注意:在进入下一部分之前，使用以下命令删除sqlalchemy-tuts数据库中的所有表格:
metadata.drop_all(engine)
定义模型
模型是一个对应于数据库表的 Python 类，它的属性代表列。
要使类成为有效的模型，它必须执行以下操作:
- 从通过调用
declarative_base()函数创建的声明性基类继承。 - 通过
__tablename__属性定义表名。 - 至少定义一列，该列必须是主键的一部分。
最后两点不言自明，但第一点值得解释一下。
基类维护一个类和表的目录。换句话说，声明性基类包装了映射器和MetaData。映射器将子类映射到表中，MetaData保存关于数据库及其包含的表的所有信息。就像在 Core 中一样，在 ORM 中，我们使用MetaData对象的create_all()和drop_all()方法来创建和删除表。
下面的列表定义了一个Post模型，可以用来存储博客文章。
from sqlalchemy import create_engine, MetaData, Table, Integer, String, \
Column, DateTime, ForeignKey, Numeric
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime
Base = declarative_base()
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
slug = Column(String(100), nullable=False)
content = Column(String(50), nullable=False)
published = Column(String(200), nullable=False, unique=True)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
让我们一行一行地浏览代码。
- 在第 1-4 行，我们导入必要的类和函数。
- 在第 6 行中，我们通过调用
declarative_base()函数来创建一个基础声明类。 - 在第 10-16 行，我们将列定义为类属性。
请注意，我们使用相同的Column类来定义列，就像我们在使用 SQLAlchemy Core 时所做的那样。唯一的区别是，现在第一个参数是类型，而不是列名。另外，请注意传递给Column()的关键字参数在 ORM 和 Core 中的工作方式完全相同。
由于 ORM 建立在 Core 之上，SQLAlchemy 将使用模型定义创建一个Table对象，并通过调用mapper()函数将其与模型相关联。这就完成了将Post模型与其对应的Table实例进行映射的过程。我们现在可以使用Post模型来查询和操作数据库。
我们可以使用__table__属性查看与模型关联的Table实例。
>>>
>>> Post.__table__
Table('posts', MetaData(bind=None),
Column('id', Integer(), table=<posts>, primary_key=True, nullable=False),
Column('title', String(length=100), table=<posts>, nullable=False),
Column('slug', String(length=100), table=<posts>, nullable=False),
Column('content', String(length=50), table=<posts>, nullable=False),
Column('published', String(length=200), table=<posts>, nullable=False), Column('created_on', DateTime(), table=<posts>, default=ColumnDefault(<function datetime.now at 0x7f226c152bf8>)),
Column('updated_on', DateTime(), table=<posts>, onupdate=ColumnDefault(<function datetime.now at 0x7f226c162268>), default=ColumnDefault(<function datetime.now at 0x7f226c1621e0>)),
schema=None)
>>>
经典映射
在阅读了上面的部分之后，你可能会得到一个印象，要使用 SQLAlchemy 的 ORM，你必须将你现有的Table实例重写为模型。然而，这根本不是事实。
事实证明，您可以使用mapper()函数将任何普通 Python 类映射到Table实例。这里有一个例子:
from sqlalchemy import MetaData, Table, String, Column, Text, DateTime, Boolean
from sqlalchemy.orm import mapper
from datetime import datetime
metadata = MetaData()
post = Table('post', metadata,
Column('id', Integer(), primary_key=True),
Column('title', String(200), nullable=False),
Column('slug', String(200), nullable=False),
Column('content', Text(), nullable=False),
Column('published', Boolean(), default=False),
Column('created_on', DateTime(), default=datetime.now)
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)
class Post(object):
pass
mapper(Post, post)
mapper()类有两个参数:要映射的类和Table对象。
通过这样做，Post类将拥有与表的列相对应的属性。因此，Post类现在具有以下属性:
post.idpost.titlepost.slugpost.contentpost.publishedpost.created_onpost.updated_on
上面清单中的代码在功能上等同于我们前面定义的Post模型。
现在你应该很清楚declarative_base()在幕后为我们做了什么。
添加键和约束
当使用 ORM 时，我们使用__table_args__属性向模型添加键和约束。
class User(Base):
__tablename__ = 'users'
id = Column(Integer)
username = Column(String(100), nullable=False)
email = Column(String(100), nullable=False)
password = Column(String(200), nullable=False)
__table_args__ = (
PrimaryKeyConstraint('id', name='user_pk')
UniqueConstraint('username'),
UniqueConstraint('email'),
)
class Post(Base):
__tablename__ = 'posts'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
slug = Column(String(100), nullable=False)
content = Column(String(50), nullable=False)
published = Column(String(200), nullable=False, default=False)
user_id = Column(Integer(), nullable=False)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
__table_args__ = (
ForeignKeyConstraint(['user_id'], ['users.id']),
Index('title_content_index' 'title', 'content'), # composite index on title and content
)
定义关系
一对多
我们通过在子类上放置外键来创建一对多关系。例如:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(string(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book")
class Book(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
title = Column(string(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
线author_id = Column(Integer, ForeignKey('authors.id'))在Author和Book模型之间建立了一对多的关系。
relationship()功能在模型上添加属性以访问相关数据。至少，它接受代表关系目标的类名。
线条books = relationship("Book")给Author类增加了一个books属性。
给定一个Author对象a，我们现在可以使用a.books访问他写的书。
如果我们想从一个Book对象访问书的作者呢？
实现这一点的一种方法是在Author模型上定义单独的relationship()，如下所示:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(string(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book")
class Book(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
title = Column(string(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author")
给定一个Book对象b，我们现在可以以b.author的身份访问它的作者。
或者，我们可以使用backref参数来指定要添加到关系另一端的属性名称。
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(string(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Book", backref="book")
class Book(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
title = Column(string(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
此外，您可以在关系的任何一方定义relationship()。因此，前面的代码也可以写成:
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(string(100), nullable=False)
last_name = Column(String(100), nullable=False)
class Book(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
title = Column(string(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author", backref="books")
一对一
在 SQLAlchemy 中建立一对一的关系几乎和一对多的关系一样，唯一的区别就是我们给relationship()函数传递了一个额外的参数uselist=False。这里有一个例子:
class Person(Base):
__tablename__ = 'persons'
id = Column(Integer(), primary_key=True)
name = Column(String(255), nullable=False)
designation = Column(String(255), nullable=False)
doj = Column(Date(), nullable=False)
dl = relationship('DriverLicense', backref='person', uselist=False)
class DriverLicense(Base):
__tablename__ = 'driverlicense'
id = Column(Integer(), primary_key=True)
license_number = Column(String(255), nullable=False)
renewed_on = Column(Date(), nullable=False)
expiry_date = Column(Date(), nullable=False)
person_id = Column(Integer(), ForeignKey('persons.id')) # Foreign key
给定一个Person对象p，p.dl将返回一个DriverLicense对象。如果我们没有将uselist=False传递给relationship()函数，那么Person和DriverLicense之间的关系将是一对多的，并且p.dl将返回一个DriverLicense对象列表，而不是一个单独的对象。uselist=False参数对DriverLicense对象的persons属性没有任何影响。像往常一样，它会返回一个Person对象。
多对多
创建多对多关系需要一个额外的表，称为关联表或中间表。我们将这个表定义为Table类的一个实例，然后使用relationship()函数的secondary参数将其连接到模型。
Base = declarative_base()
author_book = Table('author_book', Base.metadata,
Column('author_id', Integer(), ForeignKey("authors.id")),
Column('book_id', Integer(), ForeignKey("books.id"))
)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
author_id = Column(Integer, ForeignKey('authors.id'))
author = relationship("Author", secondary=author_book, backref="books")
一个作者可以写一本书或多本书。同样，一本书可以由多位作者撰写。因此，作者和书之间存在着多对多的关系。
为了表示多对多关系，我们创建了一个名为author_book的关联表。
给定一个Author对象a，我们可以访问他作为a.books所写的所有书籍。同样，给定一个Book对象b，则b.authors将返回一个Author对象列表。
在这种情况下，我们已经在Book模型上定义了relationship()，但是我们也可以在Author模型中轻松定义它。
有时您希望在关联表中存储额外的数据。为了实现这一点，我们必须将关联表定义为一个模型类。
Base = declarative_base()
class Author_Book(Base):
__tablename__ = 'author_book'
author_id = Column(Integer(), ForeignKey("authors.id")),
book_id = Column(Integer(), ForeignKey("books.id"))
extra_data = Column(String(100))
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
books = relationship("Author_Book", backref='author')
class Book(Base):
__tablename__ = 'books'
id = Column(Integer, primary_key=True)
title = Column(String(100), nullable=False)
copyright = Column(SmallInteger, nullable=False)
authors = relationship("Author_Book", backref="book")
创建表格
与在 SQLAlchemy Core 中一样，使用MetaData实例的create_all()方法创建表格。
Base.metadata.create_all(engine)
要丢弃表格，请调用drop_all方法。
Base.metadata.drop_all(engine)
我们现在将使用模型重新定义表，并通过调用create_all()方法将它们保存到数据库中。下面是这样做的完整代码:
from sqlalchemy import create_engine, MetaData, Table, Integer, String, \
Column, DateTime, ForeignKey, Numeric, SmallInteger
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
engine = create_engine("postgres+psycopg2://postgres:pass@localhost/sqlalchemy_tuts")
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer(), primary_key=True)
first_name = Column(String(100), nullable=False)
last_name = Column(String(100), nullable=False)
username = Column(String(50), nullable=False)
email = Column(String(200), nullable=False)
created_on = Column(DateTime(), default=datetime.now)
updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
orders = relationship("Order", backref='customer')
class Item(Base):
__tablename__ = 'items'
id = Column(Integer(), primary_key=True)
name = Column(String(200), nullable=False)
cost_price = Column(Numeric(10, 2), nullable=False)
selling_price = Column(Numeric(10, 2), nullable=False)
# orders = relationship("Order", backref='customer')
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer(), primary_key=True)
customer_id = Column(Integer(), ForeignKey('customers.id'))
date_placed = Column(DateTime(), default=datetime.now)
line_items = relationship("OrderLine", secondary="order_lines", backref='order')
class OrderLine(Base):
__tablename__ = 'order_lines'
id = Column(Integer(), primary_key=True)
order_id = Column(Integer(), ForeignKey('orders.id'))
item_id = Column(Integer(), ForeignKey('items.id'))
quantity = Column(SmallInteger())
item = relationship("Item")
Base.metadata.create_all(engine)
在下一章中，我们将学习如何使用 SQLAlchemy ORM 处理数据库。
使用 SQLAlchemy ORM 的 CRUD
原文：https://overiq.com/sqlalchemy-101/crud-using-sqlalchemy-orm/
最后更新于 2020 年 7 月 27 日
创建会话
使用 SQLAlchemy ORM 时，我们使用Session对象与数据库进行交互。Session对象还包装了数据库连接和事务。一旦Session开始与数据库通信，事务就隐式开始，并将保持打开状态，直到Session被提交、回滚或关闭。
创建Session对象的一种方法是使用sqlalchemy.orm包中的Session类。
from sqlalchemy.orm import create_engine, Session
engine = create_engine("postgres+psycopg2://postgres:pass@localhost/mydb")
session = Session(bind=engine)
每次您想要与数据库通信时，都必须创建Session对象。
Session构造函数接受许多参数来定制它的工作。如果我们选择使用这个方法创建 Session，我们将不得不在整个应用中反复调用带有相同参数集的Session构造函数。
为了让事情变得更简单，SQLAlchemy 提供了sessionmaker类，该类使用为其构造函数设置的默认参数来创建Session类。
from sqlalchemy.orm import sessionmaker, Session
Session = sessionmaker(bind=engine)
您应该在全局范围内的应用中调用sessionmaker一次。
一旦我们访问了定制的Session类，您可以根据需要多次实例化它，而无需向它传递任何参数。
session = Session()
请注意，实例化Session对象不会立即建立与数据库的连接。只有当您开始向数据库发送查询时，才会建立连接。
注意:本教程涵盖的内容还不止于此。有关更多详细信息，请查看官方文档。
插入数据
要使用 SQLAlchemy ORM 创建新记录，我们需要执行以下步骤:
- 创建一个对象。
- 将对象添加到会话中。
- 提交会话。
让我们创建两个新的Customer对象，如下所示:
c1 = Customer(first_name = 'Toby',
last_name = 'Miller',
username = 'tmiller',
email = 'tmiller@example.com',
address = '1662 Kinney Street',
town = 'Wolfden'
)
c2 = Customer(first_name = 'Scott',
last_name = 'Harvey',
username = 'scottharvey',
email = 'scottharvey@example.com',
address = '424 Patterson Street',
town = 'Beckinsdale'
)
c1, c2
预期输出:
(<Customer:None-johngreen>, <Customer:None-katwilson>)
这里我们已经创建了两个Customer对象。我们可以使用点(.)运算符访问对象的属性，如下所示:
c1.first_name, c1.last_name
c2.first_name, c2.last_name
预期输出:
('John', 'Green')
('Katherine', 'Wilson')
接下来，我们将对象添加到会话中。
session.add(c1)
session.add(c2)
向会话中添加对象实际上并没有将它们写入数据库，它只是准备要在下一次提交中保存的对象。我们可以通过检查对象的主键来验证这一点。
c1.id, c2.id
预期输出:
(None, None)
两个对象的id属性的值都是None。这意味着我们的对象还没有保存在数据库中。
我们可以使用add_all()方法，而不是一次向会话中添加一个对象。add_all()方法接受要添加到会话中的对象列表。
session.add_all([c1, c2])
多次向会话添加对象不会引发任何错误。您可以随时使用session.new查看添加到会话中的对象。
session.new
预期输出:
IdentitySet([<Customer:None-johngreen>, <Customer:None-katwilson>])
最后，要将对象保存到数据库中，调用commit()方法如下:
session.commit()
一旦提交事务，由Session对象引用的连接资源将返回到连接池。后续操作将在新的事务中进行。
访问Customer对象的id属性现在将返回主键，而不是None。
c1.id, c2.id
预期输出:
(1, 2)
此时，Customer表应该是这样的:
我们的顾客没有点任何东西。因此c1.orders和c2.orders将返回一个空列表。
c1.orders, c2.orders
预期输出:
([], [])
让我们在customers表中增加一些客户:
c3 = Customer(
first_name = "John",
last_name = "Lara",
username = "johnlara",
email = "johnlara@mail.com",
address = "3073 Derek Drive",
town = "Norfolk"
)
c4 = Customer(
first_name = "Sarah",
last_name = "Tomlin",
username = "sarahtomlin",
email = "sarahtomlin@mail.com",
address = "3572 Poplar Avenue",
town = "Norfolk"
)
c5 = Customer(first_name = 'Toby',
last_name = 'Miller',
username = 'tmiller',
email = 'tmiller@example.com',
address = '1662 Kinney Street',
town = 'Wolfden'
)
c6 = Customer(first_name = 'Scott',
last_name = 'Harvey',
username = 'scottharvey',
email = 'scottharvey@example.com',
address = '424 Patterson Street',
town = 'Beckinsdale'
)
session.add_all([c3, c4, c5, c6])
session.commit()
在我们接受订单之前，让我们在items表中添加一些产品。
i1 = Item(name = 'Chair', cost_price = 9.21, selling_price = 10.81, quantity = 5)
i2 = Item(name = 'Pen', cost_price = 3.45, selling_price = 4.51, quantity = 3)
i3 = Item(name = 'Headphone', cost_price = 15.52, selling_price = 16.81, quantity = 50)
i4 = Item(name = 'Travel Bag', cost_price = 20.1, selling_price = 24.21, quantity = 50)
i5 = Item(name = 'Keyboard', cost_price = 20.1, selling_price = 22.11, quantity = 50)
i6 = Item(name = 'Monitor', cost_price = 200.14, selling_price = 212.89, quantity = 50)
i7 = Item(name = 'Watch', cost_price = 100.58, selling_price = 104.41, quantity = 50)
i8 = Item(name = 'Water Bottle', cost_price = 20.89, selling_price = 25, quantity = 50)
session.add_all([i1, i2, i3, i4, i5, i6, i7, i8])
session.commit()
现在创建一些订单:
o1 = Order(customer = c1)
o2 = Order(customer = c1)
line_item1 = OrderLine(order = o1, item = i1, quantity = 3)
line_item2 = OrderLine(order = o1, item = i2, quantity = 2)
line_item3 = OrderLine(order = o2, item = i1, quantity = 1)
line_item3 = OrderLine(order = o2, item = i2, quantity = 4)
session.add_all([o1, o2])
session.new
session.commit()
请注意，我们只是在会话中添加Order对象(即o1和o2)。Order和OrderLine对象是一对多关系。向会话添加一个Order对象也隐含地向会话添加相关的OrderLine对象。但是，即使您仍然将OrderLine对象手动添加到会话中，您也不会得到任何错误。
我们也可以这样做，而不是在创建OrderLine实例时传递Order对象:
o3 = Order(customer = c1)
orderline1 = OrderLine(item = i1, quantity = 5)
orderline2 = OrderLine(item = i2, quantity = 10)
o3.order_lines.append(orderline1)
o3.order_lines.append(orderline2)
session.add_all([o3])
session.commit()
提交后，orders和order_lines表现在应该如下所示:
[]
再次访问Customer对象的orders属性，这次会得到一个非空列表，如下图:
c1.orders
预期输出:
[<Order:8>, <Order:9>]
从关系的另一面，我们可以使用Order对象上的customer属性来访问订单所属的Customer对象。
o1.customer
预期输出:
<Customer:1-tmiller>
客户c1现在有两个订单。要查看订单中的订单行，请使用Order对象的order_lines属性。
c1.orders[0].order_lines, c1.orders[1].order_lines
预期输出:
([<OrderLine:1>, <OrderLine:2>], [<OrderLine:3>, <OrderLine:4>])
要访问订单行中的项目，请使用item属性。
for ol in c1.orders[0].order_lines:
ol.id, ol.item, ol.quantity
print('-------')
for ol in c1.orders[1].order_lines:
ol.id, ol.item, ol.quantity
预期输出:
(1, <Item:1-Chair>, 3)
(2, <Item:2-Pen>, 2)
-------
(3, <Item:1-Chair>, 1)
(4, <Item:2-Pen>, 4)
请记住，所有这些之所以成为可能，是因为我们在模型中定义了relationship()。
查询数据
要查询数据库，我们使用session对象的query()方法。query()方法返回类型为sqlalchemy.orm.query.Query的对象，简称为Query。Query对象表示将用于查询数据库的SELECT语句。下表列出了Query类的一些常用方法。
| 方法 | 描述 |
|---|---|
all() |
以列表形式返回查询结果(用Query表示)。 |
count() |
返回查询中记录的总数。 |
first() |
返回查询的第一个结果，如果结果中没有行，则返回None。 |
scalar() |
返回第一行的第一列，如果结果集为空，则返回None。如果遇到多行，它会抛出MultipleResultsFound异常。 |
one |
只返回一行。如果遇到多行，它会抛出MultipleResultsFound异常。如果结果集为空，则抛出NoResultFound异常。 |
get(pk) |
如果没有找到匹配给定主键(pk)或None的对象，则返回该对象。 |
filter(*criterion) |
将WHERE子句应用于查询后，返回一个新的Query实例。 |
limit(limit) |
将LIMIT子句应用于查询后，返回一个新的Query实例。 |
offset(offset) |
将OFFSET子句应用于查询后，返回一个新的Query实例。 |
order_by(*criterion) |
在查询中应用ORDER BY子句后，返回一个新的Query实例。 |
join(*props, **kwargs) |
在查询上创建 SQL INNER JOIN 之后，返回一个新的Query实例。 |
outerjoin(*props, **kwargs) |
在查询上创建 SQL LEFT OUTER JOIN 后，返回一个新的Query实例。 |
group_by(*criterion) |
在查询中添加GROUP BY子句后，返回一个新的Query实例。 |
having(criterion) |
在查询中添加HAVING子句后，返回一个新的Query实例。 |
all()方法
最简单的形式是query()方法可以将一个或多个模型类或列作为参数。以下代码返回customers表中的所有记录。
session.query(Customer).all()
预期输出:
[<Customer:1-jongreen>,
<Customer:2-katwilson>,
<Customer:3-johnlara>,
<Customer:4-sarahtomlin>,
<Customer:5-tmiller>,
<Customer:6-scottharvey>]
同样，下面的代码返回来自items和orders表的所有记录。
session.query(Item).all()
session.query(Order).all()
预期输出:
[<Item:1-Chair>,
<Item:2-Pen>,
<Item:3-Headphone>,
<Item:4-Travel Bag>,
<Item:5-Keyboard>,
<Item:6-Monitor>,
<Item:7-Watch>,
<Item:8-Water Bottle>]
[<Order:1>, <Order:2>, <Order:3>]
要获取用于查询数据库的原始 SQL，只需按如下方式打印sqlalchemy.orm.query.Query对象:
print(session.query(Customer))
预期输出:
SELECT
customers. ID AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_address,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
在大的结果集中调用all()方法是低效的，相反，我们可以使用 for 循环来迭代Query对象，如下所示:
q = session.query(Customer)
for c in q:
print(c.id, c.first_name)
预期输出:
1 John
2 Katherine
3 John
4 Sarah
5 Toby
6 Scott
前面的查询已经从表的所有列中返回了数据。我们可以通过将列名显式传递给query()方法来防止这种情况，如下所示:
session.query(Customer.id, Customer.first_name).all()
预期输出:
[(1, 'John'),
(2, 'Katherine'),
(3, 'John'),
(4, 'Sarah'),
(5, 'Toby'),
(6, 'Scott')]
请注意，现在列表中的每一项都是元组，而不是模型实例。
count()方法
count()方法返回查询返回的结果数。
session.query(Customer).count() # get the total number of records in the customers table
session.query(Item).count() # get the total number of records in the items table
session.query(Order).count() # get the total number of records in the orders table
预期输出:
6
8
3
first()方法
first()方法返回查询的第一个结果，如果查询返回零个结果，则返回None。
session.query(Customer).first()
session.query(Item).first()
session.query(Order).first()
预期输出:
<Customer:1-johngreen>
<Item:1-Chair>
<Order:1>
get()方法
get()方法返回与传递给它的主键匹配的实例，如果没有找到这样的对象，则返回None。
session.query(Customer).get(1)
session.query(Item).get(1)
session.query(Order).get(100)
预期输出:
<Customer:1-johngreen>
<Item:1-Chair>
filter()方法
filter()方法允许我们通过在查询中添加WHERE子句来过滤结果。它至少接受一列、一个运算符和值。这里有一个例子:
session.query(Customer).filter(Customer.first_name == 'John').all()
预期输出:
[<Customer:1-johngreen>, <Customer:3-johnlara>]
该查询返回所有名字为John的客户。该查询的 SQL 等价物是:
print(session.query(Customer).filter(Customer.first_name == 'John'))
预期输出:
SELECT
customers.id AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_address,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
WHERE
customers.first_name = %(first_name_1)s
WHERE子句中的字符串%(first_name_1)s是一个占位符，在执行查询时将被实际值(即John)替换。
我们可以将多个过滤器传递给filter()方法，它们将使用 SQL AND运算符连接在一起。例如:
session.query(Customer).filter(Customer.id <= 5, Customer.town == "Norfolk").all()
预期输出:
[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]
该查询返回主键小于等于 5 且城镇名称以Nor开头的所有客户。它的 SQL 等价物是:
print(session.query(Customer).filter(Customer.id <= 5, Customer.town.like("Nor%"))
预期输出:
组合条件的另一种方式是使用连词(即and_()、or_()和not_())。以下是一些例子:
# find all customers who either live in Peterbrugh or Norfolk
session.query(Customer).filter(or_(
Customer.town == 'Peterbrugh',
Customer.town == 'Norfolk'
)).all()
# find all customers whose first name is John and live in Norfolk
session.query(Customer).filter(and_(
Customer.first_name == 'John',
Customer.town == 'Norfolk'
)).all()
# find all johns who don't live in Peterbrugh
session.query(Customer).filter(and_(
Customer.first_name == 'John',
not_(
Customer.town == 'Peterbrugh',
)
)).all()
预期输出:
[<Customer:1-johngreen>,
<Customer:2-katwilson>,
<Customer:3-johnlara>,
<Customer:4-sarahtomlin>]
[<Customer:1-johngreen>, <Customer:3-johnlara>]
[<Customer:1-johngreen>, <Customer:3-johnlara>]
下面的清单显示了如何使用一些常见的比较运算符和filter()方法。
为空
session.query(Order).filter(Order.date_shipped == None).all()
预期输出:
[<Order:1>, <Order:2>, <Order:3>]
不为空
session.query(Order).filter(Order.date_shipped != None).all()
预期输出:
[]
在…里
session.query(Customer).filter(Customer.first_name.in_(['Toby', 'Sarah'])).all()
预期输出:
[<Customer:4-sarahtomlin>, <Customer:5-tmiller>]
不在
session.query(Customer).filter(Customer.first_name.notin_(['Toby', 'Sarah'])).all()
预期输出:
[<Customer:1-johngreen>,
<Customer:2-katwilson>,
<Customer:3-johnlara>,
<Customer:6-scottharvey>]
在...之间
session.query(Item).filter(Item.cost_price.between(10, 50)).all()
预期输出:
[<Item:3-Headphone>,
<Item:4-Travel Bag>,
<Item:5-Keyboard>,
<Item:8-Water Bottle>]
不在中间
session.query(Item).filter(not_(Item.cost_price.between(10, 50))).all()
预期输出:
[<Item:1-Chair>, <Item:2-Pen>, <Item:6-Monitor>, <Item:7-Watch>]
喜欢
session.query(Item).filter(Item.name.like("%r")).all()
预期输出:
[<Item:1-Chair>, <Item:6-Monitor>]
like()方法执行区分大小写的匹配。对于不区分大小写的匹配，使用ilike()。
session.query(Item).filter(Item.name.ilike("w%")).all()
预期输出:
[<Item:7-Watch>, <Item:8-Water Bottle>]
不喜欢
session.query(Item).filter(not_(Item.name.like("W%"))).all()
预期输出:
[<Item:1-Chair>,
<Item:2-Pen>,
<Item:3-Headphone>,
<Item:4-Travel Bag>,
<Item:5-Keyboard>,
<Item:6-Monitor>]
极限()方法
limit()方法将LIMIT子句添加到查询中。它接受您希望从查询中返回的行数。
session.query(Customer).limit(2).all()
session.query(Customer).filter(Customer.address.ilike("%avenue")).limit(2).all()
预期输出:
[<Customer:1-johngreen>, <Customer:2-katwilson>]
[<Customer:2-katwilson>, <Customer:4-sarahtomlin>]
上述查询的 SQL 等价物如下:
print(session.query(Customer).limit(2))
print(session.query(Customer).filter(Customer.address.ilike("%avenue")).limit(2))
预期输出:
SELECT
customers. id AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_address,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
LIMIT %(param_1)s
SELECT
customers.id AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_address,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
WHERE
customers.address ILIKE % (address_1)s
LIMIT %(param_1)s
offset()方法
offset()方法将OFFSET子句添加到查询中。它接受偏移量作为参数。它常用于limit()从句。
session.query(Customer).limit(2).offset(2).all()
预期输出:
[<Customer:3-johnlara>, <Customer:4-sarahtomlin>]
上述查询的等效 SQL 如下:
print(session.query(Customer).limit(2).offset(2))
预期输出:
SELECT
customers. ID AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_addrees,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
LIMIT %(param_1)s OFFSET %(param_2)s
order_by()方法
通过在查询中添加ORDER BY子句，使用order_by()方法对结果进行排序。它接受订单应该基于的列名。默认情况下，它按升序排序。
session.query(Item).filter(Item.name.ilike("wa%")).all()
session.query(Item).filter(Item.name.ilike("wa%")).order_by(Item.cost_price).all()
预期输出:
[<Item:7-Watch>, <Item:8-Water Bottle>]
[<Item:8-Water Bottle>, <Item:7-Watch>]
要按降序排序，使用desc()功能，如下所示:
from sqlalchemy import desc
session.query(Item).filter(Item.name.ilike("wa%")).order_by(desc(Item.cost_price)).all()
预期输出:
[<Item:7-Watch>, <Item:8-Water Bottle>]
join()方法
join()方法用于创建 SQL INNER JOIN。它接受要为其创建 SQL JOIN 的表名。
让我们用join()方法找到所有有一个或多个订单的客户。
session.query(Customer).join(Order).all()
预期输出:
[<Customer:1-johngreen>]
该查询相当于以下 SQL:
print(session.query(Customer).join(Order))
预期输出:
SELECT
customers.id AS customers_id,
customers.first_name AS customers_first_name,
customers.last_name AS customers_last_name,
customers.username AS customers_username,
customers.email AS customers_email,
customers.address AS customers_address,
customers.town AS customers_town,
customers.created_on AS customers_created_on,
customers.updated_on AS customers_updated_on
FROM
customers
JOIN orders ON customers.id = orders.customer_id
join()方法通常用于在单个查询中从一个或多个表中获取数据。例如:
session.query(Customer.id, Customer.username, Order.id).join(Order).all()
预期输出:
[(1, 'johngreen', 1), (1, 'johngreen', 2), (1, 'johngreen', 3)]
我们可以通过如下链接join()方法为两个以上的表创建 SQL JOIN:
session.query(Table1).join(Table2).join(Table3).join(Table4).all()
这里还有一个例子，它使用 3 个连接来查找John Green的第一个顺序中的所有项目。
session.query(
Customer.first_name,
Item.name,
Item.selling_price,
OrderLine.quantity
).join(Order).join(OrderLine).join(Item).filter(
Customer.first_name == 'John',
Customer.last_name == 'Green',
Order.id == 1,
).all()
预期输出:
[('John', 'Chair', Decimal('10.81'), 3), ('John', 'Pen', Decimal('4.51'), 2)]
outerjoin()方法
outerjoin()方法的工作原理和join()一样，但是创造了LEFT OUTER JOIN。
session.query(
Customer.first_name,
Order.id,
).outerjoin(Order).all()
预期输出:
[('John', 1),
('John', 2),
('John', 3),
('Katherine', None),
('Toby', None),
('Scott', None),
('Sarah', None),
('John', None)]
在这个查询中，左边的表是customers表。因此，它将返回来自customers表(左表)的所有行，并且只从orders表(右表)返回满足连接条件的行。
您也可以通过将full=True传递给outerjoin()方法来创建FULL OUTER JOIN。例如:
session.query(
Customer.first_name,
Order.id,
).outerjoin(Order, full=True).all()
预期输出:
[('John', 1),
('John', 2),
('John', 3),
('Katherine', None),
('Toby', None),
('Scott', None),
('Sarah', None),
('John', None)]
group_by()方法
我们使用group_by()方法对结果进行分组。它接受一列或多列，并根据列中的值对行进行分组。
下面的查询使用join()和group_by()来统计约翰·格林的订单数量。
from sqlalchemy import func
session.query(func.count(Customer.id)).join(Order).filter(
Customer.first_name == 'John',
Customer.last_name == 'Green',
).group_by(Customer.id).scalar()
预期输出:
3
具有()方法
为了根据聚合函数返回的值过滤出结果，我们使用having()方法，该方法将HAVING子句添加到SELECT语句中。就像where()条款一样，它接受一个条件。
# find the number of customers lives in each town
session.query(
func.count("*").label('town_count'),
Customer.town
).group_by(Customer.town).having(func.count("*") > 2).all()
预期输出:
[(3, 'Norfolk')]
处理重复项
为了处理结果集中的重复行，我们使用DISTINCT选项。我们可以使用distinct()方法将DISTINCT选项添加到SELECT语句中。例如:
from sqlalchemy import distinct
session.query(Customer.town).filter(Customer.id < 10).all()
session.query(Customer.town).filter(Customer.id < 10).distinct().all()
session.query(
func.count(distinct(Customer.town)),
func.count(Customer.town)
).all()
预期输出:
[('Norfolk'),
('Peterbrugh'),
('Norfolk'),
('Norfolk'),
('Wolfden'),
('Beckinsdale')]
[('Peterbrugh'), ('Beckinsdale'), ('Wolfden'), ('Norfolk')]
[(4, 6)]
铸造
将数据从一种类型转换为另一种类型是一种常见的操作，通过sqlalchemy包中的cast()功能完成。
from sqlalchemy import cast, Date, distinct, union
session.query(
cast(func.pi(), Integer),
cast(func.pi(), Numeric(10,2)),
cast("2010-12-01", DateTime),
cast("2010-12-01", Date),
).all()
预期输出:
[(3,
Decimal('3.14'),
datetime.datetime(2010, 12, 1, 0, 0),
datetime.date(2010, 12, 1))]
联盟
对于联合查询，我们使用Query对象的union()方法。它需要一个或多个查询。例如:
s1 = session.query(Item.id, Item.name).filter(Item.name.like("Wa%"))
s2 = session.query(Item.id, Item.name).filter(Item.name.like("%e%"))
s1.union(s2).all()
预期输出:
[(2, 'Pen'),
(4, 'Travel Bag'),
(3, 'Headphone'),
(5, 'Keyboard'),
(7, 'Watch'),
(8, 'Water Bottle')]
默认情况下，union()从结果集中删除所有重复的行。如果您想保留副本，请使用union_all()。
s1.union_all(s2).all()
预期输出:
[(7, 'Watch'),
(8, 'Water Bottle'),
(2, 'Pen'),
(3, 'Headphone'),
(4, 'Travel Bag'),
(5, 'Keyboard'),
(8, 'Water Bottle')]
更新数据
要更新对象，只需将其属性设置为新值，将对象添加到会话中并提交更改。
i = session.query(Item).get(8)
i.selling_price = 25.91
session.add(i)
session.commit()
这样我们一次只能更新一个对象。要同时更新多行，请使用Query对象的update()方法。它返回更新的记录总数。例如:
# update quantity of all quantity of items to 60 whose name starts with 'W'
session.query(Item).filter(
Item.name.ilike("W%")
).update({"quantity": 60}, synchronize_session='fetch')
session.commit()
预期输出:
2
删除数据
要删除对象，请使用会话对象的delete()方法。它接受一个对象，并将其标记为在下一次提交时删除。
i = session.query(Item).filter(Item.name == 'Monitor').one()
i
session.delete(i)
session.commit()
预期输出:
<Item:6-Monitor>
该提交从items表中移除Monitor。
要一次删除多条记录，请使用Query对象的delete()方法。
session.query(Item).filter(
Item.name.ilike("W%")
).delete(synchronize_session='fetch')
session.commit()
预期输出:
2
该提交将删除所有名称以W开头的项目。
原始查询
ORM 也给了你直接使用的灵活性，直接使用 SQL 使用text()函数。例如:
from sqlalchemy import text
session.query(Customer).filter(text("first_name = 'John'")).all()
session.query(Customer).filter(text("town like 'Nor%'")).all()
session.query(Customer).filter(text("town like 'Nor%'")).order_by(text("first_name, id desc")).all()
预期输出:
[<Customer:1-johngreen>, <Customer:3-johnlara>]
[<Customer:1-johngreen>, <Customer:3-johnlara>, <Customer:4-sarahtomlin>]
[<Customer:3-johnlara>, <Customer:1-johngreen>, <Customer:4-sarahtomlin>]
处理
事务是一种执行一组 SQL 语句的方式，以便成功执行所有语句或根本不执行任何语句。如果事务中涉及的任何语句失败，那么数据库将返回到事务启动前的状态。
我们目前在数据库中有两个订单。发送订单的过程如下:
- 在
orders表的date_shipped栏中设置发货日期 - 从
items表中减去订购项目的数量
这两个操作必须作为一个单元来执行，以确保表中的数据是正确的。
在下面的列表中，我们定义了dispatch_order()方法，该方法接受order_id作为参数，并在事务中执行上述任务。
from sqlalchemy import update
from sqlalchemy.exc import IntegrityError
from datetime import datetime
def dispatch_order(order_id):
# check whether order_id is valid or not
order = session.query(Order).get(order_id)
if not order:
raise ValueError("Invalid order id: {}.".format(order_id))
if order.date_shipped:
print("Order already shipped.")
return
try:
for i in order.order_lines:
i.item.quantity = i.item.quantity - i.quantity
order.date_shipped = datetime.now()
session.commit()
print("Transaction completed.")
except IntegrityError as e:
print(e)
print("Rolling back ...")
session.rollback()
print("Transaction failed.")
我们的第一个订单是 3 把椅子和 2 支笔。用1的订单 id 调用dispatch_order()函数，将返回如下输出:
dispatch_order(1)
预期输出:
Transaction completed.
此时，items和order_lines表应该是这样的:
[]
[]
我们的下一个订单是 1 把椅子和 4 支笔，但是我们现在只有 3 支笔了！
让我们试着运行dispatch_order()二阶，看看会发生什么。
dispatch_order(2)
预期输出:
(psycopg2.IntegrityError) new row for relation "items" violates check constraint "quantity_check"
DETAIL: Failing row contains (2, Pen, 3.45, 4.51, -3).
[SQL: 'UPDATE items SET quantity=%(quantity)s WHERE items.id = %(items_id)s'] [parameters: ({'quantity': 1, 'items_id': 1}, {'quantity': -3, 'items_id': 2})] (Background on this error at: http://sqlalche.me/e/gkpj)
Rolling back ...
Transaction failed.
不出所料，我们的发货失败了，因为我们的库存中没有足够的笔，并且因为我们正在使用事务，所以我们的数据库恢复到了事务开始之前的状态。
我希望这个教程会对你有所帮助。如果你遇到任何错别字或错误。使用下面的联系页面或评论框联系。
MySQL Connector/Python 教程
MySQL Connector/Python 介绍
原文：https://overiq.com/mysql-connector-python-101/intro-to-mysql-connector-python/
最后更新于 2020 年 7 月 27 日
MySQL Connector/Python 是创建给甲骨文的包，用于使用 Python 访问 MySQL 数据库。它于 2012 年推出，此后经历了许多改进。
MySQL Connector/Python 的 API 符合 DB API 规范 v2.0 PEP 249。
那么什么是 DB-API 呢？
数据库应用编程接口或数据库应用编程接口只是一个标准，提倡在各种数据库后端使用通用的 Python 应用编程接口。该规范使得编写跨各种数据库后端一致工作的代码变得容易，通常需要进行一些调整。
这意味着在学习了 MySQL Connector/Python 之后，您可以轻松地将 Psycopg 用于 PostgreSQL，将 pyodbc 用于 MS-SQL，或者轻松实现 DB-API 的任何其他驱动程序。
在下一课中，我们将学习如何安装 MySQL Connector/Python。
安装 Python MySQL 连接器
原文：https://overiq.com/mysql-connector-python-101/installing-mysql-connector-python/
最后更新于 2020 年 7 月 27 日
先决条件
在安装 MySQL Connector/Python 之前，请确保您的系统上安装了 MySQL 和 Python。如果您还没有安装这些，请访问以下链接获取进一步的说明。
MySQL Connector/Python 版本
在本教程中，我们将使用 Connector/Python 8.0 版本，该版本适用于 MySQL (8.0、5.7、5.6、5.5)和 Python (3.6、3.5、3.4、2.7)。如果你想使用旧版本的 MySQL 或 Python，那么在这个链接上查看版本矩阵。
创建虚拟环境
使用mkdir命令创建名为learn-mysql-connector的新目录，如下所示:
$ mkdir learn-mysql-connector
我们将使用这个目录来存储本教程中的示例。本教程中使用的所有示例都可以在链接下载。
接下来，将当前工作目录更改为learn-mysql-connector，并使用virtualenv包创建一个新的虚拟环境:
$ cd learn-mysql-connector
$ virtualenv env
注意:如果您是 virtualenv 新手，请访问 Virtualenv 指南了解更多信息。
如果您在 Linux 或 Mac OS 上，请执行以下命令来激活虚拟环境。
$ source env/bin/activate
要停用虚拟环境类型，请执行以下操作:
$ source env/bin/deactivate
Windows 用户可以使用以下命令来激活和停用虚拟环境:
C:\>
C:\> env\Scripts\activate
(env) C:\>
C:\> env\Scripts\deactivate
C:\>
我们现在准备安装 MySQL Connector/Python。
安装 MySQL Connector/Python
在大多数平台上安装 Connector/Python 最简单的方法是通过pip命令。
$ pip install mysql-connector-python
另一种安装 MySQL Connector/Python 的方法是直接从官方网站下载。下面的屏幕显示了 Ubuntu Linux 可用的包列表。
测试装置
软件包安装完成后，启动 Python 解释器并导入mysql.connector模块，如下所示:
Python 3.5.2 (default, Nov 17 2016, 17:05:23)
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>
>>> import mysql.connector as m
>>>
>>> m.__version__
'8.0.12'
>>>
如果您获得了前面代码片段中指出的版本，那么您就有了一个工作正常的 Connector/Python 安装。
扩展
最初，MySQL Connector/Python 是用纯 Python 编写的。在 2.1 版本中，引入了 C 扩展，与纯 Python 实现相比，它在处理大型结果集时允许更好的性能。
如果您已经在 Linux 上使用 pip 安装了 Connector/Python，那么很可能 C 扩展是与软件包一起安装的。如果不是这样，您可以从下载页面下载带有 C 扩展的 Connector/Python 包。
名字中有“cext”的包带有 C 扩展，而其余的都是纯 Python 实现。
我们也可以通过导入_mysql_connector模块而不是mysql.connector来检查你是否安装了 C 扩展。
>>>
>>> import _mysql_connector
>>>
导入语句成功是因为我在 Linux 上，pip已经安装了 C 扩展。
但是，如果您在 Windows 上，您将会看到如下错误:
>>>
>>> import _mysql_connector
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: No module named '_mysql_connector'
>>>
不幸的是，在编写的时候，Windows 包没有附带 C 扩展。
本教程中的大多数示例都使用纯 Python 实现。因此，您可以继续，而不必安装 C 扩展。但是，一旦您进入生产环境，您就应该考虑使用 C 扩展。
安装世界数据库
本教程中的所有示例都将查询世界示例数据库。要下载数据库，请访问https://dev.mysql.com/doc/index-other.html。
下载完成后，提取归档文件，您将获得一个名为
world.sql的文件。
要从world.sql文件创建数据库，请执行以下操作:
$ mysql -u root -p < /path/to/world.sql
您现在应该已经安装了世界示例数据库。在下一课中，我们将学习如何使用 Connector/Python 连接到数据库。
使用 Connector/Python 连接到 MySQL
原文：https://overiq.com/mysql-connector-python-101/connecting-to-mysql-using-connector-python/
最后更新于 2020 年 7 月 27 日
要连接到数据库，我们使用mysql.connector模块的connect()功能。它接受连接凭证并返回类型为MySQLConnection或CMySQLConnection的对象(如果安装了 C 扩展)。
下表列出了connect()函数的一些常见参数。
| 争吵 | 默认 | 描述 |
|---|---|---|
| 资料库 | 数据库名称 | |
| 用户 | 要使用 MySQL 进行身份验证的用户名 | |
| 密码 | 验证用户身份的密码 | |
| 宿主 | 127.0.0.1 | 安装 MySQL 服务器的主机名 |
| 港口 | Three thousand three hundred and six | MySQL 服务器的 TCP/IP 端口 |
注:有关完整的论点列表，请参考官方文件。
下面清单中的代码连接到世界数据库并打印连接 id。
import mysql.connector
db = mysql.connector.connect(
host='localhost',
database='world',
user='root',
password='pass'
)
print("Connection ID:", db.connection_id)
print(db)
预期输出:
Connection ID: 14
<mysql.connector.connection_cext.CMySQLConnection object at 0x7ff200815c88>
因为我在一个安装了 C 扩展的 Linux 发行版上，connect()函数返回一个类型为CMySQLConnection的对象，而不是MySQLConnection。
在未安装 C 扩展的系统上，输出将如下所示:
Connection ID: 15
<mysql.connector.connection.MySQLConnection object at 0x7fef0f102c88>
您也可以在字典中传递连接凭据，而不是将它们作为关键字参数传递。
import mysql.connector
connection_args = {
'host': 'localhost',
'database': 'world',
'user': 'root',
'password': 'pass'
}
db = mysql.connector.connect(**connection_args)
print("Connection ID:", db.connection_id)
print(db)
使用纯 Python 或 C 扩展
use_pure连接参数决定是否使用 C 扩展。如果use_pure设置为True，Connector/Python 将使用纯 Python 实现。从 8.0.11 开始，
use_pure参数默认为False。这就是前面的代码片段使用 C 扩展的原因。但是，如果use_pure设置为False并且 C 扩展不可用，那么 Connector/Python 将使用纯 Python 实现。
import mysql.connector
db = mysql.connector.connect(
host='localhost',
database='world',
user='root',
password='pass',
use_pure=True # use pure Python implementation
)
print("Connection ID:", db.connection_id)
print(db)
预期输出:
Connection ID: 18
<mysql.connector.connection.MySQLConnection object at 0x7fe4499f1c88>
关闭连接
当程序结束时，与数据库的连接会自动关闭。但是，最好在完成连接后显式关闭连接。
要关闭连接，我们使用MySQLConnection对象的close()方法。
import mysql.connector
db = mysql.connector.connect(
host='localhost',
database='world',
user='root',
password='pass',
use_pure=True
)
print("Connection ID:", db.connection_id)
print(db)
db.close() # close the connection
我们现在知道如何连接到数据库。让我们看看如何处理错误。
使用配置文件
出于测试目的，将连接凭证硬编码到应用中是可以的，但是对于生产环境来说，这是不太可行的，原因有二。
- 任何能够访问源的人都可以查看连接细节。
- 迁移到新服务器时，您必须再次更新源代码。
更好的方法是将连接细节存储在外部文件中。由于 2.0 版本的 Connector/Python 可以从一个 Windows-INI 风格的文件中读取连接细节。
以下两个参数控制配置文件的设置:
| 争吵 | 默认 | 描述 |
|---|---|---|
option_files |
它分类要读取的配置文件。它的值可以是字符串或字符串列表。 | |
option_groups |
['client', 'connector_python'] |
它指定从中读取选项的节的名称。默认情况下，选项仅从client和connector_python部分读取。 |
使用如下连接凭证创建名为my.conf的新文件:
my.conf
[connector_python]
host = 127.0.0.1
database = world
user = root
password = pass
port = 3306
下面清单中的代码从my.conf文件中读取连接细节。
import mysql.connector
db = mysql.connector.connect(option_files='my.conf')
print("Connection ID:", db.connection_id)
print(db)
db.close()
请注意，在my.conf文件中，我们已经在connector_python'部分下指定了连接细节，这是默认情况下 MySQL Connector/Python 将从其中读取选项的两个部分之一。如果要更改节名，请使用option_groups参数，如下所示:
[connection_details]
host = 127.0.0.1
database = blog
user = root
password = pass
port = 3306
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', option_groups=['connection_details'])
print("Connection ID:", db.connection_id)
print(db)
db.close()
我们还可以将连接细节分割成多个文件。当您想要跨连接共享一些配置时，这将非常有用。
my1.conf
[connector_python]
host = 127.0.0.1
port = 3306
my2.conf
[connector_python]
database = world
user = root
password = pass
要从多个配置文件中读取选项，请将option_files的值更改为列表。
import mysql.connector
db = mysql.connector.connect(option_files=['my1.conf', 'my1.conf'])
print("Connection ID:", db.connection_id)
print(db)
db.close()
使用 Connector/Python 执行查询
原文：https://overiq.com/mysql-connector-python-101/executing-queries-using-connector-python/
最后更新于 2020 年 7 月 27 日
在最后一课中，我们学习了如何使用 Connector/Python 连接 MySQL 数据库。在本课中，我们将学习如何执行查询。
创建光标对象
游标对象允许我们执行查询和检索行。光标对象是MySQLCursor类的一个实例。
我们可以通过使用连接对象的cursor()方法(即MySQLConnection)或者直接调用MySQLCursor类来创建光标对象。
就像连接对象一样，当你使用完光标后，你必须通过调用close()方法来关闭它。
下面是一个示例，它说明了创建游标的两种不同方式。
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor1 = db.cursor() # 1st method
cursor2 = MySQLCursor(db) # 2nd method
print(db)
print("cursor1: ", cursor1)
print("cursor2: ", cursor2)
cursor1.close()
cursor2.close()
db.close()
预期输出:
<mysql.connector.connection.MySQLConnection object at 0x7fcf99799da0>
cursor1: MySQLCursor: (Nothing executed yet)
cursor2: MySQLCursor: (Nothing executed yet)
我们现在准备执行对数据库的查询。
一个简单的 SELECT 语句
有几种方法可以执行查询。最常见的是execute()法。
其语法如下:
`execute(query, params=None)`
| 参数 | 描述 |
|---|---|
query |
(必需)要执行的 SQL 查询或命令 |
params |
(可选)用于为查询参数提供值的序列或映射。 |
现在让我们看一些例子:
下面的列表从城市表中选择前五行。
例 1:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("select * from city limit 5")
# iterate over result
for row in cursor:
print(row)
cursor.close()
db.close()
预期输出:
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)
执行查询后，我们使用 cursor 对象来迭代结果。请注意，每行都以元组的形式返回。要访问单个列中的数据，请使用索引。还要注意，列中的数据被转换为其等效的 Python 类型。
下面是另一个访问单个列中的数据并打印其类型的示例。
例 2:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("select * from city limit 5")
for row in cursor:
print(row[0], type(row[0]), row[1], type(row[1]) )
cursor.close()
db.close()
预期输出:
1 <class 'int'> Kabul <class 'str'>
2 <class 'int'> Qandahar <class 'str'>
3 <class 'int'> Herat <class 'str'>
4 <class 'int'> Mazar-e-Sharif <class 'str'>
5 <class 'int'> Amsterdam <class 'str'>
请记住在使用 res 之前阅读行
获取方法
我们可以使用以下方法一次访问一行或多行，而不是迭代光标对象。
| 方法 | 描述 |
|---|---|
fetchone() |
将结果集中的下一行作为元组返回。如果没有要检索的行，则返回None。 |
fetchmany([size]) |
从结果集中返回指定数量的行(作为元组列表)。如果没有要检索的行，则返回[]。默认大小为 1。 |
fetchall() |
返回结果集中的所有(或剩余)行。 |
以下是一些例子:
示例 1:使用fetchone()检索行。
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
r = cursor.execute("select * from city limit 5")
# first row
row = cursor.fetchone()
# loop over the remaining result set
while row:
print(row)
row = cursor.fetchone()
cursor.close()
db.close()
预期输出:
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)
在第 5 行，我们读取结果集中的第一行。然后，我们使用 while 循环来访问剩余的行。当fetchone()方法返回None时，循环停止。
例 2:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
r = cursor.execute("select * from city limit 5")
print(cursor.fetchone()) # fetch the first row
print(cursor.fetchmany(2)) # fetch the next 2 rows
print(cursor.fetchall()) # fetch all the remaining rows
print(cursor.fetchmany()) # the result set is now empty
cursor.close()
db.close()
预期输出:
(1, 'Kabul', 'AFG', 'Kabol', 1780000)
[(2, 'Qandahar', 'AFG', 'Qandahar', 237500), (3, 'Herat', 'AFG', 'Herat', 186800)]
[(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800), (5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)]
[]
在第 1 行，我们调用fetchone()从结果集中读取第一行。接下来，我们调用fetchmany()读取接下来的 2 行，最后调用fetchall()获取剩余的一行。结果集现在是空的，所以下一次调用fetchmany()会返回一个空列表。
缓冲和非缓冲光标
默认情况下，光标对象没有缓冲。这是什么意思？
这仅仅意味着，在调用行提取方法或迭代游标对象之前，不会从数据库服务器中提取行。换句话说，无缓冲游标按需生成结果。另一方面，当使用缓冲游标执行查询时，行被立即读入缓冲区，行提取方法从缓冲行集中返回行。
仅在以下情况下使用缓冲光标:
- 您已经知道结果集很小。或者，
- 您希望在另一个查询中使用一个查询的结果
请记住，缓冲的游标会将所有行一次加载到内存中。如果您有一个大的结果集，那么这会占用大量的内存，如果您没有足够的内存，甚至可能导致程序崩溃。因此，在使用大型结果集时，您应该始终按需加载结果(即使用无缓冲游标)。
在无缓冲游标的情况下，试图关闭游标对象或在从旧的结果集中读取结果之前执行新的查询将导致InternalError: Unread result found异常。
以下两个例子证明了这些事实:
示例 1:在读取结果集之前关闭光标对象
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("select * from city limit 5")
cursor.close() # closing cursor before reading the result set
db.close()
预期输出:
Traceback (most recent call last):
...
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
示例 2:在从旧查询读取结果集之前，执行新查询。
import mysql.connector
from mysql.connector.cursor import MySQLCursor
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("select * from city limit 5")
# result set from the above query is not read
# now we are executing a new query
cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")
for row in cursor:
print(row)
cursor.close()
db.close()
预期输出:
Traceback (most recent call last):
...
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found
但是，使用缓冲游标，您可以执行新的查询，但是之前查询的结果集将被丢弃。
我们将在本章后面学习如何创建缓冲光标。
光标属性
下表列出了一些只读属性，这些属性有助于我们获取关于上次执行的查询的相关信息。
| 属性 | 描述 |
|---|---|
column_names |
将列名作为元组返回，并从中返回数据。 |
with_rows |
如果查询产生结果集，返回True的布尔属性，否则返回False。 |
description |
返回包含结果集中列信息的元组列表。表单的元组(name, type_code, display_size, internal_ size, precision, scale, null_ok)。name是指列名，type_code 是表示列类型的整数，null_ok决定一列是否可以接受NULL值(1 表示真，0 表示假)。其余字段始终设置为None。 |
lastrowid |
返回最后修改或插入的行的 id，或者当没有这样的值时返回None(例如对于 SELECT 语句)。如果插入或更新了多行，那么它只返回第一行的 id。 |
statement |
以字符串形式返回上次执行的查询。 |
rowcount |
返回上次查询产生或影响的行数。对于无缓冲游标，rowcount最初设置为-1，并随着行的读取而递增。 |
下面是一个例子，展示了这些属性的作用:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")
print("rowcount (initial):", cursor.rowcount, end='\n\n')
# reading rows but not displaying
for row in cursor: pass
print("column_names:", cursor.column_names, end='\n\n')
print("with_rows:", cursor.with_rows, end='\n\n')
print("description: ", end="")
pprint(cursor.description)
print("\nstatement:", cursor.statement, end='\n\n')
print("lastrowid:", cursor.lastrowid, end='\n\n')
print("rowcount (final):", cursor.rowcount, end='\n\n')
cursor.close()
db.close()
预期输出:
rowcount (initial): -1
column_names: ('Code', 'Name', 'Region', 'Population', 'Continent')
with_rows: True
description: [('Code', 254, None, None, None, None, 0, 16387),
('Name', 254, None, None, None, None, 0, 1),
('Region', 254, None, None, None, None, 0, 1),
('Population', 3, None, None, None, None, 0, 1),
('Continent', 254, None, None, None, None, 0, 257)]
statement: select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5
lastrowid: None
rowcount (final): 5
请注意，在上面的输出中，列类型被打印为整数，这不是很有帮助。
我们可以使用FieldType类获得列的实际类型。FieldType类提供了 MySQL 所有支持的数据类型。要将整数类型代码转换为其字符串表示，我们使用FieldType类的get_info()方法，如下所示:
import mysql.connector
from mysql.connector import FieldType
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
cursor.execute("""select Code, Name, Region, Population, Continent from
country where Continent='North America' limit 5""")
cursor.fetchall() # read the result without printing anything
print("description: ", end="")
pprint(cursor.description)
print()
for desc in cursor.description:
print("Column '{}' is of type {}".format( desc[0], FieldType.get_info(desc[1])))
cursor.close()
db.close()
预期输出:
description: [('Code', 254, None, None, None, None, 0, 16387),
('Name', 254, None, None, None, None, 0, 1),
('Region', 254, None, None, None, None, 0, 1),
('Population', 3, None, None, None, None, 0, 1),
('Continent', 254, None, None, None, None, 0, 257)]
Column 'Code' is of type STRING
Column 'Name' is of type STRING
Column 'Region' is of type STRING
Column 'Population' is of type LONG
Column 'Continent' is of type STRING
查询参数
到目前为止，我们只执行了静态查询。为了动态创建 SQL 查询，我们需要将用户提供的数据传递到查询中。我们使用查询参数来实现这一点。
查询参数只是值的占位符，在执行查询时将被实际值替换。以下是用于指定查询参数的两种常见样式。
- 格式- %s，%d
- pyformat - %（name）s
在format样式中，我们使用像%s、%d等格式代码。作为占位符。这些占位符的值是使用序列(如列表或元组)指定的。
在pyformat样式中，扩展格式代码，如%(name)s用作占位符，值使用映射(如字典)指定。
下面的列表演示了每个参数样式的作用:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
# query with format parameter style
sql1 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""
data1 = ('Europe', 5)
cursor.execute(sql1, data1)
for row in cursor:
print(row)
print('-' * 80)
# query with pyformat parameter style
sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%(continent)s limit %(limit)s"""
data2 = {
'continent': 'Asia',
'limit': 4
}
cursor.execute(sql2, data2)
for row in cursor:
print(row)
cursor.close()
db.close()
预期输出:
('ALB', 'Albania', 'Southern Europe', 3401200, 'Europe')
('AND', 'Andorra', 'Southern Europe', 78000, 'Europe')
('AUT', 'Austria', 'Western Europe', 8091800, 'Europe')
('BEL', 'Belgium', 'Western Europe', 10239000, 'Europe')
('BGR', 'Bulgaria', 'Eastern Europe', 8190900, 'Europe')
--------------------------------------------------------------------------------
('AFG', 'Afghanistan', 'Southern and Central Asia', 22720000, 'Asia')
('ARE', 'United Arab Emirates', 'Middle East', 2441000, 'Asia')
('ARM', 'Armenia', 'Middle East', 3520000, 'Asia')
('AZE', 'Azerbaijan', 'Middle East', 7734000, 'Asia')
使用 execute()执行多个查询
execute()方法接受一个名为multi的可选关键字参数。默认设置为False。如果设置为True，允许execute()执行多个用分号分隔的查询。当用multi=True调用时，execute()方法返回一个迭代器，该迭代器可用于访问查询产生的结果集。
下面是一个实例:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
# query with format parameter style
sql1 = "select version()"
sql2 = """select Code, Name, Region, Population, Continent from
country where Continent=%s limit %s"""
sql3 = "select 1+1"
queries = [sql1, sql2, sql3]
data = ('South America', 5)
# returns an iterator
results = cursor.execute(";".join(queries), data, multi=True)
count = 1
for result in results:
# result is a cursor object i.e result == cursor
# so we have access to all cursor attributes and methods
print("Query {0} - {1} :".format(count, result.statement))
# does query has result?
if result.with_rows:
for row in result:
print(row)
count = count + 1
else:
print("No result found")
print()
cursor.close()
db.close()
预期输出:
Query 1 - select version():
('5.7.22-0ubuntu0.16.04.1',)
Query 2 - select Code, Name, Region, Population, Continent from
country where Continent='South America' limit 5:
('ARG', 'Argentina', 'South America', 37032000, 'South America')
('BOL', 'Bolivia', 'South America', 8329000, 'South America')
('BRA', 'Brazil', 'South America', 170115000, 'South America')
('CHL', 'Chile', 'South America', 15211000, 'South America')
('COL', 'Colombia', 'South America', 42321000, 'South America')
Query 3 - select 1+1:
(2,)
这种方法的缺点是所有的查询参数都必须在单个序列或映射中传递。如果您有大量的参数，那么您可能会失去对它们的跟踪。一个更好更直接的方法是单独执行每个查询。
有时，您可能希望使用不同的参数集执行相同的查询。实现这一点的一种方法是在循环中调用execute()方法，如下所示:
import mysql.connector
from mysql.connector.cursor import MySQLCursor
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """update country
set LifeExpectancy = %(life_expectency)s,
Population = %(population)s
where Name = %(name)s"""
data_list = [
{
'life_expectency': 78.9,
'population': 103500,
'name': 'Aruba'
},
{
'life_expectency': 46.9,
'population': 22720400,
'name': 'Afghanistan'
},
{
'life_expectency': 38.3,
'population': 12879000,
'name': 'Angola'
},
]
for data in data_list:
cursor.execute(sql, data)
# db.commit() # call commit() method to save the changes
cursor.close()
db.close()
这个脚本更新了阿鲁巴、阿富汗和安哥拉的预期寿命和人口。
但是，如果您在执行脚本后查看country表，您会发现该表根本没有更新，这是因为在执行任何 DML 语句后，您都必须调用连接对象的commit()方法来提交事务并将更改保存到数据库中。我们将在“交易”一章中了解有关交易的更多信息。
介绍 executemany()
executemany()方法是用不同参数集执行查询的另一种方式。它在内部调用execute()方法。它的语法如下:
executemany(query, parameter_seqeunce)
| 参数 | 描述 |
|---|---|
query |
要执行的查询 |
parameter_sequence |
包含要在查询中使用的参数的序列 |
executemany()方法丢弃查询返回的结果集，所以不要用它来执行SELECT语句。
前面清单中的代码可以使用executemany()改写如下:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """update country
set LifeExpectancy = %(life_expectency)s,
Population = %(population)s
where Name = %(name)s"""
data_list = [
{
'life_expectency': 78.9,
'population': 103500,
'name': 'Aruba'
},
{
'life_expectency': 46.9,
'population': 22720400,
'name': 'Afghanistan'
},
{
'life_expectency': 38.3,
'population': 12879099,
'name': 'Angola'
},
]
cursor.executemany(sql, data_list)
# db.commit()
cursor.close()
db.close()
我们刚刚删除了 for 头并用executemany()替换了execute()调用，其余代码完全相同。
对于大多数查询来说，在一个循环中调用executemany()和调用execute()并没有真正的区别。但是对INSERT和REPLACE语句进行了优化。
考虑以下INSERT陈述:
insert into customer(name, age)
values('John', 45);
insert into customer(name, age)
values('Max', 25);
insert into customer(name, age)
values('Jane', 20);
insert into customer(name, age)
values('Bob', 34);
executemany()会将上述 4 个语句转换成一个单独的INSERT语句，如下所示:
insert into customer(name, age)
values('John', 45),
('Max', 25),
('Jane', 20),
('Bob', 34);
因为后一种形式将在单个事务中插入四行(而不是 4 行)，所以它比单行INSERT语句快得多。
REPLACE语句也是如此。
下面是一个使用executemany()方法创建一个新表并在其中插入一些行的例子。
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql1 = """
create temporary table customer(
id int AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age SMALLINT NOT NULL
)
"""
cursor.execute(sql1)
sql2 = """
insert into customer(name, age)
VALUES (%(name)s, %(age)s)
"""
data_list = [
{
'name': 'John',
'age': 45
},
{
'name': 'Max',
'age': 25
},
{
'name': 'Jane',
'age': 20
},
{
'name': 'Bob',
'age': 34
},
]
cursor.executemany(sql2, data_list)
print("Query:", cursor.statement)
print("Rowcount:", cursor.rowcount) # rows inserted
db.commit() # commit the changes
cursor.close()
db.close()
预期输出:
Query: insert into customer(name, age)
VALUES ('John', 45),('Max', 25),('Jane', 20),('Bob', 34)
Rowcount: 4
自定义光标对象
游标子类用于自定义游标返回结果集的方式，或者以其他方式修改对象行为。要创建这些类的对象，我们可以直接调用子类，或者将一次性参数传递给连接对象的cursor()方法。
下面是继承自MySQLCursor类的列表子类。
MySQLCursorBufferedMySQLCursorRawMySQLCursorBufferedRawMySQLCursorDictMySQLCursorBufferedDictMySQLCursorNamedTupleMySQLCursorBufferedNamedTupleMySQLCursorPrepared
让我们逐一详细讨论。
MySQLCursorBuffered 类
MySQLCursorBuffered类创建一个缓冲光标。
要创建缓冲光标，请将buffered=True传递给连接对象的cursor()方法。或者，将buffered=True传递给connect()功能，这将打开从连接对象创建的所有光标对象的缓冲。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# turn on buffering for all cursor objects created from
# this connection
#
# db = mysql.connector.connect(
# option_files='my.conf',
# use_pure=True,
# buffered=True
# )
# only this cursor will buffer results
cursor = db.cursor(buffered=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): 3
('Australia', 'Oceania', 18886000, 79.8)
('Switzerland', 'Europe', 7160400, 79.6)
('Sweden', 'Europe', 8861400, 79.6)
rowcount (final): 3
请注意，执行查询后，rowcount属性返回 3，这是因为我们使用的是缓冲游标，执行查询后会立即提取行。如果我们使用无缓冲光标，rowcount将返回-1。
MySQLCursorRaw 类
默认情况下，游标对象在提取行时会自动将 MySQL 类型转换为其等效的 Python 类型。如果要关闭此转换，请使用MySQLCursorRaw光标。
要创建原始光标，请将raw=True传递给连接对象的cursor()方法。或者，将raw=True传递给connect()功能，这将默认情况下使所有从连接创建的光标都是原始的。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# all cursor objects created from
# this connection will be raw
#
# db = mysql.connector.connect(
# option_files='my.conf',
# use_pure=True,
# raw=True
# )
# only this cursor is raw
cursor = db.cursor(raw=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): -1
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3
MySQLCursorBufferedRaw 类
MySQLCursorBufferedRaw类创建一个原始缓冲光标。
向MySQLCursorBufferedRaw光标传递raw=True和buffered=True到连接对象的cursor()方法。或者，将raw=True和buffered=True传递到connect()功能，这将默认使所有从连接创建的光标原始并缓冲。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# all cursor objects created from
# this connection will be raw and buffered
#
# db = mysql.connector.connect(
# option_files='my.conf',
# use_pure=True,
# raw=True
# buffered=True
# )
# only this cursor will be raw and buffered
cursor = db.cursor(raw=True, buffered=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): 3
(bytearray(b'Australia'), bytearray(b'Oceania'), bytearray(b'18886000'), bytearray(b'79.8'))
(bytearray(b'Switzerland'), bytearray(b'Europe'), bytearray(b'7160400'), bytearray(b'79.6'))
(bytearray(b'Sweden'), bytearray(b'Europe'), bytearray(b'8861400'), bytearray(b'79.6'))
rowcount (final): 3
MySQLCursorDict 类
MySQLCursorDict类允许我们将行作为字典而不是元组来访问。
要创建MySQLCursorDict光标，请将dictionary=True传递给连接对象的cursor()方法。
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# return rows as dictionary instead of tuple
cursor = db.cursor(dictionary=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print(row['Name'], row['Continent']) # access columns by names
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): -1
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Population': 18886000, 'Name': 'Australia'}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 7160400, 'Name': 'Switzerland'}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Population': 8861400, 'Name': 'Sweden'}
Sweden Europe
rowcount (final): 3
MySQLCursorBufferedDict
MySQLCursorBufferedDict的工作方式与MySQLCursorDict完全相同，但返回一个缓冲光标。
要创建MySQLCursorBufferedDict光标，请将buffered=True和dictionary=True传递给连接对象的cursor()方法。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# return a buffered MySQLCursorDict
cursor = db.cursor(dictionary=True, buffered=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print(row['Name'], row['Continent'])
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): 3
{'Continent': 'Oceania', 'LifeExpectancy': 79.8, 'Name': 'Australia', 'Population': 18886000}
Australia Oceania
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Switzerland', 'Population': 7160400}
Switzerland Europe
{'Continent': 'Europe', 'LifeExpectancy': 79.6, 'Name': 'Sweden', 'Population': 8861400}
Sweden Europe
rowcount (final): 3
MySQLCursorNamedTuple 类
MySQLCursorNamedTuple类创建一个以namedtuple形式返回行的光标。
创建MySQLCursorNamedTuple将named_tuple=True传递给连接对象的cursor()方法。
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# return a unbuffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print(row.Name, row.Continent) # access columns as attributes
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): -1
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3
MySQLCursorBufferedNamedTuple 类
类似于MySQLCursorNamedTuple，但创建一个缓冲光标。
创建MySQLCursorBufferedNamedTuple将named_tuple=True和buffered=True传递给连接对象的cursor()方法。
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(named_tuple=True, buffered=True)
sql = """
SELECT Name, Continent, Population, LifeExpectancy FROM country
where LifeExpectancy BETWEEN 70 and 80
order by LifeExpectancy DESC
limit 3
"""
cursor.execute(sql)
print("rowcount (initial):", cursor.rowcount)
for row in cursor:
print(row)
print(row.Name, row.Continent) # access columns as attributes
print("rowcount (final):", cursor.rowcount)
cursor.close()
db.close()
预期输出:
rowcount (initial): 3
Row(Name='Australia', Continent='Oceania', Population=18886000, LifeExpectancy=79.8)
Australia Oceania
Row(Name='Switzerland', Continent='Europe', Population=7160400, LifeExpectancy=79.6)
Switzerland Europe
Row(Name='Sweden', Continent='Europe', Population=8861400, LifeExpectancy=79.6)
Sweden Europe
rowcount (final): 3
MySQLCursorPrepared 类
MySQLCursorPrepared类创建一个游标来执行准备好的语句。
准备好的语句是一种特性，它允许我们重用一条 SQL 语句，而不会产生多次编译它的成本。当您想要重复执行具有不同参数集的查询时，它们非常有用。
与不同，非准备语句可以使用 format ( %s)或 pyformat ( %(name)s)参数样式。对于准备好的语句，只能使用 format( %d)或 qmark ( ?)参数样式。
要创建MySQLCursorPrepared光标，请将prepared=True传递给连接对象的cursor()方法。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
# return a buffered MySQLCursorNamedTuple
cursor = db.cursor(prepared=True)
sql1 = """
create temporary table customer(
id int AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age SMALLINT NOT NULL
)
"""
cursor.execute(sql1)
sql2 = """
insert into customer(name, age)
VALUES (%s, %s)
"""
data_list = [
('John', 45),
('Max', 25),
('Jane', 20),
('Bob', 34),
]
cursor.executemany(sql2, data_list)
print("rowcount:", cursor.rowcount)
print(cursor)
cursor.close()
db.close()
预期输出:
rowcount: 4
MySQLCursorPrepared:
insert into customer(name, age)
VALUES ..
在接下来的几课中，我们将看到一些INSERT、UPDATE和DELTETE语句的实际例子:
Connector/Python 中的异常处理
原文：https://overiq.com/mysql-connector-python-101/exception-handling-in-connector-python/
最后更新于 2020 年 7 月 27 日
编程时错误总是发生，所以最好装备自己如何处理它们。
错误和警告
MySQL 中有两个级别的错误消息严重性。
- 错误
- 警告
错误
错误表示查询或命令有问题，导致无法执行。
mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist
警告
警告告诉您发生了一些意想不到的事情，可能会导致问题，但还不足以阻止语句执行。我们可以使用SHOW WARNINGS;命令显示警告。例如:
mysql>
mysql> CREATE database if not exists world;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------+
| Note | 1007 | Can't create database 'world'; database exists |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> SELECT 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.12 sec)
mysql> show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
1 row in set (0.00 sec)
mysql>
错误代码
MySQL 中的错误和警告包含三条信息:
mysql> select * from earth;
ERROR 1146 (42S02): Table 'world.earth' doesn't exist
-
不可移植到其他数据库的唯一 MySQL 特定错误代码(
1146)。 -
一个 5 字符代码(
42S02)称为 SQLSTATE ，用于指示操作的成功或失败。SQLSTATE 可以跨其他数据库移植。它由两部分组成:前两个字符代表 SQL 错误类，后三个字符代表子类。每个类可以属于以下四个类别之一。- 成功(00 级)
- 警告(01 级)
- 无数据(02 级)
- 例外(所有其他 07-HZ)
-
错误的文本描述。
那么为什么是两个错误代码呢？
这是因为SQLSTATE代表一组错误。因此，我们只能用它来处理一般性错误。如果您想处理一些特定的错误，请使用 MySQL 特定的错误代码。
有数百个 MySQL 特有的错误代码。幸运的是，您不需要记住它们，因为mysql.connector.errorcode模块包含了所有 MySQL 特有的错误代码。
>>>
>>> from mysql.connector import errorcode
>>> errorcode.ER_BAD_TABLE_ERROR
1051
>>>
例外类
所有用于处理错误和警告的异常类都在mysql.connector.errors模块中定义。
mysql.connector.errors.Error是所有其他异常的基类。我们可以用它来捕捉任何类型的异常。
这里有一个例子:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """
CREATE TABLE `city` (
`ID` int(11) AUTO_INCREMENT PRIMARY KEY,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT ''
)
"""
try:
cursor.execute(sql)
db.commit()
except mysql.connector.Error as err:
print(err)
print("Error Code:", err.errno)
print("SQLSTATE", err.sqlstate)
print("Message", err.msg)
cursor.close()
db.close()
预期输出:
1050 (42S01): Table 'city' already exists
Error Code: 1050
SQLSTATE 42S01
Message Table 'city' already exists
当抛出异常时，您可以访问异常对象的错误代码、SQLSTATE 和以errno、sqlstate和msg属性形式出现的错误消息。
mysql.connector.errors.Error基类进一步细分为以下三个类:
DatabaseErrorInterfaceErrorPoolError
我们一个一个来讨论。
-
DatabaseError:与数据库相关的错误引发此异常。它可以捕捉各种错误，如数据处理问题、SQL 语法错误、MySQL 内部问题等。如果建立了连接并且出现了问题，那么DatabaseError将会抓住它。DatabaseError有 6 种类型:DataErrorInternalErrorIntegrityErrorOperationalErrorNotSupportedErrorProgrammingError
DataError:该错误表示数据处理有问题，如被零除、数值超出范围、类型无效等。InternalError:当数据库遇到内部错误时，会引发此异常。例如无效光标、事务不同步等。IntegrityError:当外键约束失败时，会引发此异常。OperationalError:对于程序员无法控制的事情，会引发这个异常。例如，意外断开、内存分配错误等、事务失败，选定的数据库不存在。NotSupportedError:调用不支持的方法或 api 时，会引发此异常。例如，在不支持事务的连接上调用rollback()。ProgrammingError:编程错误引发此异常。例如，表未找到或已经存在，MySQL 语法错误，指定的参数数量错误，连接凭证错误等。
-
InterfaceError:这个异常是针对与接口相关的错误(在我们的例子中接口是 MySQL Connector/Python)而不是数据库本身引发的。 -
PoolError:针对连接池相关的错误引发。我们将在下一章中学习连接池。
下图显示了异常类的层次结构:
StandardError
|__ Error
|__ InterfaceError
|__ PoolError
|__ DatabaseError
|__ DataError
|__ InternalError
|__ IntegrityError
|__ OperationalError
|__ NotSupportedError
|__ ProgrammingError
所有异常类都映射到一个或多个 SQLSTATE 类，您可以通过键入以下命令来打印此映射。
>>>
>>> from mysql.connector import errors
>>> from pprint import pprint
>>>
>>> pprint(errors._SQLSTATE_CLASS_EXCEPTION)
{'02': <class 'mysql.connector.errors.DataError'>,
'07': <class 'mysql.connector.errors.DatabaseError'>,
'08': <class 'mysql.connector.errors.OperationalError'>,
'0A': <class 'mysql.connector.errors.NotSupportedError'>,
'0K': <class 'mysql.connector.errors.OperationalError'>,
...
'HY': <class 'mysql.connector.errors.DatabaseError'>,
'HZ': <class 'mysql.connector.errors.OperationalError'>,
'XA': <class 'mysql.connector.errors.IntegrityError'>}
>>>
>>>
现在我们来看一些例子。
示例 1:处理一般错误
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """
select * from town limit 5
"""
try:
cursor.execute(sql)
for row in cursor:
print(row)
cursor.close()
db.close()
except mysql.connector.ProgrammingError as err:
print(err.errno)
print(err.sqlstate)
print(err.msg)
except mysql.connector.Error as err:
print(err)
请记住ProgrammingError可以捕捉从语法错误到找不到表的各种异常。如果您想捕捉一些特定的错误，请使用errorcode模块。
示例 2:处理特定错误
import mysql.connector
from mysql.connector import errorcode
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """
select * from town limit 5
"""
try:
cursor.execute(sql)
for row in cursor:
print(row)
db.close()
except mysql.connector.ProgrammingError as err:
if errorcode.ER_NO_SUCH_TABLE == err.errno:
print("No table exists")
else:
print("Table exists")
print(err)
except mysql.connector.Error as err:
print("Some other error")
print(err)
示例 3:以相同的方式处理多个错误
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql = """
insert into city(Name, CountryCode, District, Population)
VALUES ('Kabul', 'AFGANISTAN', 'Kabol', 1780000)
"""
try:
cursor.execute(sql)
db.commit()
db.close()
except (mysql.connector.IntegrityError, mysql.connector.DataError) as err:
print("DataError or IntegrityError")
print(err)
except mysql.connector.ProgrammingError as err:
print("Programming Error")
print(err)
except mysql.connector.Error as err:
print(err)
处理警告
默认情况下，MySQL Connector/Python 既不获取警告，也不在出现警告时引发异常。但是，我们可以使用connect()函数的以下参数来改变这一点。
| 争吵 | 描述 |
|---|---|
get_warnings |
如果设置为True，则每次查询后自动获取警告，无需手动执行SHOW WARNINGS查询。其默认值为False。 |
raise_on_warnings |
如果设置为True，出现警告时会引发异常。默认为False。设置raise_on_warnings=True，也设置get_warnings=True |
以上两个参数也可用作连接对象的属性，可用于设置和检索当前设置。
import mysql.connector
from mysql.connector import errors
db = mysql.connector.connect(option_files='my.conf')
db.get_warnings = True # this is not required if raise_on_warnings=True
db.raise_on_warnings = True
设置get_warnings=True(或raise_on_warnings=True)后，使用光标对象的fetchwarnings()方法检索实际警告。fetchwarnings()方法返回一个元组列表，其中包含消息级、错误代码和由之前执行的查询生成的消息本身。
下面的示例演示如何获取查询产生的警告:
import mysql.connector
db = mysql.connector.connect(option_files='my.conf',
get_warnings=True)
# db.get_warnings = True # we could have set get_warnings like this too
cursor = db.cursor()
cursor.execute("CREATE database if not exists world;")
print(cursor.fetchwarnings())
cursor.execute("select 1/0;")
cursor.fetchall() # fetch all the rows from the result set
print(cursor.fetchwarnings())
预期输出:
[('Note', 1007, "Can't create database 'world'; database exists")]
[('Warning', 1365, 'Division by 0')]
请注意，如果查询返回结果集(如SELECT语句)，那么您需要在调用fetchwarnings()方法之前获取所有行，否则fetchwarnings()方法将返回None。
下面是一个示例，展示了如何在出现警告时引发异常。
import mysql.connector
from mysql.connector import errors
db = mysql.connector.connect(option_files='my.conf',
raise_on_warnings=True)
# db.raise_on_warnings = True # we could have set raise_on_warnings like this
cursor = db.cursor()
try:
cursor.execute("CREATE database if not exists world;")
print(cursor.fetchwarnings())
cursor.execute("select 1/0;")
print(cursor.fetchall()) # fetch all the rows from the result set
print(cursor.fetchwarnings())
except errors.DatabaseError as e:
print(e)
except:
print('some error occurred')
预期输出:
1007: Can't create database 'world'; database exists
注意设置raise_on_warnings=True会隐式设置get_warnings=True。
您现在应该对如何处理错误有了一个很好的了解。在接下来的几页中，我们将看到一些实用的SELECT、INSERT、UPDATE和DELTETE语句示例:
使用 Connector/Python 创建表
原文：https://overiq.com/mysql-connector-python-101/creating-tables-using-connector-python/
最后更新于 2020 年 7 月 27 日
下面清单中的代码创建了一个数据库和两个表。
import mysql.connector
from mysql.connector import errorcode
from collections import OrderedDict
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
db_name = 'blog'
tables = OrderedDict()
tables['category'] = '''
create table category
(
id int not null auto_increment primary key,
name varchar(50) not null
)
'''
tables['post'] = '''
create table post
(
id int not null auto_increment PRIMARY KEY,
title varchar(200) not null,
content text not null,
date TIMESTAMP not null,
category_id int not null,
FOREIGN key (category_id) REFERENCES category(id)
)
'''
def create_db(cursor):
try:
cursor.execute("create database {}".format(db_name))
print("Database created.")
except mysql.connector.Error as err:
print("Database creation failed:", err)
exit(1)
try:
db.database = db_name
print('Database {} already exist.'.format(db_name))
except mysql.connector.Error as err:
# database doesn't exist, create one
if errorcode.ER_BAD_DB_ERROR == err.errno:
create_db(cursor)
db.database = db_name
for k, v in tables.items():
try:
cursor.execute(v)
print('Table {} created.'.format(k))
except mysql.connector.Error as err:
if errorcode.ER_TABLE_EXISTS_ERROR == err.errno:
print('Table {} already exists.'.format(k))
cursor.close()
db.close()
预期输出:
Database created.
Table category created.
Table post created.
工作原理:
-
在第 5-10 行，我们使用
OrderedDict字典而不是常规字典来定义表，这是因为常规字典没有保留元素的顺序。 -
在第 11 行，我们尝试使用连接对象的
database属性来更改数据库。如果数据库不存在，我们调用create_db()来创建数据库。 -
在第 12-20 行，我们通过循环表字典中的项目来创建表。
尝试删除post表，再次运行脚本。这次您将获得以下输出:
Database blog already exists.
Table category already exists.
Table post created.
使用 Connector/Python 插入数据
原文：https://overiq.com/mysql-connector-python-101/inserting-data-using-connector-python/
最后更新于 2020 年 7 月 27 日
在上一课中，我们已经创建了名为blog的数据库以及两个表category和post。在本课中，我们将在表中插入一些行。
插入单行
import mysql.connector
from datetime import datetime
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor()
sql1 = "insert into category(name) value(%s)"
data1 = ('python',)
sql2 = "insert into post(title, content, date, category_id) value(%s, %s, %s, %s)"
cursor.execute(sql1, data1)
category_id = cursor.lastrowid # get the id of the last inserted row
data2 = ('title 1', 'content 1', datetime.now().date() , category_id)
cursor.execute(sql2, data2)
db.commit() # commit the changes
cursor.close()
db.close()
工作原理:
-
在第 5 行，我们在
category表中插入一个新的类别 -
在第 6 行，我们使用光标对象的
lastrowid属性读取最后插入的 id 的值。 -
在第 8 行，我们为新帖子创建数据，在第 10 行，我们在帖子表中执行 insert 语句。
-
最后，在第 14 行，我们调用
commit()方法将更改保存到数据库中。如果您不调用commit()方法，您的更改将不会保存到数据库中，这是必要的。
批量插入
下面的列表将批量插入行到类别和发布表中。
import mysql.connector
from datetime import datetime
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor(buffered=True)
sql1 = "insert into category(name) value(%s)"
data1 = [
('css',),
('java',),
('design',),
('ui',),
('php',),
]
cursor.executemany(sql1, data1) # insert categories into the category table
db.commit() # commit the changes
sql2 = "insert into post(title, content, category_id) value(%s, %s, %s, %s)"
data2 = [
('title 2', 'content 2', datetime.now.date(), 1),
('title 3', 'content 3', datetime.now.date(), 1),
('title 4', 'content 4', datetime.now.date(), 1),
('title 5', 'content 5', datetime.now.date(), 1),
('title 6', 'content 6', datetime.now.date(), 1),
]
cursor.executemany(sql2, data2)
db.commit() # commit the changes
cursor.close()
db.close()
使用 Connector/Python 更新数据
原文：https://overiq.com/mysql-connector-python-101/updating-data-using-connector-python/
最后更新于 2020 年 7 月 27 日
在上一课中，我们看到了如何在表中插入行。在本课中，我们将看到如何更新数据的示例。
更新单行
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor(buffered=True)
sql1 = "update category set name=%s WHERE ID=2"
data1 = ('CSS',)
cursor.execute(sql1, data1)
db.commit() # commit the changes
print("Rows affected:", cursor.rowcount)
cursor.close()
db.close()
预期输出:
Rows affected: 1
批量更新行
import mysql.connector
from datetime import datetime, timedelta
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor(buffered=True)
sql1 = "update post set date=%s"
data1 = [
(datetime.now().date() + timedelta(days=10),),
]
cursor.executemany(sql1, data1)
db.commit() # commit the changes
print("Rows affected:", cursor.rowcount)
cursor.close()
db.close()
预期输出:
Rows affected: 6
使用 Connector/Python 删除数据
原文：https://overiq.com/mysql-connector-python-101/deleting-data-using-connector-python/
最后更新于 2020 年 7 月 27 日
在上一课中，我们看到了如何更新表中的行。在本课中，我们将看到如何删除数据的示例。
删除单行
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor(buffered=True)
sql1 = "delete from category where name=%s limit 1"
data1 = ('php',)
cursor.execute(sql1, data1)
db.commit() # commit the changes
print("Rows affected:", cursor.rowcount)
cursor.close()
db.close()
预期输出:
Rows affected: 1
批量删除行
import mysql.connector
db = mysql.connector.connect(option_files='my.conf', use_pure=True)
cursor = db.cursor(prepared=True)
sql1 = "delete from post where id=%s"
data1 = [
(3,), (4,), (5,), (6,)
]
cursor.executemany(sql1, data1)
db.commit() # commit the changes
print("Rows affected:", cursor.rowcount)
cursor.close()
db.close()
预期输出:
Rows affected: 4
使用 Connector/Python 调用存储过程
原文：https://overiq.com/mysql-connector-python-101/calling-stored-procedures-using-connector-python/
最后更新于 2020 年 7 月 27 日
什么是存储过程？
存储过程只是一组组合在一起并给定名称的 SQL 语句。存储过程用于希望多次执行同一组语句的情况。我们使用CREATE PROCEDURE语句创建存储过程。存储过程也可以接受参数。一旦存储过程被创建，我们可以使用CALL语句调用它，该语句后跟过程名，后跟括号内的参数(如果有)。
这里有一个简单的存储过程，它接受一个大陆并返回其中国家的名称。
mysql> use world;
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_country_by_continent
-> (
-> IN con CHAR(20)
-> )
-> BEGIN
-> SELECT Name
-> FROM country
-> WHERE Continent = con;
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
请注意，在前面的代码片段中，con参数前面是IN关键字，因此它是一个输入参数。输入参数用于从调用程序向存储过程传递值。存储过程可以更改输入参数的值，但是当过程返回时，调用程序看不到这种更改。如果您想从存储过程中返回值，那么使用OUT关键字而不是IN将参数声明为输出参数。输出参数的初始值为NULL。
除了输入和输出参数之外，还有一种参数叫做输入/输出参数。顾名思义，输入/输出参数既可以用作输出参数，也可以用作输入参数。换句话说，输入/输出参数可用于向存储过程传递值。此外，存储过程可以修改输入/输出参数并将其返回给调用程序。要创建输入/输出参数，在变量名前面加上INOUT关键字。
让我们创建一个同时使用输入和输出参数的存储过程。
mysql>
mysql> DELIMITER //
mysql> CREATE PROCEDURE get_country_info
-> (
-> IN country CHAR(20),
-> OUT info VARCHAR(200)
-> )
-> BEGIN
-> SELECT CONCAT_WS(":", Name, Continent, Population, LifeExpectancy, HeadOfState)
-> INTO info
-> FROM country
-> WHERE Name = country;
->
-> select city.Name, city.Population, city.District from city
-> INNER JOIN country
-> where city.CountryCode = country.Code and country.Name= country
-> order by Population desc limit 10;
->
-> END //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql>
以下是该存储过程的工作原理:
-
存储过程接受两个参数:
country和info。country为输入参数，info为输出参数。 -
接下来，我们使用
SELECT语句获取一些关于国家的相关数据。为了将数据存储到输出参数中，我们使用了INTO子句。 -
最后，我们按降序打印全国人口最多的 10 个城市。
我们现在在world数据库中有两个存储过程。要查看它们，请键入以下命令:
mysql> show PROCEDURE STATUS where db = "world";
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
| Db | Name | Type | Definer | Modified | Created |
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
| world | get_country_by_continent | PROCEDURE | root@localhost | 2018-10-05 10:28:37 | 2018-10-05 10:28:37 |
| world | get_country_info | PROCEDURE | root@localhost | 2018-10-05 11:25:35 | 2018-10-05 11:25:35 |
+-------+--------------------------+-----------+----------------+---------------------+---------------------+
2 rows in set (0.01 sec)
mysql>
注意:前面输出中的一些列被省略，以节省空间。
要调用get_country_by_continent存储过程，请使用如下的CALL语句:
mysql>
mysql> CALL get_country_by_continent("South America");
+------------------+
| Name |
+------------------+
| Argentina |
| Bolivia |
| Brazil |
| Chile |
| Colombia |
| Ecuador |
| Falkland Islands |
| French Guiana |
| Guyana |
| Peru |
| Paraguay |
| Suriname |
| Uruguay |
| Venezuela |
+------------------+
14 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
同样，我们可以这样称呼get_country_info:
mysql>
mysql> call get_country_info("Brazil", @info);
+----------------+------------+-------------------+
| Name | Population | District |
+----------------+------------+-------------------+
| São Paulo | 9968485 | São Paulo |
| Rio de Janeiro | 5598953 | Rio de Janeiro |
| Salvador | 2302832 | Bahia |
| Belo Horizonte | 2139125 | Minas Gerais |
| Fortaleza | 2097757 | Ceará |
| Brasília | 1969868 | Distrito Federal |
| Curitiba | 1584232 | Paraná |
| Recife | 1378087 | Pernambuco |
| Porto Alegre | 1314032 | Rio Grande do Sul |
| Manaus | 1255049 | Amazonas |
+----------------+------------+-------------------+
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
注意输出参数(即@info)是如何传递给程序的。程序执行后，我们可以使用SELECT语句打印输出参数的值，如下所示:
mysql>
mysql> select @info;
+---------------------------------------------------------------+
| @info |
+---------------------------------------------------------------+
| Brazil:South America:170115000:62.9:Fernando Henrique Cardoso |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
使用 callproc()调用存储过程
要调用存储过程，我们使用光标对象的callproc()方法。callproc()方法的语法如下:
callproc(proc_name, args=())
| 争吵 | 描述 |
|---|---|
proc_name |
要调用的存储过程 |
args |
要传递给存储过程的参数序列 |
成功后，callproc()返回输入序列的修改版本。输入参数(即使用IN关键字编码的参数)保持不变，但输入和输出参数(即分别使用OUT和INOUT关键字编码的参数)可能包含新值。
一旦存储过程被执行，您可以使用光标对象的stored_results()方法获取结果。stored_results()返回一个迭代器，可用于迭代结果集中的行。
需要注意的是，存储过程产生的结果集会被立即获取并存储为MySQLCursorBuffered实例。
下面的列表演示了如何在操作中使用callproc():
import mysql.connector
from pprint import pprint
db = mysql.connector.connect(option_files='my.conf')
cursor = db.cursor()
args = cursor.callproc('get_country_by_continent', ('South America',) )
print("Return value:", args)
for result in cursor.stored_results():
pprint(result.fetchall())
print('-' * 100)
args = cursor.callproc('get_country_info', ('Japan', None) )
print("Return value:", args)
for result in cursor.stored_results():
pprint(result.fetchall())
预期输出:
Return value: ('South America',)
[('Argentina',),
('Bolivia',),
('Brazil',),
('Chile',),
('Colombia',),
('Ecuador',),
('Falkland Islands',),
('French Guiana',),
('Guyana',),
('Peru',),
('Paraguay',),
('Suriname',),
('Uruguay',),
('Venezuela',)]
----------------------------------------------------------------------------------------------------
Return value: ('Japan', 'Japan:Asia:126714000:80.7:Akihito')
[('Tokyo', 7980230, 'Tokyo-to'),
('Jokohama [Yokohama]', 3339594, 'Kanagawa'),
('Osaka', 2595674, 'Osaka'),
('Nagoya', 2154376, 'Aichi'),
('Sapporo', 1790886, 'Hokkaido'),
('Kioto', 1461974, 'Kyoto'),
('Kobe', 1425139, 'Hyogo'),
('Fukuoka', 1308379, 'Fukuoka'),
('Kawasaki', 1217359, 'Kanagawa'),
('Hiroshima', 1119117, 'Hiroshima')]
请注意，在第二次调用callproc()方法时，我们将None作为第二个参数传递，这是因为所生成的get_country_info存储接受两个参数(一个输入和一个输出)。
使用 Connector/Python 处理事务
原文：https://overiq.com/mysql-connector-python-101/handling-transactions-with-connector-python/
最后更新于 2020 年 7 月 27 日
处理
事务是一种执行一组 SQL 语句的方式，这样要么所有语句成功执行，要么根本不执行。换句话说，事务中的所有语句都作为一个单元执行。如果事务中的任何一条语句失败，数据库将回滚到事务开始的位置。
注意:在 MySQL 中，只有 InnoDB 表支持事务。
MySQL 中的自动提交模式
默认情况下，每当您执行一条 SQL 语句时，MySQL 都会立即将该语句的结果写入数据库。考虑这个问题的另一种方式是，MySQL 将每个语句作为单独的事务来执行，该事务在您执行语句时隐式开始，并在语句执行完毕后立即结束。这种行为被称为自动提交模式。
要查看当前的自动提交设置，请在 MySQL shell 中键入以下内容:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
mysql>
如您所见，@@autocommit系统变量被设置为 1，这意味着自动提交模式被启用。
如果您禁用自动提交模式，那么后面的 SQL 语句将成为事务的一部分，并且您必须在 MySQL 会话结束之前使用COMMIT语句手动提交它们，否则，语句所做的更改将被回滚。
要禁用自动提交模式，请将AUTOCOMMIT设置为0，如下所示:
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
为了验证更改，让我们再次打印@@autocommit系统变量的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql>
自动提交模式现已禁用。从现在开始，您必须在 MySQL 会话结束之前显式提交语句。如果不这样做，语句所做的更改将不会保存到数据库中。
让我们看看这个行为的实际情况:
现在city表中有 4080 行。
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
现在让我们看看如果我们试图执行一条从city表中删除所有行的 SQL 语句会发生什么。
mysql>
mysql> delete from city;
Query OK, 4080 rows affected (0.30 sec)
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
看起来city表中的所有行都被删除了。然而，情况并非如此，因为我们还没有提交交易(通过COMMIT声明)。
点击Ctrl+D或输入exit退出当前 MySQL 会话。
mysql>
mysql> exit
Bye
$
重新启动 mysql 客户端，再次查询city表。
$ mysql -u root -p
Enter password:
mysql>
mysql> use world
Database changed
mysql>
mysql>
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4080 |
+----------+
1 row in set (0.00 sec)
mysql>
不出所料，city表中没有删除任何行。但是，如果我们在执行DELETE语句之前没有禁用自动提交模式(SET AUTOCOMMIT=0)，那么city表中的所有行都将被删除。
此外，当我们重新启动一个新的会话时，自动提交模式会再次打开。因此，单独的 SQL 语句也将在单独的事务中执行。
现在，您知道了自动提交是如何工作的，现在让我们将注意力转移到 MySQL Connector/Python 如何处理事务上。
使用 MySQL Connector/Python 执行事务
默认情况下，Connector/Python 会关闭自动提交模式。这就是为什么我们必须使用连接对象的commit()方法来提交更改的原因。也可以使用连接对象的rollback()方法回滚事务。
要启用自动提交模式，请将连接对象的autocommit参数设置为True。
db = mysql.connector.connect(option_files='my.conf', autocommit=True)
下面的列表演示了 Connector/Python 在autocommit=True时的行为。
import mysql.connector
from mysql.connector import errors
try:
db = mysql.connector.connect(option_files='my.conf', autocommit=True)
cursor = db.cursor()
sql1 = """
create table employees(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary INT NOT NULL
)
"""
cursor.execute(sql1)
sql2 = """
insert into employees(name, salary) value('John', 15000)
"""
cursor.execute(sql2)
sql2 = """
insert into employees(name, salary) value('Bob', 20000)
"""
cursor.execute(sql2)
# db.commit() # we don't need to call commit() because autocommit=True
print('Table created successfully.')
except errors.Error as e:
print(e)
finally:
cursor.close()
db.close()
预期输出:
Table created successfully.
这个脚本创建一个employees表，并在其中插入两行。由于我们已经设置了autocommit=True，我们不需要手动调用commit()方法来提交更改。
如果看employees表，应该是这样的:
mysql> select * from employees;
+----+------+--------+
| id | name | salary |
+----+------+--------+
| 1 | John | 15000 |
| 2 | Bob | 20000 |
+----+------+--------+
2 rows in set (0.00 sec)
现在让我们看看如果我们尝试用autocommit=False执行相同的脚本会发生什么。
首先，使用DROP语句放下employees表:
mysql> drop table employees;
Query OK, 0 rows affected (0.54 sec)
接下来，设置autocommit=False并再次运行脚本。
这一次你会得到和以前一样的输出。但是，如果您查看world数据库，您会发现employees表仍然被创建，但是没有任何行。
出现这种情况是因为CREATE、DROP、TRUNCATE等 DDL 语句。总是执行隐式提交。这种行为不仅仅是 MySQL 特有的，事实上，Oracle 也是这么做的。因此，您不应该试图在事务中包含这样的语句。
start_transaction()方法
如果启用了自动提交(即autocommit=True)，并且您想要作为单个单元执行一组语句，那么您必须使用连接对象的start_transaction()方法显式启动事务。
一旦执行了事务中的所有语句，您可以通过调用commit()方法提交它们，或者通过调用rollback()方法撤销它们。
一旦执行了事务中的所有语句，就可以使用commit()方法提交它们。要撤销更改，请调用rollback()方法，而不是commit()。
如果事务已经在进行中，那么调用start_transaction()会引发ProgrammingError异常。您可以使用in_transaction属性检查正在进行的交易。如果交易有效，in_transaction返回True，否则返回False。
下面的列表演示了start_transaction()方法的作用。
import mysql.connector
from mysql.connector import errors
try:
db = mysql.connector.connect(option_files='my.conf', autocommit=True)
cursor = db.cursor()
db.start_transaction()
# these two INSERT statements are executed as a single unit
sql1 = """
insert into employees(name, salary) value('Tom', 19000)
"""
sql2 = """
insert into employees(name, salary) value('Leo', 21000)
"""
cursor.execute(sql1)
cursor.execute(sql2)
db.commit() # commit changes
print('Transaction committed.')
except errors.Error as e:
db.rollback() # rollback changes
print("Rolling back ...")
print(e)
finally:
cursor.close()
db.close()
预期输出:
Transaction committed.
这个脚本试图在一个事务中执行两个INSERT语句，这意味着两个语句要么都成功执行，要么根本不执行。
当自动提交被禁用时，也可以使用start_transaction()方法来更改事务的默认行为。要了解更多可用选项，请访问文档。
使用 Connector/Python 的连接池
原文：https://overiq.com/mysql-connector-python-101/connection-pooling-using-connector-python/
最后更新于 2020 年 7 月 27 日
什么是连接池？
到目前为止，每次我们想与数据库对话时，我们都创建了一个新的连接。如果您的应用的用户基数很小，这很好。但是，如果您已经知道您的应用将被全球数百万人使用，那么这种方法是不太可行的。
问题是，打开和维护连接是一项昂贵的操作，会浪费大量资源。诸如此类的问题可能会使您的应用变慢并且没有响应。最终，驱使用户远离应用。
输入连接池进行救援。
连接池是一种将连接缓存在内存中的标准方式，以便在需要时可以重用它们，而不是从头开始创建连接。连接池如果实现正确，可以显著提高性能。
在连接池中，每当应用需要与数据库对话时，它都会从池中获取连接。执行所需的查询后，应用释放连接并将其推回到池中。如果所有连接都在使用，则会创建一个新连接并将其添加到池中。
在 MySQL Connector/Python 中创建连接池
创建连接池有两种方法。
mysql.connector.connect()mysql.connector.pooling.MySQLConnectionPool
通过 connect()函数创建连接池
为了使用connect()函数创建连接池，我们向其传递以下任何一个与池相关的参数。
| 争吵 | 描述 |
|---|---|
pool_name |
(可选)连接池的名称。此参数是可选的，如果未指定，将通过按顺序连接来自host、port、user和database连接参数的值来自动生成池名称。 |
pool_size |
(可选)池的大小，即池中要容纳的连接数。此参数是可选的，如果未指定，则默认为 5。 |
这里有一个例子:
db = mysql.connector.connect(option_files='my.conf',
pool_name='my_connection_pool',
pool_size=10)
上面对connect()函数的调用做了两件事:
- 创建一个名为
my_connection_pool的大小为 10 的连接池。 - 从池中返回第一个连接，并将其分配给
db变量。
从现在开始，对具有相同池名的connect()的后续调用将返回来自现有池的连接。如果调用了connect()函数，并且池中没有可用的连接，则会引发PoolError异常。
而且connect()返回的连接对象是PooledMySQLConnection类型，而不是MySQLConnection或CMySQLConnection类型。
池化连接对象(即PooledMySQLConnection)对象的行为与MySQLConnection或CMySQLConnection完全相同，唯一的区别是:
-
close()将连接返回到连接池，而不是实际关闭连接。 -
池连接对象有一个名为
pool_name的属性，该属性返回连接所属的连接池的名称。
请注意，连接是由池延迟创建的。这意味着，如果您创建大小为 10 的连接池，但只有 5 个用户同时访问应用，那么将只建立 5 个连接。第六个连接等将按需创建。
现在让我们举一些例子:
示例 1:从连接池中获取连接
import mysql.connector
from mysql.connector import errors
try:
# create connection pool and fetch the first connection
db1 = mysql.connector.connect(option_files='my.conf',
pool_name='my_connection_pool',
pool_size=3)
print("Connection db1:", db1.connection_id)
# fetch the second connection from the pool
db2 = mysql.connector.connect(pool_name='my_connection_pool')
print("Connection db2:", db2.connection_id)
# fetch the third connection from the pool
db3 = mysql.connector.connect(pool_name='my_connection_pool')
print("Connection db3:", db3.connection_id)
try:
# fetch the fourth connection from the pool
db4 = mysql.connector.connect(pool_name='my_connection_pool')
except errors.PoolError as e:
# connection pool exhausted, so we can't fetch 4th connection
print(e)
print('Closing db3 connection ')
db3.close()
# lets try fetching db4 again
db4 = mysql.connector.connect(pool_name='my_connection_pool')
except errors.Error as e:
print(e)
else:
print("Connection db1:", db1.connection_id)
print("Connection db2:", db2.connection_id)
print("Connection db4:", db4.connection_id)
db1.close()
db2.close()
db4.close()
预期输出:
Connection db1: 163
Connection db2: 164
Connection db3: 165
Failed getting connection; pool exhausted
Closing db3 connection
Connection db1: 163
Connection db2: 164
Connection db4: 165
注意:每次运行程序时，连接 id 可能会有所不同。
以下是它的工作原理:
-
在第 78 行，我们创建一个大小为 3 的连接，并从中获取第一个连接。
-
在第 13 行，我们从池中获取第二个连接。
-
在第 17 行，我们从池中获取第三个连接。
-
连接池现已耗尽，后续调用具有相同池名的
connect()将引发PoolError异常。这正是第 25 行发生的事情。 -
在第 31 行，我们关闭
db3连接。因此，现在池中有一个可用的连接。对connect()的后续调用将这个连接分配给db4变量(在第 34 行)。 -
最后，我们打印连接 id，然后关闭连接。注意
db3和db4的连接 id 是相同的，这是因为db4正在重用db3使用的同一连接。
示例 2:对从连接池中提取的连接执行查询
import mysql.connector
from mysql.connector import errors
try:
# create connection pool and fetch the first connection
db = mysql.connector.connect(option_files='my.conf',
pool_name='my_connection_pool',
pool_size=3)
cursor = db.cursor(dictionary=True)
print("Pool name:", db.pool_name)
print("Connection ID:", db.connection_id)
cursor.execute('select * from city ORDER BY id desc limit 10')
print("\nResult: ")
for row in cursor:
print(row)
except errors.Error as e:
print(e)
finally:
db.close()
预期输出:
Pool name: my_connection_pool
Connection ID: 208
Result:
{'Population': 1780000, 'Name': 'Kabul', 'District': 'Kabol', 'CountryCode': 'AFG', 'ID': 4081}
{'Population': 92020, 'Name': 'Rafah', 'District': 'Rafah', 'CountryCode': 'PSE', 'ID': 4079}
{'Population': 100231, 'Name': 'Nablus', 'District': 'Nablus', 'CountryCode': 'PSE', 'ID': 4078}
{'Population': 113901, 'Name': 'Jabaliya', 'District': 'North Gaza', 'CountryCode': 'PSE', 'ID': 4077}
{'Population': 119401, 'Name': 'Hebron', 'District': 'Hebron', 'CountryCode': 'PSE', 'ID': 4076}
{'Population': 123175, 'Name': 'Khan Yunis', 'District': 'Khan Yunis', 'CountryCode': 'PSE', 'ID': 4075}
{'Population': 353632, 'Name': 'Gaza', 'District': 'Gaza', 'CountryCode': 'PSE', 'ID': 4074}
{'Population': 128037, 'Name': 'Gweru', 'District': 'Midlands', 'CountryCode': 'ZWE', 'ID': 4073}
{'Population': 131367, 'Name': 'Mutare', 'District': 'Manicaland', 'CountryCode': 'ZWE', 'ID': 4072}
{'Population': 164362, 'Name': 'Mount Darwin', 'District': 'Harare', 'CountryCode': 'ZWE', 'ID': 4071}
通过 MySQL 连接池类创建连接池
创建连接池的另一种方法是直接使用MySQLConnectionPool类实例化它。MySQLConnectionPool是创建和管理连接池的主类。MySQLConnectionPool构造函数的语法如下:
MySQLConnectionPool(pool_name=None,
pool_size=5,
**kwargs)
| 争吵 | 描述 |
|---|---|
pool_name |
(必需)连接池的名称。此参数是可选的，如果未指定，将通过按顺序连接来自host、port、user和database连接参数的值来自动生成池名称。 |
pool_size |
(可选)连接池的大小。默认为 5。 |
kwargs |
(可选)它指的是我们传递给connect()函数的连接参数。 |
这里有一个例子:
con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
pool_size=5,
option_files='my.conf',)
实例化MySQLConnectionPool只会创建连接池，不会自动从池中获取第一个连接。
为了获得连接，我们使用MySQLConnectionPool实例的get_connection()方法。
# fetch the first connection
db1 = con_pool.get_connection()
# fetch the second connection
db2 = con_pool.get_connection()
通常，当连接池耗尽时，会引发PoolError异常。
现在让我们举一些例子:
示例 1:从连接池中获取连接
import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector import errors
try:
# create the connection pool
con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
pool_size=3,
option_files='my.conf',)
db1 = con_pool.get_connection()
print("Connection db1:", db1.connection_id)
# fetch the second connection from the pool
db2 = con_pool.get_connection()
print("Connection db2:", db2.connection_id)
# fetch the third connection from the pool
db3 = con_pool.get_connection()
print("Connection db3:", db3.connection_id)
try:
# fetch the fourth connection from the pool
db4 = con_pool.get_connection()
except errors.PoolError as e:
# connection pool exhausted, so we can't fetch 4th connection
print(e)
print('Closing db3 connection ')
db3.close()
# lets try fetching db4 again
db4 = con_pool.get_connection()
except errors.Error as e:
print(e)
finally:
print("Connection db1:", db1.connection_id)
print("Connection db2:", db2.connection_id)
print("Connection db4:", db4.connection_id)
db1.close()
db2.close()
db4.close()
预期输出:
Connection db1: 229
Connection db2: 230
Connection db3: 231
Failed getting connection; pool exhausted
Closing db3 connection
Connection db1: 229
Connection db2: 230
Connection db4: 231
这个例子的工作原理与我们在前面部分中所看到的例子完全一样。唯一不同的是，这里我们是通过MySQLConnectionPool显式创建连接池，然后通过get_connection()方法获取连接。
示例 2:对从连接池中提取的连接执行查询
from mysql.connector.pooling import MySQLConnectionPool
from mysql.connector import errors
try:
# create the connection pool
con_pool = MySQLConnectionPool(pool_name='my_connection_pool',
pool_size=3,
option_files='my.conf', )
# fetch the first connection
db = con_pool.get_connection()
cursor = db.cursor(dictionary=True)
print("Pool name:", db.pool_name)
print("Connection ID:", db.connection_id)
cursor.execute('select * from city ORDER BY id desc limit 10')
print("\nResult: ")
for row in cursor:
print(row)
except errors.Error as e:
print(e)
finally:
db.close()
预期输出:
Pool name: my_connection_pool
Connection ID: 241
Result:
{'Population': 1780000, 'District': 'Kabol', 'Name': 'Kabul', 'CountryCode': 'AFG', 'ID': 4081}
{'Population': 92020, 'District': 'Rafah', 'Name': 'Rafah', 'CountryCode': 'PSE', 'ID': 4079}
{'Population': 100231, 'District': 'Nablus', 'Name': 'Nablus', 'CountryCode': 'PSE', 'ID': 4078}
{'Population': 113901, 'District': 'North Gaza', 'Name': 'Jabaliya', 'CountryCode': 'PSE', 'ID': 4077}
{'Population': 119401, 'District': 'Hebron', 'Name': 'Hebron', 'CountryCode': 'PSE', 'ID': 4076}
{'Population': 123175, 'District': 'Khan Yunis', 'Name': 'Khan Yunis', 'CountryCode': 'PSE', 'ID': 4075}
{'Population': 353632, 'District': 'Gaza', 'Name': 'Gaza', 'CountryCode': 'PSE', 'ID': 4074}
{'Population': 128037, 'District': 'Midlands', 'Name': 'Gweru', 'CountryCode': 'ZWE', 'ID': 4073}
{'Population': 131367, 'District': 'Manicaland', 'Name': 'Mutare', 'CountryCode': 'ZWE', 'ID': 4072}
{'Population': 164362, 'District': 'Harare', 'Name': 'Mount Darwin', 'CountryCode': 'ZWE', 'ID': 4071}
使用 Connector/Python 的 C 扩展
原文：https://overiq.com/mysql-connector-python-101/using-connector-python-c-extension/
最后更新于 2020 年 7 月 27 日
到目前为止，在本系列中，我们只使用了 MySQL Connector/Python 的纯 Python 实现。在本课中，我们将看到如何利用 Connector/Python 提供的 C 扩展。
Connector/Python 的 2.1 版本引入了 C 扩展。从 8.0 版本开始，默认情况下启用 C 扩展。当处理大型结果集时，C 扩展通常用于生产环境。
我们可以通过两种不同的方式启用 C 扩展:
-
将
use_pure参数设置为False，调用mysql.connector.connect()函数。但是，从 8.0 版本开始，use_pure参数默认为False，所以您实际上不需要将其传递给connect()函数。但是，如果您使用的是旧版本的 Connector/Python，那么您需要将use_pure=False传递给connect()函数，否则，默认情况下将使用纯 Python 实现。如果use_pure设置为False并且系统上没有安装 C 扩展，那么将使用纯 Python 实现。 -
使用 C 扩展的另一种方法是导入
_mysql_connector模块，而不是mysql.connector。_mysql_connector模块只是原始 MySQL C 客户端库的一个包装器。使用_mysql_connector模块的缺点是其 API 不同于mysql.connector，与 MySQL C 客户端库非常相似。
由于到目前为止我们看到的所有例子都使用了 Connector/Python 的纯 Python 实现，所以本课的其余部分只讨论如何通过_mysql_connector模块使用 C 扩展。
_ mysql _ 连接器模块
简单选择
import _mysql_connector
from mysql.connector.constants import ClientFlag
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL()
db.connect(**conn_args)
db.query('select * from city limit 5')
print("Rows:", db.num_rows())
if db.have_result_set:
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print("Rows:", db.num_rows())
db.free_result()
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
Rows: 0
(1, b'Kabul', b'AFG', b'Kabol', 1780000)
(2, b'Qandahar', b'AFG', b'Qandahar', 237500)
(3, b'Herat', b'AFG', b'Herat', 186800)
(4, b'Mazar-e-Sharif', b'AFG', b'Balkh', 127800)
(5, b'Amsterdam', b'NLD', b'Noord-Holland', 731200)
Rows: 5
以下是它的工作原理:
-
连接数据库的第一步是初始化
MySQL类的一个实例。MySQL实例用于打开和管理连接。它还用于执行语句和读取结果集。 -
在第 2 行，我们调用
MySQL实例的connect()方法来连接数据库。connect()方法以连接凭证为参数。 -
在第 4 行，我们使用
query()方法来执行 SQL 语句。请注意，在将查询发送到数据库之前，我们没有创建任何游标对象。成功后，query()方法返回True，否则引发异常。 -
在第 5 行，我们调用
num_rows()方法获取结果集中的行数。结果集中的行数只能在从数据库中提取行后确定。默认情况下，MySQL实例不缓冲(存储)结果集。这意味着在调用行提取方法之前，不会提取行。换句话说，行是按需获取的。此时，我们还没有获取任何行，因此对num_rows()方法的调用将返回 0。我们将在下一节看到如何创建缓冲的MySQL实例。 -
在第 22 行，我们使用
have_result_set属性来检查查询是否返回行。如果查询返回行，则have_result_set返回True，否则返回False。请注意，该属性仅指示查询是否可以生成行。它实际上并不计算结果集中的行数。这意味着对于返回零结果的SELECT语句，have_result_set属性将返回True。 -
在第 23 行和第 27 行，我们使用
fetch_row()方法从结果中获取行。fetch_row()方法返回复位集的下一行，如果结果集用尽，则返回None。该行作为元组返回。 -
在第 28 行，我们再次调用
num_rows()方法。但是这次我们已经读取了结果集中的所有行(即 5 行)。因此，num_rows()将返回实际行数，而不是 0。 -
在第 30 行，我们调用
free_result()方法来释放与结果集相关的内存。 -
在第 32 行，我们通过调用
close()方法来关闭与数据库的连接。 -
最后，在第 34 行，我们使用
_mysql_connector.MySQLError类来捕获异常。不同于由各种异常类组成的mysql.connector模块。_mysql_connector模块仅提供以下两个例外类:
1. `MySQLError`
2. `MySQLInterfaceError`
`MySQLError`是`Exception`的子类，`MySQLInterfaceError`是`MySQLError`的子类。因此，对于 MySQL 服务器返回的任何错误，我们都可以使用`MySQLError`。如果你想捕捉一些特定的错误，像往常一样使用 MySQL 错误代码。我们将在本课的后面部分看到一个这样的例子。
MySQL构造函数的常见参数
下表列出了一些常见的参数，我们可以将这些参数传递给MySQL构造函数来定制其默认行为。
| 争吵 | 描述 |
|---|---|
buffered |
如果设置为True，则在执行查询后立即提取行，否则按需提取行。默认为False。 |
raw |
如果设置为True，MySQL 类型到其等价 Python 类型的转换将不会发生，结果将按原样返回。默认为True。 |
charset_name |
向数据库服务器发送数据和从数据库服务器接收数据时使用的字符集。 |
use_unicode |
如果设置为True，CHAR，VARCHAR和TEXT将使用配置的字符集(通过charset_name参数)作为 unicode 字符串返回 |
现在让我们看一些如何使用这些参数的例子:
示例 1:使用buffered参数
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL(buffered=True)
db.connect(**conn_args)
db.query('select * from city limit 5')
print("Rows:", db.num_rows())
if db.have_result_set:
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print("Rows:", db.num_rows())
db.free_result()
db.close()
except _mysql_connector.MySQLError as e:
print(e)
预期输出:
Rows: 5
(1, b'Kabul', b'AFG', b'Kabol', 1780000)
(2, b'Qandahar', b'AFG', b'Qandahar', 237500)
(3, b'Herat', b'AFG', b'Herat', 186800)
(4, b'Mazar-e-Sharif', b'AFG', b'Balkh', 127800)
(5, b'Amsterdam', b'NLD', b'Noord-Holland', 731200)
Rows: 5
因为我们使用的是缓冲的MySQL实例，所以在执行查询后会立即提取行，并且对num_rows()的第一次调用会返回实际的行数，而不是 0。
示例 2:使用raw参数
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL(buffered=True, raw=True,)
db.connect(**conn_args)
db.query('select * from city limit 5')
print("Rows:", db.num_rows())
if db.have_result_set:
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print("Rows:", db.num_rows())
db.free_result()
db.close()
except _mysql_connector.MySQLError as e:
print(e)
预期输出:
Rows: 5
(bytearray(b'1'), bytearray(b'Kabul'), bytearray(b'AFG'), bytearray(b'Kabol'), bytearray(b'1780000'))
(bytearray(b'2'), bytearray(b'Qandahar'), bytearray(b'AFG'), bytearray(b'Qandahar'), bytearray(b'237500'))
(bytearray(b'3'), bytearray(b'Herat'), bytearray(b'AFG'), bytearray(b'Herat'), bytearray(b'186800'))
(bytearray(b'4'), bytearray(b'Mazar-e-Sharif'), bytearray(b'AFG'), bytearray(b'Balkh'), bytearray(b'127800'))
(bytearray(b'5'), bytearray(b'Amsterdam'), bytearray(b'NLD'), bytearray(b'Noord-Holland'), bytearray(b'731200'))
Rows: 5
例 3:使用charset_name和use_unicode
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL(buffered=True, charset_name='utf8', use_unicode=True)
db.connect(**conn_args)
db.query('select * from city order by name desc limit 5')
print("Rows:", db.num_rows())
if db.have_result_set:
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print("Rows:", db.num_rows())
db.free_result()
db.close()
except _mysql_connector.MySQLError as e:
print(e)
预期输出:
Rows: 5
(548, 'Šumen', 'BGR', 'Varna', 94686)
(3736, 'Štšolkovo', 'RUS', 'Moskova', 104900)
(3479, 'Šostka', 'UKR', 'Sumy', 90000)
(2450, 'Šiauliai', 'LTU', 'Šiauliai', 146563)
(3665, 'Šahty', 'RUS', 'Rostov-na-Donu', 221800)
Rows: 5
如果不将charset_name和use_unicode参数传递给MySQL()构造函数，那么输出将如下所示:
Rows: 5
(548, b'\x8aumen', b'BGR', b'Varna', 94686)
(3736, b'\x8at\x9aolkovo', b'RUS', b'Moskova', 104900)
(3479, b'\x8aostka', b'UKR', b'Sumy', 90000)
(2450, b'\x8aiauliai', b'LTU', b'\x8aiauliai', 146563)
(3665, b'\x8aahty', b'RUS', b'Rostov-na-Donu', 221800)
Rows: 5
列信息
为了获得表中列的信息，我们使用fetch_fields()方法。它返回元组列表，每列一个元组..
import _mysql_connector
from mysql.connector.constants import FieldType
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL(buffered=True)
db.connect(**conn_args)
db.query('select * from city limit 5')
fields_info = db.fetch_fields()
print("{:15} {:10} {:15}".format("Column Name", "Type", "NULL_OK", ))
for info in fields_info:
# print(info)
print("{:15} {:<10} {:<15}".format(
info[4].decode('utf-8'),
FieldType.get_info(info[-3]),
info[-1]))
db.free_result()
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
Column Name Type NULL_OK
ID LONG 0
Name STRING 0
CountryCode STRING 0
District STRING 0
Population LONG 0
转义字符串和传递参数
在将用户输入传递给查询之前，您必须对其进行转义。逃跑是借助escape_string()方法完成的。它接受一个字符串，并将转义后的字符串作为bytes返回。这里有一个例子:
>>>
>>> db.escape_string("delete from category;")
b'delete from category;'
>>>
>>> db.escape_string("'Another string'")
b"\\'Another string\\'"
>>>
>>>
我们可以将bytes类型转换为str，如下所示:
>>>
>>> db.escape_string("'").decode("utf8")
"\\'"
>>>
>>>
>>> db.escape_string("'Another string'").decode("utf8")
"\\'Another string\\'"
>>>
>>>
一旦我们逃脱了输入，我们就可以安全地将其传递给查询。
下面清单中的代码从用户那里获取一个关键字，并返回其名称包含该关键字的所有城市。
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
try:
db = _mysql_connector.MySQL(buffered=True,
charset_name='utf8',
use_unicode=True)
db.connect(**conn_args)
city = input("Enter keyword: ")
cleaned_city = db.escape_string(city).decode('utf-8') # escape the string entered
db.query('select * from city where name '
'like "%{}%" limit 5'.format(cleaned_city))
if db.num_rows():
print("\nList of cities containing '{}' in their "
"name.\n".format(cleaned_city))
# print("Rows:", db.num_rows())
print("{:<5} {:<20} {:10} {:15} {:10}".format
("ID", "City", "Country", "District", "Population"))
if db.have_result_set:
row = db.fetch_row()
while row:
print("{:<5} {:<20} {:10} {:15} {:10}".format(row[0],
row[1],
row[2],
row[3],
row[4]))
row = db.fetch_row()
print("\nRows:", db.num_rows())
else:
print("Nothing Found :(")
db.free_result()
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
Enter keyword: de
List of cities containing 'de' in their name.
ID City Country District Population
15 Enschede NLD Overijssel 149544
24 Leiden NLD Zuid-Holland 117196
29 Ede NLD Gelderland 101574
30 Delft NLD Zuid-Holland 95268
73 Lomas de Zamora ARG Buenos Aires 622013
Rows: 5
执行多个查询
要在单个查询中执行多个语句，只需将标志MULTI_STATEMENTS传递给 connect()方法。
这里有一个例子:
import _mysql_connector
from mysql.connector.constants import ClientFlag
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
db = _mysql_connector.MySQL(buffered=True,
charset_name='utf8',
use_unicode=True)
db.connect(**conn_args, client_flags=ClientFlag.MULTI_STATEMENTS)
db.query('select * from city ORDER BY name desc limit 5;'
' select * from city ORDER BY id desc limit 6')
print("Rows:", db.num_rows()) # row count in the first result set
if db.have_result_set: # loop over the first result set
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print()
print("Does another result set exists? ", db.more_results())
print("Prepare the next result set: ", db.next_result())
print()
print("Rows:", db.num_rows()) # row count in the second result set
if db.have_result_set: # loop over the second result set
row = db.fetch_row()
while row:
print(row)
row = db.fetch_row()
print("\nDoes another result set exists? ", db.more_results())
db.free_result()
db.close()
预期输出:
Rows: 5
(548, 'Šumen', 'BGR', 'Varna', 94686)
(3736, 'Štšolkovo', 'RUS', 'Moskova', 104900)
(3479, 'Šostka', 'UKR', 'Sumy', 90000)
(2450, 'Šiauliai', 'LTU', 'Šiauliai', 146563)
(3665, 'Šahty', 'RUS', 'Rostov-na-Donu', 221800)
Does another result set exists? True
Prepare the next result set: True
Rows: 6
(4081, 'Kabul', 'AFG', 'Kabol', 1780000)
(4079, 'Rafah', 'PSE', 'Rafah', 92020)
(4078, 'Nablus', 'PSE', 'Nablus', 100231)
(4077, 'Jabaliya', 'PSE', 'North Gaza', 113901)
(4076, 'Hebron', 'PSE', 'Hebron', 119401)
(4075, 'Khan Yunis', 'PSE', 'Khan Yunis', 123175)
这里有几件事需要注意:
-
从查询中读取第一个结果集后，我们调用
more_results()方法(第 31 行)。如果结果集较多，则more_results()方法返回True，否则返回False。 -
在我们开始读取下一个结果集中的行之前，我们必须首先准备它，即把结果指针移动到下一个结果集。这是通过
next_result()方法完成的(第 33 行)。 -
最后，我们再次调用
more_results()，但是这次它返回False，因为没有更多的结果集。
在下一节中，我们将演示一些如何创建表、插入、更新和删除数据的示例。
创建表格
import _mysql_connector
from mysql.connector import errorcode
from collections import OrderedDict
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='world'
)
db_name = 'blog'
tables = OrderedDict()
tables['category'] = '''
create table category
(
id int not null auto_increment primary key,
name varchar(50) not null
)
'''
tables['post'] = '''
create table post
(
id int not null auto_increment PRIMARY KEY,
title varchar(200) not null,
content text not null,
date TIMESTAMP not null,
category_id int not null,
FOREIGN key (category_id) REFERENCES category(id)
)
'''
def create_db(conn):
try:
conn.query("create database {}".format(db_name))
print("Database created.")
except _mysql_connector.MySQLInterfaceError as err:
print("Database creation failed:", err)
exit(1)
try:
db = _mysql_connector.MySQL(buffered=True)
db.connect(**conn_args)
try:
db.select_db(db_name)
print('Database {} already exist.'.format(db_name))
except _mysql_connector.MySQLInterfaceError as err:
# database doesn't exist, create one
if errorcode.ER_BAD_DB_ERROR == err.errno:
create_db(db)
db.select_db(db_name)
for k, v in tables.items():
try:
db.query(v)
print('Table {} created.'.format(k))
except _mysql_connector.MySQLInterfaceError as err:
if errorcode.ER_TABLE_EXISTS_ERROR == err.errno:
print('Table {} already exists.'.format(k))
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
插入行
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='blog'
)
try:
db = _mysql_connector.MySQL()
db.connect(**conn_args)
category_list = [
('python',),
('css', ),
('java',),
('c',),
('php',),
]
stmt = "INSERT INTO category(name) values('{}')"
for category in category_list:
db.query(stmt.format(category[0]))
print("Row ID:", db.insert_id()) # return last inserted id
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
更新行
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='blog'
)
try:
db = _mysql_connector.MySQL()
db.connect(**conn_args)
stmt = "UPDATE category set name = upper(name)"
db.query(stmt)
print("Affects rows:", db.affected_rows()) # return affected rows
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
Affects rows: 5
处理
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='blog'
)
try:
db = _mysql_connector.MySQL()
db.connect(**conn_args)
stmt1 = "INSERT INTO category(name) values('shell scripting')"
stmt2 = "INSERT INTO post(title, content, category_id) values('{}', '{}', '{}')"
db.query('START TRANSACTION') # start transaction
db.query(stmt1)
category_id = db.insert_id()
db.query(stmt2.format('Title', 'Content', category_id))
db.commit() # commit transaction
print("Transaction committed.")
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
Transaction committed.
删除行
import _mysql_connector
conn_args = dict(
user='root',
password='pass',
host='127.0.0.1',
database='blog'
)
try:
db = _mysql_connector.MySQL()
db.connect(**conn_args)
db.query('DELETE FROM post')
db.query('DELETE FROM category')
print('All rows deleted.')
db.close()
except _mysql_connector.MySQLInterfaceError as e:
print(e)
预期输出:
All rows deleted.
