attention

使用session.query代替model.query，model.query需要数据库数据映射到模型，即时修改的数据库数据使用model.query可能不会直接查询出来

session.add()执行之后，会将SQL语句添加到pending里面，再次使用session.query会强制执行pending中的SQL，然后再进行查询操作，而model.query不会强制执行pending中的SQL。(具体的原理和实现方式待研究。)

日志如下：

>>> from app import db
>>> session = db.session
>>> from app import User
>>> user = User(name='test1', pwd='test1')
>>> session.add(user)
>>> session.query(User).filter()
>>> session.query(User).filter().all()
2022-01-06 16:57:54,286 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-01-06 16:57:54,287 INFO sqlalchemy.engine.Engine INSERT INTO user (name, pwd, addtime) VALUES (%(name)s, %(pwd)s, %(addtime)s)
2022-01-06 16:57:54,287 INFO sqlalchemy.engine.Engine [generated in 0.00019s] {'name': 'test1', 'pwd': 'test1', 'addtime': None}
2022-01-06 16:57:54,292 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.pwd AS user_pwd, user.addtime AS user_addtime 
FROM user
2022-01-06 16:57:54,292 INFO sqlalchemy.engine.Engine [generated in 0.00038s] {}

session

首先创建一个db对象，这个对象有flask_sqlalchemy中关于数据库的所有封装

from flask import Flask
from flask_sqlalchemy imort SQLAlchemy


app = Flask(__name__)
db = SQLAlchemy(app, engine_options={'pool_recycle': 3600, 'pool_size': 20})
# session = db.session

Flask_sqlalchemy封装了很多SQLAlchemy的很多操作，可以直接使用flask中app的config来生成数据库连接，使用SQLlchemy原生方法来生成数据库连接可以使用，sqlalchemy默认的引擎是MySQLdb，pip install mysql-python，但是这个库已经不支持python3了，在python3环境下安装会出现ModuleNotFoundError: No module named 'ConfigParser'错误，因为python3将ConfigParser.py文件改成了configparser.py，将文件，也就是模块，复制一份为ConfigParser.py可以解决问题。

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
# 数据库+引擎://用户:密码@地址:端口/库?charset=utf8
mysql_uri = "mysql+pymysql://root:root@127.0.0.1:3306/SQLAlchemyTest?charset=utf8"

# paramstyle in ["qmark", "numeric", "named", "format" or "pyformat"]
engine = create_engine(
    mysql_uri,
    paramstyle=None,
    echo=True,
    echo_pool=True,
    pool_pre_ping=True,  # 悲观方式，每次执行语句先执行select语句实现ping的功能，可以在执行失败后重试
)
session_caller = sessionmaker(bind=engine, autoflush=True, autocommit=False, expire_on_commit=True, info=None)
session = session_caller()
# session = scoped_session(session_factory=Session_caller, scopefunc=None)

create_engine接受的参数paramstyle是python默认的参数风格类型，在PEP249(paramstyle)中有详细的讲解

一般使用pymysql来作为sqlalchemy的引擎，但是connectionless情况下在paramstyle上，踩到了sqlalchemy和pymysql的坑，pymysql在__init__.py指定了参数放个为pyformat，所以如果你想指定paramstyle这个参数，应该指定为None或者pyformat，否则会导致出错。

在connection模式下正常。

如果不涉及transaction，推荐使用connection模式。即通过engine.connect()创建连接，执行SQL

使用named

# 使用named
engine = create_engine(mysql_uri, paramstyle='named')
session = sessionmaker(engine)()
sql = 'select * from dev_user where id={}'
session.execute(sql.format(':user_id'), {'user_id': 100}).fetchone()
# sqlalchemy 并没有将:user_id格式化，而是直接传给了pymysql，导致出错
# ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':user_id' at line 1")
# [SQL: select * from dev_user where id=:user_id]
# [parameters: {'user_id': 100}]

使用pyformat

# 使用pyformat
engine.paramstyle = 'pyformat'
session.execute(sql.format('%(user_id)s'), {'user_id': 100}).fetchone()
# sqlalchemy 没有获取到params参数
# ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%(user_id)s' at line 1")
# [SQL: select * from dev_user where id=%%(user_id)s]

