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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
