SQLAlchemy完全入门

Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

https://edu.csdn.net/course/detail/35475

最近想要学习SQLAlchemy, 发现网上的中文文档大多是机翻的, 读起来特别变扭, 因此对照着最新的英文文档梳理了一遍, 写下来记录一下
目前SQLAlchemy的版本为1.4.x, 风格处于1.x过渡到2.0的时代. 为了尽量让这篇文章的兼容之后的版本, 本文将讲述1.x和2.0两种风格的接口(主要是查询的接口)

其实在2.0风格中, 主要受到影响的是ORM的查询方式, 详情见文档: 2.0 Migration - ORM Usage

安装

[fast →](https://blog.csdn.net/biggbang)
1

检测sqlalchemy版本:

[fast →](https://blog.csdn.net/biggbang)
1

使用步骤

一般来说SQLAlchemy的使用方式有两种: Core和ORM
两种有什么不同呢?

1. ORM是构建在Core之上的
2. Core更加底层, 可以执行直接执行SQL语句
3. ORM类似于Django的ORM, 由于sqlalchemy提供了一套接口, 所以不需要我们直接写SQL语句 (1.x版本)
4. 至于要用哪个, 等到你用到时, 你会知道的

组件依赖关系图:

Core

一般来说, 使用步骤如下:

1. 配置数据库连接
2. 建立连接
3. 创建表
4. 执行SQL语句, 按需开启事件是否自动提交
5. 拿到返回数据, 执行其他代码

数据库的连接的格式

我们在创建引擎(连接)时, 需要指定数据库的URL, URL格式, 见: Engine Configuration, 总的来说, 格式就是: dialect[+driver]://user:password@host/dbname[?key=value..]

• dialect 数据库名称(方言): 如mysql
• driver 连接数据库的库: 如: pymysql
• user 用户名
• password 密码
• host 地址
• dbname 数据库名称
• key=value 指的是给数据库的参数

如下面的URL:

|  | mysql+pymysql://root:passwd@127.0.0.1:3306/test\_db?charset=utf8 |


1
2
3
4
5

建立连接

调用sqlalchemy.create_engine方法, 为了兼容2.0风格的接口, 可以加上future参数. 至于什么是2.0风格的接口, 可以看看官方文档: 2.0 style
create_engine有几个参数需要我们注意:

• url 即数据库url, 其格式见上文: 数据库的连接的格式
• echo参数为True时, 将会将engine的SQL记录到日志中 ( 默认输出到标准输出)
• echo_pool 为True时，会将连接池的记录信息输出
• future 使用2.0样式Engine 和 Connection API

更多参数见官方文档: sqlalchemy.create_engine

例子

|  | from sqlalchemy import create\_engine |
|  |  |
|  | # 兼容2.0的写法 |
|  | # 返回对象不一样 |
|  | engine1 = create\_engine("sqlite+pysqlite:///:memory:", echo=True, future=True) |
|  | print(type(engine1)) |
|  | #  |
|  |  |
|  | engine2 = create\_engine("sqlite+pysqlite:///:memory:", echo=True) |
|  | print(type(engine2)) |
|  | #  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

注意, 由于sqlalchemy使用lazy initialization的策略连接数据库, 故此时还未真正地连接上数据库

创建表

我们想要让数据库创建一个表, 需要利用MetaData对象, 关于一些常用的MetaData方法, 见: MetaData
除了要MetaData对象外, 我们还需要Table对象, 用于定义一个表的结构
Table的一般使用

|  | mytable = Table("mytable", metadata, |
|  |  Column('mytable\_id', Integer, primary\_key=True), |
|  |  Column('value', String(50)) |
|  |  ) |


1
2
3
4
5
6
7
8

Table的参数:

• name 表名称
• metadata 该表所属的MetaData对象
• 其他参数: 通过Column指定一列数据, 格式见: Column定义

例子:

|  | from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey |
|  | from sqlalchemy import create\_engine, text |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  | metadata\_obj = MetaData() |
|  |  |
|  | user\_table = Table( |
|  | "user\_account", |
|  |  metadata\_obj, |
|  |  Column('id', Integer, primary\_key=True), |
|  |  Column("username", String(30))) # String也可以不实例化 |
|  |  |
|  | # 第二个表 |
|  | address\_table = Table( |
|  | "address", |
|  |  metadata\_obj, |
|  |  Column("id", Integer, primary\_key=True), |
|  | # 定义外键 |
|  |  Column("uid", ForeignKey("user\_account.id"), nullable=False), |
|  |  Column('email\_address', String(32), nullable=False) |
|  | ) |
|  | # 相当于执行 CREATE TABLE 语句 |
|  | metadata\_obj.create\_all(engine) |
|  |  |
|  | """ |
|  | -- 相当于: |
|  | CREATE TABLE user\_account ( |
|  |  id INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  username VARCHAR(30),  |
|  |  PRIMARY KEY (id) |
|  | ); |
|  | CREATE TABLE address ( |
|  |  id INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  uid INTEGER NOT NULL,  |
|  |  email\_address VARCHAR(32) NOT NULL,  |
|  |  PRIMARY KEY (id),  |
|  |  FOREIGN KEY(uid) REFERENCES user\_account (id) |
|  | ) |
|  | """ |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

create_all方法, 默认会在创建表之间检测一下表是否存在, 不存在时才创建.

Table的一些属性

|  | # ---------- 访问所有列 |
|  | # .c => Column |
|  | print(user\_table.c.keys()) |
|  | # ['id', 'username'] |
|  |  |
|  | # ---------- 访问某一列 |
|  | print(repr(user\_table.c.username)) |
|  | # Column('username', String(length=30), table=) |
|  |  |
|  | # ---------- 返回主键 |
|  | print(user\_table.primary\_key) |
|  | # 隐式生成 |
|  | # PrimaryKeyConstraint(Column('id', Integer(), table=, primary\_key=True, nullable=False)) |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

在事务中执行SQL

通常, 我们通过调用engine.connect和engine.begin方法开始一个事件
sqlalchemy使用事务有两种风格commit as you go和Begin once, 前者需要我们手动提交, 后者会自动提交

手动提交

engine.connect方法符合python的上下文管理协议, 会返回一个Connection对象, 该方法会在不手动提交的情况下回滚.举个例子:

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy import text |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  | with engine.connect() as conn: |
|  | # 执行 |
|  |  result = conn.execute(text("select 'hello world'")) # text 可以使用SQL语句 |
|  |  print(result.all()) |
|  | # conn.commit() |
|  | # [('hello world',)] |
|  |  |
|  | # 最后会ROLLBACK |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

上面的代码中, 相当于开启了事务, 由于最后没有调用commit方法, 所以会回滚.

自动提交

engine.begin方法也符合python的上下文管理协议, 只要执行时不报错就会自动提交, 报错时会回滚.

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy import text |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  | with engine.begin() as conn: |
|  |  result = conn.execute(text("select 'hello world'")) |
|  |  print(result.all()) |
|  | # [('hello world',)] |
|  |  |
|  | # COMMIT |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

绑定参数

上面在事务中执行SQL语句时, 我们用到了sqlalchemy.text, 可以直接定义文本SQL字符串
为了避免被SQL注入, 故在需要传入参数的场景中需要根据sqlalchemy的方式传入, 而不是直接拼接成字符串.
使用:y的格式定义参数, 且将值以字典的形式传给execute

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy import text |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  | with engine.begin() as conn: |
|  |  result = conn.execute(text("select name from userinfo where name like :y"), {"y": "lcz%"}) |
|  |  print(result.all()) |
|  | # [('lczmx',)] |
|  |  |
|  | # COMMIT |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

多个参数时, 可以这样

|  | with engine.connect() as conn: |
|  |  conn.execute( |
|  |  text("INSERT INTO userinfo (id, name) VALUES (:x, :y)"), |
|  |  [{"x": 1, "y": "lcmx"}, {"x": 2, "y": "xxx"}]) |
|  |  conn.commit() |


1
2
3
4
5
6
7
8
9

这种方式也可以

|  | stmt = text("SELECT x, y FROM some\_table WHERE y > :y ORDER BY x, y").bindparams(y=6) |
|  |  |
|  | with engine.connect() as conn: |
|  |  conn.execute(stmt) |
|  |  conn.commit() |


1
2
3
4
5
6
7
8
9

增删改查

处理使用text直接执行SQL外, 你还可以使用其他语法增删改查数据
假如表结构如下:

`|  |  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |  |
|  |
|  | $ |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |
|  |`[fast →](https://blog.csdn.net/biggbang)show create table address; +---------+-----------------------------------------+ | | Table | Create Table | | +---------+-----------------------------------------+ | | address | CREATE TABLE `address` ( |  `id` int NOT NULL AUTO\_INCREMENT, |  `uid` int NOT NULL, |  `email\_address` varchar(32) NOT NULL, |  PRIMARY KEY (`id`), |  KEY `uid` (`uid`), |  CONSTRAINT `address\_ibfk\_1` FOREIGN KEY (`uid`) REFERENCES `user\_account` (`id`) | ) ENGINE=InnoDB AUTO\_INCREMENT=2 DEFAULT CHARSET=gbk | | +---------+------------------------------------------+ |  | +--------------+------------------------------------+ | | Table | Create Table | | +--------------+------------------------------------+ | | user\_account | CREATE TABLE `user\_account` ( |  `id` int NOT NULL AUTO\_INCREMENT, |  `username` varchar(30) DEFAULT NULL, |  PRIMARY KEY (`id`) | ) ENGINE=InnoDB AUTO\_INCREMENT=6 DEFAULT CHARSET=gbk | | +--------------+-------------------------------------+ | 1 row in set (0.00 sec) |  |  |[restart ↻](https://blog.csdn.net/biggbang)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

插入数据

使用insert(...).values(...)形式为数据库插入数据

|  | from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey |
|  | from sqlalchemy import create\_engine, insert |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}?charset=utf8".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  | metadata\_obj = MetaData() |
|  |  |
|  | user\_table = Table( |
|  | "user\_account", |
|  |  metadata\_obj, |
|  |  Column('id', Integer, primary\_key=True), |
|  |  Column("username", String(30))) # String也可以不实例化 |
|  |  |
|  | # 第二个表 |
|  | address\_table = Table( |
|  | "address", |
|  |  metadata\_obj, |
|  |  Column("id", Integer, primary\_key=True), |
|  |  Column("uid", ForeignKey("user\_account.id"), nullable=False), |
|  |  Column('email\_address', String(32), nullable=False) |
|  | ) |
|  |  |
|  | metadata\_obj.create\_all(bind=engine) |
|  |  |
|  | with engine.connect() as conn: |
|  | # 插入一条普通数据 |
|  |  conn.execute(insert(user\_table).values(id=1, username="lczmx")) |
|  | # 插入外键等数据 |
|  |  conn.execute(insert(address\_table).values(uid=1, email\_address="lczmx@foxmail.com")) |
|  |  |
|  | # 自动生成value, 不需要我们手动指定 |
|  |  |
|  |  conn.execute(insert(user\_table), |
|  |  [{"username": "张三"}, |
|  |  {"username": "李四"}, |
|  |  {"username": "王五"}, |
|  |  {"username": "赵六"}, |
|  |  ]) |
|  |  |
|  |  conn.commit() |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

SQLAlchemy还提供了更复杂的用法, 见: Inserting Rows with Core

注意: 插入数据没有返回值

删除数据

使用delete(...).where(...)的形式删除数据

目前的表数据:

|  | select u.id as uid, u.username, a.id as aid, a.email\_address as email\_address  |
|  | from user\_account as u  |
|  | left join address as a on u.id=a.uid; |


1
2
3
4
5
6
7

|  | +-----+----------+------+-------------------+ |
|  | | uid | username | aid | email\_address | |
|  | +-----+----------+------+-------------------+ |
|  | | 1 | lczmx | 1 | lczmx@foxmail.com | |
|  | | 2 | 张三 | NULL | NULL | |
|  | | 3 | 李四 | NULL | NULL | |
|  | | 4 | 王五 | NULL | NULL | |
|  | | 5 | 赵六 | NULL | NULL | |
|  | +-----+----------+------+-------------------+ |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14

例子:

|  | from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey |
|  | from sqlalchemy import create\_engine, delete |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}?charset=utf8".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  | metadata\_obj = MetaData() |
|  |  |
|  | user\_table = Table( |
|  | "user\_account", |
|  |  metadata\_obj, |
|  |  Column('id', Integer, primary\_key=True), |
|  |  Column("username", String(30))) # String也可以不实例化 |
|  |  |
|  | # 第二个表 |
|  | address\_table = Table( |
|  | "address", |
|  |  metadata\_obj, |
|  |  Column("id", Integer, primary\_key=True), |
|  |  Column("uid", ForeignKey("user\_account.id"), nullable=False), |
|  |  Column('email\_address', String(32), nullable=False) |
|  | ) |
|  |  |
|  | metadata\_obj.create\_all(bind=engine) |
|  |  |
|  | with engine.connect() as conn: |
|  | # 一般删除 |
|  | # user\_table.c 获取的是 列数据 |
|  |  result1 = conn.execute(delete(user\_table).where(user\_table.c.id == 3)) |
|  |  print(f"受影响行数: {result1.rowcount}") # 受影响行数: 1 |
|  |  |
|  | # and 删除 |
|  |  result2 = conn.execute(delete(user\_table).where(user\_table.c.username == "张三", user\_table.c.id == 2)) |
|  |  print(f"受影响行数: {result2.rowcount}") # 受影响行数: 1 |
|  |  |
|  |  conn.commit() |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

.rowcount属性获取受影响的行数

更多见: The delete() SQL Expression Construct

更新数据

使用update(...).where(...).values(...)的形式更新数据

|  | select u.id as uid, u.username, a.id as aid, a.email\_address as email\_address  |
|  | from user\_account as u  |
|  | left join address as a on u.id=a.uid; |
|  |  |


1
2
3
4
5
6
7
8

|  | +-----+----------+------+-------------------+ |
|  | | uid | username | aid | email\_address | |
|  | +-----+----------+------+-------------------+ |
|  | | 1 | lczmx | 1 | lczmx@foxmail.com | |
|  | | 2 | 张三 | NULL | NULL | |
|  | | 3 | 李四 | NULL | NULL | |
|  | | 4 | 王五 | NULL | NULL | |
|  | | 5 | 赵六 | NULL | NULL | |
|  | +-----+----------+------+-------------------+ |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14

例子:

|  | from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey |
|  | from sqlalchemy import create\_engine, update, bindparam, select |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}?charset=utf8". |
|  | format(**DATABASE\_CONFIG), echo=True, future=True) |
|  | metadata\_obj = MetaData() |
|  |  |
|  | user\_table = Table( |
|  | "user\_account", |
|  |  metadata\_obj, |
|  |  Column('id', Integer, primary\_key=True), |
|  |  Column("username", String(30))) # String也可以不实例化 |
|  |  |
|  | # 第二个表 |
|  | address\_table = Table( |
|  | "address", |
|  |  metadata\_obj, |
|  |  Column("id", Integer, primary\_key=True), |
|  |  Column("uid", ForeignKey("user\_account.id"), nullable=False), |
|  |  Column('email\_address', String(32), nullable=False) |
|  | ) |
|  |  |
|  | metadata\_obj.create\_all(bind=engine) |
|  |  |
|  | with engine.connect() as conn: |
|  | # 一般更新 |
|  |  result1 = conn.execute(update(user\_table).where( |
|  |  user\_table.c.username == "王五").values(username="王老五")) |
|  |  print(f"受影响行数: {result1.rowcount}") # 受影响行数: 1 |
|  |  |
|  | # 更新数据 加上 原来的数据 |
|  |  result2 = conn.execute( |
|  |  update(user\_table).where(user\_table.c.username == "赵六").values( |
|  |  username=user\_table.c.username + "一号")) |
|  |  print(f"受影响行数: {result2.rowcount}") # 受影响行数: 1 |
|  |  |
|  | # 以字典的形式, 替换更新多个值 |
|  |  result3 = conn.execute( |
|  |  update(user\_table).where(user\_table.c.username == bindparam('old\_name')).values( |
|  |  username=bindparam('new\_name')), |
|  |  [ |
|  |  {"old\_name": "张三", "new\_name": "新张三"}, |
|  |  {"old\_name": "李四", "new\_name": "新李四"}, |
|  |  ] |
|  |  ) |
|  |  |
|  |  print(f"受影响行数: {result3.rowcount}") # 受影响行数: 2 |
|  |  |
|  | # 以 子查询 的方式 更新数据 |
|  |  scalar\_subq = ( |
|  |  select(address\_table.c.email\_address). |
|  |  where(address\_table.c.uid == user\_table.c.id). |
|  |  order\_by(address\_table.c.id). |
|  |  limit(1). |
|  |  scalar\_subquery() |
|  |  ) |
|  | # 将email\_address的值 赋给 username |
|  |  update(user\_table).values(username=scalar\_subq) |
|  |  |
|  | """ |
|  |  -- 以上查询, 相当于: |
|  |  UPDATE user\_account SET username=(SELECT address.email\_address |
|  |  FROM address |
|  |  WHERE address.uid = user\_account.id ORDER BY address.id |
|  |  LIMIT :param\_1) |
|  |  """ |
|  |  conn.commit() |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

