1

I'm trying to save to two tables. Let say I need to insert to tbl_1 first and use the generated id to be inserted to tbl_2 then after the insert I will call the commit command. On my current approved its like 

tbl_1.add(data1)
tbl_1.commit()

-- Retrieve the ID from tbl_1
tbl2.add(tbl_1.id, data2)
tbl2.commit()

The problem with this approach is if tbl2.add fails I need to delete what was added to tbl_1. And I don't like having to do delete action since my table id incrementation will be messy. What I'm thinking is like this

tbl_1.add(data1)
tbl2.add(tbl_1.id, data2)
tbl2.commit()

Any idea on how to achieve this using sqlalchemy?

***** Edit ****** tbl_1 and tbl2 are the same session object of sqlalchemy. I tried setting the autocommit to True and it works what I did was

tbl_1.add(data1)
tbl_1.flush()
tbl2.add(tbl_1.id, data2)
tbl2.commit()

Are there any problems setting autocommit to True or False?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
MadzQuestioning
  • 3,341
  • 8
  • 45
  • 76
  • Normally you'd just flush changes in between, or in other words send actual SQL INSERT to the DB, thus creating the id. Your syntax is unfamiliar, so cannot help you further. Don't often see commit being called on table objects. – Ilja Everilä Aug 18 '17 at 10:11
  • @IljaEverilä hi sorry tbl_1 and tbl2 are session(the same) instance of sqlalchemy. – MadzQuestioning Aug 18 '17 at 10:15
  • 1
    Don't use autocommit here, you'll just implicitly do what you did explicitly before. Using autocommit with a Session is [discouraged](http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.autocommit). With autocommit set to (the recommended setting of) False your latter example should work the way you want: either both inserts succeed together, or they fail together. Stressing the word "should", as your example is not a [mcve], so it is a bit hard to say what really goes on. – Ilja Everilä Aug 18 '17 at 10:23
  • 1
    This sounds like an XYWZ problem. Notice that no matter what, the ID's will be autoincremented permanently and have holes on rollbacks on all databases desbite the technique here. – Antti Haapala -- Слава Україні Aug 18 '17 at 10:28

0 Answers0