SQLAlchemy core
目录
模式和类型
1 类型
- 通用类型
- SQL标准类型
- 厂商自定义类型
- 用户定义类型
2 元数据
元数据，元数据用来把数据库结构结合在一起，以便在SQLAlchemy中快速访问它。一般可以把元数据看作一种table对象目录，其中包含与引擎和连接有关的信息。
这些表通过字典MetaData.tables来访问
from sqlalchemy import MetaData
metadata = MetaData()
#
3 表
3.1 列
#
from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey
from datetime import datetime
from sqlalchemy import DateTime
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True), # 主键
Column('cookie_name', String(50), index=True), # 索引
Column('cookie_recipe_url', String(255), unique=True), # 唯一
Column('email_address', String(255), nullable=False), # 非空
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)), # 长度和精度，长度11位，精度为2位
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now) # 1、如果未指定日期，把当前日期设置为列的默认值；2、通过onupdate，使得每次更新记录时，都把当前时间设置给当前列。
)
3.2 键和约束
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint
PrimaryKeyConstraint('user_id', name='user_pk') # 主键
UniqueConstraint('username', name='uix_username') # 约束
CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive') # 检查约束
3.3 索引
# 显示创建索引
from sqlalchemy import Index
Index('ix_cookies_cookie_name', 'cookie_name')
# 创建函数索引
Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name))
3.4 关联关系和外键约束
from sqlalchemy import ForeignKey
orders = Table('orders', metadata,
Column('order_id', Integer(), primary_key=True),
Column('user_id', ForeignKey('users.user_id')), ➊
Column('shipped', Boolean(), default=False)
)
4 表的持久化
metadata.create_all(engine)
from sqlalchemy import create_engine
# engine = create_engine("mysql+pymysql://【此处填用户名】:【此处填密码】@【此处填host】:【此处填port】/【此处填数据库的名称】?charset=utf8")
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/core?charset=utf8')
metadata.create_all(engine)
5 创建表
from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey, Boolean, create_engine)
metadata = MetaData() # 元数据，元数据用来把数据库结构结合在一起，以便在SQLAlchemy中快速访问他
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2))
)
users = Table('users', metadata,
Column('user_id', Integer(), primary_key=True),
Column('username', String(15), 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),
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('shipped', Boolean(), default=False)
)
line_items = Table('line_items', metadata,
Column('line_items_id', Integer(), primary_key=True),
Column('order_id', ForeignKey('orders.order_id')),
Column('cookie_id', ForeignKey('cookies.cookie_id')),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2))
)
engine = create_engine('mysql+pymysql://root:123@127.0.0.1:3306/core?charset=utf8') # 初始化引擎
metadata.create_all(engine) # 创建库
connection = engine.connect() # 实际打开数据库连接
SQLAlchemy core 处理数据
1 插入数据 insert()
# 方法1 insert作为table对象的实例方法
ins = cookies.insert().values(
cookie_name="chocolate chip",
cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
cookie_sku="CC01",
quantity="12",
unit_cost="0.50"
)
ins.compile().params #compile()方法返回一个SQLCompiler对象，该对象允许我们通过params属性访问随查询一起发送的实际参数
result = connection.execute(ins) # 把语句发送到数据库
result.inserted_primary_key # 获得刚才插入的记录的ID
# 方法2 insert作为独立的函数
from sqlalchemy import insert
ins = insert(cookies).values(
cookie_name="chocolate chip",
cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
cookie_sku="CC01",
quantity="12",
unit_cost="0.50"
)
# 方法3 execute接收关键字参数
ins = cookies.insert()
result = connection.execute(ins, cookie_name='dark chocolate chip',
cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html',
cookie_sku='CC02',
quantity='1',
unit_cost='0.75')
# 方法4 插入多个值
inventory_list = [
{
'cookie_name': 'peanut butter',
'cookie_recipe_url': 'http://some.aweso.me/cookie/peanut.html',
'cookie_sku': 'PB01',
'quantity': '24',
'unit_cost': '0.25'
},
{
'cookie_name': 'oatmeal raisin',
'cookie_recipe_url': 'http://some.okay.me/cookie/raisin.html',
'cookie_sku': 'EWW01',
'quantity': '100',
'unit_cost': '1.00'
}
]
result = connection.execute(ins, inventory_list)
2 查询数据 select
# 1 查询所有
## 方法1
from sqlalchemy.sql import select
s = select([cookies])
rp = connection.execute(s)
results = rp.fetchall()
## 方法2
from sqlalchemy.sql import select
s = cookies.select()
rp = connection.execute(s)
results = rp.fetchall()
# ResultProxy
first_row = results[0] # 返回第一行
print(first_row[1]) # 通过索引访问列
print(first_row.cookie_name) # 通过名称访问列
print(first_row[cookies.c.cookie_name]) # 通过column对象访问列
# 2 控制查询中的列数
s = select([cookies.c.cookie_name, cookies.c.quantity])
rp = connection.execute(s)
print(rp.keys())
result = rp.first() # 获取第一个值
# 3 排序
## 方法1
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity, cookies.c.cookie_name)
rp = connection.execute(s)
for cookie in rp:
print('{} - {}'.format(cookie.quantity, cookie.cookie_name))
## 方法2
s = select([cookies.c.cookie_name, cookies.c.quantity]).order_by(cookies.c.quantity, cookies.c.cookie_name)
rp = connection.execute(s)
for cookie in rp:
print('{} - {}'.format(cookie.quantity, cookie.cookie_name))
## 降序 desc()函数
s = s.order_by(desc(cookies.c.quantity))
# 4 限制返回结果集的条数
s = select([cookies.c.cookie_name, cookies.c.quantity])
s = s.order_by(cookies.c.quantity)
s = s.limit(2)
rp = connection.execute(s)
print([result.cookie_name for result in rp])
# 5 内置SQL函数及标签 sum()、count()
from sqlalchemy.sql import func
s = select([func.sum(cookies.c.quantity)])
rp = connection.execute(s)
print(rp.scalar()) # 只返回第一个记录最左边的列
注意：直接导入sum可能会引起问题，且易和python内置sum混淆
s = select([func.count(cookies.c.cookie_name).label('inventory_count')]) # label显示别名
rp = connection.execute(s)
record = rp.first()
print(record.keys()) # 显示resultProxy中的列
print(record.inventory_count) # 自动生成列名
# 6 过滤
## 方法1，完全匹配
s = select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
rp = connection.execute(s)
record = rp.first()
print(record.items())
## 方法2 模糊匹配
s = select([cookies]).where(cookies.c.cookie_name.like('%o%'))
rp = connection.execute(s)
for record in rp.fetchall():
print(record.cookie_name)
最佳实践
- 获取单条记录，要多用first方法，尽量不要使用fetchone和scalar方法
- 尽量使用可迭代的resultProxy，不要用fetchall金额fetchone方法，因为前者效率更高
- 避免使用fetchone，如不小心，它会一直让连接处于打开状态
- 谨慎使用scalar方法，因为如果查询返回多行多列，就会引发错误
ClauseElement方法：
| 方法 | 用途 |
|---|---|
| between(cleft,cright) | 查找在cleft和cright之间的列 |
| concat(column_two) | 连接列 |
| distinct() | 查找列的唯一值 |
| in_([list]) | 查找列在列表中的位置 |
| is_(none) | 查找none位置(通常用于检查none和null) |
| contains(string) | 查找包含string的列（区分大小写） |
| endswith(string) | 查找以string结尾的列（区分大小写） |
| like(string) | 查找与string匹配的列（区分大小写） |
| startswith() | 查找以string开头的列（区分大小写） |
| ilike(string) | 查找与string匹配的列（不区分大小写） |
连接词
and_()、or_()、not_()
3 更新数据 update
from sqlalchemy import update
u = update(cookies).where(cookies.c.cookie_name == "chocolate chip")
u = u.values(quantity=(cookies.c.quantity + 120)) # 生成方法构建语句
result = connection.execute(u)
print(result.rowcount) # 打印更新行数
s = select([cookies]).where(cookies.c.cookie_name == "chocolate chip")
result = connection.execute(s).first()
for key in result.keys():
print('{:>20}: {}'.format(key, result[key]))
4 删除数据
from sqlalchemy import delete
u = delete(cookies).where(cookies.c.cookie_name == "chocolate chip")
result = connection.execute(u)
print(result.rowcount)
s = select([cookies]).where(cookies.c.cookie_name == "chocolate chip")
result = connection.execute(s).fetchall()
print(len(result))
5 连接
# 使用连接查询多个表
columns = [orders.c.order_id, users.c.username, users.c.phone,cookies.c.cookie_name, line_items.c.quantity,
line_items.c.extended_cost]
cookiemon_orders = select(columns)
cookiemon_orders = cookiemon_orders.select_from(orders.join(users).join( line_items).join(cookies)).where(users.c.username ==
'cookiemon')
result = connection.execute(cookiemon_orders).fetchall()
for row in result:
print(row)
===
SELECT orders.order_id, users.username, users.phone, cookies.cookie_name, line_items.quantity, line_items.extended_cost FROM orders JOIN users ON users.user_id = orders.user_id JOIN line_items ON orders.order_id = line_items.order_id JOIN cookies ON cookies.cookie_id = line_items.cookie_id where users.username='cookiemon';
# 使用外连接查询多个表
columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)
result = connection.execute(all_orders).fetchall()
for row in result:
print(row)
6 别名
>>> manager = employee_table.alias('mgr') # 不写自动选择别名，避免出现名称冲突
>>> stmt = select([employee_table.c.name],
... and_(employee_table.c.manager_id==manager.c.id,
... manager.c.name=='Fred'))
>>> print(stmt)
SELECT employee.name
FROM employee, employee AS mgr
WHERE employee.manager_id = mgr.id AND mgr.name = ?
7 分组
columns = [users.c.username, func.count(orders.c.order_id)]
all_orders = select(columns)
all_orders = all_orders.select_from(users.outerjoin(orders))
all_orders = all_orders.group_by(users.c.username)
result = connection.execute(all_orders).fetchall()
for row in result:
print(row)
8 链式调用(函数)
def get_orders_by_customer(cust_name):
columns = [orders.c.order_id, users.c.username, users.c.phone,cookies.c.cookie_name, line_items.c.quantity,line_items.c.extended_cost]
cust_orders = select(columns)
cust_orders = cust_orders.select_from(users.join(orders).join(line_items).join(cookies))
cust_orders = cust_orders.where(users.c.username == cust_name)
result = connection.execute(cust_orders).fetchall()
return result
get_orders_by_customer('cakeeater')
# 带条件的链式调用
def get_orders_by_customer(cust_name, shipped=None, details=False):
columns = [orders.c.order_id, users.c.username, users.c.phone]
joins = users.join(orders)
if details:
columns.extend([cookies.c.cookie_name,line_items.c.quantity,line_items.c.extended_cost])
joins = joins.join(line_items).join(cookies)
cust_orders = select(columns)
cust_orders = cust_orders.select_from(joins)
cust_orders = cust_orders.where(users.c.username == cust_name)
if shipped is not None:
cust_orders = cust_orders.where(orders.c.shipped == shipped)
result = connection.execute(cust_orders).fetchall()
return result
get_orders_by_customer('cakeeater')
get_orders_by_customer('cakeeater', details=True)
get_orders_by_customer('cakeeater', shipped=True)
get_orders_by_customer('cakeeater', shipped=False)
get_orders_by_customer('cakeeater', shipped=False, details=True)
9 原始查询
可以直接使用原始sql
# 完全原始查询
res = connection.execute("select * from users").fetchall()
print(res)
# 部分文本查询
from sqlalchemy import text
res = select([users]).where(text("username='cookiemon'"))
r = connection.execute(res).fetchall()
print(r)
异常和事务
1 异常
1.1 attribute
当试图访问一个不存在的属性时，就会出现AttributeError错误
1.2 IntegrityError
当试图做一些违反列约束或表约束的事情时，就会出现这个错误。
这种错误通常出现在你的操作破坏了唯一性约束的情况
1.3 处理错误
IntegrityError 错误的处理方法， 但是这种处理方法对
SQLAlchemy 产生的各种类型的错误都有效
from sqlalchemy.exc import IntegrityError ➊
ins = insert(users).values(username="cookiemon",email_addre="damon@cookie.com",phone="111-111-1111",password="password")
try:
result =connection.execute(ins)
except IntegrityError as error: ➋
print(error.orig.message, error.params)
浙公网安备 33010602011771号