5.flask-sqlalchemy

数据库

安装模块

pip install flask-sqlalchemy
pip install pymysql

安装mysql

地址

介绍

ORM介绍

1. ORM Object Relationship Mapping
2. 模型与表之间的映射

SQLAlchemy

采用写原生sql的方式在代码中会出现大量的sql语句，会出现一些问题:

1. sql语句重复利用率不高，越复杂的sql语句条件越多，代码越长。会出现很多相近的sql语句
2. 很多sql语句是在业务逻辑中拼出来的，如果有数据库需要更改，就要去修改这些逻辑，这会很容易漏掉对某些sql语句的修改。
3. 写sql时容易忽略web安全问题，给未来造成隐患

ORM,全称Object Relational Mapping，中文叫做对象关系映射，通过ORM可以通过类的方式去操作数据库，而不用再写原生的sql语句。通过把表映射成类，把行作实例，把字段作为属性，ORM在执行对象操作的时候最终还是会把对象的操作转换为数据库原生语句。使用ORM有许多有点:

1. 易用性：使用ORM做数据库的开发可以有效的减少重复sql语句的概率，写出来的模型也更加直观清晰。
2. 性能损耗小:ORM转换成底层数据库操作指令确实会有一些开销。但从实际的情况来看，这种性能损耗很少，只要不是对性能有严格的要求，综合考虑开发效率，代码的阅读性，带来的好处要远大于性能损耗，而且项目越大作用越明显。
3. 设计灵活:可以轻松的写出复杂的查询
4. 可移植性:SQLAlchemy封装了底层的数据库实现，支持多个关系数据库引擎，包括流行的MySQL、PostgreSQL和SQLite。可以非常轻松的切换数据库

连接数据库

• 创建数据库

• 定义数据库连接字符串DB_URI

格式:dialect+dricer://username:password@host:port/database

如下：

HOSTNAME = "127.0.0.1"
PORT = "3306"
DATABASE = "test"
USERNAME = "root"
PASSWORD = "123456"
DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)

• 将定义好的数据库连接字符串DB_URI放到配置文件中

app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI

• 定义SQLAlchemy对象

db = SQLAlchemy(app)

运行后，出现如下警告：

 SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '

只需要添加如下即可：

# 屏蔽SQLalchemy发送的信号
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

创建模型

通过使用db.Model进行创建模型类。

最小应用

from flask import Flask
from flask_sqlalchemy import SQLAlchemyapp = Flask(__name__)HOSTNAME = "127.0.0.1"
PORT = "3306"
DATABASE = "test"
USERNAME = "root"
PASSWORD = "123456"
DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)# 将定义好的数据库连接字符串DB_URI放到配置文件中
app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False# SQLAlchemy对象
db = SQLAlchemy(app)class UserModel(db.Model):id = db.Column(db.Integer,primary_key=True,autoincrement=True)name = db.Column(db.String(200))# 初始化def __init__(self, name):self.name = name# 打印字符串def __repr__(self):return '<User %r>' % self.nameif __name__ == '__main__':app.run()

为了创建初始数据库，只需要从交互式 Python shell 中导入 db 对象并且调用 SQLAlchemy.create_all()方法来创建表和数据库:

>>> from yourapplication import db
>>> db.create_all()

如下：

>>> from app import db
>>> db.create_all()

这样就会在数据库生成表

如果想要删除，就是用db.drop_all()。

目前数据库已经生成，现在进行创建一些用户:

>>> from app import UserModel
>>> user = UserModel(name="test1")

但是它们还没有真正地写入到数据库中，因此动手确保它们已经写入到数据库中:

>>> db.session.add(user)
>>> db.session.commit()

访问数据库中的数据也是十分简单的:

>>> users = UserModel.query.all()
>>> usersOut[10]: [<User 'test1'>]

返回结果大小控制

• all() 返回所有
• first() 查询并返回第一条,没有数据为空
• one() 查询所有并严格返回一条数据，如果查询到多条数据或没有数据，都会报错
• one_or_none 同 one，没有数据会返回None，不会报错，其他一样。
• scalar 同 one，但是只返回那条数据的第一个字段。

增删查改

定义模型

class UserModel(db.Model):id = db.Column(db.Integer,primary_key=True,autoincrement=True)name = db.Column(db.String(200))# 初始化def __init__(self, name):self.name = name# 打印字符串def __repr__(self):return '<User %r>' % self.name

