SqlAlchemy的使用

SQLAlchemy构建使用

连接池

python语言下的开源软件，提供了SQL工具包即对象映射ORM工具。它可以高效高性能访问数据库设计，
是在flask框架的一个扩展，python社区内应用最广的ORM工具之一。

配置

# 安装
pip install flask-sqlalchemy

# 如果用mysql, 还需安装mysql客户端库
pip install mysqlclient

连接设置

# 创建简单配置信息

app = Flask(__name__)

class Config(object):
	SQLALCHEMY_DATABASE_URI = '数据库连接信息'
	SQLALCHEMY_TRACK_MODIFICATIONS = False		# flask中是否追踪数据修改
	SQLALCHEMY_ECHO = True						# 显示生成的sql语句, 可以用于调试

app.config.from_object(Config)

其他配置参考

SQLALCHEMY_DATABASE_URI			# 用于连接的数据库 URI 。
SQLALCHEMY_BINDS				# 一个映射 binds 到连接 URI 的字典。
SQLALCHEMY_ECHO					# Ture == 记录所有发给 stderr 的语句，打印sql语句
SQLALCHEMY_RECORD_QUERIES		# 可以用于显式地禁用或启用查询记录。
SQLALCHEMY_NATIVE_UNICODE		# 可以用于显式禁用原生 unicode 支持。
SQLALCHEMY_POOL_SIZE			# 数据库连接池的大小。默认是引擎默认值（通常 是 5 ）
SQLALCHEMY_POOL_TIMEOUT			# 设定连接池的连接超时时间。默认是 10 。
SQLALCHEMY_POOL_RECYCLE			# 多少秒后自动回收连接。它默认移除闲置多于8小时
'''
这对MySQL是必要的，它默认移除闲置多于 8 小时的连接。注意如果使用了MySQL，Flask-SQLALchemy自动设定这个值为 2 小时。
'''

调用方式

# 创建对象
app = Flask(__name__)
db = SQLAlchemy(app)

# 先有db, 后有app --> init_app
db = SQLAlchemy()
app = Flask(__name__)

db.init_app(app)			# 后加载

# 
with app.app_context():
	User.query.all()

模型类映射构建

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

db = SQLAlchemy(app)

class User(db.Model):							# 继承Model
    """
    用户表映射构建
    """
    __tablename__ = 'user_basic'				# 保证表名、字段名、类型一致

    class STATUS:								# 枚举常量
        ENABLE = 1
        DISABLE = 0
	
    # 格式 --> 名称 = db.Column('数据库字段名', db.字段类型, doc='描述信息')
    # 名称 与 '数据库字段名' 一致时, 可省略
    
    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')	# 若用迁移, 必加长度String()
    password = db.Column(db.String, doc='密码')	
    name = db.Column('user_name', db.String, doc='昵称')
    profile_photo = db.Column(db.String, doc='头像')
    ......

常用字段类型

类型名	python中类型	说明
Integer	int	普通整数，一般是32位
SmallInteger	int	取值范围小的整数，一般是16位
BigInteger	int或long	不限制精度的整数
Float	float	浮点数
Numeric	decimal.Decimal	普通整数，一般是32位
String	str	变长字符串
Text	str	变长字符串，对较长或不限长度的字符串做了优化
Unicode	unicode	变长Unicode字符串
UnicodeText	unicode	变长Unicode字符串，对较长或不限长度的字符串做了优化
Boolean	bool	布尔值
Date	datetime.date	时间
Time	datetime.datetime	日期和时间
LargeBinary	str	二进制文件

列(字段参数)选项

选项名	说明
primary_key	如果为True，代表表的主键
unique	如果为True，代表这列不允许出现重复的值
index	如果为True，为这列创建索引，提高查询效率
nullable	如果为True，允许有空值，如果为False，不允许有空值
default	为这列定义默认值

关系选项

选项名	说明
backref	在关系的另一模型中添加反向引用
primary join	明确指定两个模型之间使用的联结条件
uselist	如果为False，不使用列表，而使用标量值
order_by	指定关系中记录的排序方式
secondary	指定多对多关系中关系表的名字
secondary join	在SQLAlchemy中无法自行决定时，指定多对多关系中的二级联结条件

增删改查

增

# 创建一个模型对象, 使用db对象的 session.add 方法进行添加
user = User(mobile='15612345678', name='itcast')

db.session.add(user)
'--过程中, 开启并自动完成一次事务--'
db.session.commit()

# add_all 批量添加, 使用 list
db.session.add_all([user1, user2, user3])
db.session.commit()

