Select function and table columns on a single SQLAlchemy query

user_1930146 0
Viewed 352

I did some searching but couldn't find any examples.

Is there a way to query multiple columns from both tables and a function call while using SQLAlchemy Core?

This is the query I want to achieve:

SELECT
    t.id, t.field_1, t.field_2,
    some_function(t.field_2, t.field_3).*
FROM my_table t

Where some_function is a function that returns a 3-tuple, say calc_field_1, calc_field_2 and calc_field_3

The resulting set should be a table like so:

id field_1 field_2 calc_field_1 calc_field_2 calc_field_3
1 ... ... ... ... ...
2 ... ... ... ... ...
3 ... ... ... ... ...
sqlalchemy python
1 Answers

If it is possible to change original SQL to this

SELECT
    t.id, t.field_1, t.field_2,
    calc_field_1, calc_field_2, calc_field_3
FROM my_table t, some_function(t.field_2, t.field_3);

then it's possible to express on Sqlalchemy Core

stmt = (
    select([
        my_table.c.id,
        my_table.c.field_1,
        my_table.c.field_2,
        column('calc_field_1'),
        column('calc_field_2'),
        column('calc_field_3')
    ])
    .select_from(my_table)
    .select_from(func.some_function(my_table.c.field_2, my_table.c.field_3))
)

Currently (as of Sqlalchemy 1.3) docs suggest that here. Sqlalchemy 1.4 (in beta) should provide table valued functions

If you can't move the function to the FROM-clause, then..

from sqlalchemy import literal_column
from sqlalchemy.sql.expression import Grouping
stmt = (
    select([
        my_table.c.id,
        my_table.c.field_1,
        my_table.c.field_2,
        Grouping(
            func.some_function(my_table.c.field_2, my_table.c.field_3)
        ).op('.')(literal_column('calc_field_1')).label('calc_field_1'),
        Grouping(
            func.some_function(my_table.c.field_2, my_table.c.field_3)
        ).op('.')(literal_column('calc_field_2')).label('calc_field_2'),
        Grouping(
            func.some_function(my_table.c.field_2, my_table.c.field_3)
        ).op('.')(literal_column('calc_field_3')).label('calc_field_3'),
    ])
    .select_from(my_table)
)

it will produce the following SQL

SELECT t.id, t.field_1, t.field_2,
  (some_function(t.field_2, t.field_3)) . calc_field_1 AS calc_field_1,
  (some_function(t.field_2, t.field_3)) . calc_field_2 AS calc_field_2,
  (some_function(t.field_2, t.field_3)) . calc_field_3 AS calc_field_3
FROM my_table t

And a shorthand operation:

from sqlalchemy.sql import functions

class some_function(functions.GenericFunction):
    def __getitem__(self, key):
        return Grouping(self).op('.')(column(key, String))

stmt = (
    select([
        my_table.c.id,
        my_table.c.field_1,
        my_table.c.field_2,
        some_function(my_table.c.field_1, my_table.c.field_2)['calc_field_1'].label('calc_field_1'),
        some_function(my_table.c.field_1, my_table.c.field_2)['calc_field_2'].label('calc_field_2'),
        some_function(my_table.c.field_1, my_table.c.field_2)['calc_field_3'].label('calc_field_3'),
    ])
    .select_from(my_table)
)

Solution is taken from here, checked with postgresql

user_4651360
Related