1

I am trying to run a query that uses variable substitution for both single variables and a sequence of variables. Here is a simplification of the kind of query I want to run:

SELECT 
    t.geography_id,
    t.source_id,
    t.target_date
FROM (VALUES %s) AS t(geography_id, source_id, target_date)
UNION
SELECT
    my_table.geography_id,
    my_table.source_id,
    my_table.target_date
FROM my_table where my_table.target_date = %s

With arguments [(1, 4, date(2021, 1, 1)), (2, 5, date(2021, 1, 2))], date(2021, 1, 3) (a list of tuples and then a date), I would want the resulting query to look like this after variable substitution:

SELECT 
    t.geography_id,
    t.source_id,
    t.target_date
FROM (VALUES (1, 4, '2021-01-01'::date),
     (2, 5, '2021-01-02'::date)) AS t(geography_id, source_id, target_date)
UNION
SELECT
    my_table.geography_id,
    my_table.source_id,
    my_table.target_date
FROM my_table where my_table.target_date = '2021-01-03'::date

What does not work

I can accomplish the first part of the query using execute_values like so:

sql = """
    SELECT 
        t.geography_id,
        t.source_id,
        t.target_date
    FROM (VALUES %s) AS t(geography_id, source_id, target_date)
"""

argslist = [(1, 4, date(2021, 1, 1)), (2, 5, date(2021, 1, 2))]

execute_values(cur, sql, argslist)

However, the execute_values documentation says that the sql argument "must contain a single %s placeholder, which will be replaced by a VALUES list", which means I cannot add the other part of my query.

I also tried out the execute_batch method, but it seems to run a separate statement for each item in the list, so it does not return the complete result I expected.

What does work

What does work is using AsIs to turn my sequential argument into a string to pass into a regular execute call. However, I'm reluctant to use this because then I have to worry about escaping, sanitizing, data type conversion, etc.

So I am wondering if there is a better way to accomplish this with the pyscopg2 methods or if I will need to restructure or split up my query. Thanks in advance for any insight you might have.

Improve this question
2
  • Does this answer your question? psycopg2 prepared delete statement, have a look at my answer there. Commented Apr 15, 2021 at 10:04
  • Thank you, that helped! Commented Apr 22, 2021 at 18:00

1 Answer 1

Reset to default
1

Here's what I ended up doing, with help from Maurice's comment:

query_template = """
    SELECT 
       t.geography_id,
        t.source_id,
        t.target_date
    FROM (VALUES ({}) AS t(geography_id, source_id, target_date)
    UNION
    SELECT
        my_table.geography_id,
        my_table.source_id,
        my_table.target_date
    FROM my_table where my_table.target_date = %s
"""

argslist = [(1, 4, date(2021, 1, 1)), (2, 5, date(2021, 1, 2))]

# Replace "{}" in query template with placeholders for argslist values
query = sql.SQL(query_template).format(
    sql.SQL('), (').join(
        (sql.SQL(', ').join(sql.Placeholder() * len(argslist[0]))) * (
            len(argslist))))

# Flatten argslist for query arguments
query_args = [value for row in skip_table for value in row]
query_args.append(date(2021, 1, 3))

query_string = query.as_string(con)
cur.execute(query_string, query_args)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.