I am having a strange issue with merging a polymorphic relationship, I've managed to reduce my problem to this code:
from sqlalchemy import * from sqlalchemy import select, and_, event, inspect from sqlalchemy.orm import * from sqlalchemy.ext.declarative import * Base = declarative_base() class Type(Base): __tablename__ = 'type' id = Column(Integer, primary_key=True) type = Column(String(8), unique=True) class Thing(Base): __tablename__ = 'thing' id = Column(Integer, primary_key=True) type_id = Column(Integer, ForeignKey(Type.id), nullable=False) txt = Column(String(8)) type = relationship(Type) tags = relationship('Tag', backref='thing') __mapper_args__ = { 'polymorphic_on': select([Type.id]).where(Type.id == type_id).as_scalar(), 'with_polymorphic': '*' } @event.listens_for(Thing, 'init', propagate=True) def set_identity(instance, *args, **kwargs): mapper = object_mapper(instance) instance.type_id = select([Type.id]).where(Type.type == mapper.polymorphic_identity) class Stuff(Thing): stuff = Column(String(8)) __mapper_args__ = { 'polymorphic_identity': 'stuff' } class Junk(Thing): junk = Column(String(8)) __mapper_args__ = { 'polymorphic_identity': 'junk' } class Tag(Base): __tablename__ = 'tag' id = Column(Integer, primary_key=True) thing_id = Column(Integer, ForeignKey(Thing.id), nullable=False) txt = Column(String(8)) e = create_engine('mysql+pymysql://username:password@localhost/test', echo=True) Base.metadata.drop_all(e) Base.metadata.create_all(e) s = Session(e) s.add_all([Type(type='stuff'), Type(type='junk')]) thing = Stuff(id=1, txt='thing1', stuff='stuff1') tag = Tag(id=1, txt='tag1', thing=thing) s.merge(tag) s.commit() thing = Stuff(id=1, txt='thing2', stuff='stuff2') tag = Tag(id=1, txt='tag2', thing=thing) s.merge(tag) s.commit() This gives me the error sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'PRIMARY'") [SQL: 'INSERT INTO thing (id, type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id \nFROM type \nWHERE type.type = %(type_1)s), %(txt)s, %(stuff)s)'] [parameters: {'txt': 'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'}] And the full output is 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin' 2017-02-01 08:07:29,401 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `type` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `tag` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `type` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine DESCRIBE `thing` 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine ROLLBACK 2017-02-01 08:07:29,417 INFO sqlalchemy.engine.base.Engine CREATE TABLE type ( id INTEGER NOT NULL AUTO_INCREMENT, type VARCHAR(8), PRIMARY KEY (id), UNIQUE (type) ) 2017-02-01 08:07:29,433 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,479 INFO sqlalchemy.engine.base.Engine COMMIT 2017-02-01 08:07:29,479 INFO sqlalchemy.engine.base.Engine CREATE TABLE thing ( id INTEGER NOT NULL AUTO_INCREMENT, type_id INTEGER NOT NULL, txt VARCHAR(8), stuff VARCHAR(8), junk VARCHAR(8), PRIMARY KEY (id), FOREIGN KEY(type_id) REFERENCES type (id) ) 2017-02-01 08:07:29,479 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,512 INFO sqlalchemy.engine.base.Engine COMMIT 2017-02-01 08:07:29,513 INFO sqlalchemy.engine.base.Engine CREATE TABLE tag ( id INTEGER NOT NULL AUTO_INCREMENT, thing_id INTEGER NOT NULL, txt VARCHAR(8), PRIMARY KEY (id), FOREIGN KEY(thing_id) REFERENCES thing (id) ) 2017-02-01 08:07:29,513 INFO sqlalchemy.engine.base.Engine {} 2017-02-01 08:07:29,575 INFO sqlalchemy.engine.base.Engine COMMIT 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine INSERT INTO type (type) VALUES (%(type)s) 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine {'type': 'stuff'} 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine INSERT INTO type (type) VALUES (%(type)s) 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine {'type': 'junk'} 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine SELECT tag.id AS tag_id, tag.thing_id AS tag_thing_id, tag.txt AS tag_txt FROM tag WHERE tag.id = %(param_1)s 2017-02-01 08:07:29,591 INFO sqlalchemy.engine.base.Engine {'param_1': 1} 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine SELECT thing.id AS thing_id, thing.type_id AS thing_type_id, thing.txt AS thing_txt, (SELECT type.id FROM type WHERE type.id = thing.type_id) AS _sa_polymorphic_on, thing.stuff AS thing_stuff FROM thing WHERE thing.id = %(param_1)s AND (SELECT type.id FROM type WHERE type.id = thing.type_id) IN (%(param_2)s) 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 'stuff'} 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine INSERT INTO thing (id, type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id FROM type WHERE type.type = %(type_1)s), %(txt)s, %(stuff)s) 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine {'txt': 'thing1', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff1'} 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine INSERT INTO tag (id, thing_id, txt) VALUES (%(id)s, %(thing_id)s, %(txt)s) 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine {'id': 1, 'txt': 'tag1', 'thing_id': 1} 2017-02-01 08:07:29,606 INFO sqlalchemy.engine.base.Engine COMMIT 2017-02-01 08:07:29,622 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2017-02-01 08:07:29,622 INFO sqlalchemy.engine.base.Engine SELECT tag.id AS tag_id, tag.thing_id AS tag_thing_id, tag.txt AS tag_txt FROM tag WHERE tag.id = %(param_1)s 2017-02-01 08:07:29,622 INFO sqlalchemy.engine.base.Engine {'param_1': 1} 2017-02-01 08:07:29,622 INFO sqlalchemy.engine.base.Engine SELECT thing.id AS thing_id, thing.type_id AS thing_type_id, thing.txt AS thing_txt, (SELECT type.id FROM type WHERE type.id = thing.type_id) AS _sa_polymorphic_on, thing.stuff AS thing_stuff FROM thing WHERE thing.id = %(param_1)s AND (SELECT type.id FROM type WHERE type.id = thing.type_id) IN (%(param_2)s) 2017-02-01 08:07:29,622 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 'stuff'} 2017-02-01 08:07:29,637 INFO sqlalchemy.engine.base.Engine INSERT INTO thing (id, type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id FROM type WHERE type.type = %(type_1)s), %(txt)s, %(stuff)s) 2017-02-01 08:07:29,637 INFO sqlalchemy.engine.base.Engine {'txt': 'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'} 2017-02-01 08:07:29,637 INFO sqlalchemy.engine.base.Engine ROLLBACK C:\Python35\lib\site-packages\pymysql\cursors.py:166: Warning: (1292, "Truncated incorrect DOUBLE value: 'stuff'") result = self._query(query) Traceback (most recent call last): File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context context) File "C:\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 462, in do_execute cursor.execute(statement, parameters) File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 166, in execute result = self._query(query) File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 322, in _query conn.query(q) File "C:\Python35\lib\site-packages\pymysql\connections.py", line 835, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "C:\Python35\lib\site-packages\pymysql\connections.py", line 1019, in _read_query_result result.read() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 1302, in read first_packet = self.connection._read_packet() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 981, in _read_packet packet.check_error() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "C:\Python35\lib\site-packages\pymysql\err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.IntegrityError: (1062, "Duplicate entry '1' for key 'PRIMARY'") The above exception was the direct cause of the following exception: Traceback (most recent call last): File "sqlalchemy_casc_backref_test.py", line 76, in <module> s.commit() File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 874, in commit self.transaction.commit() File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 461, in commit self._prepare_impl() File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 441, in _prepare_impl self.session.flush() File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2137, in flush self._flush(objects) File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2257, in _flush transaction.rollback(_capture_exception=True) File "C:\Python35\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__ compat.reraise(exc_type, exc_value, exc_tb) File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise raise value File "C:\Python35\lib\site-packages\sqlalchemy\orm\session.py", line 2221, in _flush flush_context.execute() File "C:\Python35\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 389, in execute rec.execute(self) File "C:\Python35\lib\site-packages\sqlalchemy\orm\unitofwork.py", line 548, in execute uow File "C:\Python35\lib\site-packages\sqlalchemy\orm\persistence.py", line 176, in save_obj mapper, table, insert) File "C:\Python35\lib\site-packages\sqlalchemy\orm\persistence.py", line 827, in _emit_insert_statements params) File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 945, in execute return meth(self, multiparams, params) File "C:\Python35\lib\site-packages\sqlalchemy\sql\elements.py", line 263, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1053, in _execute_clauseelement compiled_sql, distilled_params File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context context) File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1393, in _handle_dbapi_exception exc_info File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 202, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "C:\Python35\lib\site-packages\sqlalchemy\util\compat.py", line 185, in reraise raise value.with_traceback(tb) File "C:\Python35\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context context) File "C:\Python35\lib\site-packages\sqlalchemy\engine\default.py", line 462, in do_execute cursor.execute(statement, parameters) File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 166, in execute result = self._query(query) File "C:\Python35\lib\site-packages\pymysql\cursors.py", line 322, in _query conn.query(q) File "C:\Python35\lib\site-packages\pymysql\connections.py", line 835, in query self._affected_rows = self._read_query_result(unbuffered=unbuffered) File "C:\Python35\lib\site-packages\pymysql\connections.py", line 1019, in _read_query_result result.read() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 1302, in read first_packet = self.connection._read_packet() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 981, in _read_packet packet.check_error() File "C:\Python35\lib\site-packages\pymysql\connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "C:\Python35\lib\site-packages\pymysql\err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval) sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry '1' for key 'PRIMARY'") [SQL: 'INSERT INTO thing (id, type_id, txt, stuff) VALUES (%(id)s, (SELECT type.id \nFROM type \nWHERE type.type = %(type_1)s), %(txt)s, %(stuff)s)'] [parameters: {'txt': 'thing2', 'id': 1, 'type_1': 'stuff', 'stuff': 'stuff2'}] Why is the second merge generating an INSERT statement? I can see where merge actually selects the appropriate Thing with id = 1 from the database, but it still generates the INSERT rather than update. If I remove all polymorphism from the experiment, this does not occur. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.