# 而使用named风格的参数则没问题
session.execute(sql.format(':user_id'), {'user_id': 100}).fetchone()
# SQL:
# select * from dev_user where id=%(user_id)s
# {'user_id': 101}

sqlalchemy有三种使用方式：

raw sql
sql expression
ORM

前两种成为core方式，注意如果是sqlalchemy+cx_oracle的话, 需要禁掉 connection pool, 否则会有异常. 方法是设置sqlalchemy.poolclass为sqlalchemy.pool.NullPool

对于事务的支持

创建连接开启事务，对于添加而没有commit的数据可以创建保存点

db.session.add(u1)
# 创建一个save point
db.session.begin_nested()

db.session.add(u2)
db.session.rollback()
# 只回退u2，不回退u1
db.session.commit()

下面是数据库模型demo

class User(db.Model):
    __tablename__ = "dev_user"
    id = db.Column(INTEGER(unsigned=True), primary_key=True)
    username = db.Column(db.String(80), unique=True, doc="用户名", comment="用户id")
    email = db.Column(db.String(120), unique=True, doc="用户邮箱", comment="用户邮箱")
    is_active = db.Column(db.Boolean, default=False, doc="已激活", comment="是否已激活")
    phone = db.Column(db.String(20), index=True, doc="用户手机号", comment="用户手机号")
    age = db.Column(INTEGER(), doc="用户年龄", comment="用户年龄")

    def __repr__(self):
        return f"<User {self.username!r}>"


class Blog(db.Model):
    __tablename__ = "blog"
    id = db.Column(INTEGER(unsigned=True), primary_key=True)
    content = db.Column(db.Text, comment="博客内容")
    user_id = db.Column(INTEGER(unsigned=True), db.ForeignKey("dev_user.id"), index=True,
                        doc="用户id", comment="用户id")


class Comment(db.Model):
    __tablename__ = "comment"
    id = db.Column(INTEGER(unsigned=True), primary_key=True)
    comment = db.Column(db.String(150), doc="用户评论", comment="用户评论")
    user_id = db.Column(INTEGER(unsigned=True), index=True, doc"用户id", comment="用户id")


class UserInfo(db.Model):
    __tablename__ = "user_info"
    id = db.Column(INTEGER(unsigned=True), primary_key=True)
    user_id = db.Column(INTEGER(unsigned=True), db.ForeignKey("dev_user.id"),
												index=True, doc="用户id", comment="用户id",)
    user = db.relationship("User", backref="user_info", uselist=False)
    company = db.Column(db.String(100), unique=True, doc="公司简称", comment="公司简称")

多数据库支持

flask_sqlalchemy使用bind的概念来绑定多个数据库

# 在配置config类中可以使用
class Config(object):
    SQLALCHEMY_DATABASE_URI = 'postgres://localhost/main'
    SQLALCHEMY_BINDS = {
    		'users':        'mysqldb://localhost/users',
    		'appmeta':      'sqlite:////path/to/appmeta.db'
		}
# 在app.config属性的设置中可以使用
app.config["SQLALCHEMY_DATABASE_URI"] = 'postgres://localhost/main'
app.config["SQLALCHEMY_BINDS"] = {
    'users':        'mysqldb://localhost/users',
    'appmeta':      'sqlite:////path/to/appmeta.db'
}

在flask_sqlalchemy中，有很多封装有bind参数，可以在其中指定多数据库设置中的某一个，比如

db.session.execute(sql, bind='user')

在SQL上的区别为：

-- 不使用bind
SELECT user.username AS username_1 FROM user WHERE user.id = 100;
-- 使用bind
SELECT test.user.username AS username_1 FROM test.user WHERE test.user.id = 100;
-- 表名前缀为数据库名

在数据库的定义中使用多数据库支持

class User(db.Model):
    __tablename__ = "user"
    # 指定`__bind_key__`在执行sql语句时会直接选择指定的数据库
    __bind_key__ = 'users'
    # 或者使用__table_args__
    __table_args__ = {
        'schema': "users",  # 指定bind
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8mb4',
    }
    # __table_args__也可以使用元组
    # __table_args__ = (
    #	    db.UniqueConstraint("username", "id", name="uix_username_id"),
    #     db.Index("ix_username", "username")
    # )
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    # sqlalchemy的多态特性
    __mapper_args__ = {
        "order_by": username.desc()
    }

