SQLAlchemy 使用笔记 （一）Core模式

1、连接数据库

1.1 为 SQLite创建链接引擎

from sqlalchemy import create_engine

# 连接相对路径的 name.db 数据库，future=True参数可以充分利用sqlalchemy 2.0版本的性能。
engine = create_engine('sqlite+pysqlite:///name.db', future=True)    # 使用pysqlite接口来连接sqlite
engine = create_engine('sqlite:///name.db')				             # 使用默认的接口。

# 连接window系统中绝对路径下的 name.db 数据库
engine = create_engine('sqlite:///c:\\dbdir\\name.db')

#  连接Unix系统中绝对路径下的 name.db 数据库
engine = create_engine('sqlite:home/cookiemonster/cookies.db')

# 连接内存中的数据库
engine = create_engine('sqlite:///:memory:')

1.2 PostgreSQL 数据库创建引擎

# 远程链接
engine = create_engine('postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]')

# 本地链接
engine = create_engine('postgresql+psycopg2://user:password@localhost:port/dbname[?key=value&key=value...]')

1.3 MySQL数据库创建引擎

engine = create_engine('mysql+pymysql://user:password@host[:port]/dbname')

1.4 Sql Server数据库连接

engine = create_engine("mssql+pyodbc://user:password@host[:port]/dbname?driver=ODBC Driver 17 for SQL Server")
# 注：需 pip 安装 pyodbc 库，再安装 odbc 驱动
# 驱动地址：https://docs.microsoft.com/zh-cn/sql/connect/odbc/microsoft-odbc-driver-for-sql-server

import pyodbc

pyodbc.drivers()  # 查看当前系统中可用的odbc驱动。

create_engine 还可添加参数：

echo = True	开启这个参数会记录引擎处理的操作，例如 SQL 语句及其参数。默认值为 false。
encoding	用于指定 SQLAlchemy 所使用的字符串编码方式，默认值为 utf-8 。
isolation_level	这个参数用来为 SQLAlchemy 指定隔离级别。 例如：使用 psycopg2的PostgreSQL 有 READ COMMITTED，READ UNCOMMITTED，REPEATABLE READ，SERIALIZABLE和AUTOCOMMIT选项，默认值为 READ COMMITTED。PyMySQL 也有同样的选项，InnoDB 数据库来说，默认值为 REPEATABLE READ
pool_recycle	这个参数会定期回收数据库连接或者让数据库连接超时。 如：pool_recycle=3600 默认情况下，mysql超过8小时，才会关闭空闲链接。

1.5 创建引擎之后，连接数据库

connection = engine.connect()

注：connection.close() 关闭链接。

2、数据类型

2.1 通用类型及对应关系

类型名	python中类型	SQL	说明
Integer	int	INTEGER	普通整数，一般是32位
SmallInteger	int
BigInteger	int或long	BIGINT
Float	float	Float 或 REAL	浮点数
Numeric	decimal.Decimal	NUMERIC或DECIMAL	精确浮点数
String	str		可变长字符串
Text	str	CLOB或TEXT	可变长字符串
Unicode	Unicode	UNICODE或VARCHAR	变长Unicode字符串
UnicodeText	unicode		变长Unicode字符串，对较长或不限长度的字符串做了优化
Boolean	bool	BOOLEAN或SAMLLINT	布尔值
Date	datetime.date	DATE(sqlite:STRING)	日期
Time	datetime.time	DATETIME	时间
DateTime	datetime.datetime	DATETIME	时间日期
LargeBinary	byte	BLOB或BYTEA	二进制文件

《参考引用处》
SQL 标准类型的行为和可用性因数据库而异。 SQL 标准类型在 sqlalchemy.types 模块中是可用的。为了将 SQL 标准类型和通用类型区分开，标准类型全部采用大字母。
厂商自定义类型和 SQL 标准类型一样有用，但是它们只适用于特定的后端数据库。可以通过所选方言的文档或 SQLALchemy 站点确定有哪些类型可用。它们在 sqlalchemy.dialects 模块中都是可用的，并且每种数据库方言都有若下子模块。同样，这些类型采用的全是大写字母，以便同通用类型区分开。

2.2 元数据

