Sqlalchemy Error creating backref relationship

Question

I have two very simple models. My Post model assumes two relationships in the User table. One for the post owner, and one for the last post editor. They can be different values, but both refer to the same User table.

My models are configured as follows

 class Post(Base): last_editor_id = Column(BigInteger, ForeignKey('users.id'), nullable=True) last_editor = relationship('User', backref='posts', foreign_keys=[last_editor_id]) owner_id = Column(BigInteger, ForeignKey('users.id'), nullable=False, index=True) owner = relationship('User', backref='posts', foreign_keys=[owner_id]) class User(Base): '''This represents a user on the site''' __tablename__ = 'users' id = Column(BigInteger, primary_key=True, unique=True) name = Column(BigInteger, nullable=False) 

When I try to create these models, I get the following error

 sqlalchemy.exc.ArgumentError: Error creating backref 'posts' on relationship 'Post.owner': property of that name exists on mapper 'Mapper|User|users' 

How do I fix this so that I can support both forgeign keys in a Post model?

Answer 1

The error tells you that you used post as a name more than once for your backrefs, all you have to do is give unique backref names. Here is a complete example: I added the id primary key to the Post class, as well as some __repr__ , so we get some readable output.

 from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, BigInteger, ForeignKey, Integer from sqlalchemy.orm import relationship, sessionmaker Base = declarative_base() engine = create_engine('sqlite://') ## In Memory. Session = sessionmaker() Session.configure(bind=engine) session = Session() class Post(Base): __tablename__ = 'post' id = Column(Integer, primary_key=True) last_editor_id = Column(BigInteger, ForeignKey('users.id'), nullable=True) last_editor = relationship('User', backref='editor_posts', foreign_keys=[last_editor_id]) owner_id = Column(BigInteger, ForeignKey('users.id'), nullable=False, index=True) owner = relationship('User', backref='owner_posts', foreign_keys=[owner_id]) def __repr__(self): return '<Post: {}>'.format(self.id) class User(Base): '''This represents a user on the site''' __tablename__ = 'users' id = Column(BigInteger, primary_key=True, unique=True) name = Column(BigInteger, nullable=False) def __repr__(self): return '<User: {}>'.format(self.name) Base.metadata.create_all(engine) bob = User(name='Bob', id=1) alice = User(name='Alice', id=2) post = Post(owner=alice, last_editor=bob, id=1) session.add(post) session.commit() bob = session.query(User).get(1) print bob # <User: Bob> print bob.editor_posts # [<Post: 1>] print bob.owner_posts # [] post = session.query(Post).get(1) print post.owner # <User: Alice> print post.last_editor # <User: Bob> 

Now, when you request a user, you can set this user.owner_posts or user.editor_posts object.

Answer 2

In general, this is a name issue for backref.

Since 1: n relationships are sometimes a bit confusing, I set the relationship attribute always on a unique site to avoid confusion.

then the name backref is always the only one. and the relationship attribute is always in the class referenced by the foreign key.

Now, to my suggestion for fixed code:

 class Post(Base): last_editor_id = Column(BigInteger, ForeignKey('users.id'), nullable=True) owner_id = Column(BigInteger, ForeignKey('users.id'), nullable=False, index=True) class User(Base): '''This represents a user on the site''' __tablename__ = 'users' id = Column(BigInteger, primary_key=True, unique=True) name = Column(BigInteger, nullable=False) owned_posts = relationship('Post', backref='owner') edited_posts = relationship('Post', backref='last_editor') 

Now you can get all owned User posts with User.owned_posts and all post owners with Post.owner . Same thing with last_edited attribute.

For more information, you can read the docs relationship setting