I resolved this issue, it was a misconfiguration of my 'polymorphic_on' On Wednesday, February 1, 2017 at 8:35:59 AM UTC-6, Shane Carey wrote: > > I took the SQL into mysql directly to see what may be going wrong, it > looks like the generated > > 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 = 1 AND (SELECT type.id FROM type WHERE type.id = > thing.type_id) IN ('stuff'); > > returns no rows, the final row of that query should be ... (SELECT > type.type FROM type ... > > which returns one row. > > I feel like I misconfigured something to generate the incorrect SQL. > > On Wednesday, February 1, 2017 at 8:14:06 AM UTC-6, Shane Carey wrote: >> >> 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.