Web后端学习笔记 Flask（7）数据库_db.session.query

高级查询：

1. group_by

根据某个字段进行分组，比如说需要根据某个字段分组，来统计每组有多少人。

2. having

having是对查询结果进一步过滤，比如只想看到未成年人的数量，那么首先可以对年龄进行分组统计人数，然后再对分组进行having过滤。

3. join查询分为两种，一种是inner join, 另一种是outer join。默认的是inner join,如果指定的是left join或者right join则为outer join。如果想要查询user及其对应的address.

在数据库中插入测试数据：

# -*- coding: utf-8 -*-
 
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func, Enum
from sqlalchemy import Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from datetime import datetime
import random
from sqlalchemy import Table
import time
 
 
HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"
 
# dialect+driver://username:password@host:port/database
 
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)
 
# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类
 
db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中
 
 
class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    age = Column(Integer, default=0)
    gender = Column(Enum("male", "female", "secret"), default="secret")
 
    def __repr__(self):
        return str(self.id) + " | " + self.username
 
 
# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user1 = User(username="tom", age=88, gender="male")
# user2 = User(username="kit", age=22, gender="female")
# user3 = User(username="jane", age=23, gender="female")
# user4 = User(username="janny", age=45, gender="male")
# user5 = User(username="Austing", age=12, gender="male")
# user6 = User(username="Cart", age=34, gender="female")
# user7 = User(username="Buck", age=34, gender="secret")
# user8 = User(username="Buck", age=34, gender="secret")
 
 
# db_session.add_all([user1, user2, user3, user4,
#                     user5, user6, user7, user8])
#
# db_session.commit()
 
# 统计每个年龄段的人数 group_by
result = db_session.query(User.age, func.count(User.id)).group_by(User.age).all()
print(result)
 
# having，针对产找出来的数据进行再一层的过滤 having
result = db_session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age > 20).all()
print(result)
 

join操作：

result = db_session.query(User.username, func.count(Article.id)).join(Article, User.id == Article.uid).group_by(User.id).order_by(func.count(Article.id)).desc().all()
print(result)

subquery()实现复杂的查询：
在数据库中插入测试数据：

例如，需要查找和一个人城市相同的人，或者年龄相同的人

原生的sql语句如下所示：

多次查询虽然也可以实现子查询的功能，但是多次查询会影响数据库的性能，如果网站的访问量过大的话，会造成网站性能不好。因此应该尽量采用子查询对数据库查询进行优化。子查询能够一次完成查询，避免多次查询数据库。

在sqlalchemy中实现子查询需要通过以下几个步骤：

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

2. 在子查询中，将需要用到的字段同构label取一个别名。

3. 在父查询中，如果需要使用子查询的字段，那么可以通过子查询的变量上的c属性获得。

示例代码：

# -*- coding: utf-8 -*-
 
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func, Enum
from sqlalchemy import Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from datetime import datetime
import random
from sqlalchemy import Table
import time
 
 
HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"
 
# dialect+driver://username:password@host:port/database
 
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)
 
# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类
 
db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中
 
 
class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    age = Column(Integer, default=0)
    city = Column(String(50), nullable=False)
 
    def __repr__(self):
        return str(self.id) + " | " + self.username + " | " + str(self.age) + " | " + self.city
 
 
# Base.metadata.drop_all()
# Base.metadata.create_all()
#
# user1 = User(username="tom", age=88, city="北京")
# user2 = User(username="kit", age=22, city="上海")
# user3 = User(username="jane", age=23, city="常山")
# user4 = User(username="janny", age=45, city="长沙")
# user5 = User(username="Austing", age=12, city="香港")
# user6 = User(username="Cart", age=34, city="西安")
# user7 = User(username="Buck", age=34, city="上海")
# user8 = User(username="Buck", age=34, city="北京")
 
 
# db_session.add_all([user1, user2, user3, user4,
#                     user5, user6, user7, user8])
#
# db_session.commit()
 
# 两次查询的方式得到结果，效率低
user = db_session.query(User).filter(User.username == "tom").first()
users = db_session.query(User).filter(User.city == user.city).all()
print(users)
 
# 通过子查询的方式实现
# 通过.label()给查询到的字段起一个新的名字
sub_res = db_session.query(User.city.label("city")).filter(User.username == "tom").subquery()
res = db_session.query(User).filter(User.city == sub_res.c.city)
print(res)

Flask-sqlalchemy插件介绍：

flask-sqlalchemy相当于将sqlalchemy结合到flask中，并且对初始化数据，连接数据库，以及一些包的导入统一做了封装，进行进一步的简化，使得更加易于使用。