添加

>>> db.drop_all()
>>> db.create_all()

• 添加单个数据

>>> add1()

• 一次性添加多个数据

>>> add2()

查询

详细查询方法，见后续

• 使用filter_by来做条件查询

>>> user = UserModel.query.filter(UserModel.name.contains("t")).all()
>>> print(user)
[<User 't1'>]

• 使用filter来做条件查询

>>> user = UserModel.query.filter_by(name="t1").all()
>>> user
[<User 't1'>]

• 使用get方法查找数据，get方法是根据id来查询的，只会返回一条数据或者None

>>> user = UserModel.query.get(1)
>>> user
Out[7]: <User 't1'>

• 使用first方法获取结果集中的第一条数据

>>> user = UserModel.query.first()
>>> user
Out[7]: <User 't1'>

修改

修改对象，首先在数据库查找数据，然后将数据进行修改，最后做commit操作

>>> user = UserModel.query.first()
>>> user.name = "ttt"
>>> db.session.commit()

删除

删除对象，首先在数据库查找数据，然后将查找到的数据进行删除，最后执行commit操作

>>> user = UserModel.query.first()
>>> db.session.delete(user)
>>> db.session.commit()

表名与列名设置

• 表名通过 __tablename__进行设置表名称，否则会以类名进行命名。例如UserModel的表名为user_model

class Article(db.Model):__tablename__ = "article"

• 列名，通过db.Column中的name参数进行命名

db.Column(name="xxx")

常用数据类型详解

• Integer:整型
• Float:浮点类型
• Boolean:传递True/False进去
• DECIMAL:定点类型，是专门为了解决浮点精度丢失的问题的，在存储相关的字段的时候建议都是要这个数据字段，并且这个类型使用的时候需要传递两个参数，第一个参数使用来标记字段能够存储多少个数字，第二个参数是表示小数点的最大位数
• enum:枚举类型。指定某个字段只能是枚举中指定的几个值，不能为其他值，在ORM模型中，使用Enum来作为枚举，示例代码如下:

import enum
class TagEnum(enum.Enum):python = "python"flask = "flask"django = "django"class Article(Base):__tablename__ = "article"id = Column(Integer,primary_key=True,autoincrement=True)# tag = Column(Enum('python','django','flask'))tag = Column(Enum(TagEnum))# article = Article(tag='1')
# article = Article(tag='python')
article = Article(tag=TagEnum.python)

• Date:传递datetime.date()进去。存储时间，只能存储年月日。映射到数据库中是date类型。在python中，可以使用datetime.date来指定

class Article(Base):__tablename__ = "article"id = Column(Integer,primary_key=True,autoincrement=True)create_time = Column(Date)from datetime import date
article = Article(create_time=date(year=2017,month=10,day=8))

• DateTime:传递datetime.datetime()进去。存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在python代码中，可以使用"datetime.datetime"来指定，示例：

class Article(Base):__tablename__ = "article"id = Column(Integer,primary_key=True,autoincrement=True)create_time = Column(DateTime)from datetime import datetime
article = Article(create_time=datetime(year=2017,month=11,day=11,hour=11,minute=11,second=11,microsecond=11))

• Time:传递datetime.time()进去。存储时间，可以存储时分秒，映射到数据库中也是time类型。在python代码中，可以使用"datetime.time"来指定，示例：

class Article(Base):__tablename__ = "article"id = Column(Integer,primary_key=True,autoincrement=True)create_time = Column(Time)from datetime import time
article = Article(create_time=time(hour=11,minute=11,second=11,microsecond=11))
# article = Article(create_time=datetime.now())

• String:字符类型，使用时需要指定长度，区别于Text类型
• Text:文本类型.存储长字符串，一般可以存储6w多个字符。如果超出了这个范围，可以使用LONGTEXT类型，映射到数据库中就是text类型
• LONGTEXT:长文本类型

Column常用参数

• default:设置某个字段的默认值。默认值可以为一个表达式或者变量，函数
• nullable:设置某个字段是否可空，默认值为True
• primary_key:设置某个字段为主键
• unique:设置某个字段为唯一的字段，不允许有重复值，默认值为True
• autoincrement:设置这个字段为自动增长的
• name:指定orm模型中某个属性映射到表中的字段名，如果不指定，那么会使用这个属性的名字作为字段名。如果指定了，就会使用指定的这个值作为参数，这个参数也可以当做位置参数，在第1个参数来指定。

