Re: [sqlalchemy] Defining model.py for existing database

[Larry Martell]

You can use the sqlacodegen tool for generating models from an existing schema.

https://pypi.org/project/sqlacodegen/

On Fri, Dec 18, 2020 at 3:41 PM Rich Shepard  wrote:
>
> I'm working on learning SQLAlchemy to use for my business tracking
> application. I have the views written (in tkinter) and two versions of
> model.py as the model. Long ago I asked about using base or declarative base
> but no longer have that thread saved.
>
> I've read about base mapping and declarative base and am still confused.
>
> The short version, which I believe is correct, references the existing,
> populated postgresql-12 tables:
>
> """
>This is the SQLAlchemy declarative mapping python classes to postgres
>tables for the business tracker.
> """
>
> from sqlalchemy import create_engine
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, Unicode, Integer, String, Date
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy import ForeignKey
> from sqlalchemy.orm import relationship
> from sqlalchemy import CheckConstraint
> from sqlalchemy.orm import Session
> from sqlalchemy.dialects import postgresql
>
> """Base = declarative_base()"""
> Base = automap_base()
>
> engine = create_engine('postgresql+psycopg2:///bustrac')
>
> # reflect the tables
> Base.prepare(engine, reflect=True)
>
> state_code = postgresql.Enum('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 
> 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 
> 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 
> 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 
> 'WA', 'WV', 'WI', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 
> 'PE', 'QC', 'SK', 'YT', name='state_code')
>
> Industries = Base.classes.industries
> Status = Base.classes.status
> StatusTypes = Base.classes.statusTypes
> ActivityTypes = Base.classes.activityTypes
> Organizations = Base.classes.organizations
> Locations = Base.classes.locations
> People = Base.classes.people
> Activities = Base.classes.activities
> Cases = Base.classes.cases
> Projects = Base.classes.projects
>
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
>
> The longer version defines the classes for each table.
>
> When I fully understand the model format I'll ask for where to start in the
> docs to learn how to structure the controller portion using .sql scripts
> already written.
>
> TIA,
>
> Rich
>
> --
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.2012181231590.29974%40salmo.appl-ecosys.com.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CACwCsY6HaEYNmVW%3DjvnTMOLFyV4EggiB63gqd_gOoVTU5RrjQw%40mail.gmail.com.

[sqlalchemy] Defining model.py for existing database

[Rich Shepard]

I'm working on learning SQLAlchemy to use for my business tracking
application. I have the views written (in tkinter) and two versions of
model.py as the model. Long ago I asked about using base or declarative base
but no longer have that thread saved.

I've read about base mapping and declarative base and am still confused.

The short version, which I believe is correct, references the existing,
populated postgresql-12 tables:

"""
  This is the SQLAlchemy declarative mapping python classes to postgres
  tables for the business tracker.
"""

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Unicode, Integer, String, Date
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy import CheckConstraint
from sqlalchemy.orm import Session
from sqlalchemy.dialects import postgresql

"""Base = declarative_base()"""
Base = automap_base()

engine = create_engine('postgresql+psycopg2:///bustrac')

# reflect the tables
Base.prepare(engine, reflect=True)

state_code = postgresql.Enum('AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 
'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 
'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 
'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 
'WA', 'WV', 'WI', 'WY', 'AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 
'PE', 'QC', 'SK', 'YT', name='state_code')

Industries = Base.classes.industries
Status = Base.classes.status
StatusTypes = Base.classes.statusTypes
ActivityTypes = Base.classes.activityTypes
Organizations = Base.classes.organizations
Locations = Base.classes.locations
People = Base.classes.people
Activities = Base.classes.activities
Cases = Base.classes.cases
Projects = Base.classes.projects

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

The longer version defines the classes for each table.

When I fully understand the model format I'll ask for where to start in the
docs to learn how to structure the controller portion using .sql scripts
already written.

TIA,

Rich

--
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/alpine.LNX.2.20.2012181231590.29974%40salmo.appl-ecosys.com.

Re: [sqlalchemy] One to One relation problem [re-open?]

['Sören Textor' via sqlalchemy]

Ah. I see. Thus this was a newbie question. Thanks again!

Mike Bayer  schrieb am Fr. 18. Dez. 2020 um 19:44:

