【sqlalchemy用法】

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql
"""
app.config["SQLALCHEMY_DATABASE_URL"]= "[db]+[db_engine]://[USER]:[PWD]@[IP]:[PORT]/[DB_NAME]"
[db]:数据库类型,比如mysql、sqllite
[db_engine]:数据库引擎,比如pymysql
[USER]:数据库用户名
[PWD]:数据库密码
[IP]:数据库机器的ip
[PORT]:默认端口3306
[DB_NAME]:数据库库名
"""

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:root@127.0.0.1:3306/school_db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SECRET_KEY"] = 'SDFMLINFKAEFMPAEJFOAMFAEKFL'
app.config['SQLALCHEMY_ECHO'] = True

# 实例化一个db
db = SQLAlchemy(app)

# 学生表


class Student(db.Model):
    # 表名
    __tablename__ = "student"
    # 字段
    id = db.Column(db.Integer, primary_key=True)            # int,primary_key
    name = db.Column(db.String(64), nullable=False)         # string not null
    gender = db.Column(db.Enum("男", "女"), nullable=False)   # 枚举 not null
    phone = db.Column(db.String(11))                        # 11为字符串 可以为空
    grade = db.relationship("Grade", backref="student")       # 和成绩关联
# 课程表


class Course(db.Model):
    __tablename__ = "course"
    id = db.Column(db.Integer, primary_key=True)            # int,primary_key
    name = db.Column(db.String(64), nullable=False)         # string not null
    # teacher_id = db.Column(db.)

# 老师表


class Teacher(db.Model):
    __tablename__ = "teacher"
    id = db.Column(db.Integer, primary_key=True)            # int,primary_key
    name = db.Column(db.String(64), nullable=False)         # string not null
    gender = db.Column(db.Enum("男", "女"), nullable=False)   # 枚举 not null
    phone = db.Column(db.String(11))                        # 11为字符串 可以为空

# 分数表


class Grade(db.Model):
    __tablename__ = "grade"
    id = db.Column(db.Integer, primary_key=True)            # int,primary_key
    grade = db.Column(db.String(10), nullable=False)
    student_id = db.Column(db.Integer, db.ForeignKey(
        "student.id"))  # 一对多 多的一方要引入ForeignKey


if __name__ == "__main__":
    db.create_all()  # 创建数据库
    # db.drop_all() # 删除数据库

from sqlalchemy_create_db import db,Student,Grade

"""增"""
s1 = Student(name='张三1',gender="女",phone='123123123')
s2 = Student(name='李四1',gender="女",phone='312312312')
s3= Student(name='王五1',gender="女",phone='334234234')
db.session.add(s1) # 添加一个
db.session.add_all([s1,s2,s3]) # 添加多个
db.session.commit()

"""查"""
# get查询一个
stu = Student.query.get(1)
stu_name = stu.name
stu_phone = stu.phone
stu_gender = stu.gender
print(stu_name,stu_phone,stu_gender)

# all查全部
stus = Student.query.all()   # 查询出来的是一个列表对象 可以for循环
print(stus)
for stu in stus:
    print(stu.name,stu.phone,stu.gender)

# 条件查询
stus = Student.query.filter(Student.id > 5)
for stu in stus:
    print(stu.name,stu.phone,stu.gender)
stus = Student.query.filter(Student.name > "张三")
for stu in stus:
    print(stu.name,stu.phone,stu.gender)
stus = Student.query.filter(Student.gender == "男")
for stu in stus:
    print(stu.name,stu.phone,stu.gender)

# # filter_by查询 类似sql语法
stu = Student.query.filter_by(name="张三").first()  # 查询到的第一个
print('1-->',stu.name,stu.phone,stu.gender)
stus = Student.query.filter_by(name="张三").all()    # 查询到的所有
for stu in stus:
    print('2-->',stu.name,stu.phone,stu.gender)
stus = Student.query.filter_by(name="张三").filter(Student.id==2)    # 查询结果还可以继续filter
for stu in stus:
    print('3-->',stu.name,stu.phone,stu.gender)

"""改"""
# 第一种 查询后直接更新
stu = Student.query.filter(Student.gender=="男").update({"gender":"女"}) # 将所有男的改为女的 返回值stu是修改的数量
print(stu)
db.session.commit()

# # 第二种 先查询查询结果更新
stu = Student.query.filter(Student.gender=="女").first() # 查询一个直接修改
stu.gender = "男"
db.session.commit()

stus = Student.query.filter(Student.gender=="女").all()  # 查询多个循环修改
for stu in stus:
    stu.gender = "男"
db.session.commit()

"""删"""
# 查询结果直接删除
stu = Student.query.filter(Student.id>1).delete()
db.session.commit()

"""---------------------- 一查多 ---------------------------"""

# 一对多 多的一方要引入foreign_key
# 增加成绩
grade1 = Grade(grade=100,student_id=1)
grade2 = Grade(grade=95,student_id=1)
grade3 = Grade(grade=80,student_id=1)

db.session.add(grade1)
db.session.add(grade2)
db.session.add(grade3)
db.commit()
"""---------------------- 多查一 ---------------------------"""

# 一查多
stu = Student.query.get(1)  # 获得一个学生对象
for i in stu.grades:    # 这个学生对象上已经有了所有的成绩 在创建表时候有外键和relationship
    print(stu.name,i.grade) # for循环遍历

# # 多查一
grade = Grade.query.filter(Grade.grade=='100').first()
for i in grade:
    print(i.student.name,i.student.gender)
grade.student #访问学生表
grade.student.name #访问学生表的具体信息