Re: [sqlalchemy] Update Username and Password from vault

[Mike Bayer]

no, create_engine() does not connect at all. connections occur when you 
first call `engine.connect()`.   From that point, the behavior of subsequent 
`engine.connect()` calls depends on connection pool configuration.   all 
connection pools have points at which they continue to establish new 
connections as the application proceeds, it's just a question of how often and 
under what circumstances.The default QueuePool will make new connections 
when it goes into "overflow", as well as when existing connections are 
invalidated due to connectivity problems or if the pool_recycle timeout is 
reached.



On Thu, Sep 7, 2023, at 2:34 PM, Steven Schierholz wrote:
> That makes sense but doesn't connect only happen once when create_engine() is 
> called?
> 
> On Thursday, September 7, 2023 at 12:00:35 PM UTC-6 Mike Bayer wrote:
>> __
>> the documentation for this pattern is at 
>> https://docs.sqlalchemy.org/en/20/core/engines.html#generating-dynamic-authentication-tokens
>>  , and a completely specific example is at 
>> https://docs.sqlalchemy.org/en/20/dialects/mssql.html#mssql-pyodbc-access-tokens
>>  .   Basically your application needs to have some way to retrieve the 
>> correct credentials as it runs, and you hook that into the event to populate 
>> the connect arguments with the correct credentials.
>> 
>> On Thu, Sep 7, 2023, at 1:54 PM, Steven Schierholz wrote:
>>> So I have seen some chats here about cred refresh from vault and some 
>>> suggestions have been to use @event.listens_for(engine, "do_connect") to 
>>> update creds when the connection is established. My understanding of this 
>>> is that connecting to the database should only happen once when my flask 
>>> application starts up, but I need to update the creds without restarting my 
>>> application so I'm not sure that the event listener will work in my case.
>>> 
>>> Am I understanding that correctly? If so, is there a way to get the right 
>>> creds to pass to the engine for sqlalchemy every 24 hours when the creds 
>>> from vault get updated without restarting my application?
>>> 
>>> 
>>> --
>>> 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/2de2e3fb-526c-49f7-8780-9ef55a9ad8bcn%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/5c9c5b5f-a84b-41b8-8eb2-c3117fbf50f5n%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/71dcdb8d-2493-403d-83f7-7060041a563c%40app.fastmail.com.

Re: [sqlalchemy] Update Username and Password from vault

[Steven Schierholz]

That makes sense but doesn't connect only happen once when create_engine() 
is called?
On Thursday, September 7, 2023 at 12:00:35 PM UTC-6 Mike Bayer wrote:

> the documentation for this pattern is at 
> https://docs.sqlalchemy.org/en/20/core/engines.html#generating-dynamic-authentication-tokens
>  
> , and a completely specific example is at 
> https://docs.sqlalchemy.org/en/20/dialects/mssql.html#mssql-pyodbc-access-tokens
>  
> .   Basically your application needs to have some way to retrieve the 
> correct credentials as it runs, and you hook that into the event to 
> populate the connect arguments with the correct credentials.
>
> On Thu, Sep 7, 2023, at 1:54 PM, Steven Schierholz wrote:
>
> So I have seen some chats here about cred refresh from vault and some 
> suggestions have been to use @event.listens_for(engine, "do_connect") to 
> update creds when the connection is established. My understanding of this 
> is that connecting to the database should only happen once when my flask 
> application starts up, but I need to update the creds without restarting my 
> application so I'm not sure that the event listener will work in my case.
>
> Am I understanding that correctly? If so, is there a way to get the right 
> creds to pass to the engine for sqlalchemy every 24 hours when the creds 
> from vault get updated without restarting my application?
>
>
> -- 
> 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/2de2e3fb-526c-49f7-8780-9ef55a9ad8bcn%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/2d36beb7-d2ae-4178-8787-442e74cc9615n%40googlegroups.com.

Re: [sqlalchemy] Update Username and Password from vault

[Steven Schierholz]

That makes sense but doesn't connect only happen once when create_engine() 
is called?

On Thursday, September 7, 2023 at 12:00:35 PM UTC-6 Mike Bayer wrote:

> the documentation for this pattern is at 
> https://docs.sqlalchemy.org/en/20/core/engines.html#generating-dynamic-authentication-tokens
>  
> , and a completely specific example is at 
> https://docs.sqlalchemy.org/en/20/dialects/mssql.html#mssql-pyodbc-access-tokens
>  
> .   Basically your application needs to have some way to retrieve the 
> correct credentials as it runs, and you hook that into the event to 
> populate the connect arguments with the correct credentials.
>
> On Thu, Sep 7, 2023, at 1:54 PM, Steven Schierholz wrote:
>
> So I have seen some chats here about cred refresh from vault and some 
> suggestions have been to use @event.listens_for(engine, "do_connect") to 
> update creds when the connection is established. My understanding of this 
> is that connecting to the database should only happen once when my flask 
> application starts up, but I need to update the creds without restarting my 
> application so I'm not sure that the event listener will work in my case.
>
> Am I understanding that correctly? If so, is there a way to get the right 
> creds to pass to the engine for sqlalchemy every 24 hours when the creds 
> from vault get updated without restarting my application?
>
>
> -- 
> 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/2de2e3fb-526c-49f7-8780-9ef55a9ad8bcn%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/5c9c5b5f-a84b-41b8-8eb2-c3117fbf50f5n%40googlegroups.com.

Re: [sqlalchemy] Update Username and Password from vault

[Mike Bayer]

the documentation for this pattern is at 
https://docs.sqlalchemy.org/en/20/core/engines.html#generating-dynamic-authentication-tokens
 , and a completely specific example is at 
https://docs.sqlalchemy.org/en/20/dialects/mssql.html#mssql-pyodbc-access-tokens
 .   Basically your application needs to have some way to retrieve the correct 
credentials as it runs, and you hook that into the event to populate the 
connect arguments with the correct credentials.

On Thu, Sep 7, 2023, at 1:54 PM, Steven Schierholz wrote:
> So I have seen some chats here about cred refresh from vault and some 
> suggestions have been to use @event.listens_for(engine, "do_connect") to 
> update creds when the connection is established. My understanding of this is 
> that connecting to the database should only happen once when my flask 
> application starts up, but I need to update the creds without restarting my 
> application so I'm not sure that the event listener will work in my case.
> 
> Am I understanding that correctly? If so, is there a way to get the right 
> creds to pass to the engine for sqlalchemy every 24 hours when the creds from 
> vault get updated without restarting my application?
> 
> 
> -- 
> 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/2de2e3fb-526c-49f7-8780-9ef55a9ad8bcn%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/a6b972d3-0700-4772-a2c8-243706e4c6e9%40app.fastmail.com.

[sqlalchemy] Update Username and Password from vault

[Steven Schierholz]

So I have seen some chats here about cred refresh from vault and some 
suggestions have been to use @event.listens_for(engine, "do_connect") to 
update creds when the connection is established. My understanding of this 
is that connecting to the database should only happen once when my flask 
application starts up, but I need to update the creds without restarting my 
application so I'm not sure that the event listener will work in my case.

Am I understanding that correctly? If so, is there a way to get the right 
creds to pass to the engine for sqlalchemy every 24 hours when the creds 
from vault get updated without restarting my application?

-- 
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/2de2e3fb-526c-49f7-8780-9ef55a9ad8bcn%40googlegroups.com.

Re: [sqlalchemy] Duplicating a relationship from one ORM model to a subquery model

[Mike Bayer]

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

[zedr...@gmail.com]

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

[Mike Bayer]

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

[zedr...@gmail.com]

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.