[sqlalchemy] recipe advice for special 1:1 relationships
I've used sqlalchemy for many years and something that has come up now and
then is the need for adding a relationship to a mapper that normally would
be a collection (uselist=True) but instead we want to target a specific
record in that collection.
As a simplified illustration, suppose you have CreditApp and Applicant
classes mapped.
mapper(CreditApp, creditapp_table,
properties = {
'applicants': relationship(Applicant,
backref='app')
})
That would work fine if you are happy to work with *applicants *as a
collection.
BUT, in this case we really want 2 very specific 1:1 Applicant
relationships, the primary Applicant and a secondary (joint-signer)
Applicant:
We can hack at the primaryjoin:
mapper(CreditApp, creditapp_table,
properties={
'primaryapplicant': relationship(Applicant,
primaryjoin=and_(
creditapp_table.c.id == applicant_table.c.appid,
applicant_table.c.primary == u'Y',
# <== THIS IS WHAT WE DON'T WANT
),
foreign_keys=[applicant_table.c.appid],
uselist=False,
backref='app'),
'secondaryapplicant': relationship(Applicant,
primaryjoin=and_(
creditapp_table.c.id == applicant_table.c.appid,
applicant_table.c.primary == u'N',
# <== THIS IS WHAT WE DON'T WANT
),
foreign_keys=[applicant_table.c.appid],
uselist=False,
backref='app'),
})
This kind of works, but it is ugly since sqlalchemy doesn't really
understand what we've done.
For example, if I set
myapp.primaryapplicant = Applicant()
sqlalchemy doesn't really understand the new record should have primary
flag set to 'Y'
Also:
myapp.primaryapplicant = None
may issue SQL that deletes both applicants if I recall.
What is a better recipe for this? Would association proxies help? Would
polymorphic inheritance work this out properly (single table inheritance)?
Please let me know. Thanks!
Kent
--
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] recipe advice for special 1:1 relationships
On Thu, Apr 4, 2019 at 9:34 AM Kent wrote:
>
> I've used sqlalchemy for many years and something that has come up now and
> then is the need for adding a relationship to a mapper that normally would be
> a collection (uselist=True) but instead we want to target a specific record
> in that collection.
>
> As a simplified illustration, suppose you have CreditApp and Applicant
> classes mapped.
>
> mapper(CreditApp, creditapp_table,
> properties = {
> 'applicants': relationship(Applicant,
> backref='app')
> })
>
> That would work fine if you are happy to work with applicants as a collection.
>
> BUT, in this case we really want 2 very specific 1:1 Applicant relationships,
> the primary Applicant and a secondary (joint-signer) Applicant:
>
> We can hack at the primaryjoin:
>
> mapper(CreditApp, creditapp_table,
> properties={
> 'primaryapplicant': relationship(Applicant,
> primaryjoin=and_(
> creditapp_table.c.id == applicant_table.c.appid,
> applicant_table.c.primary == u'Y',
> # <== THIS IS WHAT WE DON'T WANT
> ),
> foreign_keys=[applicant_table.c.appid],
> uselist=False,
> backref='app'),
> 'secondaryapplicant': relationship(Applicant,
> primaryjoin=and_(
> creditapp_table.c.id == applicant_table.c.appid,
> applicant_table.c.primary == u'N',
> # <== THIS IS WHAT WE DON'T WANT
> ),
> foreign_keys=[applicant_table.c.appid],
> uselist=False,
> backref='app'),
> })
>
> This kind of works, but it is ugly since sqlalchemy doesn't really understand
> what we've done.
>
> For example, if I set
>
> myapp.primaryapplicant = Applicant()
>
> sqlalchemy doesn't really understand the new record should have primary flag
> set to 'Y'
>
> Also:
>
> myapp.primaryapplicant = None
>
> may issue SQL that deletes both applicants if I recall.
>
>
> What is a better recipe for this? Would association proxies help? Would
> polymorphic inheritance work this out properly (single table inheritance)?
Single table inheritance might work well for this. It would be
adding in that WHERE clause automatically.Took many years for
single inheritance to support most/all cases but POC below works
including with joinedload/selectinload whatever
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import declared_attr
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
b1 = relationship("B1", uselist=False)
b2 = relationship("B2", uselist=False)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey("a.id"))
data = Column(String)
__mapper_args__ = {
"polymorphic_on": data
}
class B1(B):
__mapper_args__ = {
"polymorphic_identity": "b1"
}
class B2(B):
__mapper_args__ = {
"polymorphic_identity": "b2"
}
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(b1=B1(), b2=B2()))
s.commit()
a1 = s.query(A).first()
print(a1.b1)
print(a1.b2)
>
> Please let me know. Thanks!
>
> Kent
>
> --
> 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.
Re: [sqlalchemy] Re: recipe advice for special 1:1 relationships
DELETEs are always by primary key, and you'll want to set up the cascade="all,delete-orphan" and youll need single_parent=True also. On Thu, Apr 4, 2019 at 11:55 AM Kent wrote: > > Will > > a.b1 = None > > > issue a delete statement that also contains the WHERE clause to make that > safe? (Or, is the delete always by primary key anyway?) > > -- > 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.
[sqlalchemy] Re: recipe advice for special 1:1 relationships
Excellent, thanks very much! -- 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: recipe advice for special 1:1 relationships
Will a.b1 = None issue a delete statement that also contains the WHERE clause to make that safe? (Or, is the delete always by primary key anyway?) -- 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] Support for backup node connection.
Unfortunately, I am using Superset which uses SQLALCHEMY for metadata DB connection. So I am not sure if I could have control over the code or not. Is there any other way I could do this? On Thursday, 4 April 2019 15:24:42 UTC-4, Mike Bayer wrote: > > the issue describing how this feature can be added is at: > > https://github.com/sqlalchemy/sqlalchemy/issues/4392 > > for now you need to use creator to connect to psycopg2 directly and > return the connection yourself from a function, which you pass to > create_engine: > > https://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-connect-arguments > > and the URL format is at > > https://www.postgresql.org/docs/current/libpq-connect.html#libpq-multiple-hosts > > . > > > On Thu, Apr 4, 2019 at 2:42 PM rishi reddy > wrote: > > > > Hi All, > > > > I have Postgres DB two node cluster and using SQLALCHEMY_DATABASE_URI > were we can give only one node details. But how can i achieve a scenario > were if the given node goes down SQLALCHEMY will connect to the other > backup node. Is there an option of giving the backup node details in > SQLALCHEMY_DATABASE_URI? > > > > Thanks in advance. > > -Rishi > > > > -- > > 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 . > > To post to this group, send email to sqlal...@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.
[sqlalchemy] Support for backup node connection.
Hi All, I have Postgres DB two node cluster and using SQLALCHEMY_DATABASE_URI were we can give only one node details. But how can i achieve a scenario were if the given node goes down SQLALCHEMY will connect to the other backup node. Is there an option of giving the backup node details in SQLALCHEMY_DATABASE_URI? Thanks in advance. -Rishi -- 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] Support for backup node connection.
the issue describing how this feature can be added is at: https://github.com/sqlalchemy/sqlalchemy/issues/4392 for now you need to use creator to connect to psycopg2 directly and return the connection yourself from a function, which you pass to create_engine: https://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-connect-arguments and the URL format is at https://www.postgresql.org/docs/current/libpq-connect.html#libpq-multiple-hosts . On Thu, Apr 4, 2019 at 2:42 PM rishi reddy wrote: > > Hi All, > > I have Postgres DB two node cluster and using SQLALCHEMY_DATABASE_URI were we > can give only one node details. But how can i achieve a scenario were if the > given node goes down SQLALCHEMY will connect to the other backup node. Is > there an option of giving the backup node details in SQLALCHEMY_DATABASE_URI? > > Thanks in advance. > -Rishi > > -- > 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.
Re: [sqlalchemy] Support for backup node connection.
On Thu, Apr 4, 2019 at 3:49 PM rishi reddy wrote: > > Unfortunately, I am using Superset which uses SQLALCHEMY for metadata DB > connection. So I am not sure if I could have control over the code or not. Is > there any other way I could do this? unfortunately no until someone has time to help with the linked feature request. > > On Thursday, 4 April 2019 15:24:42 UTC-4, Mike Bayer wrote: >> >> the issue describing how this feature can be added is at: >> >> https://github.com/sqlalchemy/sqlalchemy/issues/4392 >> >> for now you need to use creator to connect to psycopg2 directly and >> return the connection yourself from a function, which you pass to >> create_engine: >> https://docs.sqlalchemy.org/en/latest/core/engines.html#custom-dbapi-connect-arguments >> and the URL format is at >> https://www.postgresql.org/docs/current/libpq-connect.html#libpq-multiple-hosts >> . >> >> >> On Thu, Apr 4, 2019 at 2:42 PM rishi reddy wrote: >> > >> > Hi All, >> > >> > I have Postgres DB two node cluster and using SQLALCHEMY_DATABASE_URI were >> > we can give only one node details. But how can i achieve a scenario were >> > if the given node goes down SQLALCHEMY will connect to the other backup >> > node. Is there an option of giving the backup node details in >> > SQLALCHEMY_DATABASE_URI? >> > >> > Thanks in advance. >> > -Rishi >> > >> > -- >> > 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. >> > To post to this group, send email to sqlal...@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. -- 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.