修改后的结果:

|  | +-----+----------+------+-------------------+ |
|  | | uid | username | aid | email\_address | |
|  | +-----+----------+------+-------------------+ |
|  | | 1 | lczmx | 1 | lczmx@foxmail.com | |
|  | | 2 | 新张三 | NULL | NULL | |
|  | | 3 | 新李四 | NULL | NULL | |
|  | | 4 | 王老五 | NULL | NULL | |
|  | | 5 | 赵六一号 | NULL | NULL | |
|  | +-----+----------+------+-------------------+ |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14

更多见: Updating and Deleting Rows with Core

查询数据

由于2.0的查询方式, Core和ORM都可以使用, 所以放在一起, 见下文: 查询数据详解

处理查询返回的数据

我们执行conn.execute方法的结果为: CursorResult对象
其本质上是继承与Result对象, 其使用方式见: Result

例子:
假如查询的表:

|  | mysql> select * from user\_account; |
|  | +----+----------+ |
|  | | id | username | |
|  | +----+----------+ |
|  | | 9 | lczmx | |
|  | | 10 | jack | |
|  | | 11 | tom | |
|  | | 12 | mike | |
|  | +----+----------+ |
|  | 4 rows in set (0.00 sec) |
|  |  |
|  | mysql> |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

利用SQLAlchemy获取数据:

