Re: [sqlalchemy] relationship without using a column value (using table name)?

[Mark Aquino]

I guess I could do that: I am using a joined table inheritance schema for 
most of the classes in my data model (and that base table does have an 
entity_type_id) but was trying to link a few "entity_type"s to some of the 
classes that didn't inherit from the base.

The difference in the Django model is that the joined class (Addresses) has 
a parent_id column on it, and this line is key: "*class_.id* == foreign(
remote(Address.parent_id))".  without some column on the parent class to 
map to in the primaryjoin clause the relationship function doesn't seem to 
work.  I don't think the discriminator there is necessary to create 
relationship because there IS a "pseudo foreign key", it just has no 
referential integrity.  A bit of a digression but I can't really tell what 
the where clause part is necessary for tbh (Address.discriminator == 
discriminator). When would class_.id == Address.parent_ id but the class 
name != Address.discriminator?

 It would be nice if you could somehow override it and directly specify how 
to create the relationship with a custom query, but oh well...


On Monday, March 16, 2020 at 1:50:27 PM UTC-4, Mike Bayer wrote:
>
>
>
> On Mon, Mar 16, 2020, at 1:23 PM, Mark Aquino wrote:
>
> Unfortunately none of those recipes work for what I'm trying to 
> accomplish, and the mapper just complains that there is no "unambiguous" 
> foreign key column to map the two classes.
>
> Normal referential integrity rules would dictate that I create a column on 
> the related class that referred to the entity_type.id, but in a 
> non-polymorphic/shared table setting it seems completely unnecessary.: what 
> is the point in having a column on a single table like called 
> "entity_type_id" that has the same value in every row?  
>
> I need the relationship to be constructed by a query that looks like:
>
> select entity_type.*, something.*
> from entity_type, something
> where entity_type.table_name = 'something';
>
>
> would you perhaps want to use joined table inheritance?  that's the kind 
> of query it emits.   however the structure you gave does seem to be 
> equivalent to Django's generic foreign key to me. the "table_name" is 
> known as the "discriminator".   
>
>
>
> or 
> select something.*, (select * from entity_type where 
> entity_type.table_name='something') from something;
>
> Is it impossible to create a relationship like this using sqlalchemy?
>
> sqlalchemy doesn't allow this:
>
> @event.listens_for(GenericEntityTypeMixin, "mapper_configured", 
> propagate=True)
> def setup_listener(mapper, class_):
> discriminator = class_.__tablename__
> class_.comments = relationship(
> "EntityType",
> primaryjoin=
> foreign(remote(EntityType.table_name)) == discriminator
> ,
> viewonly=True
> )
>
> On Saturday, March 14, 2020 at 12:55:03 PM UTC-4, Mike Bayer wrote:
>
> this is called a "Generic foreign key" and it's not really a real 
> relational database pattern.There are a series of examples in 
> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.generic_associations
>  
> that show four different ways to achieve this pattern, one of which is the 
> "generic foreign key" that is for example what Django offers, where the 
> combination of a "discriminator" (here you call it table_name) and an id 
> can link to different source tables.   However there are three other ways 
> given of doing the same thing that all use correct referential integrity. 
>Which one to use depends on how you need to be querying the 
> "entity_type"  table, however all four will store essentially the same 
> information, just in different formats.
>
>
>
>
>
>
>
> On Sat, Mar 14, 2020, at 9:55 AM, Mark Aquino wrote:
>
> Is it possible to create a relationship via the table name as the "foreign 
> key"? I tried playing around with the foreign and remote options and tried 
> utilizing what's described here: 
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#non-relational-comparisons-materialized-path
>  but 
> I couldn't get it to work for my case.
>
> I have a table that has entity types and a table_name column, e.g.
>
> class EntityType(Base):
> __tablename__ = "entity_type"
> id = Column(UUID, primary_key=True, server_default=FetchedValue())
> table_name = Column(String, nullable=False)
> prefix = Column(Text, unique=True, nullable=False)
> alt_prefix = Column(Text)
> ui_label = Column(Text, unique=True, nullable=False)
> entry_key = Column(Text, unique=True, nullable=False)
>
> config_entity_column_ui_visibility = 
> relationship("ConfigEntityColumnUiVisibility")
>
> def __repr__(self):
> return (
> f" prefix={self.prefix} ui_label={self.ui_label} "
> f"entry_key={self.entry_key}>"
> )
>
>
> I want to create a relationship to this table from other tables via their 
> table name rather than a column on the table.  Is this possible?
>
>
> e.g.
>
> 

Re: [sqlalchemy] association_proxy

[Jonathan Vanasco]

On Monday, March 16, 2020 at 12:31:09 PM UTC-4, Mike Bayer wrote:
>
> I sometimes get a "moderators spam report" for SQLAlchemy and then I know 
> I have to go to the admin interface on the website.   I likely approve them 
> really quick before you see them.   as far as originals missing i dont know 
> where to go for that.
>
>
Maybe you are replying before approving? This is just so odd - i've looked 
through the admin interface many times and can't find them.  I'll ask a 
google friend if they know how this happens.