> hey there -
>
> you can assign the "id" but that doesn't give SQLAlchemy any clue that you
> are working with the "daughter" relationship so it doesn't know to
> deassociate m2.daughter.  You'll note that relationally, there's no issue
> as from a foreign key perspective Mama->daughter is many to one.  Some FAQ
> on this here:
> https://docs.sqlalchemy.org/en/13/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7
>
>
> Usually if you were modelling "Mama->Daughter", you'd have
> Daughter.mama_id foreign key since Parent->Chlld is typically one-to-many,
> not many-to-one.
>
>
>
> On Fri, Dec 18, 2020, at 12:50 PM, 'Sören Textor' via sqlalchemy wrote:
>
> This example fails. Instead of assigning an objekt, I assign just the
> daughters id ...
> But I think that's "correct"?
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Super(Base):
> __tablename__ = "super"
> id = Column(Integer, primary_key=True)
>
>
> class Mama(Super):
> __tablename__ = "mama"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>
> daughter_id = Column(Integer, ForeignKey("daughter.id"))
> daughter = relationship("Daughter", foreign_keys=[daughter_id],
> back_populates="mama" )
>
>
> class Daughter(Super):
> __tablename__ = "daughter"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
> mama = relationship( "Mama", foreign_keys=[Mama.daughter_id], uselist=
> False, back_populates="daughter",  )
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> session = Session(e)
>
> m1 = Mama()
> m2 = Mama()
> d1 = Daughter()
> d2 = Daughter()
>
> session.add(m1)
> session.add(m2)
> session.add(d1)
> session.add(d2)
> session.commit()
>
> m1.daughter = d1
> m2.daughter = d2
> session.commit()
>
> m1.daughter_id = d2.id #instead of m1.daughter = d2
> session.commit()
>
> assert m1.daughter is d2
> assert m2.daughter is None # FAILS
> assert m2.daughter_id is None #FAILS
>
> SirAnn
> -- Originalnachricht --
> Von: "Sören Textor" 
> An: "mike...@zzzcomputing.com" 
> Gesendet: 18.12.2020 16:52:35
> Betreff: Re: [sqlalchemy] One to One relation problem
>
> Hi Mike.
> Thanks for answering. I‘ll check it out on monday.
> We use MSSQL2016 and flask. That‘s the only difference I see at the first
> look.
>
> I‘ll send a detailed answer to the group afterwards. Without the super
> class it also works fine. That‘s why I thought it is an issue with foreign
> keys.
>
> We are running the newest 1.3.x of SQLAlchemy.
>
> Mike Bayer  schrieb am Fr. 18. Dez. 2020 um
> 16:31:
>
>
> hey there -
>
> these mappings are pretty good, as is always the case I cannot predict why
> an issue is occurring, or usually even understand the issue, without
> running the code.  your code is pretty runnable with a few imports added so
> that's great.   however adding an assertion for the condition you describe
> "m2.daughter is not None" is not reproducible on my end. Try out the
> script below and see if you have different results.
>
>
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
>
> Base = declarative_base()
>
>
> class Super(Base):
> __tablename__ = "super"
> id = Column(Integer, primary_key=True)
>
>
> class Mama(Super):
> __tablename__ = "mama"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>
> daughter_id = Column(Integer, ForeignKey("daughter.id"))
> daughter = relationship(
> "Daughter", foreign_keys=[daughter_id], back_populates="mama"
> )
>
>
> class Daughter(Super):
> __tablename__ = "daughter"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
> mama = relationship(
> "Mama",
> foreign_keys=[Mama.daughter_id],
> uselist=False,
> back_populates="daughter",
> )
>
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
> session = Session(e)
>
> m1 = Mama()
> m2 = Mama()
> d1 = Daughter()
> d2 = Daughter()
>
> session.add(m1)
> session.add(m2)
> session.add(d1)
> session.add(d2)
> session.commit()
>
> m1.daughter = d1
> m2.daughter = d2
> session.commit()
>
> m1.daughter = d2
> session.commit()
>
>
> assert m2.daughter is None
>
>
>
> On Fri, Dec 18, 2020, at 2:01 AM, 'Sören Textor' via sqlalchemy wrote:
>
> Hello
> I have a huge problem with 

Re: [sqlalchemy] One to One relation problem [re-open?]

[Mike Bayer]

hey there -

