- changed status to resolved
relatioship behaviour
Issue #3460
resolved
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:
- Why
ConnectedTable(connection=mytable_1)
generate insert a record (id_mytable=None) instead of (id_mytable=1)? - Why generate also a UPDATE?
Comments (3)
-
repo owner -
reporter I needed to remove ForeignKey from
Mytable.id
becauseConnectedTable.connection
can contain the value -1.Exist the possibility to have the same behaviour of a "normal" relationship object?
-
repo owner 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
- Log in to comment
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.