1. 数据库连接

定义好数据库连接字符串：

from flask import Flask, url_for
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
 
HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "cms"
USERNAME = "root"
PASSWORD = "root1234"
# dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)
app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI
 
db = SQLAlchemy(app)    # 初始化数据库 
 
app.config["TEMPLATE_AUTO_RELOAD"] = True

2. 创建orm模型

    不需要再创建基类，不需要再导入各种数据类型，直接使用db下面相应的属性名即可

3. 将orm模型映射到数据库

    省略

4. 使用session

    省略

5. 查询数据

from flask import Flask, url_for
from flask_sqlalchemy import SQLAlchemy
 
app = Flask(__name__)
 
HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "flask_sqlalchemy"
USERNAME = "root"
PASSWORD = "root1234"
# dialect+driver://username:password@host:port/database
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)
app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI
app.config["TEMPLATE_AUTO_RELOAD"] = True
 
db = SQLAlchemy(app)    # 初始化数据库
 
 
class Person(db.Model):
    __tablename__ = "person"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
 
    def __repr__(self):
        return str(self.id) + " | " + self.name
 
 
db.drop_all()
db.create_all()
 
 
p1 = Person(name="zhang")
p2 = Person(name="wang")
 
db.session.add(p2)
db.session.commit()
 
res = db.session.query(Person).all()
print([p for p in res])
 
 
@app.route('/')
def hello_world():
    print(url_for("news.news_list"))
    return 'Hello World!'
 
 
if __name__ == '__main__':
    app.run(debug=True)

在flask-sqlalchemy中，__tablename__也可以省略不写，会自动进行添加（但是不推荐这种写法）

alembic数据库迁移工具基本使用：

        在使用sqlalchemy的过程中，在定义好orm模型之后，是通过.create_all()方法进行数据表的创建，创建好之后的不能再进行变更。但是在实际的开发中，可能需要在已经创建好的数据表中删除或者增加字段，而alembic就可以解决表之间的映射问题。对于orm模型新增的字段，如果要将新增字段映射到数据表中，就需要通过alembic工具进行。

        alembic的使用方式优点类似于git，第一：所有命令以alembic开头，第二：alembic的迁移文件也是通过版本进行控制的。通过pip install alembic进行安装。

alembic使用：

1. 初始化alembic仓库，在pycharm中右键项目文件，选择open in terminate, 打开项目终端

初始化alembic仓库之后，会在项目文件夹中生成一个alembic文件夹：

2. 创建模型类。创建一个models.py文件，然后定义模型类：

# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, create_engine, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
 
 
HOST_NAME = "127.0.0.1"
PORT = "3306"
DATABASE = "alembic_demon"
USERNAME = "root"
PASSWORD = "root1234"
 
# dialect+driver://username:password@host:port/database
 
DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{database}".format(
    username=USERNAME, password=PASSWORD, host=HOST_NAME, port=PORT, database=DATABASE
)
 
# 创建数据库引擎
engine = create_engine(DB_URI)
Base = declarative_base(engine)   # 通过继承Base创建ORM模型/ 创建基类
 
db_session = sessionmaker(engine)()   # 创建会话，才能实现增删改查
# 1. 创建ORM模型，必须继承自SQLAlchemy：
# 2. 在ORM中创建一些属性，跟表中的字段一一映射，这些属性必须是SQLAlchemy提供的数据类型
# 3. 将创建好的ORM模型映射到数据库中
 
 
class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)
    password = Column(String(100), nullable=False)
    age = Column(Integer, default=0)
    country = Column(String(50), nullable=False)
 
    def __repr__(self):
        return str(self.id) + " | " + self.username
 
 
# class Article(Base):
#     __tablename__ = "article"
#     id = Column(Integer, primary_key=True, autoincrement=True)
#     title = Column(String(100), nullable=False)
#     content = Column(Text, nullable=False)
 
 
# ROM->迁移脚本->映射到数据库中
 
 

3. 修改配置文件：

在alembic.ini中设置数据库连接，设置的格式为：

sqlalchemy.url = driver://user:pass@localhost/dbname

sqlalchemy.url = mysql+pymysql://root:root1234@localhost/alembic_demon?charset=utf8
# 指定连接数据的方式

为了使得模型类能够更新到数据库当中，需要在env.py中设置target_metadata，默认target_metadata=None，需要使用sys啊把当前的项目路基那个导入到path中：

