Python-SQLalchemy1

Python Flask-SQLalchemy 操作数据库

SQLalchemy 的安装

pip install flask-sqlalchemy

SQLalchemy 创建models

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
import pymysql
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:@127.0.0.1:3308/edu"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SECRET_KEY"] = "chunge"
# 初始化
db = SQLAlchemy(app)
class Student(db.Model):
    # 配置表名
    __tablename__ = "student"
    # primary_key：主键
    # autoincrement：自增
    # nullable：不能为空
    # Enum：枚举类型
    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    name = db.Column(db.String(64),
                     nullable=False)
    gender = db.Column(db.Enum("男", "女"),
                       nullable=False)
    phone = db.Column(db.String(11))
# 课程表
class Course(db.Model):
    __tablename__ = "course"
    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    name = db.Column(db.String(64),
                     nullable=False)
    teacher_id = db.Column(db.Integer,
                           nullable=False)
# 教师表
class Teacher(db.Model):
    __tablename__ = "teacher"
    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    name = db.Column(db.String(64),
                     nullable=False)
    gender = db.Column(db.Enum("男", "女"),
                       nullable=False)
    phone = db.Column(db.String(11))
# 成绩表
class Grade(db.Model):
    __tablename__ = "grade"
    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    course_id = db.Column(db.Integer,
                          nullable=False)
    student_id = db.Column(db.Integer,
                           nullable=False)
    grade = db.Column(db.Integer)
# 创建数据表
if __name__ == '__main__':
    db.create_all()

Flask-SQLalchemy的简单增删改查操作

from models import db, Student
# 增
# s1 = Student(name="zhangsan", gender="男", phone="18621526136")
# s2 = Student(name="lisi", gender="女", phone="18621526136")
# s3 = Student(name="wangwu", gender="男")
# # 单行增加
# db.session.add(s1)
# db.session.commit()
# # 批量增加
# db.session.add_all([s1, s2, s3])
# db.session.commit()
# 查
# get里面放ID,就是再model中定义的id
# stu = Student.query.get(4)
# print(stu.name, stu.gender, stu.phone)
#
# # 查询全部
# stus = Student.query.all()
# for stu in stus:
#     print(stu.name, stu.gender, stu.phone)
#
# # filter() 条件查询
# stus = Student.query.filter(Student.name == "lisi")
# for stu in stus:
#     print(stu.id, stu.name, stu.gender, stu.phone)
# filter_by().all() 代表查询到的全部
# filter_by().first() 代表查询到的第一个
# stus = Student.query.filter_by(name="zhangsan").first()
# print(stus)
#
# # 改
# # 第一种方式
# stu = Student.query.filter(Student.name == "zhangsan").update({"gender": "女"})
# # 显示更新行数
# print(stu)
# db.session.commit()
# 第二种方式
# stus = Student.query.filter(Student.gender == "女").all()
# for stu in stus:
#     stu.gender = "男"
#     db.session.add(stu)
#
# db.session.commit()
# 删
# 第一种方式
stu = Student.query.filter(Student.id <= 2).delete()
print(stu)
db.session.commit()