Inserting two related objects fail in SQLAlchemy

Question

Hi Community,

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: AttributeError: 'RowTuple' object has no attribute '_sa_instance_state'

I tried moving assignment of the author to the constructor -- same error. I can't assign IDs directly -- this breaks the idea of ORM.

What do I do wrong?

Answer 1

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)