|  | from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey |
|  | from sqlalchemy import create\_engine, text |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  | with engine.connect() as conn: |
|  | # 执行 |
|  |  result = conn.execute(text("select * from user\_account;")) |
|  |  |
|  | for row in result.all(): |
|  | # 使用f-strings 格式化字符串 |
|  |  print(f"id: {row.id:3}, username: {row.username:20}") |
|  | # 打印的结果: |
|  | """ |
|  |  id: 9, username: lczmx  |
|  |  id: 10, username: jack  |
|  |  id: 11, username: tom  |
|  |  id: 12, username: mike  |
|  |  """ |
|  |  conn.commit() |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

ORM

和Core一样, ORM也有一定的使用步骤:

1. 配置数据库连接, 见上文: 数据库的连接的格式
2. 创建会话
3. 创建表
4. 使用接口, 增删改查数据
5. 拿到返回数据, 执行其他代码

在学习SQLAlcehmy的ORM之前, 建议先了解一些概念, 以免后面会混淆

1. 会话 Session
   会话是SQLAlchemy ORM与数据库的交互对象
   它可以管理建立连接中engine, 并为通过会话加载或与会话关联的对象提供标识映射 (identity map)
   在使用时与Connection非常相似, 你可以对比着使用
2. Base
   通过sqlalchemy.orm.declarative_base创建
   作为定义表的基类, 内部有包含MetaData对象
   可以类似于Django一样定义表

