subject:"Re\: \[sqlalchemy\] Overlapping FK problem"

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread Mike Bayer

well if only one relationship() has the foreign(Transaction.schema), then it 
doesn't conflict with anything else.

I'd still take it off though for consistency


On Mon, Dec 28, 2020, at 1:34 PM, sector119 wrote:
> Thanks!
> 
> service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == 
> Transaction.schema, Service.id == foreign(Transaction.service_id))'*)
> eliminates the error
> 
> But why I have no need to do the same with organization = relationship(...) ?
> 
> Because Service model references Organization one ?
>  
> понедельник, 28 декабря 2020 г. в 19:53:24 UTC+2, Mike Bayer: 
>> 
>> 
>> On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote:
>>> Thank You, Mike,
>>> 
>>> Do you mean that I have to remove all foreign() annotations from all my 
>>> relationships like this?
>>> service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == 
>>> Transaction.schema, Service.id == Transaction.service_id)'*)
>>> organization = relationship(*'Organization'*, 
>>> primaryjoin=*'and_(Organization.schema == Transaction.schema, 
>>> Organization.id == Transaction.organization_id)'*)
>>> person = relationship(*'Person'*, primaryjoin=*'and_(Person.schema == 
>>> Transaction.schema, Person.id == Transaction.person_id)'*)
>> 
>> 
>> no, only the foreign() annotation that surrounds "Transaction.schema".  
>> leave the one that surrounds "Transaction.service_id".   that should 
>> eliminate the error.
>> 
>> 
>> 
>>> 
>>> if so, I still get the same warning..
>>> 
>>> And must I set primaryjoin for service and organization relationships at 
>>> all if I've set up FK for them
>>> ForeignKeyConstraint(
>>> (schema, service_id),
>>> (Service.schema, Service.id)
>>> )
>>> and
>>> ForeignKeyConstraint(
>>> (schema, organization_id),
>>> (Organization.schema, Organization.id)
>>> ),
>>> 
>>> 
>>> понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: 
 __
 by having foreign() on the Transaction.schema column, that means when you 
 do this:
 
 t1 = Transaction()
 
 t1.service = some_service()
 
 the ORM is being instructed to copy some_service.schema over to t1.schema. 
   Because "foreign" means "this is the column that mirrors the value of a 
 canonical value on the related row".
 
 so you have to decide what should happen if you did this:
 
 t1.service = Service(schema="a", ...)
 t1.organiazation = Organization(schema="b", ...)
 t1.person = Person(schema="c", ...)
 
 is the above possible?  or an error condition?
 
 overall, if the plan is that "schema" will match across all the objects 
 involved, and your application will make sure those are all set as needed, 
 just remove the foreign() annotation from the Transaction.service column.  
 the primary joins already have enough information based on the service_id, 
 organization_id and person_id columns.
 
 
 On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
