from flask import Flask,jsonify,render_template from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) class Config(): # DEBUG调试模式 DEBUG = True # json多字节转unicode编码 JSON_AS_ASCII = False # 数据库链接配置 # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码" SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4" # 动态追踪修改设置，如未设置只会提示警告 SQLALCHEMY_TRACK_MODIFICATIONS = True # 查询时会显示原始SQL语句 SQLALCHEMY_ECHO = True app.config.from_object(Config) db = SQLAlchemy() db.init_app(app) """创建模型类""" class Student(db.Model): __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True,comment="主键ID") name = db.Column(db.String(250), comment="姓名") age = db.Column(db.Integer, comment="年龄") sex = db.Column(db.Boolean, default=False, comment="性别") money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包") def __repr__(self): return self.name class Teacher(db.Model): __tablename__ = "tb_teacher" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), comment="姓名") sex = db.Column(db.Boolean, default=False, comment="性别") option = db.Column(db.Enum("讲师","助教","班主任"), default="讲师", comment="教职") def __repr__(self): return self.name class Course(db.Model): __tablename__ = "tb_course" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), unique=True, comment="课程名称") price = db.Column(db.Numeric(6, 2)) def __repr__(self): return self.name @app.route("/") def index(): """数据库进阶操作""" """filter设置判断条件 == 判断相等 >= < > <= != """ # student = Student.query.filter(Student.name=="xiaohui32号").first() # if student is None: # return jsonify({"error":"100404","errmsg":"没有该学生信息！"}) # print(student) """filter设置模糊查询""" # like模糊条件（最好只用于少量数据） # 模型.字段.like("%值%") 等价于 模型.字段.contains("值") 包含xxx # 模型.字段.like("值%") 等价于 模型.字段.startswith("值") 以xxx开头 # 模型.字段.like("%值") 等价于 模型.字段.endswith("值") 以xxx结尾 # 模型.字段.like("__") 值长度为2个字符的.几个下划线代表几个字符 # student_list = Student.query.filter(Student.name.like("%xiaohui%")).all() # student_list = Student.query.filter(Student.name.startswith("xiao")).all() # student_list = Student.query.filter(Student.name.like("________")).all() """filter_by设置精确条件查找数据""" # filter_by 只支持一个等号作为判断条件，而且字段左边不需要声明模型类名 # 可以用于获取一条数据，也可以获取多条数据 # student = Student.query.filter_by(money=1000).first() # print(student) """filter多条件查询""" # 多条件需要基于逻辑运算来编写，当然，可以其他的声明方式 """and_ 并且, 与""" # from sqlalchemy import and_ # # 方式1: # student_list1 = Student.query.filter(Student.money==1000,Student.sex==True).all() # # 方式2: # student_list2 = Student.query.filter(and_(Student.money==1000,Student.sex==True)).all() # # print(student_list1, student_list2) """or_ 或者，或""" # from sqlalchemy import or_ # student_list = Student.query.filter( or_(Student.age > 17, Student.age < 15) ).all() # print(student_list) """not_ 排除，非""" from sqlalchemy import not_ # student_list = Student.query.filter(not_(Student.age > 17)).all() # print(student_list) """filter值范围查询""" # 查询年龄=15或者17或者19的 # student_list = Student.query.filter(Student.age.in_([15,17,19])).all() # print(student_list) """order_by结果排序""" # order_by(模型.字段.desc()) db.desc(模型.字段) 倒序 # order_by(模型.字段.asc()) db.asc(模型.字段) 升序 # student_list = Student.query.order_by(db.desc(Student.money)).all() # student_list = Student.query.order_by(Student.money.desc()).all() # print(student_list) """count 统计结果数量""" # ret = Student.query.filter(Student.age>17).count() # print(ret) """limit 结果数量进行限制""" """offse 对查询开始位置进行设置""" # 对学生的钱包进行从大到小排名，第3-第5名的学生 # student_list = Student.query.order_by(Student.money.desc()).offset(2).limit(3).all() #offset从下标2开始取（limit）三个 # print(student_list) """paginate分页器""" # paginate(page=当前页码, per_page=每一页数据量, max_per_page=每一页最大数据量) # 当前页码，默认是从request.args["page"]，如果当前参数没有值，则默认为1 # 每一页数据量，默认是100条 # 因为分页器有提供了一个 request.args.["per_page"]给客户端设置每一页数据量，所以再次限定客户端最多能设置的每一页数据量 pagination = Student.query.filter(Student.sex==True).paginate(per_page=1) print(pagination) return render_template("list.html",pagination=pagination) # print( pagination.items ) # 获取当前页数据量 # print( pagination.has_next ) # 如果还有下一页数据，则结果为True # print( pagination.has_prev ) # 如果有上一页数据，则结果为True # print( pagination.page ) # 当前页页码 request.args.get("page",1) # print( pagination.total ) # 本次查询结果的数据总量[被分页的数据量总数] # print( pagination.pages ) # 总页码 # print( pagination.prev() ) # 上一页的分页器对象，如果没有上一页，则默认为None # print( pagination.next() ) # 下一页的分页器对象，如果没有下一页，则默认为None # if pagination.has_next: # print( pagination.next().items ) # 下一页的数据列表 return "Ok" if __name__ == '__main__': # with app.app_context(): # db.create_all() # 根据模型创建所有的数据表 # # db.drop_all() # 删除模型对应的所有数据表 app.run()
class Student(db.Model): """个人信息主表""" .... # 关联属性，这个不会被视作表字段，只是模型的属性。 # 因为StudentInfo和Student是一对一的关系，所以uselist=False表示关联一个数据 info = db.relationship("StudentInfo",uselist=False,backref="own") class StudentInfo(db.Model): """个人信息附加表""" # 外键， # 如果是一对一，则外键放在附加表对应的模型中 # 如果是一对多，则外键放在多的表对象的模型中 uid = db.Column(db.Integer, db.ForeignKey(Student.id),comment="外键")
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) class Config(): # DEBUG调试模式 DEBUG = True # json多字节转unicode编码 JSON_AS_ASCII = False # 数据库链接配置 # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码" SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4" # 动态追踪修改设置，如未设置只会提示警告 SQLALCHEMY_TRACK_MODIFICATIONS = True # 查询时会显示原始SQL语句 SQLALCHEMY_ECHO = True app.config.from_object(Config) db = SQLAlchemy() db.init_app(app) """创建模型类""" class Student(db.Model): __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True,comment="主键ID") name = db.Column(db.String(250), comment="姓名") age = db.Column(db.Integer, comment="年龄") sex = db.Column(db.Boolean, default=False, comment="性别") money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包") # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！ # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性 info = db.relationship("StudentInfo", backref="own", uselist=False) def __repr__(self): return self.name class StudentInfo(db.Model): __tablename__ = "tb_student_info" id = db.Column(db.Integer, primary_key=True, comment="主键ID") sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生") address = db.Column(db.String(255), nullable=True, comment="家庭住址") mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话") def __repr__(self): return self.own.name class Teacher(db.Model): __tablename__ = "tb_teacher" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), comment="姓名") sex = db.Column(db.Boolean, default=False, comment="性别") option = db.Column(db.Enum("讲师","助教","班主任"), default="讲师", comment="教职") def __repr__(self): return self.name class Course(db.Model): __tablename__ = "tb_course" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), unique=True, comment="课程名称") price = db.Column(db.Numeric(6, 2)) def __repr__(self): return self.name @app.route("/") def index(): """添加数据""" # student = Student( # name= "xiaohuang", # age = 13, # sex = True, # money = 1000, # info = StudentInfo( # mobile="13312345678", # address="南京市xxx号" # ) # ) # db.session.add(student) # db.session.commit() """查询/读取""" # student = Student.query.first() # print(student.age) # print(student.info.mobile) # # student = StudentInfo.query.filter(StudentInfo.mobile=="13312345678").first() # print(student.own.name) """修改更新""" # student = Student.query.get(1) # student.age = 18 # student.info.address = "北京市朝阳区xxx号" # db.session.commit() """删除""" # student = Student.query.get(2) # db.session.delete(student.info) # 先删除外键模型，再删主模型 # db.session.delete(student) # db.session.commit() return "Ok" if __name__ == '__main__': # with app.app_context(): # db.drop_all() # db.create_all() app.run()
一对多
class Teacher(db.Model): ... # 关联属性，一的一方添加模型关联属性 course = db.relationship("Course", uselist=True, backref="teacher",lazy='dynamic') class Course(db.Model): ... # 外键，多的一方模型中添加外间 teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id))
-
-
第二个参数backref为类Teacher申明新属性的方法
-
第三个参数lazy决定了什么时候SQLALchemy从数据库中加载数据
-
lazy='subquery'，查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了。
-
lazy=True或lazy='select'，查询当前数据模型时，不会把外键模型的数据查询出来，只有操作到外键关联属性时，才进行连表查询数据[执行SQL]
-
-
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) class Config(): # DEBUG调试模式 DEBUG = True # json多字节转unicode编码 JSON_AS_ASCII = False # 数据库链接配置 # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码" SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4" # 动态追踪修改设置，如未设置只会提示警告 SQLALCHEMY_TRACK_MODIFICATIONS = True # 查询时会显示原始SQL语句 SQLALCHEMY_ECHO = True app.config.from_object(Config) db = SQLAlchemy() db.init_app(app) """创建模型类""" class Student(db.Model): __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True,comment="主键ID") name = db.Column(db.String(250), comment="姓名") age = db.Column(db.Integer, comment="年龄") sex = db.Column(db.Boolean, default=False, comment="性别") money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包") # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！ # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性 info = db.relationship("StudentInfo", backref="own", uselist=False) def __repr__(self): return self.name class StudentInfo(db.Model): __tablename__ = "tb_student_info" id = db.Column(db.Integer, primary_key=True, comment="主键ID") sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生") address = db.Column(db.String(255), nullable=True, comment="家庭住址") mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话") def __repr__(self): return self.own.name class Teacher(db.Model): __tablename__ = "tb_teacher" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), comment="姓名") option = db.Column(db.Enum("讲师","助教","班主任"), default="讲师", comment="教职") course_list = db.relationship("Course",uselist=True, backref="teacher",lazy="subquery") def __repr__(self): return self.name class Course(db.Model): __tablename__ = "tb_course" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), unique=True, comment="课程名称") price = db.Column(db.Numeric(6, 2)) teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id),comment="老师") def __repr__(self): return self.name @app.route("/") def index(): """1对多，多对1""" """添加数据""" # 添加主模型数据，同时也添加外键模型 # teacher = Teacher( # name="灰太狼", # option="班主任", # course_list=[ # Course(name="抓羊",price="9.90"), # Course(name="挨打",price="19.90"), # Course(name="炸房子",price="29.90"), # ] # ) # db.session.add(teacher) # db.session.commit() # 添加外键模型数据，同时也添加主模型 # course = Course( # name="平底锅108种用法", # price="99.99", # teacher=Teacher(name="红太狼",option="班主任") # ) # db.session.add(course) # db.session.commit() """查询数据=》lazy="subquery查询当前数据模型时，采用子查询(subquery)，把外键模型的属性也瞬间查询出来了（先主后外键模型）"""" # teacher = Teacher.query.filter(Teacher.name=="灰太狼").first() # print(teacher.name, teacher.option) # print("---------------------------------------------------") # print(teacher.course_list) # for course in teacher.course_list: # print(course.name) # course = Course.query.filter(Course.name=="炸房子").first() # print(course) # print("%s在教%s" % (course.teacher.name,course.name)) """更新数据""" # teacher = Teacher.query.filter(Teacher.name == "灰太狼").first() # teacher.course_list[0].name="抓懒洋洋" # db.session.commit() """删除数据""" # teacher = Teacher.query.filter(Teacher.name=="灰太狼").first() # for course in teacher.course_list: # db.session.delete(course) # db.session.delete(teacher) # db.session.commit() return "Ok" if __name__ == '__main__': # with app.app_context(): # db.drop_all() # db.create_all() app.run()
多对多
achievement = db.Table('tb_achievement', db.Column('student_id', db.Integer, db.ForeignKey('tb_student.id')), db.Column('course_id', db.Integer, db.ForeignKey('tb_course.id')), ) class Course(db.Model): ... students = db.relationship('Student',secondary=achievement, backref='courses', lazy='dynamic') class Student(db.Model): ...
多对多，也可以拆解成3个模型，其中tb_achievement作为单独模型存在。
查询老师授课的所有课程
#查询讲师表id为1的老师 teacher = Teacher.query.get(1) #查询当前老师的所有课程, 根据模型中关联关系来查询数据 print(teacher.courses)
查询课程所属老师
course = Course.query.get(2) print(course) # 根据外键只能查询到ID数值, SQLAlchemy不会帮我们把ID转换成模型 print( course.teacher_id ) # 要获取外键对应的模型数据,需要找到主键模型里面的 db.relationship 里面的 backref print( course.teacher.name )
from flask import Flask from flask_sqlalchemy import SQLAlchemy app = Flask(__name__) class Config(): # DEBUG调试模式 DEBUG = True # json多字节转unicode编码 JSON_AS_ASCII = False # 数据库链接配置 # SQLALCHEMY_DATABASE_URI = "mysql://账号:密码@IP/数据库名?编码" SQLALCHEMY_DATABASE_URI = "mysql://root:123@127.0.0.1:3306/students?charset=utf8mb4" # 动态追踪修改设置，如未设置只会提示警告 SQLALCHEMY_TRACK_MODIFICATIONS = True # 查询时会显示原始SQL语句 SQLALCHEMY_ECHO = True app.config.from_object(Config) db = SQLAlchemy() db.init_app(app) """创建模型类""" # db.Table( # 表名， # db.Column("字段名",字段类型，外键声明), # db.Column("字段名",字段类型，外键声明), # ) """以db.Table关系表来确定模型之间的多对多关联""" achievement = db.Table( "tb_achievement", db.Column("student_id",db.Integer,db.ForeignKey('tb_student.id')), db.Column("course_id",db.Integer,db.ForeignKey('tb_course.id')), # 这里的表信息，在主键模型中，仅仅表达的是关联关系，所以中间表的字段，无法通过主模型来获取 db.Column("created_time",db.DateTime,comment="考试时间"), db.Column("score",db.DECIMAL(5,2),comment="成绩") ) class Student(db.Model): __tablename__ = "tb_student" id = db.Column(db.Integer, primary_key=True,comment="主键ID") name = db.Column(db.String(250), comment="姓名") age = db.Column(db.Integer, comment="年龄") sex = db.Column(db.Boolean, default=False, comment="性别") money = db.Column(db.DECIMAL(8,2), nullable=True, comment="钱包") # 关联属性，是SQLAlchemy提供给开发者快速引用外键模型的一个对象属性，不存在于mySQL中！！！ # backref 反向引用，类似django的related，通过外键模型查询主模型数据时的关联属性 info = db.relationship("StudentInfo", backref="own", uselist=False) # course_list = db.relationship("Course", secondary=achievement,backref="student_list",lazy="dynamic") def __repr__(self): return self.name class StudentInfo(db.Model): __tablename__ = "tb_student_info" id = db.Column(db.Integer, primary_key=True, comment="主键ID") sid= db.Column(db.Integer,db.ForeignKey(Student.id), comment="学生") address = db.Column(db.String(255), nullable=True, comment="家庭住址") mobile = db.Column(db.String(15), unique=True, comment="紧急联系电话") def __repr__(self): return self.own.name class Teacher(db.Model): __tablename__ = "tb_teacher" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), comment="姓名") option = db.Column(db.Enum("讲师","助教","班主任"), default="讲师", comment="教职") course_list = db.relationship("Course",uselist=True, backref="teacher",lazy="subquery") def __repr__(self): return self.name class Course(db.Model): __tablename__ = "tb_course" id = db.Column(db.Integer, primary_key=True, comment="主键ID") name = db.Column(db.String(250), unique=True, comment="课程名称") price = db.Column(db.Numeric(6, 2)) teacher_id = db.Column(db.Integer, db.ForeignKey(Teacher.id),comment="老师") student_list = db.relationship("Student",secondary=achievement,backref='course_list',lazy="dynamic") def __repr__(self): return self.name @app.route("/") def index(): """多对多""" # course1 = Course(name="坑爹", price="9.99", teacher=Teacher(name="灰太狼", option="讲师")) # course2 = Course(name="坑娘", price="9.99", teacher=Teacher(name="灰太狼", option="讲师")) # course3 = Course(name="和羊做朋友，一起坑爹", price="99.99", teacher=Teacher(name="喜洋洋", option="讲师")) # student = Student( # name="xiaohuihui", # age=5, # sex=False, # money=1000, # info=StudentInfo( # mobile="13066666666", # address="狼村1号别墅", # ), # course_list = [ # course1, # course2, # course3, # ] # ) # db.session.add(student) # db.session.commit() """查询""" # student = Student.query.filter(Student.name=="xiaohuihui").first() # print(student) # print(student.course_list) # [坑爹, 坑娘, 和羊做朋友，一起坑爹] # course = Course.query.filter(Course.name=="和羊做朋友，一起坑爹").first() # print("-----------------------------------------------") # print(course.student_list.all()) # 获取所有学生信息 """更新""" # course = Course.query.filter(Course.name == "和羊做朋友，一起坑爹").first() # course.student_list[0].name="小灰灰" # db.session.commit() return "Ok" if __name__ == '__main__': # with app.app_context(): # db.drop_all() # db.create_all() app.run()