在SQLAlchemy中, session是一个连接池, 的由其管理, 因此, 假如我们需要操作数据库的话, 需要在session中拿到Connection(连接)

创建会话

SQLAlchemy提供了两种创建会话的方法:

1. sqlalchemy.orm.Session

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy.orm import Session |
|  |  |
|  | # 创建引擎 |
|  | engine = create\_engine('postgresql://scott:tiger@localhost/') |
|  |  |
|  | # 创建会话 |
|  | # 以下with可以简写成 with Session(engine) as session, session.begin(): |
|  | with Session(engine) as session: |
|  | # 开启自动提交 |
|  | with session.begin(): |
|  | # add方法 会将some\_object 保存到数据库 |
|  | # session.add(some\_object) |
|  | # session.add(some\_other\_object) |
|  | pass |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2. sqlalchemy.orm.sessionmaker

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy.orm import sessionmaker |
|  |  |
|  | # 创建引擎 |
|  | engine = create\_engine('postgresql://scott:tiger@localhost/') |
|  |  |
|  | # 创建session |
|  | Session = sessionmaker(engine) |
|  |  |
|  | # 一般使用 |
|  | with Session() as session: |
|  | # session.add(some\_object) |
|  | # session.add(some\_other\_object) |
|  | # 提交 |
|  |  session.commit() |
|  |  |
|  | # 自动提交 |
|  | with Session.begin() as session: |
|  | # session.add(some\_object) |
|  | # session.add(some\_other\_object) |
|  | pass |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

