Re: [sqlalchemy] Duplicating a relationship from one ORM model to a subquery model
send viewonly=True to these relationships you make. these are not for writing
anyway and that will resolve the overlaps warnings (the warning says as much).
On Thu, Sep 7, 2023, at 10:41 AM, zedr...@gmail.com wrote:
> Hi Mike,
>
> Thanks a lot for taking the time to reply…
>
> Indeed, I came to a similar conclusion and worked out what seems like a
> fairly clean way to copy relationships (assuming they do not use
> secondary_join).
>
> The fact that I cannot seem to update the mapper before the class is created
> makes my code a bit more convoluted than I'd like… But this seems to work:
>
> def copy_relationship(source_model, relationship_name):
> source_relationship = getattr(source_model.__mapper__.relationships,
> relationship_name, None)
> kwargs = {}
> overlaps = [relationship_name]
> for param_name in inspect.signature(relationship).parameters:
> if value := getattr(source_relationship, param_name, None):
> if param_name in ('backref', 'back_populates'):
> overlaps.append(value)
> elif param_name not in ('init', 'primaryjoin'):
> kwargs[param_name] = value
> return relationship(**kwargs, overlaps = ','.join(overlaps))
>
> class FooViewMeta(type(db.Model)):
> def __new__(mcs, name, bases, attrs):
> attrs["__table__"] = (select(Foo).with_only_columns(Foo.id).subquery())
> attrs["bazs"] = copy_relationship(Foo, 'bazs')
> return super().__new__(mcs, name, (db.Model,), attrs)
>
> def __init__(cls, name, bases, attrs):
> super().__init__(name, bases, attrs)
> for rel_name, rel_attr in cls.__mapper__.relationships.items():
> expr = getattr(rel_attr, 'primaryjoin', None)
> if not expr: continue
> kwargs = {'operator': expr.operator}
> for attr in ('left', 'right'):
> side = getattr(expr, attr)
> if ('__table__' in side) and (side.table == Foo.__table__) and (side.name in
> cls.__table__.c):
> side = foreign(getattr(cls.__table__.c, side.name))
> kwargs[attr] = side
> rel_attr.primary_join = BinaryExpression(**kwargs)
>
> class FooView(metaclass=FooViewMeta):
> pass
>
>
>
> Running the code above without providing an overlaps argument to the new
> relationship, results in a relationship conflict warning:
> :10: SAWarning: relationship 'FooView.bazs' will copy column
> anon_1.id to column baz.foo_id,
> which conflicts with relationship(s): 'Baz.foo' (copies foo.id to
> baz.foo_id). 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.
> To silence this warning, add the parameter 'overlaps="foo"' to the
> 'FooView.bazs' relationship.
> (Background on this warning at: https://sqlalche.me/e/20/qzyx)
>
> As you can see in the code above, adding foreign to the join expression, does
> not solve the problem.
>
> All in all, I wish there was a more straightforward way to create models that
> inherit from an existing model, while loading/accessing only a subset of
> columns (basically, polymorphism, without a polymorphism key)… Will gladly
> take any suggestion on a different approach…
>
> But at least this seems to work!
>
> Thanks again,
> --
> Dave
>
>
> On Thursday, 7 September 2023 at 13:33:05 UTC+1 Mike Bayer wrote:
>>
>>
>> On Thu, Sep 7, 2023, at 4:39 AM, zedr...@gmail.com wrote:
>>>
>>> *Is there a clean way to (programmatically) duplicate all relationship from
>>> an existing model, over to a new model (that targets the same table and
>>> selects a subset of columns as a subquery)?*
>>
>> relatonships are fixed to their parent class and have internal state that is
>> dependent on it, so it can't be shared between two unrelated classes.
>>
>> you would need to introspect the attributes of each relationship that are
>> important to the application, such as the target class etc., and create a
>> new relationship() that has those attributes.Assuming the relationships
>> dont have special join conditions, this would involve just looking for
>> existing_class.__mapper__.attrs.relationship.entity, which is the mapper to
>> which it refers, and placing this into a new relationship().
>>
>>
>>>
>>
>
>
> --
> 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/3693fbd8-e845-46e8-ad11-b8611a236774n%40googlegroups.com
>
Re: [sqlalchemy] Duplicating a relationship from one ORM model to a subquery model
Hi Mike,
Thanks a lot for taking the time to reply…
Indeed, I came to a similar conclusion and worked out what seems like a
fairly clean way to copy relationships (assuming they do not use
secondary_join).
The fact that I cannot seem to update the mapper before the class is
created makes my code a bit more convoluted than I'd like… But this seems
to work:
def copy_relationship(source_model, relationship_name):
source_relationship = getattr(source_model.__mapper__.relationships,
relationship_name, None)
kwargs = {}
overlaps = [relationship_name]
for param_name in inspect.signature(relationship).parameters:
if value := getattr(source_relationship, param_name, None):
if param_name in ('backref', 'back_populates'):
overlaps.append(value)
elif param_name not in ('init', 'primaryjoin'):
kwargs[param_name] = value
return relationship(**kwargs, overlaps = ','.join(overlaps))
class FooViewMeta(type(db.Model)):
def __new__(mcs, name, bases, attrs):
attrs["__table__"] = (select(Foo).with_only_columns(Foo.id).subquery())
attrs["bazs"] = copy_relationship(Foo, 'bazs')
return super().__new__(mcs, name, (db.Model,), attrs)
def __init__(cls, name, bases, attrs):
super().__init__(name, bases, attrs)
for rel_name, rel_attr in cls.__mapper__.relationships.items():
expr = getattr(rel_attr, 'primaryjoin', None)
if not expr: continue
kwargs = {'operator': expr.operator}
for attr in ('left', 'right'):
side = getattr(expr, attr)
if ('__table__' in side) and (side.table == Foo.__table__) and (side.name in
cls.__table__.c):
side = foreign(getattr(cls.__table__.c, side.name))
kwargs[attr] = side
rel_attr.primary_join = BinaryExpression(**kwargs)
class FooView(metaclass=FooViewMeta):
pass
Running the code above without providing an overlaps argument to the new
relationship, results in a relationship conflict warning:
:10: SAWarning: relationship 'FooView.bazs' will copy column
anon_1.id to column baz.foo_id,
which conflicts with relationship(s): 'Baz.foo' (copies foo.id to
baz.foo_id). 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.
To silence this warning, add the parameter 'overlaps="foo"' to the
'FooView.bazs' relationship.
(Background on this warning at: https://sqlalche.me/e/20/qzyx)
As you can see in the code above, adding foreign to the join expression,
does not solve the problem.
All in all, I wish there was a more straightforward way to create models
that inherit from an existing model, while loading/accessing only a subset
of columns (basically, polymorphism, without a polymorphism key)… Will
gladly take any suggestion on a different approach…
But at least this seems to work!
Thanks again,
--
Dave
On Thursday, 7 September 2023 at 13:33:05 UTC+1 Mike Bayer wrote:
>
>
> On Thu, Sep 7, 2023, at 4:39 AM, zedr...@gmail.com wrote:
>
>
> *Is there a clean way to (programmatically) duplicate all relationship
> from an existing model, over to a new model (that targets the same table
> and selects a subset of columns as a subquery)?*
>
>
> relatonships are fixed to their parent class and have internal state that
> is dependent on it, so it can't be shared between two unrelated classes.
>
> you would need to introspect the attributes of each relationship that are
> important to the application, such as the target class etc., and create a
> new relationship() that has those attributes.Assuming the relationships
> dont have special join conditions, this would involve just looking for
> existing_class.__mapper__.attrs.relationship.entity, which is the mapper to
> which it refers, and placing this into a new relationship().
>
>
>
>
>
--
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/3693fbd8-e845-46e8-ad11-b8611a236774n%40googlegroups.com.
Re: [sqlalchemy] Duplicating a relationship from one ORM model to a subquery model
On Thu, Sep 7, 2023, at 4:39 AM, zedr...@gmail.com wrote: > > *Is there a clean way to (programmatically) duplicate all relationship from > an existing model, over to a new model (that targets the same table and > selects a subset of columns as a subquery)?* relatonships are fixed to their parent class and have internal state that is dependent on it, so it can't be shared between two unrelated classes. you would need to introspect the attributes of each relationship that are important to the application, such as the target class etc., and create a new relationship() that has those attributes.Assuming the relationships dont have special join conditions, this would involve just looking for existing_class.__mapper__.attrs.relationship.entity, which is the mapper to which it refers, and placing this into a new relationship(). > -- 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/3c23e775-f2d1-487d-8f5d-e7785399aeba%40app.fastmail.com.
[sqlalchemy] Duplicating a relationship from one ORM model to a subquery model
I am trying to (programmatically) create partial views of existing
SQLAlchemy models, using a metaclass.
A streamlined example, trying to get a subquery view on a model Foo with a
relationship bar, would look something like that:
class Bar(db.Model):
id = db.Column(db.Integer, primary_key=True)
class Foo(db.Model):
id = db.Column(db.Integer, primary_key=True)
bar_id = db.Column(db.Integer, db.ForeignKey("bar.id"))
bar = db.relationship(Bar, backref='foos')
class FooViewMeta(type(db.Model)):
def __new__(mcs, name, bases, attrs):
attrs["__table__"] = (select(Foo)
.with_only_columns(Foo.id, Foo.bar_id, Foo.bar)
.subquery())
attrs["bar"] = Foo.__mapper__.relationships["bar"]
attrs["__mapper_args__"] = {"exclude_properties": ["bar"]}
return super().__new__(mcs, name, (db.Model,), attrs)
class FooView(metaclass=FooViewMeta):
pass
(Omitting exclude_properties gives me a warning about conflicting columns)
I am able to load FooView and access its bar property fine.
Unfortunately, if I run:
q = Foo.query.options(joinedload(Foo.bar),)
q.first()
I get the following error, which tells me that this is not a correct way to
duplicate the mapper for that relationship:
: Mapped class
Mapper[FooView(anon_1)] does
not apply to any of the root entities in this query, e.g. Mapper[Foo(foo)].
Please specify the full path from one of the root entities to the target
attribute.
Clearly, the re-use of this relationship is confusing the mapper, who tries
to use the newer subquery model and fails.
Conversely, if I use:
attrs["bar"] = getattr(Foo, "bar")
The code works for 1-1 relationship, but fails for lazy 1-many…
*Is there a clean way to (programmatically) duplicate all relationship from
an existing model, over to a new model (that targets the same table and
selects a subset of columns as a subquery)?*
Note: unfortunately, I am not looking for polymorphic subclassing here (the
"view" classes are a subset of columns, not rows), so it does not seem like
I can use any of SQLAlchemy mapper options for that…
--
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/8b32d287-1f74-4ebb-bc86-429fb3651effn%40googlegroups.com.
