any way to make parameterized queries and encapsulate it python in function

Question

I want to implement a python function which is going to execute SQL query with parameters. To do so, I started to use psycopg2 for accessing my local db. However, I have written a bunch of very similar SQL queries whereas each SQL statement is slightly different from one to another in terms of taking different values. My goal is I want to write up parametric SQL so I could wrap it up in python function, ideally, I could make function call with arbitrary parameters so it could replace param values in SQL statement. I looked into SO post and got some idea but couldn't accomplish compact python function which could execute SQL statements with arbitrary parameters. I know how to write up python function with passing arbitrary parameters by using **kwargs, *args, but not sure how to do this for parametric SQl within python function. Is there any efficient way of doing this in python easily? Any workable approach to make this happen?

my db schema:

here is my table schema in postgresql:

CREATE TABLE mytable(
date_received DATE,
pk_est VARCHAR,
grd_name VARCHAR,
cl_val SMALLINT,
quant_received NUMERIC,
mg_fb_price NUMERIC,
freight NUMERIC,
standard_price NUMERIC,
grd_delv_cost NUMERIC,
order_type VARCHAR,
pk_name VARCHAR,
item_type VARCHAR,
waiting_days NUMERIC,
item_name VARCHAR,
mk_price_variance NUMERIC,
);

my example SQL query

here is one of the example SQL queries that needs to be parameterized:

SELECT
    date_trunc('week', date_received) AS received_week,
    cl_val,
    ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
    ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS mg_price_1,
    ROUND(ROUND(SUM(quant_received * mk_price_variance)::numeric,4) / SUM(quant_received),4) AS fb_mk_price_var,
    ROUND(ROUND(SUM(quant_received * freight)::numeric,4) / SUM(quant_received),4) AS freight_new,
    ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS grd_delv_cost_new,
    TO_CHAR(SUM(quant_received), '999G999G990D') AS Volume_Received
FROM mytable
WHERE date_received >= to_date('2010-10-01','YYYY-MM-DD')
    AND date_received <= to_date('2012-12-31','YYYY-MM-DD')
    AND item_type = 'processed'
    AND cl_val IN ('12.5','6.5','8.1','8.5','9.0')
    AND order_type IN ('formula')
    AND pk_name IN ('target','costco','AFG','KFC')
    AND pk_est NOT IN ('12')
GROUP BY received_week,cl_val
ORDER BY received_week ASC ,cl_val ASC;

my current attempt:

import psycopg2

connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
cursor = connection.cursor()
cursor.execute(
    """
    select * from mytable where date_received < any(array['2019-01-01'::timestamp, '2020-07-10'::timestamp])
    """)
record = cursor.fetchmany()

another attempt:

cursor.execute("""
    select date_trunc('week', date_received) AS received_week,
        cl_val,
        ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
    from (
        select * from mytable 
        where  item_type = %s and order_type IN %s
    ) t;
""", (item_type_value, order_type_value))

results = [r[0] for r in cursor.fetchall()]

but in my code, there is a lot of hardcoded parts that need to be parameterized. I am wondering is there any way of doing this in python. Can anyone point me out how to achieve this? Is that doable to implement parametric SQL whithin python function? Any idea? Thanks

goal

I am hoping to implement function like this:

def parameterized_sql(**kwargs, *args):
    connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
    cursor = connection.cursor()
    cursor.execute("""SQL statement with parameter""")
    ## maybe more

this is just a skeleton of python function I want to implement it but not sure it is doable. Any feedback would be helpful. Thanks

update:

I am expecting generic python function which can pass parameter value to SQL body so I can avoid writing up many SQL queries which actually have a lot of overlap from one to another, and it is not parameterized. The goal is to make parameterized SQL queries which can be executable in python function.

Could you give some more context about what is going wrong? You seem to correctly use the parameter substitution of `cursor.execute()`. It might depend on the actual values of `item_type_value` and `order_type_value`. Please give a full example including the reported errors. — justfortherec, Jul 14 '20 at 21:33
@justfortherec as you can see my above attempt is not efficient, I mean it is not parameterized. I gave SQL example, how would you parameterize my above example SQL queries to be parametric one? Any idea? — Hamilton, Jul 14 '20 at 21:38
Your example under "another attempt" is parameterized. What does not work with that approach? — justfortherec, Jul 14 '20 at 21:39
@justfortherec yes it is partially parameterized, but I don't know how do I make this less painful in terms of use if we have a bunch of similar queries. Any better idea or functional programming approach? Thanks — Hamilton, Jul 14 '20 at 21:43
I am not sure if I understand your goal. Do you want one function that you can use to parameterize several different queries? Isn't that exactly what `cursor.execute()` does? If you want to pass named arguments to `cursor.execute()`, you can use the `%(name)s` syntax and pass in a dict. See https://www.psycopg.org/docs/usage.html#query-parameters for more details. — justfortherec, Jul 14 '20 at 21:48
@justfortherec Hi, I am just curious can we parameterize this line: `SELECT date_trunc('week', date_received) from mytable`, because I want to use the record of either by `week`, `month`, `year`? Is there any way of doing this? any possible thoughts? Thanks — Hamilton, Jul 17 '20 at 16:22
This sounds like a separate question. Please ask a new question. — justfortherec, Jul 17 '20 at 19:17

