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.
