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.
