Passing reference to model to model column_property function

[Erol Merdanović]

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.

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/071cec63-88d8-4660-bfe0-e5317924493e%40googlegroups.com.

[Jonathan Vanasco]

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.

[Erol Merdanović]

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. 

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/fa2e6ac5-22ed-4448-8228-b29a11682975%40googlegroups.com.

[Erol Merdanović]

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?

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.