元数据是用来把数据库结构结合在一起，以便在 SQLAlchemy 中快速访问它。一般可以把元数据看作一种 Table 对象目录，其中包含与引擎和连接有关的信息。这些表可以通过字典MetaData.tables 来访问。读操作是线程安全的，但是表的创建并不是完全线程安全的。在把对象绑定到元数据之前，需要先导入并初始化元数据。
如下：初始化 MetaData 对象的个实例

from sqlalchemy import  MetaData

metadata = MetaData()

2.3 定义新表

通过调用 Table 构造函数来初始化 Table 对象。我们要在构造函数中提供 MetaData 象（元数据）和表名，任何其他参数都被认为是列对象。此外，还有一些额外的关键字参数，它们用来开启相关特性。列对象对应于数据表中的各个字段。列是通过调用 Column() 函数创建的，我们需要为这个函数提供 列名、类型，以及其他表示 SQL 结构和约束的参数。
如下表所示：

from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer, Numeric, DateTime, ForeignKey

metadata = MetaData()

# 定义表
users = Table('users', metadata,                
              Column('user_id', Integer, primary_key=True),
              Column('unit_cost', Numeric(12, 2)),    # 长度为12位，精度为2为的浮点数。
              
              # index=True 索引列，以加快该列的查询速度。
              # nullable=False 不可为空。
              # unique=True 值唯一。
              Column('username', String(15), index=True, nullable=False, unique=True),
              
              Column('email_address', String(255), nullable=False),
              Column('phone', String(20), nullable=False),
              Column('password', String(25), nullable=False),
              
              # 如果未指定日期，就把当前时间设置为列的默认值。
              Column('created_on', DateTime, default=datetime.now),      # 注 不是 datetime.now（）
              
              # onupdate=datetime.now 每次记录更新时，都设置为当前时间。
              Column('updated_on', DateTime, default=datetime.now, onupdate=datetime.now)
              )
              
# 外键关联表
orders = Table('orders', metadata,
               Column('order_id', Integer, primary_key=True),
               Column('user_id', ForeignKey('users.user_id')),       # 添加外键。们使用的是 一个字符串，而不是对列的实际引用。
               )

注：当使用一个现有数据库时，可以在 Column 对象之外定义表结构和约束。这一点非常重要，因为你必须告诉 SQLAlchemy 数据库中的模式、结构和约束。例如，如果数据库中的已有索引与 SQLAlchemy 使用的默认索引命名方案不匹配，那么你必须手动定义该索引。

2.4 新建生成表（表已存在，则忽略此步骤）

所有表和模式定义都与 Metadata 的实例有关，只需调用 Metadata 实例的 create_all() 方法，并提供创建表的引擎即可创建表：

metadata.create_all(engine)

默认情况下， create_all 不会尝试重新创建数据库中已经存在的表，并且它可以安全地运行多次。如果有修改了 定义表的字段，比如有新增和删除某个Column ，再使用metadata.create_all(engine)也不会对原来已生成的表有影响。

2.5 连接数据库中已有的表

使用 autoload=True, autoload_with=engine 这两个关键字参数。这会把模式信息反射到 metadata 对象中。

from sqlalchemy import create_engine, MetaData, Table, inspect

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

# 查询数据库中已存在的数据表
inspecter = inspect(engine)
print(inspecter.get_table_names())

connection = engine.connect()

metadata = MetaData()
users = Table('users', metadata, autoload=True, autoload_with=engine)    # 把已有的表映射到 Table 对象。

querys = select(users)

res = connection.execute(querys).fetchall()
# res = engine.execute(querys).fetchall()       # 也可以直接用engine对象执行查询语句。

for i in res:
    print(i)

查看表信息，及删除表。

print(metadata.tables['users'].to_metadata)            # to_metadata 属性查看表的信息
print(users.foreign_keys)				   			   #  查看具体某个信息，如：外键信息，返回 set() 类型。

# metadata.tables['users'].drop()                      # 删除表。

使用 Metadata 对象的 reflect 方法获取数据库中所有的表。

metadata.reflect(engine)
print(metadata.tables.keys())             # >>> dict_keys(['users', 'items', 'orders'])

如果映射到Table对象后，用 to_metadata 查看映射后表的信息，发现有缺漏。
比如 外键没被映射过来，可以用Table的append_constraint方法后续手动绑定：

from sqlalchemy import ForeignKeyConstraint

