9

As the title suggests, I want to add a row with an id based on another row in SQLALchemy. Currently, I'm adding an extra select, but I'd like to get rid of this. I know this is possible in MySQL, so I'm just trying to figure out the SQLAlchemy version. Here's what I currently have:

keywords = ['a', 'b', 'c']
prefix_id = session.query(Prefix.id)\
    .filter_by(name=some_prefix).first()[0]
inventory_item = InventoryItem(
    item=some_item, prefix_id=prefix_id, address=some_address)
inventory_item_metas = [InventoryItemMeta(
    inventory_item=inventory_item,
    type='keyword',
    value=keyword) for keyword in keywords]

Note that I'm adding more items based off the id returned, so I'd like to do everything in one transaction as efficiently as possible.

Eli
  • 36,793
  • 40
  • 144
  • 207
  • 1
    Look at this: [SO answer](http://stackoverflow.com/questions/8142923/how-to-add-rows-using-subqueries-in-sqlalchemy/8149752#8149752) It might give you some ideas. –  Jun 02 '15 at 07:11

1 Answers1

2

If i0 is the object you want to copy, you can remove it from the session to make it transient:

i0 = select
session.expunge(i0)

Then you set it's id to None so that it's no longer referencing any real database row:

i0.id = None

Then you can modify the object:

i0.a = 1
i0.b = 2

And finally, you add the transient object back to the session and flush it to get a new id for it:

session.add(i0)
session.flush()

You'll find that the object is now a new row:

print i0.id #New ID

And if you look things up by the old ID, you'll have another object that's the same as before the changes, with the original ID, too.