flask使用sqlalchemy连接mysql flask sqlalchemy教程

一、前言

本篇依然是面向有一定经验的开发者。本篇不会讲原理，一切以实用为主。

本篇主要讲述以下内容：

• flask-sqlalchemy的搭建
• 简单查询
• 查询指定字段
• 排序
• 分页
• 聚合函数
• 连表查询
• 修改
• 删除

 

二、flask-sqlalchemy练习环境准备

1.准备好我们要用的数据，这里我使用的是mysql

--建表
--学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

2.搭建flask-sqlalchemy环境

首先把flask和flask-sqlalchemy都装上

pip install flask

pip install flask-sqlalchemy

新建configs.py：

SQLALCHEMY_DATABASE_URI = "mysql://用户名:密码@数据库地址/数据库名称?charset=utf8"
SQLALCHEMY_TRACK_MODIFICATIONS = True

新建程序入口main.py，并编写对应表的model:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# 创建Flask应用
app = Flask(__name__)
# 加载配置文件
app.config.from_object("configs")
# 创建sqlalchemy对象
db = SQLAlchemy(app)


class Student(db.Model):
    """
    学生表
    """
    __tablename__ = "Student"

    s_id = db.Column(db.String(20), primary_key=True)
    s_name = db.Column(db.String(20), nullable=False, default="")
    s_birth = db.Column(db.String(20), nullable=False, default="")
    s_sex = db.Column(db.String(10), nullable=False, default="")


class Course(db.Model):
    """
    课程表
    """
    __tablename__ = "Course"

    c_id = db.Column(db.String(20), primary_key=True)
    c_name = db.Column(db.String(20), nullable=False, default="")
    t_id = db.Column(db.String(20), nullable=False, default="")


class Teacher(db.Model):
    """
    教师表
    """
    __tablename__ = "Teacher"

    t_id = db.Column(db.String(20), primary_key=True)
    t_name = db.Column(db.String(20), nullable=False, default="")


class Score(db.Model):
    """
    成绩表
    """
    __tablename__ = "Score"

    s_id = db.Column(db.String(20), primary_key=True)
    c_id = db.Column(db.String(20), nullable=False, default="")
    s_score = db.Column(db.Integer)


if __name__ == '__main__':
    pass

三、简单查询

1.查询所有学生，并打印姓名、性别

if __name__ == '__main__':
    # 查询所有学生，并打印其名称
    students = Student.query.all()
    for student in students:
        print(student.s_name,student.s_sex)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
赵雷 男
钱电 男
孙风 男
李云 男
周梅 女
吴兰 女
郑竹 女
王菊 女

Process finished with exit code 0

 

2.查询所有女生，并打印姓名、性别

# 查询所有女生，并打印姓名、性别
students = Student.query.filter(Student.s_sex == "女")
for student in students:
    print(student.s_name, student.s_sex)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
周梅 女
吴兰 女
郑竹 女
王菊 女

Process finished with exit code 0

3.查询性别为“男”，生日为“1990”开头的学生，并打印姓名、性别、生日

# 查询性别为“男”，生日为“1990”开头的学生，并打印姓名、性别、生日
students = Student.query.filter(Student.s_sex == "男", Student.s_birth.startswith("1990"))
for student in students:
    print(student.s_name, student.s_sex, student.s_birth)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
赵雷 男 1990-01-01
钱电 男 1990-12-21
孙风 男 1990-05-20
李云 男 1990-08-06

Process finished with exit code 0

4.查询姓名为“赵雷”的单个对象

# 查询姓名为“赵雷”的单个对象
student = Student.query.filter(Student.s_name == "赵雷").first()

5.查询姓名为“赵雷”或者“李云”的学生

# 查询姓名为“赵雷”或者“李云”的学生
from sqlalchemy import or_