you can assign the "id" but that doesn't give SQLAlchemy any clue that you are 
working with the "daughter" relationship so it doesn't know to deassociate 
m2.daughter.  You'll note that relationally, there's no issue as from a foreign 
key perspective Mama->daughter is many to one.  Some FAQ on this here: 
https://docs.sqlalchemy.org/en/13/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7


Usually if you were modelling "Mama->Daughter", you'd have Daughter.mama_id 
foreign key since Parent->Chlld is typically one-to-many, not many-to-one.



On Fri, Dec 18, 2020, at 12:50 PM, 'Sören Textor' via sqlalchemy wrote:
> This example fails. Instead of assigning an objekt, I assign just the 
> daughters id ... 
> But I think that's "correct"?
> 
> from sqlalchemy import Column
> from sqlalchemy import create_engine
> from sqlalchemy import ForeignKey
> from sqlalchemy import Integer
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship
> from sqlalchemy.orm import Session
> 
> Base = declarative_base()
> 
> 
> class Super(Base):
> __tablename__ = "super"
> id = Column(Integer, primary_key=True)
> 
> 
> class Mama(Super):
> __tablename__ = "mama"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
> 
> daughter_id = Column(Integer, ForeignKey("daughter.id"))
> daughter = relationship("Daughter", foreign_keys=[daughter_id], 
> back_populates="mama" )
> 
> 
> class Daughter(Super):
> __tablename__ = "daughter"
> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
> mama = relationship( "Mama", foreign_keys=[Mama.daughter_id], 
> uselist=False, back_populates="daughter",  )
> 
> 
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
> 
> session = Session(e)
> 
> m1 = Mama()
> m2 = Mama()
> d1 = Daughter()
> d2 = Daughter()
> 
> session.add(m1)
> session.add(m2)
> session.add(d1)
> session.add(d2)
> session.commit()
> 
> m1.daughter = d1
> m2.daughter = d2
> session.commit()
> 
> m1.daughter_id = d2.id #instead of m1.daughter = d2
> session.commit()
> 
> assert m1.daughter is d2
> assert m2.daughter is None # FAILS
> assert m2.daughter_id is None #FAILS
> 
> SirAnn
> -- Originalnachricht --
> Von: "Sören Textor" 
> An: "mike...@zzzcomputing.com" 
> Gesendet: 18.12.2020 16:52:35
> Betreff: Re: [sqlalchemy] One to One relation problem
> 
>> Hi Mike. 
>> Thanks for answering. I‘ll check it out on monday. 
>> We use MSSQL2016 and flask. That‘s the only difference I see at the first 
>> look. 
>> 
>> I‘ll send a detailed answer to the group afterwards. Without the super class 
>> it also works fine. That‘s why I thought it is an issue with foreign keys. 
>> 
>> We are running the newest 1.3.x of SQLAlchemy. 
>> 
>> Mike Bayer  schrieb am Fr. 18. Dez. 2020 um 16:31:
>>> __
>>> hey there -
>>> 
>>> these mappings are pretty good, as is always the case I cannot predict why 
>>> an issue is occurring, or usually even understand the issue, without 
>>> running the code.  your code is pretty runnable with a few imports added so 
>>> that's great.   however adding an assertion for the condition you describe 
>>> "m2.daughter is not None" is not reproducible on my end. Try out the 
>>> script below and see if you have different results.
>>> 
>>> 
>>> from sqlalchemy import Column
>>> from sqlalchemy import create_engine
>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy import Integer
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import relationship
>>> from sqlalchemy.orm import Session
>>> 
>>> Base = declarative_base()
>>> 
>>> 
>>> class Super(Base):
>>> __tablename__ = "super"
>>> id = Column(Integer, primary_key=True)
>>> 
>>> 
>>> class Mama(Super):
>>> __tablename__ = "mama"
>>> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>>> 
>>> daughter_id = Column(Integer, ForeignKey("daughter.id"))
>>> daughter = relationship(
>>> "Daughter", foreign_keys=[daughter_id], back_populates="mama"
>>> )
>>> 
>>> 
>>> class Daughter(Super):
>>> __tablename__ = "daughter"
>>> id = Column(Integer, ForeignKey("super.id"), primary_key=True)
>>> mama = relationship(
>>> "Mama",
>>> foreign_keys=[Mama.daughter_id],
>>> uselist=False,
>>> back_populates="daughter",
>>> )
>>> 
>>> 
>>> e = create_engine("sqlite://", echo=True)
>>> Base.metadata.create_all(e)
>>> 
>>> session = Session(e)
>>> 
>>> m1 = Mama()
>>> m2 = Mama()
>>> d1 = Daughter()
>>> d2 = Daughter()
>>> 
>>> session.add(m1)
>>> session.add(m2)
>>> session.add(d1)
>>> session.add(d2)
>>> session.commit()
>>> 
>>> m1.daughter = d1
>>> m2.daughter = d2
>>> session.commit()
>>> 
>>> m1.daughter = d2
>>> session.commit()
>>> 
>>> 
>>> assert m2.daughter is None
>>> 
>>> 
>>> 
>>> On Fri, Dec 18, 2020, at 

