【SQLAlchemy】

SQLAlchemy配置

learn_sql.py

from flask_sqlalchemy import SQLAlchemy
from flask import flask

app = Flask(__name__)
# app.config['SQLALCHEMY_DATABASE_URL'] = "sqplite:///" + "/home/tmp/tes.db"
app.config['SQLALCHEMY_DATABASE_URL'] = 'mysql+pymysql://root:123456@127.0.0.1:3306/test'
app.config["SQLALCHEMY_MODIFICATIONS"] = False
aoo.config["SECRET_KEY"] = "XXX"
db.SQLAlchemy(app)

# 学生表
class Student(db.Model):
	__tablename__ = 'student'
	id = db.Column(db.Integer, primary_key=True) #主键
	name = db.Cloumn(db.String(64), nullable=False) #学生姓名, nullable能否为空
	gender = db.Cloumn(db.Enum("男","女"), nullable=False )# 性别 Enum枚举 不能为空
	pthon = db.Cloumn(db.String(11)) #手机号，可以为空
	grades = db.relationship("Grade", backref = "student") #成绩关系关联  与student_id外键关联
	
# 课程表
class Cource(db.Model):
	__tablename__ = 'cource'
	id = db.Column(db.Integer, primary_key=True) #主键
	name = db.Cloumn(db.String(64), nullable=False) #课程名
	grades = db.relationship("Grade", backref = "cource") # 成绩关系关联  与course_id外键关联

# 教师表
class Teacher(db.Model):
	__tablename__ = 'teacher'
	id = db.Column(db.Integer, primary_key=True) #主键
	name = db.Cloumn(db.String(64), nullable=False) #教师姓名, nullable能否为空
	gender = db.Cloumn(db.Enum("男","女"), nullable=False )# 性别 Enum枚举 不能为空
	pthon = db.Cloumn(db.String(11)) #手机号，可以为空

# 成绩表
class Grade(db.Model):
	__tablename__ = 'grade'
	id = db.Column(db.Integer, primary_key=True) #主键
	name = db.Cloumn(db.String(3), nullable=False)
	studnet_id = db.Cloumn(db.Integer, db.ForeginKey("student.id"))
	course_id = db.Cloumn(db.Integer, db.ForeginKey("course.id"))

if __name__ = "__main__"
	db.create_all()
	db.drop_all()

SQLAlchemy增

from learn_sql import db, Student
s1 = Studeng(name="张三", gender="男", phone="12345678900")
s2 = Studeng(name="李四", gender="女", phone="12345677500")
s3 = Studeng(name="王五", gender="男", phone="12123678900")

db.session.add(s1)
db.session.commit()

db.session.add_all([s1, s2, s3])
db.session.commit()

SQLAlchemy删

 stu = Student.query.filter(Student.id == 1).delete()  # 返回删除了多少条数据
 db.session.commit()
 print(stu)

SQLAlchemy改

'第一种'
# stu = Student.query.get(1).update({"name": "张三丰"})
 stu = Student.query.filter(Student.id == 1).update({"name": "张三丰"})
 db.session.commit()
 print(stu)
 返回的修改的数量

'第二种'
stu = Student.query.filter(Student.gender =="女").first()
stu.gender = "男"
db.session.add(stu)
db.session.commit()

SQLAlchemy查

'查询一个'
stu = Student.query.get(1)  #获取id=1的人的信息
print(stu.name)
print(stu.age)

'查询全部'
stu = Student.query.all()
for i in stu:
	print(i.name, i.gender, i.phone)
	
'条件查询'
stu = Student.query.filter(Student.gender == "男")
for i in stu:
	print(i.name, i.id)

stu = Student.query.filter_by(name = "张三").all()
stu = Student.query.filter_by(name = "张三").filter(Student.id >= 2)
for i in stu:
	print(i.name)  # 张三  张三  张三

一对多

• ForeignKey 外键 多方
• relationship 一方
• 增删查改

'一对多'
stu = Student.query.get(1)  
for i in stu.grades:
	print(stu.name, i.grade)
'输出'
kk 100
kk 95

'多对一'
grade = Student.query.filter(Grade.grade == "100").all()  
for i in grade:
	print(i.strudent, i.student.name)
	
"输出"
<Student 1><student 2>  kk jj

多对多

• 第三张表