SQL炼金术:是否有一种方法可以在提交之前获取父级的ID?

问题描述

是否可以通过某种方式在提交之前获取父级的ID?我有一个具有2个表的数据库.实验和ExperimentRule.

is there a way to somehow get the ID of Parent before committing? I have a db that has 2 tables. Experiment and ExperimentRule.

查看我的模型:

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 = ""

我尝试过这种方式加载数据:

I tried loading my data this way:

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

但是，我收到一个错误，指示ExperimentID不能为"None".我在提交之前检查了一个对象，它还没有ID.

However, I received an error that the ExperimentID cannot be "None". I checked the a object before committing and it has no ID yet.

如果我这样操作，它将起作用:

It works if I do it this way:

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秒钟，这太长了.

However, this takes around 5 second which is too long.

有什么想法可以在创建ExperimentRule数据之前不提交实验数据的情况下完成此操作吗?

Any idea how this can be done without committing Experiment data before creating ExperimentRule data?

谢谢！

推荐答案

如果未在ExperimentRule.__init__中指定ExperimentID，则可以避免事先生成它:相反，首先创建一个ExperimentRule，然后将其附加到Experiment.Rules. SQLAlchemy将自动处理外键的设置.

If you don't specify ExperimentID in ExperimentRule.__init__ you can avoid having to generate it in advance: instead, create an ExperimentRule first, and append it to Experiment.Rules. SQLAlchemy will handle setting the foreign key automatically.

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()

请参见使用相关对象.

这篇关于SQL炼金术:是否有一种方法可以在提交之前获取父级的ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！