On Sun, Apr 28, 2019 at 9:17 PM <lylafi...@gmail.com> wrote: > > 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.
with the specific test case you gave, adding cascade_backrefs to *both* relationships as above resolves the assertion at the end. Is this assertion "the error" you're referring towards ? I've tested in 1.3 as well as 1.2. Is there a new test case you can provide ? > 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 <mik...@zzzcomputing.com> 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 <lyla...@gmail.com> 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 sqlal...@googlegroups.com. >> > > To post to this group, send email to sqlal...@googlegroups.com. >> > > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.