文章目录
- 1、简介
- 2、mysql连接设置
- 3、模型类字段类型
- 4、ORM使用方式
* - 4.1、先创建模型类，再迁移到数据库
- 4.2、用原生SQL创建数据库表，再编写模型类作映射
- 5、CRUD
* - 5.1、增
- 5.2、查
– - 5.3、改
- 5.4、删
- 6、事务
1、简介
SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM）工具。
而Flask-SQLAlchemy是Flask对SQLAlchemy进行了一个封装，目的于简化在Flask中SQLAlchemy的使用，
提供了有用的默认值和额外的助手来更简单地完成日常任务。
orm作用
1.省去自己拼写SQL，保证SQL语法的正确性一次编写可以适配多个数据库.
2.防止注入攻击.
3.在数据库表名或字段名发生变化时，只需修改模型类的映射，无需修改数据库操作的代码.
pip install flask-sqlalchemy
pip install mysqlclient
pip install flask_script
pip install flask_migrate ---2.7.0
2、mysql连接设置
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_script import Manager
from flask_migrate import Migrate,MigrateCommand
import pymysql
pymysql.install_as_MySQLdb()
class DatabaseConfig():
SQLALCHEMY_DATABASE_URI =
'mysql://root:123456@127.0.0.1:3306/sqlalchemyTest'
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLALCHEMY_ECHO = False
app = Flask(__name__)
manager = Manager(app)
app.config.from_object(DatabaseConfig)
db = SQLAlchemy(app)
Migrate(app,db)
manager.add_command("db",MigrateCommand)
3、模型类字段类型
类型名mysql中类型说明Integerint普通整数SmallIntegertinyint范围小的整数BigIntegerbigint不限精度的整数Floatfloat浮点数Numericdecimal普通整数Stringvarchar边长字符串Datedate时间Timedatetime日期时间Enumenum枚举值
4、ORM使用方式
4.1、先创建模型类，再迁移到数据库
优点：简单快捷，不用写sql
缺点：表结构发生变化的时候，容易发生迁移错误
sql_test.py
from main import db
from main import manager
class Student(db.Model):
__tablename__ = "student"
sid = db.Column("sid",db.Integer,primary_key=True,doc="学生id")
sname = db.Column("sname",db.String(4),nullable=False,doc="姓名")
gender = db.Column(db.Enum("男","女"),doc="性别")
phone = db.Column(db.String(11),nullable=False,doc="手机号")
1. grades = db.relationship("Grade",backref="stu")
2. grades =db.relationship("Grade",primaryjoin="Student.sid==foreign(Grade.student)",backref="stu")
courses = db.relationship("Course",secondary="student_course",backref="stu")
def __repr__(self):
sname = self.sname
phone = self.phone
return f"{sname}:{phone}"
class StudentCourse(db.Model):
id = db.Column(db.Integer,primary_key=True,doc="学生课程id")
sid = db.Column(db.Integer,db.ForeignKey("student.sid"),doc="学生id")
cid = db.Column(db.Integer,db.ForeignKey("course.cid"),doc="课程id")
class Course(db.Model):
__tablename__ = "course"
cid = db.Column("cid",db.Integer,primary_key=True,doc="课程id")
cname = db.Column("cname",db.String(4),nullable=False,doc="课程名")
teacher = db.Column("teacher",db.Integer,db.ForeignKey("teacher.tid"),doc="老师id")
grade = db.relationship("Grade",backref="cour")
class Teacher(db.Model):
__tablename__ = "teacher"
tid = db.Column(db.Integer,primary_key=True,doc = "老师id")
tname = db.Column(db.String(4),nullable=False,doc="老师姓名")
gender = db.Column(db.Enum("男", "女"), doc="性别")
phone = db.Column(db.String(11), nullable=False, doc="手机号")
course = db.relationship("Course",primaryjoin="Teacher.tid==foreign(Course.teacher)", backref="tea",uselist=False)
class Grade(db.Model):
__tablename__ = "grade"
id = db.Column(db.Integer,primary_key=True,doc="分数id")
score = db.Column(db.Float,default=0,doc="分数")
student = db.Column("student",db.Integer,db.ForeignKey("student.sid"),doc="学生id")
course = db.Column("course",db.Integer,db.ForeignKey("course.cid"),doc="课程id")
if __name__ == '__main__':
db.create_all()
4.2、用原生SQL创建数据库表，再编写模型类作映射
优点：可以很好的控制数据库表结构，避免发生迁移错误
缺点：需要编写sql语句
先创建数据库表，然后编写模型类
create table student(
id int primary key auto_increment comment "学生id",
sname varchar(4) not null comment "姓名",
gender enum("男","女") default "男" comment "性别",
phone char(11) not null comment "手机号"
) engine="innodb",charset="utf8",comment="学生表";
create table teacher(
id int primary key auto_increment comment "老师id",
tname varchar(4) not null comment "老师姓名",
gender enum("男","女") default "男" comment "性别",
phone char(11) not null comment "手机号"
) engine="innodb" default charset=utf8 comment="教师表";
create table course(
id int primary key auto_increment comment "课程id",
cname varchar(10) not null comment "课程名",
teacher int default null comment "老师id",
constraint c_t_key foreign key(teacher) references teacher(id)
) engine="innodb" default charset=utf8 comment="课程表";
create table grade(
id int primary key auto_increment comment "分数id",
score float default 0 comment "分数",
student int default null comment "学生id",
course int default null comment "课程id",
constraint g_s_key foreign key(student) references student(id),
constraint g_c_key foreign key(course) references course(id)
) engine="innodb" default charset=utf8 comment "分数表";
create table student_course(
id int primary key auto_increment comment "学生课程id",
sid int default null comment "学生id",
cid int default null comment "课程id",
constraint s_s_c foreign key(sid) references student(id),
constraint c_s_c foreign key(cid) references course(id)
) engine="innodb" default charset=utf8 comment="学生课程表";
5、CRUD
5.1、增
student = Student(sname = "张三",gender="男",phone="19971251765")
db.session.add(student)
db.session.commit()
s1 = Student(sname = "李四",gender="男",phone="19971251766")
s2 = Student(sname = "王五",gender="男",phone="19971251767")
s3 = Student(sname = "赵六",gender="女",phone="19971251768")
s4 = Student(sname = "刘七",gender="男",phone="19971251769")
db.session.add_all([s1,s2,s3,s4])
db.session.commit()
5.2、查
常规查询
s = Student.query.all()
s = Student.query.get(1)
s = Student.query.first()
s = Student.query.filter_by(gender="男").first()
ss = Student.query.filter_by(gender="男").all()
s = Student.query.filter(Student.gender=="女").first()
逻辑查询
from sqlalchemy import or_
User.query.filter(or_(Student.sname=='张三', Student.age==20)).all()
from sqlalchemy import and_
User.query.filter(and_(Student.sname=='张三', Student.age==20)).all()
from sqlalchemy import not_
User.query.filter(not_(Student.sname=='张三', Student.age==20)).all()
offset 偏移查询
Student.query.offset(2).all()
limit
s = Student.query.limit(3).all()
order_by
s = Student.query.order_by(Student.sid).all()
s = Student.query.order_by(Student.sid.desc()).all()
load_only 指定字段查询
from sqlalchemy.orm import load_only
User.query.options(load_only(Student.sname, Student.age)).filter_by(sid=1).first()
分组聚合
from sqlalchemy import func
db.session.query(Grade.student, func.count(Grade.student)).filter(Grade.score>=60).group_by(Grade.student).all()
复合查询
Student.query.filter(Student.age > 20).order_by(Student.sid.desc()).offset(2).limit(5).all()
多表关联查询
class Course(db.Model):
teacher = db.Column("teacher",db.Integer,db.ForeignKey("teacher.tid"),doc="老师id")
class Teacher(db.Model):
course = db.relationship("Course",backref="tea"，uselist=False)
c = Course.query.first()
t = c.tea
t = Teacher.query.first()
c = t.course
class Student(db.Model):
grades = db.relationship("Grade",backref="stu")
class Grade(db.Model):
student = db.Column("student",db.Integer,db.ForeignKey("student.sid"),doc="学生id")
g = Grade.query.first()
s = g.stu
s = Student.query.first()
g = s.grades(列表)
class Student(db.Model):
courses = db.relationship("Course",secondary="student_course",backref="stu")
class StudentCourse(db.Model):
sid = db.Column(db.Integer,db.ForeignKey("student.sid"),doc="学生id")
cid = db.Column(db.Integer,db.ForeignKey("course.cid"),doc="课程id")
class Course(db.Model):
... ...
s = Student.query.first()
cs = s.courses (列表)
for c in cs:
for stu in c.stu:
print(stu.sname)
t = Teacher.query.first()
c = t.course
关联字段联合查询
from sqlalchemy.orm import load_only,contains_eager
s = Teacher.query.join(Teacher.course).options(load_only(Teacher.tname),contains_eager(Teacher.course).load_only(Course.cname)).first()
print(s.tname,s.course.cname)
5.3、改
c = Student.query.filter(Student.sname=="张三").update({"sname":"章三"})
db.session.commit()
stu = Student.query.filter_by(sname="章三").first()
stu.sname = "张三"
db.session.add(stu)
db.session.commit()
5.4、删
Student.query.filter(Student.sid == 1).delete()
db.session.commit()
6、事务
try:
teacher = Teacher(tname='老王',gender="男",phone="12345678910")
db.session.add(teacher)
db.session.flush()
course = Course(cname="数学",teacher=teacher.tid)
db.session.add(course)
db.session.commit()
except:
db.session.rollback()
Original: https://blog.csdn.net/qq_46423987/article/details/126550074
Author: lTimej
Title: flask-sqlalchemy操作mysql
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/748528/
转载文章受原作者版权保护。转载请注明原作者出处！