> I get following warning with my model:
> 
> SAWarning: relationship 'Transaction.organization' will copy column 
> organizations.schema to column transactions.schema, which conflicts with 
> relationship(s): 'Transaction.service' (copies services.schema to 
> transactions.schema). If this is not the intention, consider if these 
> relationships should be linked with back_populates, or if viewonly=True 
> should be applied to one or more if they are read-only. For the less 
> common case that foreign key constraints are partially overlapping, the 
> orm.foreign() annotation can be used to isolate the columns that should 
> be written towards.   The 'overlaps' parameter may be used to remove this 
> warning.
> 
> I have all my tables partitioned by "schema" column so I have to put it 
> to every PK and FK
> I add "primaryjoin" to my relations as described at 
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
> but still get that warning. How can I fix it?
> 
> Thank You 
> 
> class Transaction(Base):
> __tablename__ = *'transactions'*
> *
*schema = Column(String(63), nullable=False, index=True)
> id = Column(BigInteger, nullable=False, index=True)
> 
> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id'*), 
> nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id'*), 
> nullable=False, index=True)
> service_id = Column(Integer, nullable=False, index=True)
> organization_id = Column(Integer, nullable=False, index=True)
> 
> ...
> 
> service = relationship(*'Service'*,
> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), 
> Service.id == foreign(Transaction.service_id))'*)
> organization = relationship(*'Organization'*,
> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
> Organization.id

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread sector119

Thanks!

service = relationship('Service', primaryjoin='and_(Service.schema == 
Transaction.schema, Service.id == foreign(Transaction.service_id))')
eliminates the error

But why I have no need to do the same with organization = relationship(...) 
?

Because Service model references Organization one ?
 

понедельник, 28 декабря 2020 г. в 19:53:24 UTC+2, Mike Bayer: 

>
>
> On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote:
>
> Thank You, Mike,
>
> Do you mean that I have to remove all foreign() annotations from all my 
> relationships like this?
> service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == 
> Transaction.schema, Service.id == Transaction.service_id)'*)
> organization = relationship(*'Organization'*, 
> primaryjoin=*'and_(Organization.schema 
> == Transaction.schema, Organization.id == Transaction.organization_id)'*)
> person = relationship(*'Person'*, primaryjoin=*'and_(Person.schema == 
> Transaction.schema, Person.id == Transaction.person_id)'*)
>
>
>
> no, only the foreign() annotation that surrounds "Transaction.schema".  
> leave the one that surrounds "Transaction.service_id".   that should 
> eliminate the error.
>
>
>
> if so, I still get the same warning..
>
> And must I set primaryjoin for service and organization relationships at 
> all if I've set up FK for them
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> )
> and
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema, Organization.id)
> ),
>
>
> понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: 
>
>
> by having foreign() on the Transaction.schema column, that means when you 
> do this:
>
> t1 = Transaction()
>
> t1.service = some_service()
>
> the ORM is being instructed to copy some_service.schema over to 
> t1.schema.   Because "foreign" means "this is the column that mirrors the 
> value of a canonical value on the related row".
>
> so you have to decide what should happen if you did this:
>
> t1.service = Service(schema="a", ...)
> t1.organiazation = Organization(schema="b", ...)
> t1.person = Person(schema="c", ...)
>
> is the above possible?  or an error condition?
>
> overall, if the plan is that "schema" will match across all the objects 
> involved, and your application will make sure those are all set as needed, 
> just remove the foreign() annotation from the Transaction.service column.  
> the primary joins already have enough information based on the service_id, 
> organization_id and person_id columns.
>
>
> On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
>
> I get following warning with my model:
>
> SAWarning: relationship 'Transaction.organization' will copy column 
> organizations.schema to column transactions.schema, which conflicts with 
> relationship(s): 'Transaction.service' (copies services.schema to 
> transactions.schema). If this is not the intention, consider if these 
> relationships should be linked with back_populates, or if viewonly=True 
> should be applied to one or more if they are read-only. For the less common 
> case that foreign key constraints are partially overlapping, the 
> orm.foreign() annotation can be used to isolate the columns that should be 
> written towards.   The 'overlaps' parameter may be used to remove this 
> warning.
>
> I have all my tables partitioned by "schema" column so I have to put it to 
> every PK and FK
> I add "primaryjoin" to my relations as described at 
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
> but still get that warning. How can I fix it?
>
> Thank You 
>
> class Transaction(Base):
> __tablename__ = *'transactions'*
>
> schema = Column(String(63), nullable=False, index=True)
> id = Column(BigInteger, nullable=False, index=True)
>
> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id 
> '*), nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id 
> '*), nullable=False, index=True)
> service_id = Column(Integer, nullable=False, index=True)
> organization_id = Column(Integer, nullable=False, index=True)
>
> ...
>
> service = relationship(*'Service'*,
> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), 
> Service.id == foreign(Transaction.service_id))'*)
> organization = relationship(*'Organization'*,
> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
> Organization.id == foreign(Transaction.organization_id))'*)
> person = relationship(*'Person'*,
> primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), 
> Person.id == foreign(Transaction.person_id))'*)
> rollback_user = relationship(*'User'*, primaryjoin=*'User.id == 
> Transaction.rollback_user_id'*)
>
> __table_args__ = (
> PrimaryKeyConstraint(*'schema'*, *'id'*),
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> ),
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema,

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread Mike Bayer



On Mon, Dec 28, 2020, at 12:37 PM, sector119 wrote:
> Thank You, Mike,
> 
> Do you mean that I have to remove all foreign() annotations from all my 
> relationships like this?
> service = relationship(*'Service'*, primaryjoin=*'and_(Service.schema == 
> Transaction.schema, Service.id == Transaction.service_id)'*)
> organization = relationship(*'Organization'*, 
> primaryjoin=*'and_(Organization.schema == Transaction.schema, Organization.id 
> == Transaction.organization_id)'*)
> person = relationship(*'Person'*, primaryjoin=*'and_(Person.schema == 
> Transaction.schema, Person.id == Transaction.person_id)'*)


no, only the foreign() annotation that surrounds "Transaction.schema".  leave 
the one that surrounds "Transaction.service_id".   that should eliminate the 
error.



> if so, I still get the same warning..
> 
> And must I set primaryjoin for service and organization relationships at all 
> if I've set up FK for them
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> )
> and
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema, Organization.id)
> ),
> 
> 
> понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: 
>> __
>> by having foreign() on the Transaction.schema column, that means when you do 
>> this:
>> 
>> t1 = Transaction()
>> 
>> t1.service = some_service()
>> 
>> the ORM is being instructed to copy some_service.schema over to t1.schema.   
>> Because "foreign" means "this is the column that mirrors the value of a 
>> canonical value on the related row".
>> 
>> so you have to decide what should happen if you did this:
>> 
>> t1.service = Service(schema="a", ...)
>> t1.organiazation = Organization(schema="b", ...)
>> t1.person = Person(schema="c", ...)
>> 
>> is the above possible?  or an error condition?
>> 
>> overall, if the plan is that "schema" will match across all the objects 
>> involved, and your application will make sure those are all set as needed, 
>> just remove the foreign() annotation from the Transaction.service column.  
>> the primary joins already have enough information based on the service_id, 
>> organization_id and person_id columns.
>> 
>> 
>> On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
>>> I get following warning with my model:
>>> 
>>> SAWarning: relationship 'Transaction.organization' will copy column 
>>> organizations.schema to column transactions.schema, which conflicts with 
>>> relationship(s): 'Transaction.service' (copies services.schema to 
>>> transactions.schema). If this is not the intention, consider if these 
>>> relationships should be linked with back_populates, or if viewonly=True 
>>> should be applied to one or more if they are read-only. For the less common 
>>> case that foreign key constraints are partially overlapping, the 
>>> orm.foreign() annotation can be used to isolate the columns that should be 
>>> written towards.   The 'overlaps' parameter may be used to remove this 
>>> warning.
>>> 
>>> I have all my tables partitioned by "schema" column so I have to put it to 
>>> every PK and FK
>>> I add "primaryjoin" to my relations as described at 
>>> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
>>> but still get that warning. How can I fix it?
>>> 
>>> Thank You 
>>> 
>>> class Transaction(Base):
>>> __tablename__ = *'transactions'*
>>> *
*schema = Column(String(63), nullable=False, index=True)
>>> id = Column(BigInteger, nullable=False, index=True)
>>> 
>>> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id'*), 
>>> nullable=False, index=True)
>>> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id'*), 
>>> nullable=False, index=True)
>>> service_id = Column(Integer, nullable=False, index=True)
>>> organization_id = Column(Integer, nullable=False, index=True)
>>> 
>>> ...
>>> 
>>> service = relationship(*'Service'*,
>>> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), 
>>> Service.id == foreign(Transaction.service_id))'*)
>>> organization = relationship(*'Organization'*,
>>> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
>>> Organization.id == foreign(Transaction.organization_id))'*)
>>> person = relationship(*'Person'*,
>>> primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), Person.id 
>>> == foreign(Transaction.person_id))'*)
>>> rollback_user = relationship(*'User'*, primaryjoin=*'User.id == 
>>> Transaction.rollback_user_id'*)
>>> 
>>> __table_args__ = (
>>> PrimaryKeyConstraint(*'schema'*, *'id'*),
>>> ForeignKeyConstraint(
>>> (schema, service_id),
>>> (Service.schema, Service.id)
>>> ),
>>> ForeignKeyConstraint(
>>> (schema, organization_id),
>>> (Organization.schema, Organization.id)
>>> ),
>>> {
>>> *'postgresql_partition_by'*: *'LIST (schema)'
** *}
>>> )
>>> 

>>> -- 
>>> SQLAlchemy - 
>>> The Python SQL Toolkit and Object Relational Mapper
>>>  
>>> http://www.sqlalchemy.org/
>>>  
>>> To post example code, please provide an MCVE:

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread sector119

>>> is the above possible?  or an error condition?

I don't want to restrict that case 

>>> overall, if the plan is that "schema" will match across all the objects 
involved, and your application will make sure those are all set as needed, 
just
>>> remove the foreign() annotation from the Transaction.service column.  
the primary joins already have enough information based on the
>>> service_id, organization_id and person_id columns.

"schema" will match across all the objects - 100%



понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: 

> by having foreign() on the Transaction.schema column, that means when you 
> do this:
>
> t1 = Transaction()
>
> t1.service = some_service()
>
> the ORM is being instructed to copy some_service.schema over to 
> t1.schema.   Because "foreign" means "this is the column that mirrors the 
> value of a canonical value on the related row".
>
> so you have to decide what should happen if you did this:
>
> t1.service = Service(schema="a", ...)
> t1.organiazation = Organization(schema="b", ...)
> t1.person = Person(schema="c", ...)
>
> is the above possible?  or an error condition?
>
> overall, if the plan is that "schema" will match across all the objects 
> involved, and your application will make sure those are all set as needed, 
> just remove the foreign() annotation from the Transaction.service column.  
> the primary joins already have enough information based on the service_id, 
> organization_id and person_id columns.
>
>
> On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
>
> I get following warning with my model:
>
> SAWarning: relationship 'Transaction.organization' will copy column 
> organizations.schema to column transactions.schema, which conflicts with 
> relationship(s): 'Transaction.service' (copies services.schema to 
> transactions.schema). If this is not the intention, consider if these 
> relationships should be linked with back_populates, or if viewonly=True 
> should be applied to one or more if they are read-only. For the less common 
> case that foreign key constraints are partially overlapping, the 
> orm.foreign() annotation can be used to isolate the columns that should be 
> written towards.   The 'overlaps' parameter may be used to remove this 
> warning.
>
> I have all my tables partitioned by "schema" column so I have to put it to 
> every PK and FK
> I add "primaryjoin" to my relations as described at 
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
> but still get that warning. How can I fix it?
>
> Thank You 
>
> class Transaction(Base):
> __tablename__ = *'transactions'*
>
> schema = Column(String(63), nullable=False, index=True)
> id = Column(BigInteger, nullable=False, index=True)
>
> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id 
> '*), nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id 
> '*), nullable=False, index=True)
> service_id = Column(Integer, nullable=False, index=True)
> organization_id = Column(Integer, nullable=False, index=True)
>
> ...
>
> service = relationship(*'Service'*,
> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), 
> Service.id == foreign(Transaction.service_id))'*)
> organization = relationship(*'Organization'*,
> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
> Organization.id == foreign(Transaction.organization_id))'*)
> person = relationship(*'Person'*,
> primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), 
> Person.id == foreign(Transaction.person_id))'*)
> rollback_user = relationship(*'User'*, primaryjoin=*'User.id == 
> Transaction.rollback_user_id'*)
>
> __table_args__ = (
> PrimaryKeyConstraint(*'schema'*, *'id'*),
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> ),
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema, Organization.id)
> ),
> {
> *'postgresql_partition_by'*: 
> *'LIST (schema)'* }
> )
>
>
> -- 
> 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+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.com
>  
> 
> .
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal,

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread sector119