Re: [sqlalchemy] One to One relation problem [re-open?]

['Sören Textor' via sqlalchemy]

This example fails. Instead of assigning an objekt, I assign just the 
daughters id ...

But I think that's "correct"?

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class Super(Base):
__tablename__ = "super"
id = Column(Integer, primary_key=True)


class Mama(Super):
__tablename__ = "mama"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)

daughter_id = Column(Integer, ForeignKey("daughter.id"))
daughter = relationship("Daughter", foreign_keys=[daughter_id], 
back_populates="mama" )



class Daughter(Super):
__tablename__ = "daughter"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)
mama = relationship( "Mama", foreign_keys=[Mama.daughter_id], 
uselist=False, back_populates="daughter",  )



e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

m1 = Mama()
m2 = Mama()
d1 = Daughter()
d2 = Daughter()

session.add(m1)
session.add(m2)
session.add(d1)
session.add(d2)
session.commit()

m1.daughter = d1
m2.daughter = d2
session.commit()

m1.daughter_id = d2.id #instead of m1.daughter = d2
session.commit()

assert m1.daughter is d2
assert m2.daughter is None # FAILS
assert m2.daughter_id is None #FAILS

SirAnn
-- Originalnachricht --
Von: "Sören Textor" 
An: "mike...@zzzcomputing.com" 
Gesendet: 18.12.2020 16:52:35
Betreff: Re: [sqlalchemy] One to One relation problem


Hi Mike.
Thanks for answering. I‘ll check it out on monday.
We use MSSQL2016 and flask. That‘s the only difference I see at the 
first look.


I‘ll send a detailed answer to the group afterwards. Without the super 
class it also works fine. That‘s why I thought it is an issue with 
foreign keys.


We are running the newest 1.3.x of SQLAlchemy.

Mike Bayer  schrieb am Fr. 18. Dez. 2020 um 
16:31:

hey there -

these mappings are pretty good, as is always the case I cannot predict 
why an issue is occurring, or usually even understand the issue, 
without running the code.  your code is pretty runnable with a few 
imports added so that's great.   however adding an assertion for the 
condition you describe "m2.daughter is not None" is not reproducible 
on my end. Try out the script below and see if you have different 
results.



from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class Super(Base):
__tablename__ = "super"
id = Column(Integer, primary_key=True)


class Mama(Super):
__tablename__ = "mama"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)

daughter_id = Column(Integer, ForeignKey("daughter.id"))
daughter = relationship(
"Daughter", foreign_keys=[daughter_id], back_populates="mama"
)


class Daughter(Super):
__tablename__ = "daughter"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)
mama = relationship(
"Mama",
foreign_keys=[Mama.daughter_id],
uselist=False,
back_populates="daughter",
)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

m1 = Mama()
m2 = Mama()
d1 = Daughter()
d2 = Daughter()

session.add(m1)
session.add(m2)
session.add(d1)
session.add(d2)
session.commit()

m1.daughter = d1
m2.daughter = d2
session.commit()

m1.daughter = d2
session.commit()


assert m2.daughter is None



On Fri, Dec 18, 2020, at 2:01 AM, 'Sören Textor' via sqlalchemy wrote:

Hello
I have a huge problem with süecific "one to one" relation.