import sys,os
sys.path.append(os.path.dirname(os.path.dirname(__file__)))   # 添加models的路径
import models

target_metadata = models.Base.metadata

4. 自动生成迁移脚本，通过下面的命令：

alembic revision --autogenerate -m "message"

在生成迁移脚本的时候，出现了如下的报错：

应该是某处的编码问题，查找报错的文件，发现在下面这个地方调用了一个read()方法：

需要将read()方法的encoding参数修改为utf8.

还有这个地方，也调用了read()方法

将encoding参数修改为utf8.

anaconda/Lib/configeparser中定义了这个read()方法：这里的read()方法有一个参数encoding：

此时，调用迁移脚本生成命令：

此时在alembic/versions目录下会生成迁移脚本：

5. 更新数据库：
alembic upgrade head

6. 如果需要在数据表中添加新的字段，则首先对orm模型进行修改，重复4，5步骤

命令和参数：

1. init：创建一个alembic仓库。
2. revision：创建一个新的版本文件。
3. --autogenerate：自动将当前模型的修改，生成迁移脚本。
4. -m：本次迁移做了哪些修改，用户可以指定这个参数，方便回顾。
5. upgrade：将指定版本的迁移文件映射到数据库中，会执行版本文件中的upgrade函数。如果有多个迁移脚本没有被映射到数据库中，那么会执行多个迁移脚本。
6. [head]：代表最新的迁移脚本的版本号。
7. downgrade：会执行指定版本的迁移文件中的downgrade函数。
8. heads：展示head指向的脚本文件版本号。
9. history：列出所有的迁移版本及其信息。
10. current：展示当前数据库中的版本号。

alembic中的经典错误：
1. FAILED: Target database is not up to date.
    原因：主要是heads和current不相同。current落后于heads的版本。
    解决办法：将current移动到head上。alembic upgrade head
2. FAILED: Can't locate revision identified by '77525ee61b5b'
    原因：数据库中存的版本号不在迁移脚本文件中
    解决办法：删除数据库的alembic_version表中的数据，重新执行alembic upgrade head
3. 执行`upgrade head`时报某个表已经存在的错误：
    原因：执行这个命令的时候，会执行所有的迁移脚本，因为数据库中已经存在了这个表。然后迁移脚本中又包含了创建表的代      码。
    解决办法：（1）删除versions中所有的迁移文件。（2）修改迁移脚本中创建表的代码，使用pass语句

Flask-SqlAlchemy下使用alembic:

使用alembic管理Flask项目下的数据库：

1. 在Flask项目下打开终端

2. 创建初始的alembic仓库

3. 修改配置文件

    alembic.ini文件修改方法不变

    env.py文件中导入app文件的路径，以及app文件：

import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(__file__)))
import app
 
target_metadata = app.db.Model.metadata     # 完成绑定

后面生成迁移脚本以及映射数据库的方法不变。

Fask-script插件介绍：

Falsk-script的作用是可以通过命令行的形式来操作flask，例如通过命令跑一个开发版的服务器，设置数据库，定时任务等。可以通过pip install flask-script来安装。

flask-script使用：

通过新建manager.py文件，导入Manager和新建manager

from flask_script import Manager
 
manager = Manager(app)
 
 
# 没有参数
@manager.command
def greet():
    print("Hello")
 
 
if __name__ == "__main__":
    manager.run()

在命令行中通过如下的命令直接运行greet()函数：

如果函数中有参数，需要使用option装饰器进行传参，有几个参数就用几个option装饰器

# 如果需要传递参数,则需要使用option装饰器
@manager.option("-u", "--username", dest="username")
@manager.option("-a", "--age", dest="age")
def info_list(username, age):
    print("The name is {}, and the age is".format(username, age))

option中，第一个参数为命令行中参数的简写，第二个为命令行中的参数，第三个参数是接受参数值的变量。

通过如下的命令运行info_list()函数:

实际应用，例如要给CMS管理系统添加一个管理员：

1. 首先创建数据库，这里使用alembic实现，回顾一下前面的知识

from flask import Flask, url_for
from flask_sqlalchemy import SQLAlchemy
import config
 
app = Flask(__name__)
app.config.from_object(config)
db = SQLAlchemy(app)    # 初始化数据库
 
 
class BackEndUser(db.Model):
    __tablename__ = "back_user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False)
    email = db.Column(db.String(50), nullable=False)
 
 
@app.route('/')
def hello_world():
    print(url_for("news.news_list"))
    return 'Hello World!'
 
 
if __name__ == '__main__':
    app.run(debug=True)

