[Sqlalchemy-tickets] Issue #3460: relatioship behaviour (zzzeek/sqlalchemy)

[Leonardo R. <iss...@bi...>]

New issue 3460: relatioship behaviour
https://bitbucket.org/zzzeek/sqlalchemy/issue/3460/relatioship-behaviour

Leonardo Rossi:

This is my code:

```python
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:

```sql
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?