虽然有两种方法创建会话, 但我们一般使用sessionmaker创建会话

另外补充一下session的其它使用方式:

|  | from sqlalchemy import create\_engine |
|  | from sqlalchemy.orm import sessionmaker |
|  |  |
|  | engine = create\_engine('postgresql://scott:tiger@localhost/') |
|  |  |
|  | Session = sessionmaker(engine) |
|  |  |
|  | # 从连接指定到session |
|  | with engine.connect() as connection: |
|  | with Session(bind=connection) as session: |
|  | # 一些操作 |
|  | pass |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

下面列出session的一些常用方法, 增删改查数据时要用到

例子:

|  | from sqlalchemy import create\_engine, text |
|  | from sqlalchemy.orm import Session |
|  |  |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  | stmt = text("SELECT id, name FROM userinfo WHERE id > :y").bindparams(y=1) |
|  | with Session(engine) as session: |
|  |  result = session.execute(stmt) |
|  |  print(result.all()) |
|  | # [(2, 'name2'), (3, 'name2')] |
|  |  |
|  | # ROLLBACK |
|  |  |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

在ORM中创建表

使用ORM时, 我们也需要MetaData, 不同的是, 我们是通过sqlalchemy.orm.registry构造的. 而且, 我们不需要像Core那样直接声明Table, 而是继承某个公共基类 (Base), 添加属性即可. 有两种方式定义基类.
方式一:

