Flask SQLAlchemy 常用查询操作

测试源码

Github源码地址

sql数据来源，https://zhuanlan.zhihu.com/p/53302593

表结构 https://zhuanlan.zhihu.com/p/38354000

sqlalchemy参考 https://www.osgeo.cn/sqlalchemy/tutorial/data.html#selecting-data

import argparse

from datetime import datetime

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///example.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app, engine_options={
   'echo': True})


class Student(db.Model):
    sid = db.Column(db.String(10), primary_key=True)
    sname = db.Column(db.String(10))
    sage = db.Column(db.DateTime())
    ssex = db.Column(db.String(10))

    scores = db.relationship('Score', backref='student', lazy=True)

    def __repr__(self):
        return '<Student - Sid: %s, Sname: %s>' % (self.sid, self.sname)


class Course(db.Model):
    cid = db.Column(db.String(10), primary_key=True)
    cname = db.Column(db.String(10))
    tid = db.Column(db.String(10), db.ForeignKey('teacher.tid'))

    scores = db.relationship('Score', backref='course', lazy=True)

    def __repr__(self):
        return '<Course %s>' % self.cname


class Teacher(db.Model):
    tid = db.Column(db.String(10), primary_key=True)
    tname = db.Column(db.String(10))

    courses = db.relationship('Course', backref='teacher', lazy=True)

    def __repr__(self):
        return '<Teacher %s>' % self.tname


class Score(db.Model):
    sid = db.Column(db.String(10), db.ForeignKey('student.sid'), primary_key=True)
    cid = db.Column(db.String(10), db.ForeignKey('course.cid'), primary_key=True)
    score = db.Column(db.Integer)

    def __repr__(self) -> str:
        return '<Score - Student: %s, Course: %s, Score: %s>' % (self.sid, self.cid, self.score)


def init_database():
    db.create_all()
    students = [
        Student(sid='01', sname='赵雷', sage=datetime.strptime(
            '1990-01-01', '%Y-%m-%d'), ssex='男'),
        Student(sid='02', sname='钱电', sage=datetime.strptime(
            '1990-12-21', '%Y-%m-%d'), ssex='男'),
        Student(sid='03', sname='孙风', sage=datetime.strptime(
            '1990-05-20', '%Y-%m-%d'), ssex='男'),
        Student(sid='04', sname='李云', sage=datetime.strptime(
            '1990-08-06', '%Y-%m-%d'), ssex='男'),
        Student(sid='05', sname='周梅', sage=datetime.strptime(
            '1991-12-01', '%Y-%m-%d'), ssex='女'