SQLALchemy session.merge is not working as expected
1,549 views
Skip to first unread message
Ricardo Champa
Mar 26, 2016, 12:06:48 PM3/26/16
to sqlalchemy
AFAIK merge performs and insert or update so what I'm doing is pretty simple. There is a relation of 1 to N between Hubs and Tags. So when I try to make a merge on tag and hub, hub goes well and load from DB the existent hub and make an update, but fails when db.session.merge(nuevo_tag) is executed throwing an exception because behind the scenes is try to make an insert, even if the tag previously exist. What I did wrong?
nuevo_hub = Hub(guid_hub,name,location,comments,id_project,creado_en,actualizado_en)
merged_hub = db.session.merge(nuevo_hub)
#db.session.commit() # If I use this line tags perform an insert.
nuevo_tag = Tag(guid_tag,project,merged_hub,TYPE_HUB,creado_en,actualizado_en)
merged_tag = db.session.merge(nuevo_tag)
db.session.commit()
Mike Bayer
Mar 26, 2016, 12:26:04 PM3/26/16
to sqlal...@googlegroups.com
On 03/26/2016 12:06 PM, Ricardo Champa wrote:
> AFAIK merge performs and |insert or update| so what I'm doing is pretty
> simple. There is a relation of 1 to N between Hubs and Tags. So when I
> try to make a merge on tag and hub, hub goes well and load from DB the
> existent hub and make an update, but fails when
> |db.session.merge(nuevo_tag)| is executed throwing an exception because
> behind the scenes is try to make an insert, even if the tag previously
> exist. What I did wrong?
>
> |nuevo_hub
> =Hub(guid_hub,name,location,comments,id_project,creado_en,actualizado_en)merged_hub
> tags perform an insert.nuevo_tag
> =Tag(guid_tag,project,merged_hub,TYPE_HUB,creado_en,actualizado_en)merged_tag
> =db.session.merge(nuevo_tag)db.session.commit()|
nothing seems wrong with the small amount code pictured and also i don't
> =Tag(guid_tag,project,merged_hub,TYPE_HUB,creado_en,actualizado_en)merged_tag
> =db.session.merge(nuevo_tag)db.session.commit()|
know what exception you are getting, nor do I have enough context to
understand the issue in general, so please provide minimal, working
mappings and a demonstration per the guidelines at
http://stackoverflow.com/help/mcve, thanks!
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
Ricardo Champa
Mar 26, 2016, 12:51:25 PM3/26/16
to sqlalchemy
Sorry, You are right.
I want to perform and insert or update but I always get this error:
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.IntegrityError) (1062, "Duplicate entry '\\x11\\x0E\\x84\\x00\\xE2\\x9B\\x11\\xD4\\xA7\\x16DfUD\\x00\\x0D' for key 'PRIMARY'") [SQL: 'INSERT INTO tags (guid_tag, id_project, guid_item, type, created_at, updated_at) VALUES (%(guid_tag)s, %(id_project)s, %(guid_item)s, %(type)s, %(created_at)s, %(updated_at)s)'] [parameters: {'guid_item': b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00"', 'id_project': 1, 'created_at': '2016-03-22 06:00:01', 'updated_at': '2016-03-23 06:21:31', 'type': 0, 'guid_tag': b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00\r'}]
There is a row in DB that has the same PK, so I expect merge bring that record from DB but instead of it perform a new insert as error message shows.
The whole definition of clases and models.
class Item(db.Model):
__tablename__ = "items"
# id_item = db.Column(db.Integer, autoincrement=True, primary_key=True)
guid_item = db.Column(db.BINARY(16), primary_key=True)
id_project = db.Column(db.Integer,db.ForeignKey("projects.id_project"))
type = db.Column(db.Integer)
name = db.Column(db.String(50), nullable=False, index= True)
created_at = db.Column(db.DateTime)
updated_at = db.Column(db.DateTime)
__mapper_args__ = {
'polymorphic_identity': 'items',
'polymorphic_on':type,
'with_polymorphic':'*'
}
__table_args__ = (
db.UniqueConstraint('name', 'id_project', name='_unique_name_project'),
)
def __init__(self,creado_en=None):
self.created_at = creado_en
self.updated_at = creado_en
class Hub(Item):
__tablename__ = "hubs"
__mapper_args__ = {
'polymorphic_identity': TYPE_HUB,
'with_polymorphic':'*'
}
guid_hub = db.Column(db.BINARY(16), db.ForeignKey(Item.guid_item), primary_key=True)
location = db.Column(db.String(50))
comments = db.Column(db.String(128))
def __init__(self, guid_hub=None, nombre=None, location=None,comments=None, id_project=None, creado_en=None, actualizado_en=None):
self.type = TYPE_HUB
self.guid_item = guid_hub
self.guid_hub = guid_hub
self.name = nombre
self.id_project = id_project
self.location = location
self.comments = comments
self.created_at = creado_en
self.updated_at = actualizado_en
class Tag(db.Model):
__tablename__ = "tags"
guid_tag = db.Column(db.BINARY(16), primary_key=True)
id_project = db.Column(db.Integer,db.ForeignKey("projects.id_project"))
guid_item = db.Column(db.BINARY(16),db.ForeignKey("items.guid_item"))
project = db.relationship(Proyecto, backref=db.backref('list_tags', lazy='dynamic'))
item = db.relationship(Item, backref=db.backref('list_tags', lazy='joined'))
type = db.Column(db.Integer) #(0,hub);(1,cable);(2,pipe);(3,electrical_pipes)
created_at = db.Column(db.DateTime)
updated_at = db.Column(db.DateTime)
def __init__(self,guid_tag,project,item,type,created_at,updated_at):
# self.guid_item = guid_tag
self.guid_tag = guid_tag
self.project = project
self.item = item
self.type = type
self.created_at = created_at
self.updated_at = updated_at> <mailto:sqlalchemy+unsub...@googlegroups.com>.
Mike Bayer
Mar 26, 2016, 1:05:23 PM3/26/16
to sqlal...@googlegroups.com
On 03/26/2016 12:51 PM, Ricardo Champa wrote:
> Sorry, You are right.
This seems like something I saw recently, you might want to check if
nuevo_tag isn't already in the session. But I can't re-identify the
problem without a much more concise and runnable example (remove all
columns, directives, and relationships not part of the problem, show use
of Session, etc).
Again the best way to make this a one-email exchange is to follow the
guidelines in the http://stackoverflow.com/help/mcve document.
>
> I want to perform and *insert or update* but I always get this error:
>
> sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked
> autoflush; consider using a session.no_autoflush block if this flush
> is occurring prematurely) (pymysql.err.IntegrityError) (1062,
> "Duplicate entry
> '\\x11\\x0E\\x84\\x00\\xE2\\x9B\\x11\\xD4\\xA7\\x16DfUD\\x00\\x0D'
> for key 'PRIMARY'") [SQL: 'INSERT INTO tags (guid_tag, id_project,
> guid_item, type, created_at, updated_at) VALUES (%(guid_tag)s,
> %(id_project)s, %(guid_item)s, %(type)s, %(created_at)s,
> %(updated_at)s)'] [parameters: {'guid_item':
> b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00"', 'id_project':
> 1, 'created_at': '2016-03-22 06:00:01', 'updated_at': '2016-03-23
> 06:21:31', 'type': 0, 'guid_tag':
> b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00\r'}]
>
>
> There is a row in DB that has the same PK, so I expect merge bring that
> record from DB but instead of it perform a new insert as error message
> shows.
>
> The whole definition of clases and models.
>
> |classItem(db.Model):__tablename__ ="items"# id_item =
> sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked
> autoflush; consider using a session.no_autoflush block if this flush
> is occurring prematurely) (pymysql.err.IntegrityError) (1062,
> "Duplicate entry
> '\\x11\\x0E\\x84\\x00\\xE2\\x9B\\x11\\xD4\\xA7\\x16DfUD\\x00\\x0D'
> for key 'PRIMARY'") [SQL: 'INSERT INTO tags (guid_tag, id_project,
> guid_item, type, created_at, updated_at) VALUES (%(guid_tag)s,
> %(id_project)s, %(guid_item)s, %(type)s, %(created_at)s,
> %(updated_at)s)'] [parameters: {'guid_item':
> b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00"', 'id_project':
> 1, 'created_at': '2016-03-22 06:00:01', 'updated_at': '2016-03-23
> 06:21:31', 'type': 0, 'guid_tag':
> b'\x11\x0e\x84\x00\xe2\x9b\x11\xd4\xa7\x16DfUD\x00\r'}]
>
>
> There is a row in DB that has the same PK, so I expect merge bring that
> record from DB but instead of it perform a new insert as error message
> shows.
>
> The whole definition of clases and models.
>
> db.Column(db.Integer, autoincrement=True, primary_key=True)guid_item
> =db.Column(db.BINARY(16),primary_key=True)id_project
> =db.Column(db.Integer,db.ForeignKey("projects.id_project"))type
> =db.Column(db.Integer)name
> =db.Column(db.String(50),nullable=False,index=True)created_at
> =db.Column(db.DateTime)updated_at =db.Column(db.DateTime)__mapper_args__
> ={'polymorphic_identity':'items','polymorphic_on':type,'with_polymorphic':'*'}__table_args__
> =(db.UniqueConstraint('name','id_project',name='_unique_name_project'),)def__init__(self,creado_en=None):self.created_at
> =creado_en self.updated_at =creado_en classHub(Item):__tablename__
> =db.Column(db.BINARY(16),primary_key=True)id_project
> =db.Column(db.Integer,db.ForeignKey("projects.id_project"))type
> =db.Column(db.Integer)name
> =db.Column(db.String(50),nullable=False,index=True)created_at
> =db.Column(db.DateTime)updated_at =db.Column(db.DateTime)__mapper_args__
> ={'polymorphic_identity':'items','polymorphic_on':type,'with_polymorphic':'*'}__table_args__
> =(db.UniqueConstraint('name','id_project',name='_unique_name_project'),)def__init__(self,creado_en=None):self.created_at
>
>
> >
> > --
> > 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+...@googlegroups.com <javascript:>
>
> >
> > --
> > 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
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> --
> 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+...@googlegroups.com
Ricardo Champa
Mar 26, 2016, 1:27:48 PM3/26/16
to sqlalchemy
I tried to simplify the code as much as i can. I did another simple sample of code wihtout columns and relationships (adding and removing) and seems working fine. There is an error with my code above but I cant figure out what.
> > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+unsub...@googlegroups.com>.
Mike Bayer
Mar 27, 2016, 8:05:34 PM3/27/16
to sqlal...@googlegroups.com
OK, here's a demonstration of the issue that this looks like, see if
cascade_backrefs solves your problem:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Hub(Base):
__tablename__ = 'hub'
id = Column(Integer, primary_key=True)
class Tag(Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True)
hub_id = Column(ForeignKey('hub.id'))
hub = relationship("Hub", backref=backref("tags"))
# with cascade_backrefs set, the Tag object will not be automatically
# cascaded into the Session when Tag(hub=some_hub) is called; see below
# hub = relationship("Hub", backref=backref("tags",
cascade_backrefs=False))
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
# 1. pre-existing Tag object in the database
s.add(Tag(id=1))
# 2. commit; this expires all state in the Session and we don't see
# id=1 in memory anymore.
s.commit()
# 3. new Hub
hub = Hub(id=1)
# 4. merged_hub is in the pending session state
merged_hub = s.merge(hub)
# assert pending state; not INSERTed yet
assert merged_hub in s.new
# 5. create a Tag, referencing the Hub. The backref assigns the Tag
# to the merged_hub.tags collection, which then *adds* the Tag into the
# session as a *new, non-merged object*. If we use the cascade_backrefs
# flag, this effect does not occur. Documentation at:
# http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html#backref-cascade
tag = Tag(id=1, hub=merged_hub)
# 6a. if we called flush() here, we'd get the same error...
# s.flush()
# 6b. ... since the tag object is *in the session*
assert tag in s.new
# 7. now we get to the merge part here, which looks in the DB for
# tag id=1, does an autoflush, raises error. We know the error is here
# due to the stack trace as well as that it refers to "autoflush".
merged_tag = s.merge(tag)
s.commit()
> self.name <http://self.name>
> > > <mailto:sqlalchemy+...@googlegroups.com
> <javascript:> <javascript:>>.
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.
cascade_backrefs solves your problem:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Hub(Base):
__tablename__ = 'hub'
id = Column(Integer, primary_key=True)
class Tag(Base):
__tablename__ = 'tag'
id = Column(Integer, primary_key=True)
hub_id = Column(ForeignKey('hub.id'))
hub = relationship("Hub", backref=backref("tags"))
# with cascade_backrefs set, the Tag object will not be automatically
# cascaded into the Session when Tag(hub=some_hub) is called; see below
# hub = relationship("Hub", backref=backref("tags",
cascade_backrefs=False))
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
# 1. pre-existing Tag object in the database
s.add(Tag(id=1))
# 2. commit; this expires all state in the Session and we don't see
# id=1 in memory anymore.
s.commit()
# 3. new Hub
hub = Hub(id=1)
# 4. merged_hub is in the pending session state
merged_hub = s.merge(hub)
# assert pending state; not INSERTed yet
assert merged_hub in s.new
# 5. create a Tag, referencing the Hub. The backref assigns the Tag
# to the merged_hub.tags collection, which then *adds* the Tag into the
# session as a *new, non-merged object*. If we use the cascade_backrefs
# flag, this effect does not occur. Documentation at:
# http://docs.sqlalchemy.org/en/rel_1_0/orm/cascades.html#backref-cascade
tag = Tag(id=1, hub=merged_hub)
# 6a. if we called flush() here, we'd get the same error...
# s.flush()
# 6b. ... since the tag object is *in the session*
assert tag in s.new
# 7. now we get to the merge part here, which looks in the DB for
# tag id=1, does an autoflush, raises error. We know the error is here
# due to the stack trace as well as that it refers to "autoflush".
merged_tag = s.merge(tag)
s.commit()
> <javascript:> <javascript:>>.
> > > To post to this group, send email to
> sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at
> https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>
> > <https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>>.
> > > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> > 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+...@googlegroups.com <javascript:>
> sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at
> https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>
> > <https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>>.
> > > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> > 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
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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+...@googlegroups.com
Reply all
Reply to author
Forward
0 new messages