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 <mik...@zzzcomputing.com > <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 <lyla...@gmail.com <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 sqlal...@googlegroups.com <javascript:>. > > > To post to this group, send email to sqlal...@googlegroups.com > <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 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.