What is the correct way to insert an object using a foreign key in SQLAlchemy?

Question

When using SQLAlchemy, what is the ideal way to insert an object into a table with a column that is a foreign key and then commit it? Is there something wrong with inserting foreign objects in the code below?

def retrieve_objects(): session = DBSession() return session.query(SomeClass).all() def insert_objects(): session = DBSession() for obj in retrieve_objects(): another_obj = AnotherClass(somefield=0) obj.someforeignkey = another_obj session.add(obj) session.flush() transaction.commit() session.close() return None 

Answer 1

If you do not use SQLAlchemy relationships on your ORM objects, you must manually use foreign keys. This means that you need to first create the parent object, return its primary key from the database and use this key in the child foreign key:

 def retrieve_objects(): session = DBSession() return session.query(SomeClass).all() def insert_objects(): session = DBSession() for obj in retrieve_objects(): another_obj = AnotherClass(somefield=0) session.add(another_obj) session.flush() # generates the pkey for 'another_obj' obj.someforeignkey = another_obj.id # where id is the pkey session.add(obj) transaction.commit()