0

I am implementing a query for average, but how can I explicitly define double in SQLAlchemy as defined in the below PostgreSQL query:

SELECT avg((tags->>'price')::double precision) from parcel_table

The Python:

parcel_table.query.\
    with_entities(
        func.avg(parcel_table.tags["price"]).
            label('average`')).\
    filter(
        parcel_table.time_stamp.between("'2017-01-01 00:00:000'",
                                        "'2017-10-01 00:00:000'"),
        parcel_table.name== 'sakshi').\
    first()
sakshi
  • 171
  • 1
  • 1
  • 14

1 Answers1

0

The expression

(tags->>'price')::double precision

being a JSON index operation with a cast has a nice shortcut in SQLAlchemy:

from sqlalchemy.dialects import postgresql

parcel_table.tags["price"].astext.cast(postgresql.DOUBLE_PRECISION)

Note that the name "price" would hint at this being about money, and floating point numbers and money are a bad match, usually.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • with_entities is not working in python as query is coming correct but due to with_entities data is not coming in python can you help me for that, – sakshi Nov 17 '17 at 09:40
  • I think problem is not with with_entities – sakshi Nov 17 '17 at 10:15
  • problem is due to astext.cast(postgresql.DOUBLE_PRECISION) if I am averaging other number without DOUBLE_PRECISION then it is working fine – sakshi Nov 17 '17 at 10:16
  • I'm afraid you've lost me. Do you get some error, or just invalid results? What did you do, and what did you expect as results? – Ilja Everilä Nov 17 '17 at 10:19
  • To add to the previous, `Model.json['foo'].astext.cast()` should be and is fine, if all values under *foo* are either numbers, strings representing numbers, or JSON nulls. If you have some other data, the cast will fail. – Ilja Everilä Nov 17 '17 at 10:33
  • I am getting output none – sakshi Nov 17 '17 at 10:37
  • is this problem due to null – sakshi Nov 17 '17 at 10:37
  • That'd sound like all your values are NULL. Are you sure you're using the correct key? The indexing operator will return NULL if accessing a non existent key, as noted [here](https://www.postgresql.org/docs/current/static/functions-json.html). Of course `AVG()` will result in NULL if your query results in 0 rows as well, so also check your filter predicates. – Ilja Everilä Nov 17 '17 at 10:39