# 或者使用创建对象方式创建表模型
user_favorites = db.Table('user_favorites',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('message_id', db.Integer, db.ForeignKey('message.id')),
    info={'bind_key': 'users'}
)

基本使用（CRUD）

简单查询

添加

利用db.session来添加数据

user = User(username='Jack', email='jack@email.com')
db.session.add(user)
db.session.commit()  # 手动commit
user = User(username='Vic', email='vic@email.com')
info = UserInfo(user=user, company='Google')
# 添加多个对象
db.session.add_all([user, info])
db,session.commit()

删除

Query对象的delete方法

update和delete方法接受一个可选参数synchronize_session=evaluate，synchronize_session可选三个值：

False：更新数据库数据，不更新session中查询到的数据
fetch：更新数据库数据，并重新从数据库读取数据
evaluate：默认参数，如果出现session中的数据和数据库数据不一致，session无法判断如何处理，会抛出InvalidRequestError异常

rows = db.session.query(User).filter(User.id == 101).delete(synchronize_session='evaluate')

但是，如果直接删除外键关联的数据，会报错，不过可以使用session.delete()方法，可以处理relationship的外键配置，如果配置了cascade='all,delete-orphan'，就会去删除从表的数据。

session.delete(instance)

db.session.query(User).filter(User.id == 101).delete()
# IntegrityError: (pymysql.err.IntegrityError) (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test`.`user_info`, CONSTRAINT `user_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dev_user` (`id`))')

instance = db.session.query(User).filter(User.id == 101).scalar()
db.session.delete(instance)
# 删除成功返回1

修改

修改有两种方式，修改属性然后提交，或者修改查询集的值

# 修改对象属性
user = db.session.query(User).filter(User.id == 102).first()
user.username = 'Thomas'
db.session.add(user)
db.session.commit()
# 修改查询集Query的值
user_query = db.sessin.query(User.id < 100).update({'is_activate': 0})
db.session.commit()

对于对象user，如果遇到无法更新某个字段值的时候，比如，你可能会遇到json类型的字段无法更新，我们可以使用flag_modified 指定修改的字段

from sqlalchemy.orm.attribute import flag_modified
flag_modified(user, "json_field")
db.session.add(user)
db.session.commit()

查询

filter_by

filter_by查询条件是=或者and的，单条件查询,条件必须关键字参数，而且and连接，返回一个query对象

# scalar只返回一个，如果没有或者有多个返回会抛出MultipleResultsFound异常
db.session.query(User).filter_by(id=101).scalar()
# 多个模型数据查询，默认查询第一个模型User的id为101的数据
db.session.query(User, UserInfo).filter_by(id=101).all()

filter

filter接受的参数类型不同，可以多个过滤条件，返回的query对象

根据主键id可以直接用get进行查询

db.session.query(User).get(100)
# 和Django一样，只获取一个结果，多个查询结果抛异常，没有则返回``None``

和Django的ORM不同，sqlalchemy把一些条件放到了model字段的属性中，比如order_by，和as

from sqlalchemy import desc, asc
query.order_by(desc(User.owned))
db.session.query(User.id).filter().order_by(asc(User.id)).offset(10).limit(10).all()
# SQL:
#  SELECT dev_user.id AS dev_user_id
# FROM dev_user ORDER BY dev_user.id DESC
# LIMIT %(param_1)s, %(param_2)s
# {'param_1': 10, 'param_2': 10}
db.session.query(User.username.label('name')).filter()
# 将username起别名为name
# SQL:
# SELECT dev_user.username as name
# FROM dev_user

首先是一些从query中获取数据的方法，all()，first()，one()，scalar()，get()，one_or_none()

all: 返回所有数据
first: 所有rows里面取第一行，如果为没有结果则为None,不会抛异常
one: 返回的rows必须是只有一行，有多行或者没有数据都会抛异常
one_or_none：返回的rows必须是一行或者没有数据，有多行数据就会抛异常
scalar：返回的rows必须是一行或者没有数据，有多行数据就会抛异常，并且scalar只会取result里面的第一个结果集