class ArticleModel(db.Model):id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),name="标题",nullable=False)phone = db.Column(db.String(11),unique=True)update_time = db.Column(db.DateTime, onupdate=datetime.now, default=datetime.now)# 初始化def __init__(self, title,phone):self.phone = phoneself.title = title

从交互式 Python shell 中进行创建：

>>> from app import db
>>> from app import ArticleModel
>>> db.drop_all()
>>> db.create_all()
>>> article = ArticleModel(title="123",phone="1234567890")
>>> db.session.add(article)
>>> db.session.commit()

• onupdate:在数据更新的时候会调用这个参数指定的值或函数，在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值，常用的就是"update_time"（每次更新数据的时候都要更新的值）

对artitle的title进行修改，进行观察

>>> article = ArticleModel.query.first()
>>> article.title = "456"
>>> db.session.commit()

现在可以看到update_time已经更新了。

query函数

• 模型对象。指定查找这个模型中所有的对象

• 模型中的属性。可以指定只查找某个模型的其中几个属性

• 聚合函数 ：

  • db.func.count:统计行的数量
  • db.func.avg:求平均值
  • db.func.max:求最大值
  • db.func.min:求最小值
  • db.func.sum:求和

• 'db.func'上，其实没有任何聚合函数，但是因为底层做了一些魔术，只要mysql中有聚合函数，都可以通过db.func调用

创建模型

class ArticleModel(db.Model):id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),name="标题",nullable=False)update_time = db.Column(db.DateTime, onupdate=datetime.now, default=datetime.now)price = db.Column(db.Float,nullable=False)# 初始化def __init__(self, title,price):self.price = priceself.title = title

添加数据

def add():for x in range(6):article = ArticleModel(title='title{}'.format(x),price=random.randint(50,100))db.session.add(article)db.session.commit()

通过交互式 Python shell 创建表和数据库:

>>> from app import *
>>> add()

调用func

# 聚合函数
count = db.session.query(db.func.count(Article.id)).first()
print(count)avg = db.session.query(db.func.avg(Article.price)).first()
print(avg)max = db.session.query(db.func.max(Article.price)).first()
print(max)min = db.session.query(db.func.min(Article.price)).first()
print(min)sum = db.session.query(db.func.sum(Article.price)).first()
print(sum)print(db.func.sum(Article.title))

>>> from app import *
>>> func()

filter方法常用过滤条件

过滤是数据库的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过filter方法实现的。

如果想看底层sql原生语句，在语句末尾不加all()，就可以打印出原生sql语句。

定义一个模型

class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)price = db.Column(db.Float,nullable=False)content = db.Column(db.Text)def __repr__(self):return "<Article(title:%s)>" % self.title

交互shell生成测试数据：

>>> for x in range(6):...:     article = Article(title='{}'.format(x))...:     db.session.add(article)...: db.session.commit()...: ...: for x in range(6):...:     article = Article(title='title{}'.format(x))...:     db.session.add(article)...: db.session.commit()

1. equals（等于）

>>> article = Article.query.filter(Article.id == 1).first()
>>> article
<Article(title:0)>

1. not equals（不等于）

>>> article = Article.query.filter(Article.title != 'title0').all()
>>> article.__str__()
[<Article(title:0)>,<Article(title:1)>,<Article(title:2)>,<Article(title:3)>,<Article(title:4)>,<Article(title:5)>,<Article(title:title1)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]

1. like & ilike(不区分大小写)

>>> articles = Article.query.filter(Article.title.like('%title%')).all()
# 不分区大小写
>>> articles
[<Article(title:title0)>,<Article(title:title1)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]>>> articles = Article.query.filter(Article.title.ilike('%title%')).all()
>>> articles
[<Article(title:title0)>,<Article(title:title1)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]

1. in（含有）

>>> articles = Article.query.filter(Article.title.in_(['title1','title2'])).all()
>>> articles
[<Article(title:title1)>, <Article(title:title2)>]

1. not in（不含有）

