On Fri, Jul 12, 2019, at 6:49 PM, Michael P. McDonnell wrote:
> Hey Mike -
>
> First off - thanks for the association proxy idea - I like that, and even if
> heavy handed - specifically like the idea that its a *view* in to column as
> opposed to a manipulable column.
> The next question is - how do I iterate over those association proxy fields
> within the object?
> So if I'm to do a marshal to json - I'd like to say "my task has a name,
> description, instructions, etc.."
the association proxy will show up in the mapping under all_orm_descriptors,
e.g.
from sqlalchemy import inspect
d = {
k getattr(some_object, k) for k in
inspect(some_object).mapper.all_orm_descriptors
}
https://docs.sqlalchemy.org/en/13/orm/mapping_api.html?highlight=all_orm_descriptors#sqlalchemy.orm.mapper.Mapper.all_orm_descriptors
>
> On Fri, Jul 12, 2019 at 11:19 AM Mike Bayer <mike...@zzzcomputing.com> wrote:
>> __
>>
>>
>> On Fri, Jul 12, 2019, at 11:20 AM, Michael P. McDonnell wrote:
>>> Hey Team -
>>>
>>> So I'm working on a relatively fun hierarchy that allows me to relate tasks
>>> to games.
>>> So I have 2 tables:
>>>
>>> # A Generic Task Definition - not related to anything
>>> class Task_Definition(Base):
>>> def __repr__(self):
>>> return (
>>> "<TaskDefinition id='" + str(self.id) + "' "
>>> + "name='" + self.name + "'>")
>>> __table__ = task_definition_table # has name, id, etc...
>>>
>>> # and a Task Table - specific to a game:
>>> class Task(Base):
>>> def __repr__(self):
>>> return (
>>> "<Task id='" + str(self.id) + "' name='" + self.name
>>> + "' game_id='" + str(self.game_id)
>>> + " task_definition_id=" + str(self.task_definition_id) + "'>")
>>> __table__ = task_table # has game_id, start_dttm, end_dttm, etc...
>>>
>>> So originally I had my task as a
>>> __table__ = join(task_definition_table, task_table)
>>>
>>> That allowed me to select a task, and see all of the task_definition
>>> properties as one "Object"
>>> But the problem is: when I created a task - it wanted to create a new
>>> task_definition at the same time, which is not what I wanted - given that
>>> task_definitions are a generic that can be used anytime.
>>>
>>> So then I created a task like this:
>>> class Task(Base):
>>> def __repr__(self):
>>> return (
>>> "<Task id='" + str(self.id) + "' name='" + self.name
>>> + "' game_id='" + str(self.game_id)
>>> + " task_definition_id=" + str(self.task_definition_id) + "'>")
>>> __table__ = task_table
>>> task_definition_id = column_property(
>>> task_definition_table.c.id,
>>> task_definition_language_table.c.task_definition_id,
>>> task_table.c.task_definition_id)
>>> name = column_property(task_definition_language_table.c.name)
>>> description = column_property(task_definition_language_table.c.description)
>>> instructions = column_property(
>>> task_definition_language_table.c.instructions)
>>>
>>> That allowed me to insert properly - but then my selects were coming back
>>> with tons of duplicate rows.
>>>
>>> When I played with the query - it was because it was doing a
>>> SELECT * from task, task_definition
>>> as opposed to a
>>> select * from task JOIN task_definition...
>>>
>>> Is there an easy way to force a join on select, and then a direct table
>>> communication on insert?
>>
>> So if you wanted to use column_property() like that, you would do it with a
>> correlated select, as seen in the example at
>> https://docs.sqlalchemy.org/en/13/orm/mapped_sql_expr.html#using-column-property.
>> However, you have a whole bunch of them here and correlated selects aren't
>> efficient from a SQL perspective.
>>
>> From the ORM point of view, exactly what you want is available using
>> relationship() with lazy='joined':
>>
>> class Task(Base):
>> # ...
>>
>> description = relationship("TaskDefinition", lazy="joined")
>>
>> But you don't want to say "mytask.description.name", OK. The data is in
>> Python now so you can use all of Python's capabilities, in this case it
>> would be to use descriptors:
>>
>> class Task(Base):
>> # ...
>>
>> @property
>> def description(self):
>> return self._description.description
>>
>> @description.setter # optional setter
>> def description(self, value):
>> self._description.description = value
>>
>> # etc ...
>>
>> _description = relationship("TaskDefinition", lazy="joined")
>>
>>
>> The above pattern is also available using the association proxy, which might
>> be a little bit heavy-handed here, however is less code up front,
>> association proxy is at
>> https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#:
>>
>> from sqlalchemy.ext.associationproxy import association_proxy
>>
>> class Task(Base):
>> # ...
>> description = association_proxy("_description", "description")
>>
>> # etc ...
>>
>> _description = relationship("TaskDefinition", lazy="joined")
>>
>>
>> hope this helps!
>>
>>
>>
>>
>>
>>>
>>>
>>> -Mike
>>>
>>>
>>> --
>>> 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.
>>> To view this discussion on the web visit
>>> https://groups.google.com/d/msgid/sqlalchemy/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/sqlalchemy/328f63b3-dda4-4b59-a85b-52846e319d53%40googlegroups.com?utm_medium=email&utm_source=footer>.
>>> 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.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sqlalchemy/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com
>>
>> <https://groups.google.com/d/msgid/sqlalchemy/9429f760-5845-47cf-92b6-dee34e9f0c1c%40www.fastmail.com?utm_medium=email&utm_source=footer>.
>> 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHqFB-1spAzQd_VgCLChyaE%2BH5Pb94ymqXtaza6%3DyAbjVQ%40mail.gmail.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHqFB-1spAzQd_VgCLChyaE%2BH5Pb94ymqXtaza6%3DyAbjVQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> 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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/62388520-54b1-4f93-98e8-90050dbee9ea%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