-- 
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/6f34d835-0804-4b4a-9a1c-ec94da25f796%40googlegroups.com.

Re: [sqlalchemy] relationship without using a column value (using table name)?

[Mike Bayer]

On Mon, Mar 16, 2020, at 1:23 PM, Mark Aquino wrote:
> Unfortunately none of those recipes work for what I'm trying to accomplish, 
> and the mapper just complains that there is no "unambiguous" foreign key 
> column to map the two classes.
> 
> Normal referential integrity rules would dictate that I create a column on 
> the related class that referred to the entity_type.id, but in a 
> non-polymorphic/shared table setting it seems completely unnecessary.: what 
> is the point in having a column on a single table like called 
> "entity_type_id" that has the same value in every row? 
> 
> I need the relationship to be constructed by a query that looks like:
> 
> select entity_type.*, something.*
> from entity_type, something
> where entity_type.table_name = 'something';

would you perhaps want to use joined table inheritance? that's the kind of 
query it emits. however the structure you gave does seem to be equivalent to 
Django's generic foreign key to me. the "table_name" is known as the 
"discriminator". 


> 
> or 
> select something.*, (select * from entity_type where 
> entity_type.table_name='something') from something;
> 
> Is it impossible to create a relationship like this using sqlalchemy?
> 
> sqlalchemy doesn't allow this:
> @event.listens_for(GenericEntityTypeMixin, "mapper_configured", 
> propagate=True)
> def setup_listener(mapper, class_):
> discriminator = class_.__tablename__
> class_.comments = relationship(
> "EntityType",
> primaryjoin=
> foreign(remote(EntityType.table_name)) == discriminator
> ,
> viewonly=True
> )
> On Saturday, March 14, 2020 at 12:55:03 PM UTC-4, Mike Bayer wrote:
>> this is called a "Generic foreign key" and it's not really a real relational 
>> database pattern. There are a series of examples in 
>> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.generic_associations
>>  that show four different ways to achieve this pattern, one of which is the 
>> "generic foreign key" that is for example what Django offers, where the 
>> combination of a "discriminator" (here you call it table_name) and an id can 
>> link to different source tables. However there are three other ways given of 
>> doing the same thing that all use correct referential integrity. Which one 
>> to use depends on how you need to be querying the "entity_type" table, 
>> however all four will store essentially the same information, just in 
>> different formats.
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> On Sat, Mar 14, 2020, at 9:55 AM, Mark Aquino wrote:
>>> Is it possible to create a relationship via the table name as the "foreign 
>>> key"? I tried playing around with the foreign and remote options and tried 
>>> utilizing what's described here: 
>>> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#non-relational-comparisons-materialized-path
>>>  but I couldn't get it to work for my case.
>>> 
>>> I have a table that has entity types and a table_name column, e.g.
>>> 
>>> class EntityType(Base):
>>> __tablename__ = "entity_type"
>>> id = Column(UUID, primary_key=True, server_default=FetchedValue())
>>> table_name = Column(String, nullable=False)
>>> prefix = Column(Text, unique=True, nullable=False)
>>> alt_prefix = Column(Text)
>>> ui_label = Column(Text, unique=True, nullable=False)
>>> entry_key = Column(Text, unique=True, nullable=False)
>>> 
>>> config_entity_column_ui_visibility = 
>>> relationship("ConfigEntityColumnUiVisibility")
>>> 
>>> def __repr__(self):
>>> return (
>>> f">> prefix={self.prefix} ui_label={self.ui_label} "
>>> f"entry_key={self.entry_key}>"
>>> )
>>> 
>>> I want to create a relationship to this table from other tables via their 
>>> table name rather than a column on the table.  Is this possible?
>>> 
>>> e.g.
>>> class GenericEntityTypeMixin:
>>> 
>>> @declared_attr.cascading
>>> def prefix(cls) -> ColumnProperty:
>>> from webapp.database.orm.models import EntityType
>>> 
>>> return column_property(
>>> select([EntityType.prefix])
>>> .where(EntityType.table_name == cls.__tablename__)
>>> .as_scalar(),
>>> info={"key": "prefix"},
>>> )
>>> 
>>> @hybrid_property
>>> def qualified_id(self):
>>> return f"{self.prefix}-{self.visible_id}"
>>> 
>>> *# @declared_attr
**# def entity_type(cls) -> RelationshipProperty:
**# how do we create relationship without using a column object on the 
foreign side?
**
***@declared_attr
>>> def entity_type_entry_key(cls):
>>> return association_proxy("entity_type", "entry_key")
>>> 

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

Re: [sqlalchemy] relationship without using a column value (using table name)?

[Mark Aquino]