|  | from sqlalchemy.orm import registry |
|  | mapper\_registry = registry() |
|  | print(mapper\_registry.metadata) # MetaData对象 |
|  | # 公共基类 |
|  | Base = mapper\_registry.generate\_base() |


1
2
3
4
5
6
7
8
9

方法二:

|  | from sqlalchemy.orm import declarative\_base |
|  |  |
|  | # 内部 return registry(...).generate\_base(...) |
|  | Base = declarative\_base() |
|  |  |


1
2
3
4
5
6
7
8
9

现在你可以像在Django ORM中一样, 定义表并在数据库中创建表, 每一个Column表示一列数据, 关于Column的写法, 见: Column定义

|  | from sqlalchemy import Column, String, Integer, create\_engine, SMALLINT, Boolean, ForeignKey |
|  | from sqlalchemy.orm import relationship, declarative\_base, sessionmaker |
|  |  |
|  | # 导入公共基类 |
|  | Base = declarative\_base() |
|  | # 数据库配置 |
|  | DATABASE\_CONFIG = { |
|  | "username": "root", |
|  | "password": "123456", |
|  | "host": "localhost", |
|  | "database": "test" |
|  | } |
|  | # 连接mysql |
|  | engine = create\_engine("mysql+pymysql://{username}:{password}@{host}/{database}".format(**DATABASE\_CONFIG), |
|  |  echo=True, future=True) |
|  |  |
|  |  |
|  | class Student(Base): |
|  |  \_\_tablename\_\_ = "student" |
|  |  sid = Column("sid", Integer, primary\_key=True) |
|  |  name = Column("name", String(32), nullable=False, index=True, comment="姓名") |
|  |  age = Column("age", SMALLINT, nullable=False, comment="年龄") |
|  |  gender = Column("gender", Boolean, nullable=False, comment="性别, True: 男, False: 女") |
|  |  |
|  |  |
|  | class Course(Base): |
|  |  \_\_tablename\_\_ = "course" |
|  |  cid = Column("cid", Integer, primary\_key=True) |
|  |  name = Column("name", String(10), nullable=False, comment="科目名") |
|  |  tid = Column("tid", ForeignKey("teacher.tid"), comment="课程教师") |
|  |  |
|  |  |
|  | class Teacher(Base): |
|  |  \_\_tablename\_\_ = "teacher" |
|  |  tid = Column("tid", Integer, primary\_key=True) |
|  |  name = Column("name", String(10), nullable=False, comment="教师名") |
|  |  |
|  |  |
|  | class Score(Base): |
|  |  \_\_tablename\_\_ = "score" |
|  |  sid = Column("sid", Integer, primary\_key=True) |
|  |  score = Column("score", SMALLINT, nullable=False, comment="成绩") |
|  |  student\_id = Column("student\_id", ForeignKey("student.sid"), comment="成绩所属学生") |
|  |  course\_id = Column("course\_id", ForeignKey("course.cid"), comment="成绩所属科目") |
|  |  |
|  |  |
|  | Base.metadata.create\_all(bind=engine) |
|  |  |
|  | """ |
|  | -- 对于sql |
|  | CREATE TABLE student ( |
|  |  sid INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  name VARCHAR(32) NOT NULL COMMENT '姓名',  |
|  |  age SMALLINT NOT NULL COMMENT '年龄',  |
|  |  gender BOOL NOT NULL COMMENT '性别, True: 男, False: 女',  |
|  |  PRIMARY KEY (sid) |
|  | ) |
|  | CREATE TABLE teacher ( |
|  |  tid INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  name VARCHAR(10) NOT NULL COMMENT '教师名',  |
|  |  PRIMARY KEY (tid) |
|  | ) |
|  | CREATE TABLE course ( |
|  |  cid INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  name VARCHAR(10) NOT NULL COMMENT '科目名',  |
|  |  tid INTEGER COMMENT '课程教师',  |
|  |  PRIMARY KEY (cid),  |
|  |  FOREIGN KEY(tid) REFERENCES teacher (tid) |
|  | ) |
|  | CREATE TABLE score ( |
|  |  sid INTEGER NOT NULL AUTO\_INCREMENT,  |
|  |  score SMALLINT NOT NULL COMMENT '成绩',  |
|  |  student\_id INTEGER COMMENT '成绩所属学生',  |
|  |  course\_id INTEGER COMMENT '成绩所属科目',  |
|  |  PRIMARY KEY (sid),  |
|  |  FOREIGN KEY(student\_id) REFERENCES student (sid),  |
|  |  FOREIGN KEY(course\_id) REFERENCES course (cid) |
|  | ) |
|  |   |
|  | """ |


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84

Base.metadata是MetaData对象, 常用的MetaData方法见: MetaData

注: 你通过Student.__table__属性可以查看Table, 也可以通过Student.name访问某一列
你也可以通过__init__显示定义某些列

增删改查数据

插入数据

接上文 “在ORM中创建表” 中的表

1.x的接口与2.0的接口一样, 都是调用session.add(instance)方法添加到数据库 (add方法下次刷新操作时, 将instance保存到数据库)
注意: 自动生成的数据, 在未插入到数据库之前, 都为None, 如: 自动生成的主键

你也可以调用add_all(instance1, instance2, ...)方法, 区别只是插入一条和多条数据而已