Inserting two related objects fail in SQLAlchemy
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):
cl开发者_如何转开发ass 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?
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)
精彩评论