>>> articles = Article.query.filter(~Article.title.in_(['title1'])).all()
>>> articles
[<Article(title:0)>,<Article(title:1)>,<Article(title:2)>,<Article(title:3)>,<Article(title:4)>,<Article(title:5)>,<Article(title:title0)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]
>>>  articles = Article.query.filter(Article.title.notin_(['title1'])).all()
>>> articles
[<Article(title:0)>,<Article(title:1)>,<Article(title:2)>,<Article(title:3)>,<Article(title:4)>,<Article(title:5)>,<Article(title:title0)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]

1. is null

>>> Article.query.filter(Article.title == None).all()
[]

1. is not null

>>> Article.query.filter(Article.title != None).all()
[<Article(title:0)>,<Article(title:1)>,<Article(title:2)>,<Article(title:3)>,<Article(title:4)>,<Article(title:5)>,<Article(title:title0)>,<Article(title:title1)>,<Article(title:title2)>,<Article(title:title3)>,<Article(title:title4)>,<Article(title:title5)>]

1. or

>>> articles = Article.query.filter(db.or_(Article.title=='title0',Article.id=='1')).all()
>>> articles
Out[34]: [<Article(title:0)>, <Article(title:title0)>]

1. and

>>> from sqlalchemy import or_,and_
>>> articles = Article.query.filter(Article.title=='0' and Article.id == 'id').all()
>>> articles
[<Article(title:0)>]>>> articles = Article.query.filter(and_(Article.title=='0',Article.id == '1')).all()
>>> articles
[<Article(title:0)>]

注意在使用or和and的时候，需要添加from sqlalchemy import or_,and_或者使用db.or_、db.and_

filter_by方法常用过滤条件

filter_by与filter区别，filter需要使用类名.列名，filter_by只需要使用列名即可

外键及其四种约束讲解

在MySQL中，外键可以让表之间的关系更加紧密，而SQLAlchemy同样支持外键。通过FpreignKey类来实现，并且可以指定表的外键约束。

外键约束有以下几项:

1. RESTRICT(restrict):父表数据被删除，会阻止删除
2. NO ACTION:在MySQL中，同RESTRICT
3. CASCADE:级联删除
4. SET NULL:父类数据被删除，子表数据设置为NULL

相关示例代码如下:

class User(db.Model):__tablename__ = 'user'id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50))class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)content = db.Column(db.Text,nullable=False)# 外键，没有指定，就默认为RESTRICT# RESTRICT:阻止删除数据# uid = Column(Integer,ForeignKey("user.id",ondelete="RESTRICT"))# 级联删除# uid = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))# 只有父表被删除，子表修改为NULLuid = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="SET NULL"))

ORM层外键

mysql级别的外键，还不够ORM，必须拿到一个表中的外键，然后再去通过这个外键再去另外一张表查找。可这样太麻烦了。SQLAlchemy提供了一个"relationship"，这个类可以定义属性，以后再访问相关联的表的时候就可以直接通过属性访问的方式就可以访问得到了。

class User(db.Model):__tablename__ = 'user'id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50))articles = db.relationship('Article')class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)content = db.Column(db.Text,nullable=False)uid = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="SET NULL"))# 映射到User模型author = db.relationship("User")def __repr__(self):return "<Article(title:%s),content:%s>" % (self.title,self.content)

一对一关系

想要一对一关系，可以把 uselist=False 传给 relationship() 。

如下:

class User(db.Model):....articles = db.relationship('Article',uselist=False)class Article(db.Model):....author = db.relationship("User",uselist=False)....

另外，可以通过backref来指定反向访问的属性名称。

如下：

class User(db.Model):....articles = db.relationship('Article',backref=db.backref("author",uselist=False))

添加数据

def add():user = User(username="demo1")db.session.add(user)db.session.commit()user = User.query.first()article = Article(title="title1",content="123",uid=user.id)db.session.add(article)db.session.commit()db.drop_all()
db.create_all()
# 创建数据
add()

一对多关系

继续采用用户(user)与文章(article)，关系为一个用户拥有多篇文章。如下：

通过backref来指定反向访问的属性名称。

class User(db.Model):....articles = db.relationship('Article',backref=db.backref("author"))class Article(db.Model):....

添加数据

def add():user = User(username="demo1")db.session.add(user)db.session.commit()user = User.query.first()for i in range(6):article = Article(title="title{}".format(i),content="{}".format(i),uid=user.id)db.session.add(article)db.session.commit()