2. 使用flask-script定义数据插入的函数：

# -*- coding: utf-8 -*-
from app import app, BackEndUser, db
from flask_script import Manager
 
manager = Manager(app)
 
 
@manager.option("-u", "--username", dest="username")
@manager.option("-e", "--email", dest="email")
def add_backend_user(username, email):
    user = BackEndUser(username=username, email=email)
    db.session.add(user)
    db.session.commit()
 
 
if __name__ == "__main__":
    manager.run()
 

3. 通过命令调用：

查看插入的数据：

这种添加管理员的方式，更加的安全与优雅

-------------------------------------------------------------------------------------------------------------------------------------------------

可以用flask-script对数据库的操作进行统一的管理，例如,创建db_manage.py文件，用于对数据库操作的方法做统一的封装：

# -*- coding: utf-8 -*-
from flask_script import Manager
 
db_manager = Manager()
 
 
@db_manager.command
def init():
    print("数据库初始化创建完成")
 
 
@db_manager.command
def revision():
    print("迁移脚本初始化完成")
 
 
@db_manager.command
def upgrade():
    print("数据库映射完成")
 

再将创建的db_manager添加到主Manager中，在manager.py文件中加入如下的代码即可：

from db_manager import db_manager
 
manager = Manager(app)
manager.add_command("db", db_manager)    # 添加子命令

然后通过下面的命令就可以调用db下面的子命令：

项目结构重构：

flask-migrate插件，底层基于alembic，相当于对alembic进行了一层封装，并集成到flask中，使用起来更加方便，类似于SQLAlchemy和flask-sqlalchemy。

python中的循环引用：创建一个第三方的文件，避免循环引用

例如，下面的app文件和models文件，models文件需要导入app文件中的db，app文件需要到models文件中User，就会造成文件之间循环引用的问题：

app.py文件：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
import config
from models import User
 
app = Flask(__name__)
app.config.from_object(config)
db = SQLAlchemy(app)
 
 
@app.route('/')
def hello_world():
    return 'Hello World!'
 
 
@app.route('/profile/')
def profile():
    return "profile page"
 
 
if __name__ == '__main__':
    app.run()

models.py文件：

# -*- coding: utf-8 -*-
from app import db
 
 
class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False)

这样就会报错：

解决方案：

创建一个第三方的文件：

例如新建exts.py文件，就可以解决循环引用的问题：

exts.py文件：

# -*- coding: utf-8 -*-
 
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()      # db没有绑定app,此时db就拿不到app中数据库的配置文件

models.py文件：

# -*- coding: utf-8 -*-
from exts import db
 
 
class User(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(50), nullable=False)

app.py文件：

from flask import Flask
import config
from models import User
from exts import db
 
app = Flask(__name__)
app.config.from_object(config)
db.init_app(app)    # db获取app中数据库的连接方式
 
 
@app.route('/')
def hello_world():
    return 'Hello World!'
 
 
@app.route('/profile/')
def profile():
    return "profile page"
 
 
if __name__ == '__main__':
    app.run()

Flask-migrate详细介绍：

在实际的开发环境中，经常需要去修改数据库的行为，一般不回去直接修改数据库，而是将数据库的ORM模型进行修改，然后再把模型映射到数据库中。利用前面介绍的alembic可以实现此功能。在flask中，通过flask-migrate实现此功能，flask-migrate是基于alembic的一个封装，并将其集成到flask中。因此所有的数据库迁移操作以及上都是通过alembic完成的。

安装：pip install flask-migrate

实际使用：

基于上面的工程，新建一个manager.py的文件：

# -*- coding: utf-8 -*-
from flask_script import Manager
from app import app
from flask_migrate import MigrateCommand, Migrate    # 用来绑定app以及db
from exts import db
 
manager = Manager(app)    # 绑定app,db到flask-migrate
Migrate(app=app, db=db)   
 
# 添加falsk-migrate中子命令到db下：
manager.add_command("db", MigrateCommand)
 
 
if __name__ == "__main__":
    manager.run()
 
 

flask-migrate 常用命令：

1. python manager.py db init          

    初始化一个alembic仓库在项目文件夹下会生成一个Migration文件夹：其中的文件与alembic init alembic命令生成的类似

2. python manager.py db migrate    生成数据库的迁移文件

3. pyhon manager.py db upgrade    将迁移文件映射到数据库当中

如果需要修改模型，则重复2，3步骤即可

可以通过python manager.py db --help查看所有命令

flask-migrate注意事项：

from models import User   # 将需要映射的模型直接导入manager.py中即可