Thank You, Mike,

Do you mean that I have to remove all foreign() annotations from all my 
relationships like this?
service = relationship('Service', primaryjoin='and_(Service.schema == 
Transaction.schema, Service.id == Transaction.service_id)')
organization = relationship('Organization', 
primaryjoin='and_(Organization.schema 
== Transaction.schema, Organization.id == Transaction.organization_id)')
person = relationship('Person', primaryjoin='and_(Person.schema == 
Transaction.schema, Person.id == Transaction.person_id)')

if so, I still get the same warning..

And must I set primaryjoin for service and organization relationships at 
all if I've set up FK for them
ForeignKeyConstraint(
(schema, service_id),
(Service.schema, Service.id)
)
and
ForeignKeyConstraint(
(schema, organization_id),
(Organization.schema, Organization.id)
),


понедельник, 28 декабря 2020 г. в 17:39:25 UTC+2, Mike Bayer: 

> by having foreign() on the Transaction.schema column, that means when you 
> do this:
>
> t1 = Transaction()
>
> t1.service = some_service()
>
> the ORM is being instructed to copy some_service.schema over to 
> t1.schema.   Because "foreign" means "this is the column that mirrors the 
> value of a canonical value on the related row".
>
> so you have to decide what should happen if you did this:
>
> t1.service = Service(schema="a", ...)
> t1.organiazation = Organization(schema="b", ...)
> t1.person = Person(schema="c", ...)
>
> is the above possible?  or an error condition?
>
> overall, if the plan is that "schema" will match across all the objects 
> involved, and your application will make sure those are all set as needed, 
> just remove the foreign() annotation from the Transaction.service column.  
> the primary joins already have enough information based on the service_id, 
> organization_id and person_id columns.
>
>
> On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
>
> I get following warning with my model:
>
> SAWarning: relationship 'Transaction.organization' will copy column 
> organizations.schema to column transactions.schema, which conflicts with 
> relationship(s): 'Transaction.service' (copies services.schema to 
> transactions.schema). If this is not the intention, consider if these 
> relationships should be linked with back_populates, or if viewonly=True 
> should be applied to one or more if they are read-only. For the less common 
> case that foreign key constraints are partially overlapping, the 
> orm.foreign() annotation can be used to isolate the columns that should be 
> written towards.   The 'overlaps' parameter may be used to remove this 
> warning.
>
> I have all my tables partitioned by "schema" column so I have to put it to 
> every PK and FK
> I add "primaryjoin" to my relations as described at 
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
> but still get that warning. How can I fix it?
>
> Thank You 
>
> class Transaction(Base):
> __tablename__ = *'transactions'*
>
> schema = Column(String(63), nullable=False, index=True)
> id = Column(BigInteger, nullable=False, index=True)
>
> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id 
> '*), nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id 
> '*), nullable=False, index=True)
> service_id = Column(Integer, nullable=False, index=True)
> organization_id = Column(Integer, nullable=False, index=True)
>
> ...
>
> service = relationship(*'Service'*,
> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), 
> Service.id == foreign(Transaction.service_id))'*)
> organization = relationship(*'Organization'*,
> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
> Organization.id == foreign(Transaction.organization_id))'*)
> person = relationship(*'Person'*,
> primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), 
> Person.id == foreign(Transaction.person_id))'*)
> rollback_user = relationship(*'User'*, primaryjoin=*'User.id == 
> Transaction.rollback_user_id'*)
>
> __table_args__ = (
> PrimaryKeyConstraint(*'schema'*, *'id'*),
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> ),
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema, Organization.id)
> ),
> {
> *'postgresql_partition_by'*: 
> *'LIST (schema)'* }
> )
>
>
> -- 
> 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+...@googlegroups.com.
> To view this discussion on the web visit 
>

