Referential Integrity and Transactions in SqlAlchemy

Question

I am attempting to insert data into two tables, the latter of which references the former. Something like:

CREATE TABLE foo (foo_id bigserial primary key, name varchar)
CREATE TABLE bar (bar_id bigserial primary key, name varchar,
                  foo_id int constraint foo_fkey references foo)

I want to add both rows within a transaction, so that if anything fails, nothing is written to the database. If I simply add both objects to my session, I don't get the auto-incremented foo_id, so I add the foo object, do a flush (which populates the id), then set up the bar object, then flush again (I have even more work to do later, but not referenced by this relationship. On that second flush, I'm getting a referential-integrity error because the row in foo hasn't actually been written.

I can accomplish this easily in SQL:

BEGIN TRANSACTION;
INSERT INTO foo (name) values('foo');
INSERT INTO bar (name, foo_id) VALUES('bar', currval(pg_get_serial_sequence('foo', 'foo_id')));
COMMIT TRANSACTION;

Is there a way for me to temporarily suspend RI checking? Or, is there a better way for me to get the new foo_id instead of doing a flush?

Here's the kind of sqla code I'm executing:

session.begin()
f = Foo()
f.name = 'Foo'
session.add(f)
session.flush()
b = Bar()
b.name = 'Bar'
b.foo_id = f.foo_id
session.add(b)
session.flush()
...
session.commit()
session.close()

I use the bar_id value later, which is why I need the second flush. It is on that second flush that the error is happening.

Can you share sample SQLA code? If there's a relationship you can usually do something like ` parent = Parent(child=child)` and let the keys get sorted at commit time, without an intermediate flush. Or you might be able to make the FK constraint deferrable (I've never tried that). — snakecharmerb
– snakecharmerb, Commented Jun 23, 2021 at 18:08
@snakecharmerb, Edited the original post to add the type of code I'm executing. The actual tables/objects have many more columns, so I'm paraphrasing here for brevity. I'll see if I can set up the relationship and see if that helps... — GoTodd
– GoTodd, Commented Jun 23, 2021 at 21:36
Well, I tried using the relationships, and I'm not getting an RI error, but I'm also not getting the ids populated, now. Not with a flush or a commit. — GoTodd
– GoTodd, Commented Jun 23, 2021 at 23:38

snakecharmerb · Accepted Answer · 2021-06-24 04:39:54Z

0

I can't reproduce the integrity error on the second flush. Assuming a model setup with a one to one relationship like this:

class Foo(Base):
    __tablename__ = 'foo'

    id = sa.Column(sa.Integer, primary_key=True)
    bar = orm.relationship('Bar', back_populates='foo', uselist=False)


class Bar(Base):
    __tablename__ = 'bar'

    id = sa.Column(sa.Integer, primary_key=True)
    foo_id = sa.Column(sa.Integer, sa.ForeignKey('foo.id'))
    foo = orm.relationship('Foo', back_populates='bar')

The the output of these actions:

# No explicit id, no flush, commit
foo1 = Foo()
bar1 = Bar(foo=foo1)
session.add(bar1)
session.commit()
print(f'foo {foo1.id}, bar {bar1.id}')

# No explicit id, flush
bar2 = Bar()
foo2 = Foo(bar=bar2)
session.add(foo2)
session.flush()
print(f'foo {foo2.id}, bar {bar2.id}')
session.commit()


# Explicit id, two flushes
foo3 = Foo()
session.add(foo3)
session.flush()
bar3 = Bar()
bar3.foo_id = foo3.id
session.add(bar3)
session.flush()
print(f'foo {foo3.id}, bar {bar3.id}')
session.commit()

is what I would expect:

foo 1, bar 1
foo 2, bar 2
foo 3, bar 3

answered Jun 24, 2021 at 4:39

snakecharmerb

57.2k13 gold badges137 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GoTodd Over a year ago

It was the last part of your example that did it for me. I was simply instantiating the "Bar" object and adding it to the collection in "Foo", but when I added the Bar object, that did it. f = Foo() f.name = 'foo' session.add(f) b = Bar() b.name = 'bar' session.add(b) f.b_list.append(b) session.flush()

Belayer · Accepted Answer · 2021-06-23 19:17:42Z

0

Postgres permits Insert statements in a CTE. This allows you to accomplish what you are wanting in a single SQL. (See demo)

with new_foo as 
     ( insert into foo(name) 
        values('foo') 
       returning foo_id
     ) 
insert into bar(name, foo_id) 
     select 'bar', foo_id 
       from new_foo;

That is providing your obscurification layer (SalAlchemy) also permits it.

answered Jun 23, 2021 at 19:17

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

1 Comment

GoTodd Over a year ago

Yeah, my problem isn't so much postgres as sqla. Thanks, though!

Collectives™ on Stack Overflow

Referential Integrity and Transactions in SqlAlchemy

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related