查询

>>> user = User.query.first()
>>> user.articles
[<Article(title:title0),content:0>,<Article(title:title1),content:1>,<Article(title:title2),content:2>,<Article(title:title3),content:3>,<Article(title:title4),content:4>,<Article(title:title5),content:5>]>>> article = Article.query.first()
>>> article.author
<User 1>>>> article.author.articles[<Article(title:title0),content:0>,<Article(title:title1),content:1>,<Article(title:title2),content:2>,<Article(title:title3),content:3>,<Article(title:title4),content:4>,<Article(title:title5),content:5>]

通过user.articles这样进行调用属性，就能够方便的获取相关表的数据。

多对多关系

想要用多对多关系，需要定义一个用于关系的辅助表。对于这个辅助表， 强烈建议不使用模型，而是采用一个实际的表:

? 如下问题，继续采用用户(user)与文章(article)，关系为一个用户拥有多篇文章，一篇文章有一个以上用户。即：

user_article = db.Table('user_article',db.Column('user_id', db.Integer, db.ForeignKey('user.id')),db.Column('article_id', db.Integer, db.ForeignKey('article.id'))
)class User(db.Model):__tablename__ = 'user'id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50))articles = db.relationship('Article',secondary=user_article,backref=db.backref("authors"))class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)content = db.Column(db.Text,nullable=False)def __repr__(self):return "<Article(title:%s),content:%s>" % (self.title,self.content)

在relationship函数中添加secondary=user_article，即可。其中user_article为中间表。

不需要设置外键了

添加数据

>>> from app import *
>>> db.drop_all()
>>> db.create_all()>>> user1 = User(username="user1")
>>> user2 = User(username="user2")
>>> 
>>>  article1 = Article(title="title{}".format(1),content="{}".format(1))
>>> article2 = Article(title="title{}".format(2),content="{}".format(2))
>>> article3 = Article(title="title{}".format(3),content="{}".format(3))
>>> 
>>> article1.authors.append(user1) # article1对应user1,user2两个用户
>>> article1.authors.append(user2)
>>> 
>>> article2.authors.append(user1) # article2对应user1一个用户
>>>  
>>> user2.articles.append(article3) # article3对应user2一个用户
>>>  
>>> db.session.add_all([user1,user2,article1, article2,article3])
>>> db.session.commit()

查询

>>> user1.articles
[<Article(title:title1),content:1>, <Article(title:title2),content:2>]>>> article1.authors
[<User 1>, <User 2>]

ORM层面删除数据注意事项

ORM层面删除数据，会无视mysql级别的外键约束，直接会对将对应的数据删除，然后将从表中的那个外键设置为NULL,如果想要避免这种行为，应该将从表中的外键的"nullable=False"。

relationship方法中的cascade参数详解

如果将数据库得到外键设置为RESTRICT，那么在ORM层面，删除了父表中的数据，那么从表中的数据将会NULL。如果不想要这种情况发生，那么应该将这个值的nullable=False。

在SQLAlchemy，只要将一个数据添加到session中，和它相关联的数据都可以一起存入到数据库中了。这些是怎么设置的呢?其实是通过relationship的时候 ，有一个关键字参数cascade可以设置这些属性:

1. save-update:默认选项。在添加一条数据的时候，会把其他和它相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。
2. delete:表示当删除某一个模型中的数据的时候，是否也删除掉使用relationship和它关联的数据。
3. delete-orphan:表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。
4. merge:默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作
5. expunge:移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。
6. all:是对save-update，merge，refresh-expire，expunge，delete几种的填写

例如：

1. author = db.relationship("User", backref="articles", cascade="save-update,delete")2. author = db.relationship("User", backref=db.backref("articles",cascade="save-update,delete"),cascade="save-update,delete")

三种排序方式详解

创建模型类

class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)content = db.Column(db.Text,nullable=False)def __repr__(self):return "<Article(title:%s),content:%s>" % (self.title,self.content)

添加数据

>>> article1 = Article(title="title{}".format(1),content="{}".format(1))
>>> article2 = Article(title="title{}".format(2),content="{}".format(2))
>>> article3 = Article(title="title{}".format(3),content="{}".format(3))>>> 
>>> db.session.add_all([article1, article2,article3])
>>> db.session.commit()

调用order_by

