How to insert into multiple tables to MySQL with sqlalchemy

Question

I'm new to sqlalchemy and only knows basic sqlalachemy knowledge.

Now I'm writing some Python code, what I need to do is like the following:

There is a User table, a Group table and a GroupUser table. To simplify the question, say I have known user id is 100. Now I want to insert a new group into the Group table, and get the group id back, then insert (group_id, user_id) tuple into GroupUser table.

The code I can write is like the following:

# Insert the group first.
session = self.DBSession()
new_group = Group(name = 'gname')
session.add(new_group)
session.commit()
# Then query back the new group id
gid = session.query(Group).filter(Group.name == 'gname').first().id
# At last, insert group-user
gu = GroupUser(gid=gid, uid=100)
session.add(gu)
session.commit()

By the way, id in Group table is autoincrement.

I wonder if this procedure can be simplified? Can I do this in a single transaction.

Answer 1

it can definitely be simplified. First of all, you only need one commit() statement at the very end. Second, you're missing flush() which will automatically give you the ID of (last) inserted group. Meaning that you won't need to query for that ID explicitly in a separate statement. Your code should look something like:

session = self.DBSession()
new_group = Group(name='gname')
session.add(new_group)
session.flush() # NOTE: this will actually insert the record in the database and set
                # new_group.id automatically. The session, however, is not committed yet! 
gu = GroupUser(gid=new_group.id, uid=100)
session.add(gu)
session.flush() # not required actually because flush() below will do it for you, 
                # but explicit is always better than implicit =)

session.commit() # this will finally commit your transaction, i.e. 2 statements above