SQL炼金术：有没有一种方法可以在提交之前获取父级的ID？

Question

是否有一种方法可以在提交前以某种方式获取父级ID？我有一个数据库，它有两个表。实验和实验规则

请参见我的模型：

class Experiment(db.Model):
    __tablename__ = 'TestE'
    __table_args__ = {'extend_existing': True}
    ExperimentID = db.Column(db.Integer, primary_key = True)
    ExperimentName = db.Column(db.String(255), nullable = False)
    Status = db.Column(db.String(50), nullable = False)
    Description = db.Column(db.String(1000), nullable = False)
    URL = db.Column(db.String(255), nullable = False)
    ModifiedDate = db.Column(db.DateTime, nullable = False)
    CreateDate = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    Rules = db.relationship('ExperimentRule', backref='experiment', lazy=True)

    def __repr__(self):
        return '<ID %r>' % self.ExperimentID

    def __init__(self,data):
        self.ExperimentName = data["Name"]
        self.Status = data["Status"]
        self.Description = data["Description"]
        self.URL = data["URL"]
        self.ModifiedDate = data["ModifiedDate"]

class ExperimentRule(db.Model):
    __tablename__ = 'TestER'
    __table_args__ = {'extend_existing': True}
    ExperimentID = db.Column(db.Integer, db.ForeignKey('TestE.ExperimentID'),nullable=False)
    ExperimentRuleID = db.Column(db.Integer, primary_key = True)
    RuleGroupName = db.Column(db.String(50), nullable = False)
    ThrottleType = db.Column(db.String(50), nullable = True)
    Throttle = db.Column(db.Integer, nullable = True)


    def __init__(self, ExperimentID, RuleGroupName, data):
        self.ExperimentID = ExperimentID
        self.RuleGroupName = RuleGroupName
        if data["ThrottleType"]:
            self.ThrottleType = data["ThrottleType"]
        else:
            self.ThrottleType = ""
        if data["Throttle"]:
            self.ThrottleType = data["Throttle"]
        else:
            self.ThrottleType = ""

我尝试通过以下方式加载数据：

a = Experiment(data)
b = ExperimentRule(a.ExperimentID, RuleGroupName, data)
db.session.add(a)
db.session.add(b)
db.session.commit()

但是，我收到了一个错误，实验ID不能为“无”。我在提交之前检查了a对象，但它还没有ID

如果我这样做，它会起作用：

a = Experiment(data)
db.session.add(a)
db.session.commit()
b = ExperimentRule(Experiment.query.order_by(Models.Experiment.ExperimentID.desc()).first().ExperimentID, RuleGroupName, data)
db.session.add(b)
db.session.commit()

但是，这大约需要5秒钟，这太长了

在创建实验规则数据之前，您知道如何在不提交实验数据的情况下做到这一点吗

谢谢

Answer 1

如果不在ExperimentRule.__init__中指定ExperimentID，可以避免事先生成它：而是首先创建一个ExperimentRule，并将其附加到Experiment.Rules。SQLAlchemy将自动处理外键设置

class ExperimentRule(db.Model):
    __tablename__ = 'TestER'
    __table_args__ = {'extend_existing': True}
    ExperimentID = db.Column(db.Integer, db.ForeignKey('TestE.ExperimentID'),nullable=False)
    ExperimentRuleID = db.Column(db.Integer, primary_key = True)
    ...

    def __init__(self, RuleGroupName, data):
        self.RuleGroupName = RuleGroupName
        if data["ThrottleType"]:
            self.ThrottleType = data["ThrottleType"]
        else:
            self.ThrottleType = ""
        ...

b = ExperimentRule('foo', data)
a = Experiment(data)
a.Rules.append(b)
session.add(a)
session.commit()

请参阅SQLAlchemy ORM教程中的Working with Related Objects

Answer 2

试试这个：

a = Experiment(data)
db.session.add(a)
db.session.flush()
b = ExperimentRule(a.ExperimentID, RuleGroupName, data)
db.session.add(b)
db.session.commit()