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()
On 03/26/2016 01:27 PM, Ricardo Champa wrote:
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.
El sábado, 26 de marzo de 2016, 18:05:23 (UTC+1), Mike Bayer escribió:
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
<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 =
> 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__
> ="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 <http://self.name>
> =nombre self.id_project =id_project self.location =location
> self.comments =comments self.created_at =creado_en self.updated_at
> =actualizado_en classTag(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_tagself.guid_tag =guid_tag self.project
=project
> self.item =item self.type =type self.created_at =created_at
> self.updated_at =updated_at|
>
>
> El sábado, 26 de marzo de 2016, 17:26:04 (UTC+1), Mike Bayer
escribió:
>
>
>
> 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
>
> > =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()|
>
> nothing seems wrong with the small amount code pictured and
also i
> don't
> 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
<http://stackoverflow.com/help/mcve>
> <http://stackoverflow.com/help/mcve
<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 [email protected] <javascript:>
> > <mailto:[email protected]
<javascript:> <javascript:>>.
> > To post to this group, send email to
[email protected]
> <javascript:>
> > <mailto:[email protected] <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 [email protected] <javascript:>
> <mailto:[email protected] <javascript:>>.
> To post to this group, send email to [email protected]
<javascript:>
> <mailto:[email protected] <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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit 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 [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.