1. order_by：可以指定根据这个表中的某个字段进行排序，如果在后面添加desc()，就能够调用desc()方法进行降序。而升序，sqlalchemy是默认为升序的，或者调用asc()方法。

>>> Article.query.order_by(Article.title.desc()).all()
[<Article(title:title3),content:3>,<Article(title:title2),content:2>,<Article(title:title1),content:1>]

模型定义时指定默认排序

在模型定义的时候指定默认排序:有些时候，不想每次在查询的时候都指定排序的方式，可以在定义模型的时候就指定排序的方式。

有以下两种方式:

1. relationship的order_by参数:在指定relationship的时候，传递order_by参数来指定排序的字段

class Article(db.Model):__tablename__ = "article"id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)content = db.Column(db.Text,nullable=False)# 排序author = db.relationship("User",backref=db.backref("articles",order_by=create_time.desc()))

1. 在模型定义中，添加以下代码:

   __mapper_args__ = { 'order_by':title, }

class Article(db.Model):....__mapper_args__ = {"order_by":create_time.desc(),}

即可让文章用标题来进行排序

limit、offset以及切片操作

添加数据

>>> db.drop_all()
>>> db.create_all()
>>> for i in range(1000):article = Article(title="title{}".format(i))db.session.add(article)
>>> db.session.commit()

select * from article limit 4 offset 9
or 
select * from article limit 9,4

含义为返回从第9行起，后面4行数据，包括第9行。

• limit:可以限制每次查询的时候只查询几条数据。

• offset:可以限制查找数据的时候过来前面多少条。

>>> articles = Article.query.offset(9).limit(4).all()
>>> for article in articles:
>>>     print(article.title)title9
title10
title11
title12

• 切片:可以Query对象使用切片操作，来获取想要的数据

>>> articles = Article.query.all()[9:9+4]
>>> for article in articles:
>>>     print(article.title)    title9
title10
title11
title12

数据查询加载技术

lazy 决定了 SQLAlchemy 什么时候从数据库中加载数据。

• 'select' (默认值) 就是说 SQLAlchemy 会使用一个标准的 select 语句必要时一次加载数据。
• 'joined' 告诉 SQLAlchemy 使用 JOIN 语句作为父级在同一查询中来加载关系。
• 'subquery' 类似 'joined' ，但是 SQLAlchemy 会使用子查询。
• 'dynamic' 在有多条数据的时候是特别有用的。不是直接加载这些数据，SQLAlchemy 会返回一个查询对象，在加载数据前您可以过滤（提取）它们。

如下：

author = db.relationship("User",backref=db.backref('articles',lazy="dynamic"))

group_by和having子句

创建模型

class User(db.Model):__tablename__ = "user"id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50),nullable=False)age = db.Column(db.Integer,default=0)gender = db.Column(db.Enum("male","female","secret"))

添加数据

user1 = User(username="angle",age=17,gender="male")
user2 = User(username="miku",age=18,gender="male")
user3 = User(username="xue",age=18,gender="female")
user4 = User(username="yue",age=19,gender="female")
user5 = User(username="mi",age=20,gender="female")db.session.add_all([user1,user2,user3,user4,user5])
db.session.commit()

• group_by

根据某个字段进行分组

示例

统计每个年龄段的人数，sql语句为：

select count(*) from user group by age;

实现：

>>> result = db.session.query(User.age,db.func.count(User.id)).group_by(User.age).all()
>>> result
[(17, 1), (18, 2), (19, 1), (20, 1)]

• having

having是对查找结果进一步过滤，在分组的基础上在进行筛选过滤

示例

统计每个年龄段的人数，并筛选出年龄小于18的，sql语句为：

select count(*) from user group by age where age < 18;

实现：

>>> result = db.session.query(User.age,db.func.count(User.id)).group_by(User.age).having(User.age < 18).all()
>>> result
[(17, 1)]

复杂查询

创建模型类

class User(db.Model):__tablename__ = "user"id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50),nullable=False)def __repr__(self):return "<User(id:%s,username:%s)>" % (self.id,self.username)class Article(db.Model):__tablename__ = 'article'id = db.Column(db.Integer,primary_key=True,autoincrement=True)title = db.Column(db.String(50),nullable=False)create_time = db.Column(db.DateTime,nullable=False,default=datetime.now)uid = db.Column(db.Integer,db.ForeignKey("user.id"))author = db.relationship("User",backref=db.backref("articles"))def __repr__(self):return "<Article(title:%s)>" % self.title