Woking (it's the tutorial code)

class Son(db.Model):
__tablename__ = 'son'
id = db.Column(db.Integer, primary_key=True)
papa_id = db.Column(db.Integer, db.ForeignKey('papa.id'))
papa = db.relationship("Papa", foreign_keys=[papa_id], 
back_populates="son")


class Papa(db.Model):
__tablename__ = 'papa'
id = db.Column(db.Integer, primary_key=True)
son = db.relationship("Son", uselist=False, 
back_populates="papa")


main:
p1 = Papa()
p2 = Papa()
s1 = Son()
s2 = Son()

db.session.add(p1)
db.session.add(p2)
db.session.add(s1)
db.session.add(s2)

db.session.commit()
p1.son = s1
p2.son = s2
db.session.commit()
p1.son = s2
db.session.commit()

Works like a charm. afterwards every relation is correct

My code (I have to use a super class, that's the only difference):

class Super(db.Model):
__tablename__ = 'super'
id = db.Column(db.Integer, primary_key=True)

class Mama(Super):
__tablename__ = 'mama'
id = 

[sqlalchemy] Re: One to One relation problem [solved]

['Sören Textor' via sqlalchemy]

Hi Mike
Thanks for looking at my code. Next time I'll post an testcase like you. 
Sorry for that one. And I cannot believe it. But it works now.
I also updated SQLAlchemy, flast-RESTful, flask-migrate and so on, to 
their newest version.


And now it seems to work. And problem before was

> assert m2.daughter:id is None

This failed. But I tried and tried and tried in out production code 
isntead of sampling a small testcase like you. bad idea.


And I really have NO idea why it works now. But I think I stop here for 
today and can have fun at wekkeend.. instead of thinking thinking 
thinking about that!!


Many thanks!



Mike Bayer  schrieb am Fr. 18. Dez. 2020 um 
16:31:

hey there -

these mappings are pretty good, as is always the case I cannot predict 
why an issue is occurring, or usually even understand the issue, 
without running the code.  your code is pretty runnable with a few 
imports added so that's great.   however adding an assertion for the 
condition you describe "m2.daughter is not None" is not reproducible 
on my end. Try out the script below and see if you have different 
results.



from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class Super(Base):
__tablename__ = "super"
id = Column(Integer, primary_key=True)


class Mama(Super):
__tablename__ = "mama"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)

daughter_id = Column(Integer, ForeignKey("daughter.id"))
daughter = relationship(
"Daughter", foreign_keys=[daughter_id], back_populates="mama"
)


class Daughter(Super):
__tablename__ = "daughter"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)
mama = relationship(
"Mama",
foreign_keys=[Mama.daughter_id],
uselist=False,
back_populates="daughter",
)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

m1 = Mama()
m2 = Mama()
d1 = Daughter()
d2 = Daughter()

session.add(m1)
session.add(m2)
session.add(d1)
session.add(d2)
session.commit()

m1.daughter = d1
m2.daughter = d2
session.commit()

m1.daughter = d2
session.commit()


assert m2.daughter is None



On Fri, Dec 18, 2020, at 2:01 AM, 'Sören Textor' via sqlalchemy wrote:

Hello
I have a huge problem with süecific "one to one" relation.