=，<，>的查询

# from sqlalchemy import true
db.session.query(User.id, User.username).filter(User.is_activate==db.true())

# 组合查询
db.session.query(User.username, UserInfo.company).filter(User.id==103, User.username=='Zachary Moreno').all()
# 组合查询也可以使用and_，翻译成的SQL相同
from sqlalchemy import and_, or_
db.session.query(User.username, UserInfo.company).filter(and_(User.id==103, User.username=='Zachary Moreno')).all()
# 翻译成的SQL：
# SELECT dev_user.username AS dev_user_username, user_info.company AS user_info_company
# FROM dev_user, user_info
# WHERE dev_user.id = %(id_1)s AND dev_user.username = %(username_1)s
# params: {'id_1': 103, 'username_1': 'Zachary Moreno'}

db.session.query(User).filter(User.id < 20).all()
db.session.query(User).filter(User.create_at > '2020-02-02 00:00:00')

模糊查询

db.session.query(User.username).filter(User.email.startswith('123')).all()
db.session.query(User.username).filter(User.email.like('123%'))
# SELECT dev_user.username from dev_user where dev_user.email like '123%'
db.session.query(User.username).filter(User.email.endswith('123')).all()

join

join进行连表操作

db.session.query(Comment.comment.label('user_comment'), User.id.label('user_id'), User.username.label('username')).join(User, User.id==Comment.user_id).filter(User.id<100).order_by(User.id.desc()).all()  # desc是字段的方法
# 对应SQL：
# SELECT comment.comment AS user_comment, dev_user.id AS user_id, dev_user.username AS username
# FROM comment INNER JOIN dev_user ON dev_user.id = comment.user_id
# WHERE dev_user.id = %(id_1)s
# params: {'id_1': 100}

可以利用sqlalchemy的别名进行多次join

from sqlalchemy.orm import aliased
Comment_alias = aliased(Comment, name='comment_alias')
db.session.query(User.id, User.username, User.email, User.phone, Comment.comment).join(Comment, Comment.user_id == User.id).join(Comment_alias, Comment_alias.user_id == User.id).all()
# SQL:
# SELECT dev_user.id AS dev_user_id, dev_user.username AS dev_user_username, dev_user.email AS dev_user_email, dev_user.phone AS dev_user_phone, comment.comment 
# FROM dev_user INNER JOIN comment ON comment.user_id = dev_user.id INNER JOIN comment AS comment_alias ON comment_alias.user_id = dev_user.id

offset/limit

from sqlalchemy import true

db.session.query(User.username, User.phone, User.email).filter(User.is_active == true()).order(User.age.desc()).offset(10).limit(20)
# SQL:
# SELECT dev_user.username AS dev_user_username, dev_user.phone AS dev_user_phone, dev_user.email AS dev_user_email
# FROM dev_user
# WHERE dev_user.is_active = true ORDER BY dev_user.age DESC
# LIMIT %(param_1)s, %(param_2)s
# {'param_1': 10, 'param_2': 20}

in/not in/is null/is not null

# in
db.session.query(User).filter(User.id.in_([100, 103, 105]))
# not in
db.session.query(User).filter(~User.id.in_([100, 103, 105]))
# is null
from sqlalchemy import null
db.session.query(User).filter(User.email == null())
# is not null
db.session.query(User).filter(User.email != null())

select_from

指定从哪个数据表中获取数据，其实获取的字段可以在query中直接指定，select_from和join中的表不能相同

db.session.query(User).select_from(Comment).join(User, Comment.user_id == User.id).all()
# 只获取User中的数据，即query中的model
# SELECT dev_user.id AS dev_user_id, dev_user.username AS dev_user_username, dev_user.email AS dev_user_email, dev_user.is_active AS dev_user_is_active, dev_user.phone AS dev_user_phone, dev_user.age AS dev_user_age
# FROM comment INNER JOIN dev_user ON dev_user.id = comment.user_id

sqlalchemy，flask-sqlalchemy连接配置和使用详解

attention

session

多数据库支持

基本使用（CRUD）