Unfortunately none of those recipes work for what I'm trying to accomplish, 
and the mapper just complains that there is no "unambiguous" foreign key 
column to map the two classes.

Normal referential integrity rules would dictate that I create a column on 
the related class that referred to the entity_type.id, but in a 
non-polymorphic/shared table setting it seems completely unnecessary.: what 
is the point in having a column on a single table like called 
"entity_type_id" that has the same value in every row?  

I need the relationship to be constructed by a query that looks like:

select entity_type.*, something.*
from entity_type, something
where entity_type.table_name = 'something';

or 
select something.*, (select * from entity_type where 
entity_type.table_name='something') from something;

Is it impossible to create a relationship like this using sqlalchemy?

sqlalchemy doesn't allow this:

@event.listens_for(GenericEntityTypeMixin, "mapper_configured", propagate=True)
def setup_listener(mapper, class_):
discriminator = class_.__tablename__
class_.comments = relationship(
"EntityType",
primaryjoin=
foreign(remote(EntityType.table_name)) == discriminator
,
viewonly=True
)

On Saturday, March 14, 2020 at 12:55:03 PM UTC-4, Mike Bayer wrote:
>
> this is called a "Generic foreign key" and it's not really a real 
> relational database pattern.There are a series of examples in 
> https://docs.sqlalchemy.org/en/13/orm/examples.html#module-examples.generic_associations
>  
> that show four different ways to achieve this pattern, one of which is the 
> "generic foreign key" that is for example what Django offers, where the 
> combination of a "discriminator" (here you call it table_name) and an id 
> can link to different source tables.   However there are three other ways 
> given of doing the same thing that all use correct referential integrity. 
>Which one to use depends on how you need to be querying the 
> "entity_type"  table, however all four will store essentially the same 
> information, just in different formats.
>
>
>
>
>
>
>
> On Sat, Mar 14, 2020, at 9:55 AM, Mark Aquino wrote:
>
> Is it possible to create a relationship via the table name as the "foreign 
> key"? I tried playing around with the foreign and remote options and tried 
> utilizing what's described here: 
> https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#non-relational-comparisons-materialized-path
>  but 
> I couldn't get it to work for my case.
>
> I have a table that has entity types and a table_name column, e.g.
>
> class EntityType(Base):
> __tablename__ = "entity_type"
> id = Column(UUID, primary_key=True, server_default=FetchedValue())
> table_name = Column(String, nullable=False)
> prefix = Column(Text, unique=True, nullable=False)
> alt_prefix = Column(Text)
> ui_label = Column(Text, unique=True, nullable=False)
> entry_key = Column(Text, unique=True, nullable=False)
>
> config_entity_column_ui_visibility = 
> relationship("ConfigEntityColumnUiVisibility")
>
> def __repr__(self):
> return (
> f" prefix={self.prefix} ui_label={self.ui_label} "
> f"entry_key={self.entry_key}>"
> )
>
>
> I want to create a relationship to this table from other tables via their 
> table name rather than a column on the table.  Is this possible?
>
>
> e.g.
>
> class GenericEntityTypeMixin:
>
> @declared_attr.cascading
> def prefix(cls) -> ColumnProperty:
> from webapp.database.orm.models import EntityType
>
> return column_property(
> select([EntityType.prefix])
> .where(EntityType.table_name == cls.__tablename__)
> .as_scalar(),
> info={"key": "prefix"},
> )
>
> @hybrid_property
> def qualified_id(self):
> return f"{self.prefix}-{self.visible_id}"
>
> 
> *# @declared_attr*
> *# def entity_type(cls) -> RelationshipProperty:*
> *# how do we create relationship without using a column object on the 
> foreign side?*
> @declared_attr
> def entity_type_entry_key(cls):
> return association_proxy("entity_type", "entry_key")
>
>
> --
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/d54d0af1-7e8e-44f1-8398-989b09b3ea8e%40googlegroups.com
>  
> 

Re: [sqlalchemy] association_proxy

[Mike Bayer]

I sometimes get a "moderators spam report" for SQLAlchemy and then I know I 
have to go to the admin interface on the website. I likely approve them really 
quick before you see them. as far as originals missing i dont know where to go 
for that.


On Mon, Mar 16, 2020, at 12:13 PM, Jonathan Vanasco wrote:
> Mike-
> 
> where do messages like these come from? The approval queue? The originals are 
> often missing. I looked in the admin and didn't see them pending either.
> 

> --
>  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/ec15f94b-626b-458c-8ae0-10062aeeadcf%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/91020909-1b01-41ed-b955-dfdeba5841a7%40www.fastmail.com.

Re: [sqlalchemy] association_proxy

[Jonathan Vanasco]

Mike-

where do messages like these come from? The approval queue?  The originals 
are often missing.  I looked in the admin and didn't see them pending 
either.

-- 
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/ec15f94b-626b-458c-8ae0-10062aeeadcf%40googlegroups.com.