Re: [sqlalchemy] Passing reference to model to model column_property function
1. Yes, literal_column does the trick.
2. I have tried with hybrid_method. It also works with literal_column, but
the problem here is that it's not including the field automatically in the
query? I suspect this is by design, but in my case it's required to execute
function so that I get a value from database and not from python method.
Can I force hybrid_method to always get a value from database?
On Sunday, 17 May 2020 16:37:25 UTC+2, Mike Bayer wrote:
>
>
>
> On Sun, May 17, 2020, at 6:54 AM, Erol Merdanović wrote:
>
> Hi
>
> First thank you for your reply.
>
> @Mike, yes. I wish to pass the row products row. I'm attaching working SQL
>
> SELECT *, get_product_price(products) FROM products;
>
>
> This works great in postgres. I tried it also on Mysql but they support
> only scalar values. I suspect that might work with other databases. I don't
> need to pass any dynamic arguments to column_property, it just needs to
> reference to existing row. I'll try with hybrid and let you know.
>
>
>
> oh is that a fixed expression ?you can probably get away with it like
> this then:
>
>
> from sqlalchemy import literal_column
>
> Product.base_price = column_property(
> cast(func.get_product_price(literal_column("products")), Numeric(15,
> 5)).label("base_price")
> )
>
> that is, if you want exactly the word "products" to appear in the SQL, and
> nothing else about it matters, you can just do it that way.
>
>
>
>
>
>
> @Jonathan
>
> Yes, if I pass the ID it works great. I'm just worried about the
> performance. If I want to execute then function in huge SELECT, I would
> need to do reselect on each row.
>
>
> On Saturday, 16 May 2020 21:42:54 UTC+2, Jonathan Vanasco wrote:
>
> It’s been a while since I’ve worked on stuff like this, but IIRC the
> simplest way was to use a function that accepts an ID and to flush in
> SqlAlchemy before executing it. Then you select the necessary row fields
> within the sql function, instead of passing args in or trying to pass a row
> in.
>
> In my experience, when you pass multiple args in, the function becomes
> very fragile and prone to break as your model changes.
>
>
> --
> 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/fa2e6ac5-22ed-4448-8228-b29a11682975%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/4600dc66-da7c-4466-b8c7-0c3b90c74acf%40googlegroups.com.
Re: [sqlalchemy] Passing reference to model to model column_property function
On Sun, May 17, 2020, at 6:54 AM, Erol Merdanović wrote:
> Hi
>
> First thank you for your reply.
>
> @Mike, yes. I wish to pass the row products row. I'm attaching working SQL
>
>> SELECT *, get_product_price(products) FROM products;
>
> This works great in postgres. I tried it also on Mysql but they support only
> scalar values. I suspect that might work with other databases. I don't need
> to pass any dynamic arguments to column_property, it just needs to reference
> to existing row. I'll try with hybrid and let you know.
oh is that a fixed expression ? you can probably get away with it like this
then:
from sqlalchemy import literal_column
Product.base_price = column_property(
cast(func.get_product_price(literal_column("products")), Numeric(15,
5)).label("base_price")
)
that is, if you want exactly the word "products" to appear in the SQL, and
nothing else about it matters, you can just do it that way.
>
> @Jonathan
>
> Yes, if I pass the ID it works great. I'm just worried about the performance.
> If I want to execute then function in huge SELECT, I would need to do
> reselect on each row.
>
>
> On Saturday, 16 May 2020 21:42:54 UTC+2, Jonathan Vanasco wrote:
>> It’s been a while since I’ve worked on stuff like this, but IIRC the
>> simplest way was to use a function that accepts an ID and to flush in
>> SqlAlchemy before executing it. Then you select the necessary row fields
>> within the sql function, instead of passing args in or trying to pass a row
>> in.
>>
>> In my experience, when you pass multiple args in, the function becomes very
>> fragile and prone to break as your model changes.
>
> --
> 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/fa2e6ac5-22ed-4448-8228-b29a11682975%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/60f2f100-de13-4854-b882-4ceeaca888a2%40www.fastmail.com.
Re: [sqlalchemy] Passing reference to model to model column_property function
Hi First thank you for your reply. @Mike, yes. I wish to pass the row products row. I'm attaching working SQL SELECT *, get_product_price(products) FROM products; This works great in postgres. I tried it also on Mysql but they support only scalar values. I suspect that might work with other databases. I don't need to pass any dynamic arguments to column_property, it just needs to reference to existing row. I'll try with hybrid and let you know. @Jonathan Yes, if I pass the ID it works great. I'm just worried about the performance. If I want to execute then function in huge SELECT, I would need to do reselect on each row. On Saturday, 16 May 2020 21:42:54 UTC+2, Jonathan Vanasco wrote: > > It’s been a while since I’ve worked on stuff like this, but IIRC the > simplest way was to use a function that accepts an ID and to flush in > SqlAlchemy before executing it. Then you select the necessary row fields > within the sql function, instead of passing args in or trying to pass a row > in. > > In my experience, when you pass multiple args in, the function becomes > very fragile and prone to break as your model changes. -- 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/fa2e6ac5-22ed-4448-8228-b29a11682975%40googlegroups.com.
Re: [sqlalchemy] Passing reference to model to model column_property function
It’s been a while since I’ve worked on stuff like this, but IIRC the simplest way was to use a function that accepts an ID and to flush in SqlAlchemy before executing it. Then you select the necessary row fields within the sql function, instead of passing args in or trying to pass a row in. In my experience, when you pass multiple args in, the function becomes very fragile and prone to break as your model changes. -- 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/d9a59130-e7f8-4c24-96ad-c0e8bb255bc3%40googlegroups.com.
Re: [sqlalchemy] Passing reference to model to model column_property function
On Sat, May 16, 2020, at 1:04 PM, Erol Merdanović wrote:
> Hi
>
> I have a model definition
>
> class Product(db.Model):
> __tablename__ = "products"
>
> id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
> sku = db.Column(db.String(255), nullable=False, unique=True, index=True)
>
>
> Product.base_price = column_property(
> cast(func.get_product_price(), Numeric(15, 5)).label("base_price")
> )
>
> get_product_price is postgres function
>
>> CREATE OR REPLACE FUNCTION public.get_product_price()
>> RETURNS numeric AS
>> $BODY$
>> BEGIN
>> return 100;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>
> If I execute the query as
>
> Product.query.order_by(Product.base_price.asc()).all()
>
> it all works great.
>
> The problem is if I want to pass the product to function
>
>> CREATE OR REPLACE FUNCTION public.get_product_price(product products)
>> RETURNS numeric AS
>> $BODY$
>> -- do something with product
>> BEGIN
>> return 100;
>> END;
>> $BODY$
>> LANGUAGE plpgsql VOLATILE
>> COST 100;
>
> Is it possible to pass the reference/alias to product to function? I tried
> with alias but without any success.
I'm not deeply familiar with PGs elaborate syntaxes, are you trying to pass
whole table rows into the function? We're only beginning to consider supporting
this syntax which you can see in issues like
https://github.com/sqlalchemy/sqlalchemy/issues/3566
https://github.com/sqlalchemy/sqlalchemy/issues/5256 .
Assuming SQLAlchemy supported that syntax, which you *can* get using workaround
in the above linked issues, it's still not clear how this would apply to a
mapped column_property(). A column_property() is a fixed SQL expression that
does not change and does not accept arguments. If you wanted to pass runtime
arguments to a mapped attribute and have it return a SQL function, use a hybrid
method:
https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#sqlalchemy.ext.hybrid.hybrid_method
>
>
> --
> 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/071cec63-88d8-4660-bfe0-e5317924493e%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/efaa2813-221e-4520-8712-cb9c23a36ec3%40www.fastmail.com.
[sqlalchemy] Passing reference to model to model column_property function
Hi
I have a model definition
class Product(db.Model):
__tablename__ = "products"
id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
sku = db.Column(db.String(255), nullable=False, unique=True, index=True)
Product.base_price = column_property(
cast(func.get_product_price(), Numeric(15, 5)).label("base_price")
)
get_product_price is postgres function
CREATE OR REPLACE FUNCTION public.get_product_price()
> RETURNS numeric AS
> $BODY$
> BEGIN
>return 100;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
If I execute the query as
Product.query.order_by(Product.base_price.asc()).all()
it all works great.
The problem is if I want to pass the product to function
CREATE OR REPLACE FUNCTION public.get_product_price(product products)
> RETURNS numeric AS
> $BODY$
> -- do something with product
> BEGIN
>return 100;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
Is it possible to pass the reference/alias to product to function? I tried
with alias but without any success.
--
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/071cec63-88d8-4660-bfe0-e5317924493e%40googlegroups.com.
