Re: [sqlalchemy] Possible regression?

[James Fennell]

Thanks for the explanation Mike! Seeing it now, I actually think there’s a 
decent reason to want the current backerefs:

My understanding is that with session.merge in SQL Alchemy it’s possible to 
draw a very clean line between entities that are persisted (or about to be 
persisted on the next flush) and entities which will never be persisted. This 
is owing to the design choice whereby SQL Alchemy doesn’t persist the entity 
you pass into the merge; instead, that is kept alone and a new entity is 
created.

With this in mind, there are two ways to see Lyla’s example.

One way: as soon as the tassel_thread was related to the persisted my_head 
(persisted because of the line my_head=session.merge(my_head)) then 
tassel_thread should be seen as in the session already. In this view, the merge 
is necessary and possibly error-prone, as here.

Another way: instead of assigning my_head=session.merge(my_head), keep the 
unpersisted head around with say persisted_head = session.merge(my_head). Then 
relating the new tassel_thread to my_head won’t add it to the session. To get a 
record into the DB, then do a session.merge on it - everything works correctly 
this way.


In both cases, there is the idea of a persisted object graph and a distinct 
unpersisted object graph. Once you relate a new entity to something in the 
persisted object graph, it becomes persistent. 

-- 
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.

Re: [sqlalchemy] Possible regression?

[Lyla Fischer]

I just tried it again, and it works. Sorry for the confusion. Knowing
myself, I misspelled something when I tried it the first time.

-Lyla

On Sun, Apr 28, 2019 at 9:57 PM Mike Bayer  wrote:

> On Sun, Apr 28, 2019 at 9:17 PM  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 
> 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  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
> >> > >
> >> > > 

Re: [sqlalchemy] Possible regression?

[Mike Bayer]

On Sun, Apr 28, 2019 at 9:17 PM  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  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  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 = 

Re: [sqlalchemy] Possible regression?

[lylafisch]

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  > 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 > 
> 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 = 

Re: [sqlalchemy] Possible regression?

[Mike Bayer]

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  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  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 

Re: [sqlalchemy] Possible regression?

[Mike Bayer]

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  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, 

[sqlalchemy] Re: Possible regression?

[James Fennell]

It seems to be related to the cascades happening recursively. The merge 
cascade goes from the tassel thread to the head, and then again down from 
the head to the tassel thread - which is kind of strange, I would expect 
the cascades to only visit each node in the object graph at most once. The 
second cascade is when the second tassel thread is created.

There are a couple of ways that I can get this to work:

   - Instead of setting head when creating the tassel thread, set head_id. 
   This avoids the first cascade being triggered.
   - Add cascade=None on the relationship in the Head to avoid the second 
   cascade being triggered. However the merge operation on the tassel thread 
   now returns a warning that the "add operation along 'Head.tassel_threads' 
   will not proceed"
   - The your second merge, instead of merging in the tassel_thread, merge 
   in the head again.

Btw, I'm using SQL Alchemy 1.2.16 and the thing you're seeing is still 
repro-able.

On Sunday, 28 April 2019 08:56:40 UTC-4, 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 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] Re: Possible regression?

[peter . m . schutt]

Hi Lyla,

I notice Head.id is defined as String:

>     id = Column(String, primary_key=True)

While TasselThread.head_id is defined as Integer:

>     head_id = Column(Integer, ForeignKey('head.id'), nullable=False)

Could it be that causes the merge to not recognize the existing instance and a 
new one is created?

-- 
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] Possible regression?

[lylafisch]

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.