Inserting two related objects fail in SQLAlchemy

Question

I'm getting the (probably trivial) error, but completely clueless about the possible causes. I want to insert two object in the DB using SQLAlchemy. Those objects are related, here are the declarations. Class User: ``` class User(Base): __tablename__ = 'cp_user' id = Column(Integer, Sequence('id_seq'), primary_key=True) # ... more properties ``` Class Picture (user may have many of them): ``` class Picture(Base): __tablename__ = 'picture' id = Column(Integer, Sequence('id_seq'), primary_key=True) authorId = Column('author_id', Integer, ForeignKey('cp_user.id')) author = relation(User, primaryjoin = authorId == User.id) # ... more properties ``` I'm trying to insert the new picture after I've fetched the right user from the DB, or just created it: ``` s = newSession() user = s.query(User.name).filter("...some filter here...").first() if not(user): user = User() s.add(user) s.commit() picture = Picture() picture.author = user s.add(picture) s.commit() ``` This fails with the exception:…

Accepted Answer

Your code fails if the `not(user)` branch is not taken.

You query User.name which is a column and not a bound object.

```
user = s.query(User).filter("...some filter here...").first()
```

An object gets it's id designed as soon as it is transmitted to the database. You are doing this in the branch with a commit. This is probably not what you want. You should issue a flush. Read the docs on the difference.

Also you should not need to commit the newly created user. If you assign a user object to a relation, this should be handled transparently. Every commit closes a transaction, which can be quite costly (locking, disk seeks, etc)

Inserting two related objects fail in SQLAlchemy

Solution

Problem