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.