查 ——（重点）

'基础查询'
User.query.all()					# 查询全部，返回list
User.query.first()					# 查询第一个，返回对象
User.query.get(2)					# 传入主键ID，返回对象，不存在返回None

# 或原生写法
db.session.query(User).all()		# db.session 与 Models.query的区别
db.session.query(User).first()		# 原生建立session机制，后者flask-sqlalchemy
db.session.query(User).get(2)

'过滤查询' 
# --> filter_by
User.query.filter_by(mobile='13911111111').first()

# --> filter --> 查询条件写法不同 
User.query.filter(User.mobile=='13911111111').first()	# (类名.字段名 == '')

'逻辑查询'
# 逻辑或 or_
from sqlalchemy import or_
User.query.filter(or_(User.mobile=='13333333333', User.name.endswith('hh')))

# 逻辑与 and_(一般情况, 直接连接)
from sqlalchemy import and_
User.query.filter(and_(User.id='1', User.name='hh')).all()

# 逻辑非 not_
from sqlalchemy import not_
User.query.filter(not_(User.mobile=='13333333333')).all()

# 拓展：运算优先级 not > and > or

'其他查询'
# 偏移offset
User.query.offset(2).all()					# 从第二条开始

# 限制limit
User.query.limit(3).all()					# 只取3条

# offset + limit 结合使用
# 从第二条开始，取3条数据...
User.query.offset(2).limit(3).all() == select from User limit 2,3 

# 排序order_by
User.query.order_by(User.id).all()			# 正序
User.query.order_by(User.id.desc()).all()	# 倒序desc

'复合查询'
User.query.filter(User.name).order_by(User.id.desc()).offset(2).limit(5).all()

'优化查询'
# 按照需求查询, 减少额外资源开销, 只查询所需字段options(load_only())
from sqlalchemy.orm import load_only
User.query.options(load_only(User.name, User.mobile)).all()

'聚合查询'
# 在查询中, 加入func, 调用count(), max(), min()等
from sqlalchemy import func
db.session.query(User.id, func.count(User.id)).all()

关联查询

'通过Foreign Key'
class User(db.Model):
	__tablename__='user_profile'
	id = db.Column('user_id'...., backref='user_profile')	# 反向查询backref
	...
	# 额外关系字段, 不做映射, 建立和其他表的关联关系, 只做查询使用 db.relationship
	profile = db.relationship('Userprofile', uselist=False)	# 是否列表返回

class Userprofile(db.Model):
	....
	id = db.Column(...db.ForeignKey('User.user_id')...)		# 必须声明外键
	....
	date = db.Column(db.Integer)
	....
# 查询
user = User.query.get(1)
user.profile			# 全部字段
user.profile.date		# 指定字段

'通过 primaryjoin'
class User(db.Model):
    ...
    # '本表.本字段 == foreign(关联表.关联字段)' --> 添加关联关系
    p = db.relationship('UserP', primaryjoin='User.id==foreign(UserP.id)')
    
# 查询
user = User.query.get(1)
user.profile		
user.profile.date

'指定字段关联 join'
	......
# 查询 --> inner join 类比
Relation.query.join(Relation.user).options(load_only(Relation.user_id, Relation.target_user_id), contains_eager(Relation.user).load_only(User.name)).filter(Relation.user_id==1).all()
'''
select from  ... as a inner join ... as b on a.id=b.targer_user_id where ...
'''
# 上述代码，实际上变形了inner join的查询方法，进行调用，注意理解
Relation.query.join().options().filter().all()

删

# 方式一
user = User.query.order_by(User.id.desc()).first()		# 删除对象
db,session.delete(user)
db.session.commit()

# 方式二
User.query.filter(User.name='python').delete()			# 常用
db.session.commit()

改

# 方式一
user = User.query.get(1)
user.name = 'python'
db.session.commit()

# 方式二
User.query.filter_by(id=1).update({'':''})				# 常用
db.session.commit()

事务

使用与理解

with app.request_context(environ):
    try:
        user = User(mobile='18911111111', name='itheima')
        db.session.add(user)
        db.session.flush() 			# 将db.session记录的sql传到数据库中执行
        db.session.commit()
    except:
        db.session.rollback()		# 事务回滚

flask中，事务的还原点 ---> 代码执行时sqlalchemy.engine.base.Engine BEGIN(implicit)
每次commit操作，实际上都手动提交了一次 --> commit()
若不提交，它会有一个回滚命令，撤销变更 --> sqlalchemy.engine.base.Engine ROLLBACK