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 <mike...@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 <lylafi...@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 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.