students = Student.query.filter(
    or_(
        Student.s_name == "赵雷",
        Student.s_name == "李云"
    )
)
for student in students:
    print(student.s_name, student.s_sex, student.s_birth)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
赵雷 男 1990-01-01
李云 男 1990-08-06

Process finished with exit code 0

四、查询指定字段

有时候我们并不需要一次性查询出所有字段，为了提高性能，我们可以查询指定字段

1.只查询学生的姓名

# 只查询学生的姓名
students = Student.query.with_entities(
    Student.s_name
).all()
for student in students:
    print(student.s_name)

2.给字段取别名

# 给字段取别名
students = Student.query.with_entities(
    Student.s_name.label("name")
).all()
for student in students:
    print(student.name)

五、排序

1.按生日正序排序

# 按生日正序排序
students = Student.query.order_by(Student.s_birth.asc())
for student in students:
    print(student.s_name, student.s_sex, student.s_birth)

2.按生日倒序排序

# 按生日倒序排序
students = Student.query.order_by(Student.s_birth.desc())
for student in students:
    print(student.s_name, student.s_sex, student.s_birth)

六、分页

sqlalchemy分页可以直接用python的切片语法，相当于sql的limit

1.按生日倒序排序后，查询前5个学生

# 按生日倒序排序后，查询前5个学生
students = Student.query.order_by(Student.s_birth.desc())[0:5]
for student in students:
    print(student.s_name, student.s_sex, student.s_birth)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
吴兰 女 1992-03-01
周梅 女 1991-12-01
钱电 男 1990-12-21
李云 男 1990-08-06
孙风 男 1990-05-20

Process finished with exit code 0

七、聚合函数

1.查询Score表中c_id="01"的平均分

from sqlalchemy import func

# 查询Score表中c_id="01"的平均分
avg_score = Score.query.with_entities(
    func.avg(Score.s_score)
).filter(
    Score.c_id == "01"
).scalar()
print(avg_score)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
64.5000

Process finished with exit code 0

2.查询Score表中s_id="01"的总分

# 查询Score表中s_id="01"的总分
sum_score = Score.query.with_entities(
    func.sum(Score.s_score)
).filter(
    Score.s_id == "01"
).scalar()
print(sum_score)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
269

Process finished with exit code 0

其他聚合函数的用法类似，我就不一一列举了

八、连表查询

1.查询每条Score对应的学生姓名、老师姓名、课程名

# 查询每条Score对应的学生姓名、老师姓名、课程名
qs = Score.query.with_entities(
    Score.s_score,
    Student.s_name,
    Teacher.t_name,
    Course.c_name
).join(
    Student, Student.s_id == Score.s_id
).join(
    Course, Course.c_id == Score.c_id
).join(
    Teacher, Teacher.t_id == Course.t_id
)

for record in qs:
    print(record.s_score, record.s_name, record.t_name, record.c_name)

运行结果：

D:\softwave\python38\python.exe D:/work/flask_demo/main.py
80 赵雷 李四 语文
90 赵雷 张三 数学
99 赵雷 王五 英语
70 钱电 李四 语文
60 钱电 张三 数学
80 钱电 王五 英语
80 孙风 李四 语文
80 孙风 张三 数学
80 孙风 王五 英语
50 李云 李四 语文
30 李云 张三 数学
20 李云 王五 英语
76 周梅 李四 语文
87 周梅 张三 数学
31 吴兰 李四 语文
34 吴兰 王五 英语
89 郑竹 张三 数学
98 郑竹 王五 英语

Process finished with exit code 0

九、修改

1.把赵雷的名字改成赵日天

# 把赵雷的名字改为赵日天
student_sz = Student.query.filter(Student.s_name == "赵雷").first()
student_sz.s_name = "赵日天"
db.session.commit()

十、删除

1.把赵日天删除

# 把赵日天删除
student_sz = Student.query.filter(Student.s_name == "赵日天").first()
db.session.delete(student_sz)
db.session.commit()