users = Table('users', metadata, autoload=True, autoload_with=engine)

# 'ArtistId' 为 users 表中的字段， 'artist.ArtistId'为artist表中的ArtistId字段。
users.append_constraint(ForeignKeyConstraint(['ArtistId'], ['artist.ArtistId']))

3. 增删改查

3.1 插入数据

方法1： 调用 表 的 insert() 方法：
在 users 表中插入一条数据

connection = engine.connect()

metadata.create_all(engine)

# 生成 <class 'sqlalchemy.sql.dml.Insert'> 语句。 可用print(ins)查看。
ins = users.insert().values(unit_cost=12.123,
                            username="abcdefg",
                            email_address='qq@com',
                            phone=13675892519,
                            password=123456
                            )

# 调用 execute()方法，执行语句，无需再调用 commit() 方法。
connection.execute(ins)

# 用了sqlite3数据库，还是得调用commit()方法，才能提交到数据库中
# connection.commit()

方法2： 使用独立的 insert() 方法插入一条数据（推荐使用）：

from sqlalchemy import insert

ins = insert(users).values(unit_cost=123.321,
                           username="Abcdefg",
                           email_address='qq@com',
                           phone=13675892519,
                           password=123456
                           )

result = connection.execute(ins)

print(result.inserted_primary_key)     # 查看插入数据的 pk 值。

插入多条数据：

# 先构建一个列表，存放插入的条目，条目为dict类型。
values_list = [{"unit_cost": 123.321,                  # 第一条
                "username": "哈哈",
                "email_address": 'qq@com',
                "phone": 13675892519,
                "password": 123456},

                {"unit_cost": 123.321,                 # 第二条
                "username": "呵呵",
                "email_address": 'qq@com',
                "phone": 13675892519,
                "password": 123456}
               ]

ins = insert(users).values(values_list)
connection.execute(ins)

# 下面方法也可行，用上面方法比较好。
ins = insert(users)
connection.execute(ins, values_list)

3.2 查询数据

from sqlalchemy import select

# 从 users 表中查询所有字段的所有数据。
s = select(users)
print(s)

rp = connection.execute(s)
result = rp.fetchall()
print(result)

# 只查询 users 表中 user_id 和 username 两个字段的数据。
s = select([users.c.user_id, users.c.username])
# s = select(users.c.user_id, users.c.username)     # 也可以不用列表。
print(s)

rp = connection.execute(s)
# result = rp.first()            # 获取查询内容的第一条数据。并且result关闭。

# fetchone 方法获取查询内容的第一条数据。
# 并保持result为打开状态，以便你做更多获取调用。即可以继续使用fetchone()获取下一条数据。
# 所以避免使用 fetchone 方法，因为如果不小心，它会一直让连接处于打开状态。
result = rp.fetchone()         

result = rp.fetchall()           # 获取所有查询的内容，是可迭代对象 >> 列表。
print(result)

first_row = result[0]          # 读取查询内容中的第一条数据。
print(first_row)
print(first_row.keys())         # 查看一行数据中的字段名。
print(first_row[2])             # 读取第一条数据中的第三个字段的内容，也就是 username 内容，下面两条代码效果相同。
print(first_row.username)       # 用属性来读取字段值。
print(first_row[users.c.username])   # 用字段的方式来读取字段值。

同插入数据，也可以使用Table 对象的 select 方法来实现查询：

s = users.select()
rp = connection.execute(s)
result = rp.fetchall()

result 为包含查询结果的列表，列表的元素为 <class ‘sqlalchemy.engine.row.LegacyRow’> 类型。

order_by 查询并排序：

from sqlalchemy import select, desc

# 按 username 字段进行升序排序。
s = select(users).order_by('username')
# s = select(users).order_by(users.c.username)    # 和上句代码效果相同。
rp = connection.execute(s)

# 利用 desc方法，按 username 字段进行降序排序。
s = select(users).order_by(desc(users.c.username))
# s = select(users).order_by(users.c.username.desc())      # 也可以用列方法中的 desc() 方法。
rp = connection.execute(s)

limit() 限制查询条数：
使用 first()和 fetchone() 方法仅获取一行。虽然 ResultPoxy提供了我们请求的那行，但查询实际运行时会访问所有结果，而不仅仅是单个记录。如果想对查询进行限制，可以使用limit() 函数让 limit 语句成为查询的一部分。

