Any Way To Make Parameterized Queries And Encapsulate It Python In Function

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

Solution 1:

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;
"""defexecute_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',)
    )

Solution 2:

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.