CODEWITHSUNDEEP
sqlalchemy
user3557405
user3557405

Reputation: 626

sqlalchemy union multiple dynamic queries

I am dynamically creating raw text SQL queries as follows:

# my_items is a list of dicts.
# Each dict has keys, col_1 and col_2, with different values.
for col_values in my_items:
    sql = sqlalchemy.text(
            "SELECT col_1, col_2
             FROM my_table WHERE col_1 = :col_1 AND col_2 = :col_2")
    result = conn.execute(sql, **col_values)

However, the above is not efficient and I would like to retrieve all data once by unioning all queries:

queries = []

for col_values in my_items:
    sql = sqlalchemy.text(
            "SELECT col_1, col_2
             FROM my_table WHERE col_1 = :col_1 AND col_2 = :col_2")
    queries.append(sql)

final_sql = union(*queries) # does not work with Text objects
result = conn.execute(final_sql) # not sure how to bind params for individual items here!!

The problem is that, first, I cannot use union as it expects statements instead of text, and secondly, I do not know how to bind the respective input parameters to their queries.

Upvotes: 0

Views: 88

Answers (1)

ljmc
ljmc

Reputation: 5264

You need to bind the parameters to the TextClause created with text and turn it into a TextualSelect.

queries = []

for col_values in my_items:
    stmt = (
        sqlalchemy.text(
            """
                SELECT col_1, col_2
                FROM my_table
                WHERE col_1 = :col_1 AND col_2 = :col_2
            """
        )
        .bindparams(**col_values)
        .columns(col_1=db.<TYPE>, col_2=db.<TYPE>)
    )
    queries.append(stmt)

final_stmt = union(*queries)

print(final_stmt.compile(compile_kwargs={"literal_binds": True}))

   SELECT col_1, col_2
   FROM my_table WHERE col_1 = 1 AND col_2 = 11
UNION ALL 
   SELECT col_1, col_2
   FROM my_table WHERE col_1 = 3 AND col_2 = 13
UNION ALL 
   SELECT col_1, col_2
   FROM my_table WHERE col_1 = 5 AND col_2 = 15

NB. I don't the actual data or schema, so I have not run this, let me know if there is any problem.

Upvotes: 1

Related Questions