Re: [sqlalchemy] SQLAlchemy is trying to insert duplicate rows in an M2M Relationship

Michael Bayer Fri, 14 Dec 2012 20:31:04 -0800

I've cobbled together a complete and simplified test case given your mapping 
and example code and I cannot reproduce with either 0.7 or 0.8 - the count of 
rows in the association table is one on the first commit, and two on the second.


You need to adapt the attached test case into a full reproducing case so that 
the specific trigger is illustrated...thanks.





On Dec 14, 2012, at 10:22 PM, Donald Stufft wrote:

> I have 2 tables with a third intermediary table. These tables are (shorted): 
> https://gist.github.com/9ff8afa793c9150c6b70
> 
> Using this the association_proxy correctly reuses existing rows in the 
> database if they already exist. However if I do this:
> 
> v = Version.query.first()
> v.classifiers = [u"Foo"]
> db.session.commit()  # A Classifier with trove=u"Foo" is either retrieved or 
> created
> 
> v.classifiers = [u"Foo", u"Bar"]
> db.session.commit()
> 
> An error occurs because SQLAlchemy tried to insert a second row in the 
> intermediary table for this Version + Classifier(u"Foo").
> 
> So my question is how can I get it to properly handle the fact that there 
> should only ever be 1 mapping of a particular Version
> to a particular Classifier?
> 
> Note, I can work around this by doing:
> 
> classifiers = [u"Foo", u"Bar"]
> for c in classifiers:
>     if not c in v.classifiers:
>         v.classifiers.append(c)
> 
> But I would really rather have a solution that is contained inside of my 
> Model.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/9BCPl6rebKsJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

On Dec 14, 2012, at 10:22 PM, Donald Stufft wrote:

I have 2 tables with a third intermediary table. These tables are (shorted): https://gist.github.com/9ff8afa793c9150c6b70

Using this the association_proxy correctly reuses existing rows in the database if they already exist. However if I do this:

v = Version.query.first()
v.classifiers = [u"Foo"]
db.session.commit() # A Classifier with trove=u"Foo" is either retrieved or created

v.classifiers = [u"Foo", u"Bar"]
db.session.commit()

An error occurs because SQLAlchemy tried to insert a second row in the intermediary table for this Version + Classifier(u"Foo").

So my question is how can I get it to properly handle the fact that there should only ever be 1 mapping of a particular Version
to a particular Classifier?

Note, I can work around this by doing:

classifiers = [u"Foo", u"Bar"]
for c in classifiers:
if not c in v.classifiers:
v.classifiers.append(c)

But I would really rather have a solution that is contained inside of my Model.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/9BCPl6rebKsJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.exc import NoResultFound


Base = declarative_base()

classifiers = Table("version_classifiers",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("classifier_id", Integer,
              ForeignKey("classifiers.id", ondelete="CASCADE"),
              nullable=False),
    Column("version_id", Integer,
              ForeignKey("versions.id", ondelete="CASCADE"),
              nullable=False),
    UniqueConstraint("classifier_id", "version_id")
)

class Classifier(Base):

    __tablename__ = "classifiers"

    id = Column(Integer, primary_key=True)
    trove = Column(UnicodeText, unique=True, nullable=False)

    def __init__(self, trove):
        self.trove = trove

    def __repr__(self):
        return "<Classifier: {trove}>".format(trove=self.trove)

    @classmethod
    def get_or_create(cls, trove):
        try:
            obj = Session.query(cls).filter_by(trove=trove).one()
        except NoResultFound:
            obj = cls(trove)
        return obj

class Version(Base):

    __tablename__ = "versions"

    id = Column(Integer, primary_key=True)
    _classifiers = relationship("Classifier", secondary=classifiers,
                               backref=backref("versions", lazy='dynamic'))
    classifiers = association_proxy("_classifiers", "trove",
                                    creator=Classifier.get_or_create)

e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)

Session = scoped_session(sessionmaker(e))

Session.add(Version())

v = Session.query(Version).first()
v.classifiers = [u"Foo"]
Session.commit()  # A Classifier with trove=u"Foo" is either retrieved or created

assert Session.scalar(select([func.count('*')]).select_from(classifiers)) == 1

v.classifiers = [u"Foo", u"Bar"]
Session.commit()

assert Session.scalar(select([func.count('*')]).select_from(classifiers)) == 2

Re: [sqlalchemy] SQLAlchemy is trying to insert duplicate rows in an M2M Relationship

Reply via email to