Woking (it's the tutorial code)

class Son(db.Model):
__tablename__ = 'son'
id = db.Column(db.Integer, primary_key=True)
papa_id = db.Column(db.Integer, db.ForeignKey('papa.id'))
papa = db.relationship("Papa", foreign_keys=[papa_id], 
back_populates="son")


class Papa(db.Model):
__tablename__ = 'papa'
id = db.Column(db.Integer, primary_key=True)
son = db.relationship("Son", uselist=False, 
back_populates="papa")


main:
p1 = Papa()
p2 = Papa()
s1 = Son()
s2 = Son()

db.session.add(p1)
db.session.add(p2)
db.session.add(s1)
db.session.add(s2)

db.session.commit()
p1.son = s1
p2.son = s2
db.session.commit()
p1.son = s2
db.session.commit()

Works like a charm. afterwards every relation is correct

My code (I have to use a super class, that's the only difference):

class Super(db.Model):
__tablename__ = 'super'
id = db.Column(db.Integer, primary_key=True)

class Mama(Super):
__tablename__ = 'mama'
id = db.Column(db.Integer, db.ForeignKey('super.id'), 
primary_key=True)


daughter_id = db.Column(db.Integer, db.ForeignKey('daughter.id'))
daughter = db.relationship("Daughter", 
foreign_keys=[daughter_id], back_populates="mama")


class Daughter(Super):
__tablename__ = 'daughter'
id = db.Column(db.Integer, db.ForeignKey('super.id'), 
primary_key=True)
mama = db.relationship("Mama", foreign_keys=[Mama.daughter_id], 
uselist=False, back_populates="daughter")


main:
m1 = Mama()
m2 = Mama()
d1 = Daughter()
d2 = Daughter()

db.session.add(m1)
db.session.add(m2)
db.session.add(d1)
db.session.add(d2)
db.session.commit()

m1.daughter = d1
m2.daughter = d2
db.session.commit()

m1.daughter = d2
db.session.commit()

everything is correct EXCEPT:
m2.daughter! it still points on d2 instead of None. And the table 
contains still the daughter_id of d2.


Thus, what foreign key did I miss?

All the best and stay healthy!
SirAnn



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

Re: [sqlalchemy] One to One relation problem

[Mike Bayer]

hey there -

these mappings are pretty good, as is always the case I cannot predict why an 
issue is occurring, or usually even understand the issue, without running the 
code.  your code is pretty runnable with a few imports added so that's great.   
however adding an assertion for the condition you describe "m2.daughter is not 
None" is not reproducible on my end. Try out the script below and see if 
you have different results.


from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()


class Super(Base):
__tablename__ = "super"
id = Column(Integer, primary_key=True)


class Mama(Super):
__tablename__ = "mama"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)

daughter_id = Column(Integer, ForeignKey("daughter.id"))
daughter = relationship(
"Daughter", foreign_keys=[daughter_id], back_populates="mama"
)


class Daughter(Super):
__tablename__ = "daughter"
id = Column(Integer, ForeignKey("super.id"), primary_key=True)
mama = relationship(
"Mama",
foreign_keys=[Mama.daughter_id],
uselist=False,
back_populates="daughter",
)


e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

m1 = Mama()
m2 = Mama()
d1 = Daughter()
d2 = Daughter()

session.add(m1)
session.add(m2)
session.add(d1)
session.add(d2)
session.commit()

m1.daughter = d1
m2.daughter = d2
session.commit()

m1.daughter = d2
session.commit()


assert m2.daughter is None



On Fri, Dec 18, 2020, at 2:01 AM, 'Sören Textor' via sqlalchemy wrote:
> Hello
> I have a huge problem with süecific "one to one" relation. 
> 
> Woking (it's the tutorial code)
> 
> class Son(db.Model):
> __tablename__ = 'son'
> id = db.Column(db.Integer, primary_key=True)
> papa_id = db.Column(db.Integer, db.ForeignKey('papa.id'))
> papa = db.relationship("Papa", foreign_keys=[papa_id], 
> back_populates="son")
> 
> class Papa(db.Model):
> __tablename__ = 'papa'
> id = db.Column(db.Integer, primary_key=True)
> son = db.relationship("Son", uselist=False, back_populates="papa")
> 
> main:
> p1 = Papa()
> p2 = Papa()
> s1 = Son()
> s2 = Son()
> 
> db.session.add(p1)
> db.session.add(p2)
> db.session.add(s1)
> db.session.add(s2)
> 
> db.session.commit()
> p1.son = s1
> p2.son = s2
> db.session.commit()
> p1.son = s2
> db.session.commit()
> 
> Works like a charm. afterwards every relation is correct
> 
> My code (I have to use a super class, that's the only difference):
> 
> class Super(db.Model):
> __tablename__ = 'super'
> id = db.Column(db.Integer, primary_key=True)
> 
> class Mama(Super):
> __tablename__ = 'mama'
> id = db.Column(db.Integer, db.ForeignKey('super.id'), primary_key=True)
> 
> daughter_id = db.Column(db.Integer, db.ForeignKey('daughter.id'))
> daughter = db.relationship("Daughter", foreign_keys=[daughter_id], 
> back_populates="mama")
> 
> class Daughter(Super):
> __tablename__ = 'daughter'
> id = db.Column(db.Integer, db.ForeignKey('super.id'), primary_key=True)
> mama = db.relationship("Mama", foreign_keys=[Mama.daughter_id], 
> uselist=False, back_populates="daughter")
> 
> main:
> m1 = Mama()
> m2 = Mama()
> d1 = Daughter()
> d2 = Daughter()
> 
> db.session.add(m1)
> db.session.add(m2)
> db.session.add(d1)
> db.session.add(d2)
> db.session.commit()
> 
> m1.daughter = d1
> m2.daughter = d2
> db.session.commit()
> 
> m1.daughter = d2
> db.session.commit()
> 
> everything is correct EXCEPT:
> *m2.daughter! *it still points on d2 instead of None. And the table contains 
> still the daughter_id of d2. 
> 
> Thus, what foreign key did I miss? 
> 
> All the best and stay healthy!
> SirAnn
> 
> 

> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/592197fc-0f15-4c99-a2a7-a9443767bcedn%40googlegroups.com
>  
> .

-- 
SQLAlchemy - 
The Python SQL 

Re: [sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic

[Mike Bayer]

your example shows two relationships to the identical target class:

children = relationship(
"ChildChildClass",
secondary="mix_child_class_child_child_class",
cascade="all, delete",
)
children2 = relationship(
"ChildChildClass",
secondary="mix_child_class_child_child_class",
passive_deletes=True
)



On Fri, Dec 18, 2020, at 8:40 AM, Mark Aquino wrote:
> Thanks. That could work. The reason we need specific relationships to the 
> child class types is for our front end when it queries the data, which we use 
> sqlalchemy-graphene to do, if we query the base class relationship it returns 
> empty rows and makes pagination impossible as the front end is unable to know 
> how many rows containing the desired type are present when the mixer table 
> contains references to several different classes by using the id shared on 
> the base class.
> 
> Mark Aquino
> 
> *From:* sqlalchemy@googlegroups.com  on behalf 
> of Mike Bayer 
> *Sent:* Thursday, December 17, 2020 9:32:40 PM
> *To:* noreply-spamdigest via sqlalchemy 
> *Subject:* Re: [sqlalchemy] Re: Can't delete cascade many-to-many with 
> polymorphic relationships unless using lazy=dynamic 
>  
> perhaps you are looking to set viewonly=True on this second relationship?  
> that will exclude it from any kind of persistence operation.it will only 
> be used to load things in from the database but not accept or honor any 
> mutations of data.
> 
> not sure why you need to have two relationships that are identical, but if 
> you do, then yes you want only one of them to be the one that writes changes 
> to the DB.
> 
> On Thu, Dec 17, 2020, at 7:40 PM, maqui...@gmail.com wrote:
>> My operating assumption is that sqlalchemy looks at each relationship and 
>> tries to delete it, but since the previous relationship to the same base 
>> class was already deleted, it throws the exception and the session rolls 
>> back.
>> 
>> The error from above is essentially the same as the actual error in my code 
>> base.  I guess ideally there would be a way to just disable that 
>> functionality but my solution works.  Just will take several hours to change 
>> everything correctly like this because we have about 200 models that are all 
>> interconnected like this. I apologize for not giving a proper working 
>> example I would have needed to create a brand new project unfortunately 
>> because the code base is so complicated
>> On Thursday, December 17, 2020 at 7:32:43 PM UTC-5 maqui...@gmail.com wrote:
>>> 1. target database = postgres, 
>>> 
>>> example queries and stacktrace:
>>> 
>>> >>> from webapp.database.orm.models import ParentClass, ChildClass, 
>>> >>> ChildChildClass
>>> >>> p = ParentClass()
>>> >>> c = ChildClass()
>>> >>> cc = ChildChildClass()
>>> >>> c.children.append(cc)
>>> >>> p.children.append(c)
>>> >>> session.add(p)
>>> >>> session.commit()
>>> >>> p = session.query(ParentClass).one()
>>> >>> [cc for c in p.children for cc in c.children]
>>> [ChildChildClass(id=UUID('1253a435-3330-4e36-bafc-ad8ff5176c4d'))]
>>> >>> session.delete(p)
>>> >>> session.flush()
>>> Traceback (most recent call last):
>>>   File "", line 1, in 
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>>  line 2496, in flush
>>> self._flush(objects)
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>>  line 2637, in _flush
>>> transaction.rollback(_capture_exception=True)
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
>>>  line 68, in __exit__
>>> compat.raise_(
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
>>>  line 178, in raise_
>>> raise exception
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
>>>  line 2597, in _flush
>>> flush_context.execute()
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>>>  line 422, in execute
>>> rec.execute(self)
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
>>>  line 538, in execute
>>> self.dependency_processor.process_deletes(uow, states)
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
>>>  line 1104, in process_deletes
>>> self._run_crud(
>>>   File 
>>> "/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
>>>  line 1201, in _run_crud
>>> raise exc.StaleDataError(
>>> sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 
>>> 'mix_child_class_child_child_class' expected to delete 1 row(s); Only 0 
>>> were matched.
>>> >>> 
>>> KeyboardInterrupt
>>> 
>>> Works when ChildClass is declared as follows instead of as written in 

Re: [sqlalchemy] Re: Can't delete cascade many-to-many with polymorphic relationships unless using lazy=dynamic

[Mark Aquino]

Thanks. That could work. The reason we need specific relationships to the child 
class types is for our front end when it queries the data, which we use 
sqlalchemy-graphene to do, if we query the base class relationship it returns 
empty rows and makes pagination impossible as the front end is unable to know 
how many rows containing the desired type are present when the mixer table 
contains references to several different classes by using the id shared on the 
base class.

Mark Aquino

From: sqlalchemy@googlegroups.com  on behalf of 
Mike Bayer 
Sent: Thursday, December 17, 2020 9:32:40 PM
To: noreply-spamdigest via sqlalchemy 
Subject: Re: [sqlalchemy] Re: Can't delete cascade many-to-many with 
polymorphic relationships unless using lazy=dynamic

perhaps you are looking to set viewonly=True on this second relationship?  that 
will exclude it from any kind of persistence operation.it will only be used 
to load things in from the database but not accept or honor any mutations of 
data.

not sure why you need to have two relationships that are identical, but if you 
do, then yes you want only one of them to be the one that writes changes to the 
DB.

On Thu, Dec 17, 2020, at 7:40 PM, maqui...@gmail.com 
wrote:
My operating assumption is that sqlalchemy looks at each relationship and tries 
to delete it, but since the previous relationship to the same base class was 
already deleted, it throws the exception and the session rolls back.

The error from above is essentially the same as the actual error in my code 
base.  I guess ideally there would be a way to just disable that functionality 
but my solution works.  Just will take several hours to change everything 
correctly like this because we have about 200 models that are all 
interconnected like this. I apologize for not giving a proper working example I 
would have needed to create a brand new project unfortunately because the code 
base is so complicated
On Thursday, December 17, 2020 at 7:32:43 PM UTC-5 maqui...@gmail.com wrote:
1. target database = postgres,

example queries and stacktrace:

>>> from webapp.database.orm.models import ParentClass, ChildClass, 
>>> ChildChildClass
>>> p = ParentClass()
>>> c = ChildClass()
>>> cc = ChildChildClass()
>>> c.children.append(cc)
>>> p.children.append(c)
>>> session.add(p)
>>> session.commit()
>>> p = session.query(ParentClass).one()
>>> [cc for c in p.children for cc in c.children]
[ChildChildClass(id=UUID('1253a435-3330-4e36-bafc-ad8ff5176c4d'))]
>>> session.delete(p)
>>> session.flush()
Traceback (most recent call last):
  File "", line 1, in 
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
 line 2496, in flush
self._flush(objects)
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
 line 2637, in _flush
transaction.rollback(_capture_exception=True)
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
 line 68, in __exit__
compat.raise_(
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
 line 178, in raise_
raise exception
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
 line 2597, in _flush
flush_context.execute()
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
 line 422, in execute
rec.execute(self)
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
 line 538, in execute
self.dependency_processor.process_deletes(uow, states)
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
 line 1104, in process_deletes
self._run_crud(
  File 
"/Users/aquinmx3/venv/espresso3.8/lib/python3.8/site-packages/sqlalchemy/orm/dependency.py",
 line 1201, in _run_crud
raise exc.StaleDataError(
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 
'mix_child_class_child_child_class' expected to delete 1 row(s); Only 0 were 
matched.
>>>
KeyboardInterrupt

Works when ChildClass is declared as follows instead of as written in original 
question:

class ChildClass(Base):
__tablename__ = "child_class"
id = Column("id", UUID(as_uuid=True), primary_key=True, default=uuid4())
parents = relationship(
"ParentClass",
secondary="mix_parent_class_child_class",
passive_deletes=True,
)
children = relationship(
"ChildChildClass",
secondary="mix_child_class_child_child_class",
cascade="all, delete",
)
children2 = relationship(
"ChildChildClass",
secondary="mix_child_class_child_child_class",
passive_deletes=True
)

>>> from webapp.database.orm.models import ParentClass, ChildClass, 
>>> ChildChildClass
>>> p = ParentClass()
>>> c =