使用flask_SQLAlchemy如何创建一对多关系表数据库(sqlite3)

使用flask_SQLAlchemy如何创建一对多关系表数据库(sqlite3)

from flask_sqlalchemy import SQLAlchemy
from flask import Flask

# 先连接数据库
app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///test.sqlite3"
app.config['SQLALCHEMY_ECHO'] = False  # 日志
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)

# 定义ORM表
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    # 定义一对多关系表
    all_item = db.relationship('Monitor', backref='author', lazy='dynamic')
    password = db.Column(db.String(256))


class Monitor(db.Model):
    __tablename__ = 'monitor'
    M_id = db.Column(db.Integer, primary_key=True)
    item_id = db.Column(db.Integer, nullable=False)
    item_name = db.Column(db.String(128), default='')
    item_price = db.Column(db.String(64), default='')
    user_price = db.Column(db.String(64), nullable=False)

    # 设置定向外键,使得一对多关系成立
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))


if __name__ == '__main__':
    # 创建表单
    db.create_all()

    u_id = 1
    m_id = 1
    nickname = 'lb'
    email = '1975284269@qq.com'
    # 注意all_item必须是可迭代对象，因为一个user可以有很多monitor
    all_item = [Monitor(M_id=m_id, item_id=10086, item_name='中国移动', item_price='100', user_price='99', user_id=u_id)]
    password = '12345'
    # 添加数据
    u1 = User(id=u_id, nickname=nickname, email=email, all_item=all_item, password=password)
    db.session.add(u1)
    # 保存数据库更改
    db.session.commit()

    # 查询所有用户数据
    x = User.query.all()
    print(x)