添加数据

db.drop_all()
db.create_all()user1 = User(username="angle")
user2 = User(username="miku")for i in range(1):article = Article(title="title1 %s" % i)article.author = user1db.session.add(article)
db.session.commit()for i in range(1,3):article = Article(title="title1 %s" % i)article.author = user2db.session.add(article)
db.session.commit()

join实现复杂查询

join查询分为两种，

1. inner join
   • left join
   • right join
2. outer join。

如果想要查询User及其对应的Address，则可以通过以下方式来实现:

for u,a in db.session.query(User,Address).filter(User.id == Address.user.id).all()print(u)print(a)

tips:join方法配合filter过滤方法一起使用

• left join

sql语句：

select user.id,user.username from user 
inner join article on user.id = article.uid 
group by user.id 
order by count(article.id) desc;

实现：

>>> result = db.session.query(User).join(Article).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<User(id:2,username:miku)>, <User(id:1,username:angle)>]

• right join

sql语句：

select article.id,article.title,article.create_time,article.uid from article 
inner join user on user.id = article.uid 
group by user.id 
order by count(article.id) desc;

实现：

>>> result = db.session.query(Article).join(User).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<Article(title:title1 1)>, <Article(title:title1 0)>]

• outer join

sql语句：

select article.id ,article.title,article.create_time,article.uid from article 
left outer join user on user.id = article.uid 
group by user.id 
order by count(article.id) desc;

实现：

>>> result = db.session.query(Article).outerjoin(User).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<Article(title:title1 1)>, <Article(title:title1 0)>]

subquery实现复杂查询

模型类

class User(db.Model):__tablename__ = "user"id = db.Column(db.Integer,primary_key=True,autoincrement=True)username = db.Column(db.String(50),nullable=False)city = db.Column(db.String(50),nullable=False)age = db.Column(db.Integer,default=0)def __repr__(self):return "<User(username:%s)>" % self.username

添加数据

>>> user1 = User(username="A",city="M城",age=15)
>>> user2 = User(username="B",city="G城",age=21)
>>> user3 = User(username="C",city="H城",age=12)
>>> user4 = User(username="D",city="J城",age=20)>>> db.session.add_all([user1,user2,user3,user4])
>>> db.session.commit()

子查询可以让多个查询变成一个查询，只要查找一次数据库，性能相对来讲更加高效一点，不用写多个sql语句就可以一些复杂的查询了，那么在sqlalchemy中，要实现一个子查询，应该使用以下几个步骤:

1.将子查询按照传统的方式写好查询代码，然后在"query"对象后面执行"subquery"方法，将这个查询变成一个子查询。

user = db.session.query(db.func.max(User.age).label("age")).subquery()

2.在子查询中，将以后需要用到的字段通过"label"方法，取个别名

db.session.query(db.func.max(User.age).label("age"))

3.在父查询中，如果想要使用子查询的字段，那么可以通过子查询的变量上的"c"属性拿到

db.session.query(User).filter(User.age == user.c.age).all()

使用别名（aliased）

SQLAlchemy 使用 aliased() 方法表示别名，当需要把同一张表连接多次的时候，常常需要用到别名。

# 把 Address 表分别设置别名
Article1 = aliased(Article)
Article2 = aliased(Article)

text - 直接写sql

• 在text里写sql语句，并在 filter 和 order_by 中使用

>>> db.session.query(User).filter(db.text("age<16"))\.order_by(db.text("age")).all()
[<User(username:C)>, <User(username:A)>]

• text里可以用 :name 传动态参数，并params传值

>>> db.session.query(User).filter(db.text("age<:value and username=:name")). \...:     params(value=16, name='A').order_by(User.id).one()
<User(username:A)>

• text里也可以给完整的sql语句,然后传给 from_statement

>>> db.session.query(User).from_statement(db.text("select * from user where username=:name and age<:age")).params(name='A',age=16).all()
[<User(username:A)>]

• 如果用from_statement中不是给的所有字段，那可用 columns 将值赋给字段

>>> stmt = db.text("select id,username FROM user where username=:name")
>>> stmt = stmt.columns(User.username, User.id)
>>> db.session.query(User).from_statement(stmt).params(name='A').all()
[<User(username:A)>]

