SQLAlchemy ORM Inserting Related Objects without Selecting Them

Question

In the SQLAlchemy ORM tutorial, it describes the process of creating object relations roughly as follows. Let's pretend I have a table Articles, a table Keywords, and a table Articles_Keywords which creates a many-many relationship.

article = meta.Session.query(Article).filter(id=1).one()
keyword1 = meta.Session.query(Keyword).filter(id=1).one()
keyword2 = meta.Session.query(Keyword).filter(id=2).one()
article.keywords = [keyword1,keyword2]
meta.Session.commit()

I already have the primary key ID numbers for the keywords in question, so all I need to do is add those IDs to the Articles_Keywords table, linked to this article. The problem is that in order to do that with the ORM, I have to select all of the Keywords from the database, which adds a lot of overhead for seemingly no reason.

Is there a way to create this relationship without running any SQL to select the keywords?

Answer 1

Unfortunately the ORM cannot keep the object state sane without querying the database. However you can easily go around the ORM and insert into the association table. Assuming that Articles_Keywords is a Table object:

 meta.Session.execute(Articles_Keywords.delete(Articles_Keywords.c.article_id == 1))
 meta.Session.execute(Articles_Keywords.insert(), [
     {'article_id': 1, 'keyword_id': 1},
     {'article_id': 1, 'keyword_id': 2},
 ])

If you go around the ORM then already loaded collections aren't updated to reflect the changes in the database. If you need to refresh the collection call Session.expire on it. This will cause the collection to be reloaded next time it is accessed.

meta.Session.expire(article, ['keywords'])

Answer 2

Ants has the right answer, but also if you mapped article_keywords explicitly as an association object, you would just create new ArticleKeyword objects and add them straight in.