s = users.select().limit(3)              # 限制只查询3条数据
# s = select(users).limit(3)

rp = connection.execute(s)
result = rp.fetchall()

for i in result:
    print(i)

cast类型转换

from sqlalchemy import cast, Numeric

stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))

该语句会同下SQL语句

SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product

这个 cast() 函数在使用时执行两个不同的函数。首先， CAST将代码转为SQL字符串中的表达式。然后，它将给定类型（例如 TypeEngine 类或实例），这意味着表达式将采用与该类型关联的表达式运算符行为，以及该类型的绑定值处理和结果行处理行为。

内置SQL函数和标签

SQLAlchemy 还可以利用后端数据库中的 SQL 函数。两个非常常用的数据库函数是 SUM() 和 COUNT() 。要使用这两个函数，需要导入 sqlalchef’ly.sql.func 模块。

from sqlalchemy.sql import func

# 对某列数据 求和
s_sum = select(func.sum(users.c.unit_cost))      # 对users表中，unit_cost字段求和
rp_sum = connection.execute(s_sum)
print(rp_sum.scalar())                           # 这里使用了 scalar(), 它只返回第一个记录最左边的列的值。

# 对某列数据进行 计数
s_count = select(func.count(users.c.phone))
rp_count = connection.execute(s_count)
# print(rp_count.scalar())
record = rp_count.first()       # 通过sum 和 count 函数读取的，都只有一条数据，所以用first或者scalar就可以了。
print(record.keys())            # >>> RMKeyView(['count_1'])
print(record.count_1)

自动生成列名，一般格式为： funcName_position ，如上的 count_1。可以通过SQLAlchemy 提供label() 函数进行自定义查询后的列名。

s_count = select(func.count(users.c.phone).label("phone_count"))
rp_count = connection.execute(s_count)

record = rp_count.first()
print(record)
print(record.keys())            # >>> RMKeyView(['phone_count'])
print(record.phone_count)

过滤查询

where() 方法包含1个列、 1个运箕符和 1个值或列。还可以把多个 where() 子句接在一起使用，也可以把多个查询条件写在一个where里，功能就像传统SQL 语句中的 AND 样。

s = select(users).where(users.c.username == "abcdefg")
conn = connection.execute(s)
result = conn.fetchall()

# 加上like()方法，查询包含 “cdefg”的。
s = select(users).where(users.c.username.like("%cdefg%"))
conn = connection.execute(s)
result = conn.fetchall()

其中的like称为ClauseElement，ClauseElement拥有许多额外的功能，如下表所示：

方法	用途
like(string)	查找与string匹配的列，区分大小写。
not_like()
ilike(string)	查找与string匹配的列，不区分大小写。
not_ilike()
contains(string)	查找包含string的数据。
between(cleft, cright, symmetric=False)	查找在 cleft和 cright 之间的列。
concat(other_column)	组合两个列的值。如： select(users.c.username.concat(users.c.phone)).where(users.c.username.like("%cdefg%")) 返回的结果为username列和phone的组合。
in_([list])	查找在list中的数据。
not_in([list])	查找不在list中的数据。
is_(None)	查找为None的数据
is_not()
endswith(string)	以 string结尾的
startswith(string)	以 string开始的
match(other, **kwargs)
regexp_match(pattern, flags=None)
regexp_replace(pattern, replacement, flags=None)
desc()
asc()
nulls_first()
nulls_last()
collate(collation)
distinct()
any_()
all_()

and 、 or 、not 连接词

from sqlalchemy import and_ , or_ , not_

s = select(users).where(and_(users.c.user_id > 3, users.c.user_id < 6))         # id 大于3且小于6的
# s = select(users).where(users.c.user_id > 3, users.c.user_id < 6)                # 等同
conn = connection.execute(s)

result = conn.fetchall()
for i in result:
    print(i)

运算符

