relatioship behaviour

[Leonardo Rossi]

This is my code:

from sqlalchemy import Column, Integer, Text, and_, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker

Base = declarative_base()


class Mytable(Base):

    __tablename__ = 'mytable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    name = Column(Text(), nullable=False)


class ConnectedTable(Base):

    __tablename__ = 'connectedtable'
    id = Column(Integer(), primary_key=True, autoincrement=True)
    id_mytable = Column(Integer(), nullable=True, index=True)
    connection = relationship(
        Mytable,
        backref='mytable',
        primaryjoin=and_(
            Mytable.id == id_mytable,
            id_mytable != -1,
            id_mytable is not None
        ),
        foreign_keys=Mytable.id,
        uselist=False,
    )


e = create_engine("mysql://root:@localhost/invenio", echo=True)
Base.metadata.create_all(e)
Session = sessionmaker(bind=e)
session = Session()

mytable_1 = Mytable(name="fuu")
mytable_2 = Mytable(name="bar")

session.add(mytable_1)
session.add(mytable_2)

session.commit()

conn_1 = ConnectedTable(connection=mytable_1)
conn_2 = ConnectedTable(id_mytable=mytable_2.id)
conn_3 = ConnectedTable(id_mytable=-1)

session.add(conn_1)
session.add(conn_2)
session.add(conn_3)

session.commit()

The commit generate these queries:

INSERT INTO mytable (name) VALUES ('fuu')
INSERT INTO mytable (name) VALUES ('bar')
COMMIT
INSERT INTO connectedtable (id_mytable) VALUES (None)
INSERT INTO connectedtable (id_mytable) VALUES (2)
INSERT INTO connectedtable (id_mytable) VALUES (-1)
UPDATE mytable SET id=%s WHERE mytable.id = (None, 1)

Genereting the error sqlalchemy.orm.exc.FlushError: Instance <Mytable at 0x7fbb55fbcd90> has a NULL identity key.

Questions:

1. Why ConnectedTable(connection=mytable_1) generate insert a record (id_mytable=None) instead of (id_mytable=1)?
2. Why generate also a UPDATE?

[Mike Bayer]

because you're establishing "Mytable.id" as the "foreign keys". I guess the terminology here is a little mixed up, but "foreign_keys" here refers to the columns that would "have" a FOREIGN KEY constraint, not those that "are" "foreign keys". The column(s) marked as "foreign" in this context are the ones that expect to receive the value of a remote column. So you want foreign keys to be id_mytable here.

[Leonardo Rossi]

I needed to remove ForeignKey from Mytable.id because ConnectedTable.connection can contain the value -1.

Exist the possibility to have the same behaviour of a "normal" relationship object?

[Mike Bayer]

that's not relevant here. Just flip the foreign_keys flag:

    connection = relationship(
        Mytable,
        backref='mytable',
        primaryjoin=and_(
            Mytable.id == id_mytable,
            id_mytable != -1,
            id_mytable is not None
        ),
        foreign_keys=id_mytable,

program runs great after that:

#!


2015-06-19 11:53:18,377 INFO sqlalchemy.engine.base.Engine INSERT INTO connectedtable (id_mytable) VALUES (%s)
2015-06-19 11:53:18,377 INFO sqlalchemy.engine.base.Engine (1L,)
2015-06-19 11:53:18,377 INFO sqlalchemy.engine.base.Engine INSERT INTO connectedtable (id_mytable) VALUES (%s)
2015-06-19 11:53:18,377 INFO sqlalchemy.engine.base.Engine (2L,)
2015-06-19 11:53:18,378 INFO sqlalchemy.engine.base.Engine INSERT INTO connectedtable (id_mytable) VALUES (%s)
2015-06-19 11:53:18,378 INFO sqlalchemy.engine.base.Engine (-1,)
2015-06-19 11:53:18,379 INFO sqlalchemy.engine.base.Engine COMMIT