score 3 · Accepted Answer · answered Jul 14 '20 at 22:12

If you want to pass named arguments to cursor.execute(), you can use the %(name)s syntax and pass in a dict. See the documentation for more details.

Here is an example using your query:

import datetime
import psycopg2

EXAMPLE_QUERY = """
SELECT
    date_trunc('week', date_received) AS received_week,
    cl_val,
    ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price_1,
    ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS mg_price_1,
    ROUND(ROUND(SUM(quant_received * mk_price_variance)::numeric,4) / SUM(quant_received),4) AS fb_mk_price_var,
    ROUND(ROUND(SUM(quant_received * freight)::numeric,4) / SUM(quant_received),4) AS freight_new,
    ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS grd_delv_cost_new,
    TO_CHAR(SUM(quant_received), '999G999G990D') AS Volume_Received
FROM mytable
WHERE date_received >= %(min_date_received)s
    AND date_received <= %(max_date_received)s
    AND item_type = %(item_type)s
    AND cl_val IN %(cl_vals)s
    AND order_type IN %(order_types)s
    AND pk_name IN %(pk_names)s
    AND pk_est NOT IN %(pk_ests)s
GROUP BY received_week ,cl_val
ORDER BY received_week ASC, cl_val ASC;
"""


def execute_example_query(cursor, **kwargs):
    """Execute the example query with given parameters."""
    cursor.execute(EXAMPLE_QUERY, kwargs)
    return cursor.fetchall()
            

if __name__ == '__main__':
    connection = psycopg2.connect(database="myDB", user="postgres", password="passw", host="localhost", port=5432)
    cursor = connection.cursor()
    execute_example_query(
        cursor,
        min_date_received = datetime.date(2010, 10, 1),
        max_date_received = datetime.date(2012, 12, 31),
        item_type = 'processed',
        cl_vals = ('12.5', '6.5', '8.1', '8.5', '9.0'),
        order_types = ('formula',),
        pk_names = ('target', 'costco', 'AFG', 'KFC'),
        pk_ests = ('12',)
    )

@ustfortherec I liked your answer :), good work! . Do you think can we use `**kwargs` in `EXAMPLE_QUERY`, so we could have SQL by passing arbitrary parameters, I mean make your attempt even more flexible and dynamic way. I am curious how this going to happen here. — kim, Jul 14 '20 at 22:50
This is possible (@adrian-klaver's answer gives you the right tools) but hard to do generally. Consider your example query: Some of the conditions are negated, others are not. I suggest you carefully read documentation linked by Adrian (https://www.psycopg.org/docs/sql.html) and by me (https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries). That gives you all the tools you need. — justfortherec, Jul 15 '20 at 06:55

Adrian Klaver · Answer 2 · 2020-07-14T22:38:58.630

Take a look at:

https://www.psycopg.org/docs/sql.html

"The module contains objects and functions useful to generate SQL dynamically, in a convenient and safe way. SQL identifiers (e.g. names of tables and fields) cannot be passed to the execute() method like query arguments:"

There are multiple examples there. If they do not show what you want to do then amend your question to show a specific example of how you want to change a query.

Here is an example that I use in code:

insert_list_sql = sql.SQL("""INSERT INTO
        notification_list ({}) VALUES ({}) RETURNING list_id
    """).format(sql.SQL(", ").join(map(sql.Identifier, list_flds)),
                sql.SQL(", ").join(map(sql.Placeholder, list_flds)))

list_flds is a list of fields that I fetch from an attrs dataclass and that may change if I modify the class. In this case I don't modify the table name, but there is nothing stopping you adding replacing the table name with a {} and then supply a table name in the format as another sql.SQL(). Just wrap the above in a function that accepts the arguments you want to make dynamic.

Do you mind provide possible demo code by using my above example query in your solution? Thanks for letting me know your insightful answer. — Hamilton, Jul 14 '20 at 22:45
I could if I knew what you want parametized. You said "there is a lot of hardcoded parts that need to be parameterized" but you have not indicated what those parts are. So please amend your question with an example query with the parts you want as parameters spelled out. Say using tags like , and so on. — Adrian Klaver, Jul 15 '20 at 01:24

any way to make parameterized queries and encapsulate it python in function

another attempt:

2 Answers2

Linked