SQLAlchemy 对大多数标准 Python 运算符做了重载，包括所有标准的比较运算符（＝＝、！＝、＜、＞、＜＝、＞＝），它们的功能和在 Python 语句中完全 样。在与 None 比较时，＝＝运算符被重载为 IS NULL 语句。算术运算符(+、-、*、 和％）还可以用来对独立于数据库的字符串做连接处理。

连接字符串用：

s = select("姓名：" + users.c.username)
conn = connection.execute(s)

result = conn.fetchall()
for i in result:
    print(i)

返回时 会拼接 “姓名：” 和 users.c.username ，如下：

('姓名：username1',)            # username1 为 users 表中 username 存储的值。
('姓名：username2',)
……

运算用：

# 对users.c.unit_cost字段的值都乘以2.
s = select("姓名：" + users.c.username, users.c.unit_cost*2)
conn = connection.execute(s)

result = conn.fetchall()
for i in result:
    print(i)

3.3 更新数据

update() 方法和前面用过的 insert() 方法很相似，它们的语法几乎完全 样，但是update() 可以指定 where 子句，用来指出要更新哪些行。与插入语句 样，更新语句可以由 update() 函数或者表格的 update() 方法来创建。如果省略 where 子句，则表示要
更新表中的所有行。

方法1、调用 表的 update() 方法

# 更新users.c.user_id == 8这行数据，phone字段的值为12345
upda = users.update().where(users.c.user_id == 8).values(phone=12345)
result = connection.execute(upda)
print(result.rowcount)    # 打印更新的结果。

方法2、导入 update() 方法

from sqlalchemy import update

upda = update(users).where(users.c.user_id == 8).values(phone=7891011)
result = connection.execute(upda)

3.4 删除数据

创建删除语句时，既可以使用 delete() 函数，也可以使用表（包含待删数据的表）的delete() 方法。与 insert()和update() 不同， delete() 不接收值参数，只接收1个可选的where 子句，用来指定删除范围（省略 where 子句表示删除表中的所有行。

方法1、调用 表的 delete() 方法

de = users.delete().where(users.c.user_id == 8)
result = connection.execute(de)
print(result.rowcount)

方法2、导入 delete()方法

from sqlalchemy import  delete

de = delete(users).where(users.c.user_id == 7)
result = connection.execute(de)

4、外键表关联查询

如：
username字段在users表中。payed_or_not字段在orders表中，orders有外键关联到users表。
可用 select_from 和join 进行关联查询。

columns = [users.c.username, orders.c.payed_or_not]
querys = select(columns).select_from(orders.join(users))
res = connection.execute(querys).fetchall()

5、分组查询

如下：按姓名进行分组，用func.count进行数量统计。

columns = [users.c.username, func.count(orders.c.payed_or_not)]

xx = items.alias("明细")
querys = select(columns).select_from(orders.join(users)).group_by(users.c.username)
res = connection.execute(querys).fetchall()

6、异常处理

sqlalchemy的异常类型在sqlalchemy.exc中，

导入可以如下：

from sqlalchemy.exc import IntegrityError  

IntegrityError ：当试图做一些违反列约束或表约束的事情时，就会发生这种错误。这种类型的错误通常出现在操作破坏了唯一性约束的情况下。

7、事务

启动事务时，数据库系统会先记录数据库的当前状态，然后再执行多个 SQL句。
如果事务中的所有 SQL 语句都成功执行，那么数据库将继续正常运行，并丢弃之前的数据库状态。
只要事务中有 个或多个语句执行失败，我们就会捕获错误，并回滚到先前的状态。

事务是通过调用连接对象的 begin() 方法启动的。调用 begin() 方法会返回一个事务对象，
我们可以用它来控制所有语句的结果。如果所有语句都成功执行，就调用事务对象的
commit() 方法来提交事务。否则，就调用事务对象的 rollback() 方法进行回滚操作。

from sqlalchemy.exc import IntegrityError


s = select([line_items.c.cookie_id, line_items.c.quantity])
s = s.where(line_items.c.order_id == order_id)

transaction = connection.begin()                 # 启动事务对象

cookies_to_ship = connection.execute(s).fetchall()
try:
    for cookie in cookies_to_ship:
        u = update(cookies).where(cookies.c.cookie_id == cookie.cookie_id)
        u = u.values(quantity = cookies.c.quantity-cookie.quantity)
        result = connection.execute(u)
    u = update(orders).where(orders.c.order_id == order_id)
    u = u.values(shipped=True)
    result = connection.execute(u)
    print("Shipped order ID: {}".format(order_id))
    transaction.commit()
    
except IntegrityError as error:
    transaction.rollback()               # 操作失败，回滚到执行之前的状态。
    print(error)