Re: [sqlalchemy] Overlapping FK problem

2020-12-28 Thread Mike Bayer

by having foreign() on the Transaction.schema column, that means when you do 
this:

t1 = Transaction()

t1.service = some_service()

the ORM is being instructed to copy some_service.schema over to t1.schema.   
Because "foreign" means "this is the column that mirrors the value of a 
canonical value on the related row".

so you have to decide what should happen if you did this:

t1.service = Service(schema="a", ...)
t1.organiazation = Organization(schema="b", ...)
t1.person = Person(schema="c", ...)

is the above possible?  or an error condition?

overall, if the plan is that "schema" will match across all the objects 
involved, and your application will make sure those are all set as needed, just 
remove the foreign() annotation from the Transaction.service column.  the 
primary joins already have enough information based on the service_id, 
organization_id and person_id columns.


On Mon, Dec 28, 2020, at 6:04 AM, sector119 wrote:
> I get following warning with my model:
> 
> SAWarning: relationship 'Transaction.organization' will copy column 
> organizations.schema to column transactions.schema, which conflicts with 
> relationship(s): 'Transaction.service' (copies services.schema to 
> transactions.schema). If this is not the intention, consider if these 
> relationships should be linked with back_populates, or if viewonly=True 
> should be applied to one or more if they are read-only. For the less common 
> case that foreign key constraints are partially overlapping, the 
> orm.foreign() annotation can be used to isolate the columns that should be 
> written towards.   The 'overlaps' parameter may be used to remove this 
> warning.
> 
> I have all my tables partitioned by "schema" column so I have to put it to 
> every PK and FK
> I add "primaryjoin" to my relations as described at 
> https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#overlapping-foreign-keys
> but still get that warning. How can I fix it?
> 
> Thank You 
> 
> class Transaction(Base):
> __tablename__ = *'transactions'*
> *
*schema = Column(String(63), nullable=False, index=True)
> id = Column(BigInteger, nullable=False, index=True)
> 
> user_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.users.id'*), 
> nullable=False, index=True)
> office_id = Column(Integer, ForeignKey(SYSTEM_SCHEMA + *'.offices.id'*), 
> nullable=False, index=True)
> service_id = Column(Integer, nullable=False, index=True)
> organization_id = Column(Integer, nullable=False, index=True)
> 
> ...
> 
> service = relationship(*'Service'*,
> primaryjoin=*'and_(Service.schema == foreign(Transaction.schema), Service.id 
> == foreign(Transaction.service_id))'*)
> organization = relationship(*'Organization'*,
> primaryjoin=*'and_(Organization.schema == foreign(Transaction.schema), 
> Organization.id == foreign(Transaction.organization_id))'*)
> person = relationship(*'Person'*,
> primaryjoin=*'and_(Person.schema == foreign(Transaction.schema), Person.id == 
> foreign(Transaction.person_id))'*)
> rollback_user = relationship(*'User'*, primaryjoin=*'User.id == 
> Transaction.rollback_user_id'*)
> 
> __table_args__ = (
> PrimaryKeyConstraint(*'schema'*, *'id'*),
> ForeignKeyConstraint(
> (schema, service_id),
> (Service.schema, Service.id)
> ),
> ForeignKeyConstraint(
> (schema, organization_id),
> (Organization.schema, Organization.id)
> ),
> {
> *'postgresql_partition_by'*: *'LIST (schema)'
** *}
> )
> 

> -- 
> 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/0d422feb-956f-4efc-b3c0-17473652c603n%40googlegroups.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/5166f726-10d9-4861-9c0c-c0606fee22c8%40www.fastmail.com.

Re: [sqlalchemy] Overlapping FK problem

Re: [sqlalchemy] Overlapping FK problem

Re: [sqlalchemy] Overlapping FK problem

Re: [sqlalchemy] Overlapping FK problem

Re: [sqlalchemy] Overlapping FK problem

Re: [sqlalchemy] Overlapping FK problem

6 matches

Site Navigation

Mail list logo

Footer information