SQL Alchemy: Is there a way to get ID of parent before committing?

Question

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

See my models:

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

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

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

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

Thanks!

score 2 · Answer 1 · answered Apr 26 '20 at 08:51

2

Try this:

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

answered Apr 26 '20 at 08:51

afghanimah

705
3
11

Tried this and it made the loading faster by 0.4 seconds. However, I do not understand why it is taking so long. It takes 6 seconds to load 3 rows into db. I think I must have something else wrong in my code. Will try to check something else. Thanks! – Roitko Apr 26 '20 at 09:05
1

Hmm, I'm not too sure about the time, but I'm pretty sure sqlalchemy is slower because it's a whole other layer on top. Maybe this post can help?: https://stackoverflow.com/questions/51821428/postgresql-sqlalchemy-commit-takes-a-lot-of-time glad I could help with the id though! – afghanimah Apr 26 '20 at 09:15

score 1 · Accepted Answer · answered Apr 26 '20 at 09:58

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

See Working with Related Objects in the SQLAlchemy ORM Tutorial.

SQL Alchemy: Is there a way to get ID of parent before committing?

2 Answers2