can't insert into one-to-many relationship in sqlalchemy

Question

I have a one to many relationship in sqlalchemy but I don't get the inserts to work properly. I have tried to make a minimal example here:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

db = create_engine('sqlite://')
db.echo = True

metadata = MetaData(db)
Base = declarative_base()
Session = sessionmaker(bind=db)
session = Session()

class Child(Base):
    __table__ = Table('child', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('parent_id', Integer),
        Column('name', String(50))
    )

class Parent(Base):
    __table__ = Table('parent', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('name', String(50))
    )

    children = relationship(Child, primaryjoin="Parent.id == Child.parent_id",
                            foreign_keys=[__table__.c.id])



Base.metadata.create_all(db)

c = Child(id=1, name="C")
p = Parent(id=1, name="P", children=[c])
session.add(p)
session.commit()

Running this gives AttributeError: 'list' object has no attribute '_sa_instance_state' from session.add(p).

I tried changing the classes to this:

class Child(Base):
    __tablename__ = 'child'

    id = Column('id', Integer, primary_key=True)
    parent_id = Column('parent_id', Integer, ForeignKey('parent.id'))
    name = Column('name', String(50))

class Parent(Base):
    __tablename__ = 'parent'

    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(50))

    children = relationship(Child, backref="parent")

and then it works. I specify that the parent_id is a foreign key there and use the backref syntax. However in my production code the Parent table is a temporary table so I can't directly reference it using a ForeignKey. So whats wrong with the first code block and how can it be fixed ?

edsioufi edsioufi · Accepted Answer · 2013-08-18T13:34:03

From the SQLAlchemy relationship API in the docs:

That is, if the primaryjoin condition of this relationship() is a.id == b.a_id, and the values in b.a_id are required to be present in a.id, then the “foreign key” column of this relationship() is b.a_id.

In your example, child.parent_id is required to be present in parent.id. So your "foreign key" column is child.parent_id.

Therefore, changing:

foreign_keys=[__table__.c.id]

to this:

foreign_keys=[Child.__table__.c.parent_id]

should solve your problem.

can't insert into one-to-many relationship in sqlalchemy

1 Answers