Flask-Script

Flask-Script的作用是可以通过命令行的形式来操作Flask。

安装

pip install flask-script

三种创建命令:使用@command修饰符、使用@option修饰符、创建command子类

@command修饰符

from flask_script import Manager
from app import app,db# 使用Manager创建一个对象
manager = Manager(app)@manager.command
def greet():print("你好")if __name__ == '__main__':manager.run()

运行：

python manage.py greet

@option修饰符

添加参数

from flask_script import Manager
from myapp import app,BackendUser,db
from db_script import db_manager# 使用Manager创建一个对象
manager = Manager(app)@manager.option("-u","--username",dest="username")
@manager.option("-a","--age",dest="age")
def add_user1(username,age):print("用户名:{},年龄:{}".format(username,age))@manager.option("-u","--username",dest="username")
@manager.option("-e","--email",dest="email")
def add_user2(username,email):user = BackendUser(username=username,email=email)db.session.add(user)db.session.commit()if __name__ == '__main__':manager.run()

运行：

python manage.py add_user1 -u angle -a 18
python manage.py add_user2 -u angle -e 18@qq.com

command子类

from flask_script import Manager  ，Server
from flask_script import Command  
from debug import app  manager = Manager(app)  class Hello(Command):def run(self):print('测试')#自定义命令一/将类Hello()映射为hello
manager.add_command('hello', Hello())#自定义命令二/启动命令
manager.add_command("runserver", Server()) #命令是runserver
if __name__ == '__main__':manager.run()

运行：

python manage.py hello

子类

db_script.py

from flask_script import  Managerdb_manager = Manager()@db_manager.command
def init():print('迁移仓库创建完毕')@db_manager.command
def revision():print("迁移脚本生成成功")@db_manager.command
def upgrade():print("脚本映射到数据库成功")

manage.py

from flask_script import Manager
from app import app
from db_script import db_manager# 使用Manager创建一个对象
manager = Manager(app)# 添加子命令
# python manage.py db init
manager.add_command("db",db_manager)if __name__ == '__main__':manager.run()

运行：

python manage.py db init

tips:执行db_script.py下的init函数

add_command()添加子类，将db_manager映射为db

项目结构重构

为什么进行项目重构？

因为flask项目中，有些变量或者对象会被调用在多个文件中，可能在调用时形成一个死循环。因此，专门将一些经常使用的对象，放在一个文件中，以便调用。

exts.py

from flask_sqlalchemy import SQLAlchemydb = SQLAlchemy()

将一些配置信息专门放在一个配置文件中。

config.py

HOSTNAME = "127.0.0.1"
PORT = "3306"
DATABASE = "test"
USERNAME = "root"
PASSWORD = "123456"
DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)SQLALCHEMY_DATABASE_URI = DB_URI
SQLALCHEMY_TRACK_MODIFICATIONS = False

app.py

from flask import Flask
from exts import db
import configapp = Flask(__name__)# 从配置文件获取配置参数
app.config.from_object(config)# 懒加载进行初始化app
db.init_app(app)

Flask-Migrate

Flask-Migrate是一个为Flask应用处理SQLAlchemy数据库迁移的扩展，使得可以通过Flask的命令行接口或者Flask-Scripts对数据库进行操作。

安装命令

pip install flask-migrate

配置flask-migrate

manage.py

from flask_script import Manager
from app import app
from flask_migrate import Migrate,MigrateCommand
from exts import dbmanager = Manager(app)# 用来绑定app和flask_migrate的
Migrate(app,db)# 添加Migrate的所有子命令到db下
manager.add_command("db",MigrateCommand)if __name__ == '__main__':manager.run()

使用init命令创建迁移仓库

python manage.py db init

使用migrate命令将模型映射到文件中

python manage.py db migrate
# python manage.py db migrate -m "initial migratetion"

使用upgrade命令将文件数据映射到数据库中

python manage.py db upgrade

使用downgrade命令回滚迁移中的数据库改动

python manage.py db downgrade version(上一个版本的版本号)

更多命令

python manage.py db --help

总步骤

python manage.py db init
python manage.py db migrate
python manage.py db upgrade# 改变字段后重复2~3步骤# 回滚操作
python manage.py db downgrade version
# python manage.py db downgrade 289402d590c2