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)
检测sqlalchemy
版本:
[fast →](https://blog.csdn.net/biggbang)
使用步骤
一般来说SQLAlchemy
的使用方式有两种: Core
和ORM
两种有什么不同呢?
ORM
是构建在Core
之上的Core
更加底层, 可以执行直接执行SQL语句ORM
类似于Django的ORM, 由于sqlalchemy提供了一套接口, 所以不需要我们直接写SQL语句 (1.x版本)- 至于要用哪个, 等到你用到时, 你会知道的
Core
一般来说, 使用步骤如下:
- 配置数据库连接
- 建立连接
- 创建表
- 执行SQL语句, 按需开启事件是否自动提交
- 拿到返回数据, 执行其他代码
数据库的连接的格式
我们在创建引擎(连接)时, 需要指定数据库的URL, URL格式, 见: Engine Configuration, 总的来说, 格式就是: dialect[+driver]://user:password@host/dbname[?key=value..]
dialect
数据库名称(方言): 如mysqldriver
连接数据库的库: 如: pymysqluser
用户名password
密码host
地址dbname
数据库名称key=value
指的是给数据库的参数
如下面的URL:
| | mysql+pymysql://root:passwd@127.0.0.1:3306/test\_db?charset=utf8 |
建立连接
调用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)) |
| | # |
注意, 由于
sqlalchemy
使用lazy initialization
的策略连接数据库, 故此时还未真正地连接上数据库
创建表
我们想要让数据库创建一个表, 需要利用MetaData
对象, 关于一些常用的MetaData
方法, 见: MetaData
除了要MetaData
对象外, 我们还需要Table
对象, 用于定义一个表的结构
Table
的一般使用
| | mytable = Table("mytable", metadata, |
| | Column('mytable\_id', Integer, primary\_key=True), |
| | Column('value', String(50)) |
| | ) |
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) |
| | ) |
| | """ |
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)) |
在事务中执行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 |
上面的代码中, 相当于开启了事务, 由于最后没有调用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 |
绑定参数
上面在事务中执行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 |
| | |
多个参数时, 可以这样
| | 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() |
这种方式也可以
| | 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() |
增删改查
处理使用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)
插入数据
使用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() |
| | |
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; |
| | +-----+----------+------+-------------------+ |
| | | uid | username | aid | email\_address | |
| | +-----+----------+------+-------------------+ |
| | | 1 | lczmx | 1 | lczmx@foxmail.com | |
| | | 2 | 张三 | NULL | NULL | |
| | | 3 | 李四 | NULL | NULL | |
| | | 4 | 王五 | NULL | NULL | |
| | | 5 | 赵六 | NULL | NULL | |
| | +-----+----------+------+-------------------+ |
| | |
例子:
| | 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() |
| | |
.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; |
| | |
| | +-----+----------+------+-------------------+ |
| | | uid | username | aid | email\_address | |
| | +-----+----------+------+-------------------+ |
| | | 1 | lczmx | 1 | lczmx@foxmail.com | |
| | | 2 | 张三 | NULL | NULL | |
| | | 3 | 李四 | NULL | NULL | |
| | | 4 | 王五 | NULL | NULL | |
| | | 5 | 赵六 | NULL | NULL | |
| | +-----+----------+------+-------------------+ |
| | |
例子:
| | 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() |
| | |
修改后的结果:
| | +-----+----------+------+-------------------+ |
| | | uid | username | aid | email\_address | |
| | +-----+----------+------+-------------------+ |
| | | 1 | lczmx | 1 | lczmx@foxmail.com | |
| | | 2 | 新张三 | NULL | NULL | |
| | | 3 | 新李四 | NULL | NULL | |
| | | 4 | 王老五 | NULL | NULL | |
| | | 5 | 赵六一号 | NULL | NULL | |
| | +-----+----------+------+-------------------+ |
| | |
更多见: 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> |
| | |
利用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() |
| | |
ORM
和Core一样, ORM也有一定的使用步骤:
- 配置数据库连接, 见上文: 数据库的连接的格式
- 创建会话
- 创建表
- 使用接口, 增删改查数据
- 拿到返回数据, 执行其他代码
在学习SQLAlcehmy的ORM之前, 建议先了解一些概念, 以免后面会混淆
- 会话
Session
会话是SQLAlchemy ORM与数据库的交互对象
它可以管理建立连接中engine
, 并为通过会话加载或与会话关联的对象提供标识映射 (identity map
)
在使用时与Connection
非常相似, 你可以对比着使用 Base
通过sqlalchemy.orm.declarative_base
创建
作为定义表的基类, 内部有包含MetaData
对象
可以类似于Django
一样定义表
在SQLAlchemy
中, session
是一个连接池, 的由其管理, 因此, 假如我们需要操作数据库的话, 需要在session
中拿到Connection
(连接)
创建会话
SQLAlchemy
提供了两种创建会话的方法:
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 |
| | |
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 |
| | |
虽然有两种方法创建会话, 但我们一般使用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 |
下面列出session
的一些常用方法, 增删改查数据时要用到
方法 | 参数 | 描述 |
---|---|---|
add |
instance |
下次刷新操作时, 将 instance 保留到数据库中 |
delete |
instance |
下次刷新操作时, 将instance 从数据库中删除 |
begin |
subtransactions nested _subtrans |
开始事务 |
rollback |
无 | 回滚当前事务 |
commit |
无 | 提交当前事务 |
close |
无 | 关闭此Session |
execute |
statement params execution_option bind_arguments 等 |
执行SQL表达式构造 |
query |
*entities **kwargs |
返回Query 对象, 可用于查询数据 |
refresh |
instance attribute_names with_for_update |
为instance 执行刷新操作 |
例子:
| | 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 |
| | |
在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() |
方法二:
| | from sqlalchemy.orm import declarative\_base |
| | |
| | # 内部 return registry(...).generate\_base(...) |
| | Base = declarative\_base() |
| | |
现在你可以像在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) |
| | ) |
| | |
| | """ |
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, ...)
方法, 区别只是插入一条和多条数据而已