Thanks for the quick responses, and for adding the warning for others!
I tried adding the lines as suggested (
head = relationship("Head", backref="tassel_threads", cascade_backrefs=False
)
, but I keep getting the same error. I also tried cascade=None, as James
suggested, but that seemed to mess up the ability of the relationships to
get at each other's primary keys or establish relationships with each
other. I ended up solving the issue by removing some of the merges. That
means that I'm depending on the cascading to get everything into the
database and linked up appropriately, andt I might need to spend some time
studying exactly how cascading works in order to be able to reliably use
the relationship feature in the future.
-Lyla
On Sunday, April 28, 2019 at 12:46:06 PM UTC-4, Mike Bayer wrote:
>
> the next 1.3 release will include a warning for this case, whether or
> not the primary key on the transient object is set up or not; it does
> a double insert in any case otherwise. It's just when the primary
> key is already present, the double insert fails on the primary key
> constraint.
>
> https://github.com/sqlalchemy/sqlalchemy/issues/4647
>
>
> On Sun, Apr 28, 2019 at 12:26 PM Mike Bayer <[email protected]
> <javascript:>> wrote:
> >
> > Hi there,
> >
> > I appreciate everyone chiming in to look at this!
> >
> > However, it's something simple and has to do with one of the API
> > quirks that we have to decide if we want to keep long term.
> > Background is at
> > https://docs.sqlalchemy.org/en/13/orm/cascades.html#backref-cascade.
> >
> > Basically, when you do this:
> >
> > my_head = Head(id="foobar")
> > my_head = db_session.merge(my_head)
> > db_session.commit()
> >
> > my_tassel_thread = TasselThread(head=my_head)
> >
> > "my_tassel_thread" is now in the Session due to the backref cascade,
> > which I think I really might consider defaulting to False at some
> > point. it also has no primary key value yet. So when you merge()
> > it, it gets put in a second time, again with no primary key.
> >
> > Another thing that makes this really bad on my part, is that if you
> > flush the session before the merge, then my_tassel_thread gets a new
> > primary key, then the merge is of itself and it works. This is bad
> > because it suggests merge() should be calling flush() automatically,
> > but im not sure that's a good idea in the bigger scheme of things.
> >
> > Short answer, set up the relationships like:
> >
> > tassel_threads = relationship("TasselThread",
> > back_populates="head", cascade_backrefs=False)
> >
> > # ...
> >
> > head = relationship("Head", back_populates="tassel_threads",
> > cascade_backrefs=False)
> >
> >
> > and then my_tassel_thread stays out of the Session.
> >
> > Also:
> >
> > > db_engine = create_engine('sqlite:///sample.db', convert_unicode=True)
> >
> > don't use convert_unicode, it's deprecated, has no purpose in the
> > modern Python ecosystem, and is going away. SQLite in particular
> > is a fully Python unicode backend that's impossible to get a plain
> > string out of.
> >
> >
> >
> >
> > On Sun, Apr 28, 2019 at 8:56 AM <[email protected] <javascript:>>
> wrote:
> > >
> > > Hi!
> > >
> > > I recently came across some confusing behavior in relations and
> cascading using sqllite, and I was hoping that I might get some help
> explaining what the behavior is here. I put together a minimum failing
> script here. I'm trying to commit one instance of each of two classes, but
> what ends up happening is that I commit two copies of the many part of a
> one-to-many relation. I suspect that this has something to do with
> cascading, but I found a bug report for similar behavior that claims to
> have been fixed several years ago, and I'm wondering if there was some kind
> of regression? I'm running SQLAlchemy 1.3.1 on Ubuntu and I'm still using
> sqllite at this stage of development.
> > >
> > > from sqlalchemy import create_engine
> > > from sqlalchemy.orm import scoped_session, sessionmaker
> > > from sqlalchemy.ext.declarative import declarative_base
> > > from sqlalchemy import Column, Integer, String, Text, ForeignKey
> > > from sqlalchemy.orm import relationship
> > >
> > > import os
> > >
> > > db_engine = create_engine('sqlite:///sample.db', convert_unicode=True)
> > > db_session = scoped_session(sessionmaker(autocommit=False,
> > > autoflush=False,
> > > bind=db_engine))
> > >
> > > Base = declarative_base()
> > > Base.query = db_session.query_property()
> > >
> > > class Head(Base):
> > > __tablename__ = 'head'
> > > id = Column(String, primary_key=True)
> > > tassel_threads = relationship("TasselThread",
> back_populates="head")
> > > def __init__(self, id):
> > > self.id=id
> > >
> > > class TasselThread(Base):
> > > __tablename__ = 'tassel_thread'
> > > id = Column(Integer, primary_key=True)
> > > head_id = Column(Integer, ForeignKey('head.id'), nullable=False)
> > > head = relationship("Head", back_populates="tassel_threads")
> > > def __init__(self, head):
> > > self.head = head
> > >
> > > def init_db():
> > > Base.metadata.create_all(bind=db_engine)
> > >
> > >
> > > def do_db_work():
> > >
> > > my_head = Head(id="foobar")
> > > my_head = db_session.merge(my_head)
> > > db_session.commit()
> > >
> > > my_tassel_thread = TasselThread(head=my_head)
> > > db_session.merge(my_tassel_thread)
> > > db_session.commit()
> > >
> > >
> > > if os.path.exists("sample_data.db"):
> > > os.remove("sample_data.db")
> > > init_db()
> > > do_db_work()
> > > a = db_session.query(TasselThread).all()
> > > print(len(a))
> > > # output: 2, should be 1
> > >
> > > Thanks for any help you might be able to provide!
> > >
> > > -Lyla Fischer
> > >
> > > --
> > > 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] <javascript:>.
> > > To post to this group, send email to [email protected]
> <javascript:>.
> > > Visit this group at https://groups.google.com/group/sqlalchemy.
> > > For more options, visit https://groups.google.com/d/optout.
>
--
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.
