make parameterized SQL select statement with psycopg2 in python

Question

I learned from psycopg2 API how to pass the parameter to SQL queries so we could easily manipulate SQL statements with the parametric way. So, a stringify parameter with %(param)s would do this. I am wondering if we could encapsulate one common parameterized SQL statement in a python function, so we could call python function by giving any arbitrary parameter values which will be consumed by SQL statement, ultimately it would server as running as many SQL statements as possible. However, I couldn't figure out how do we make parameterized SQL select statement, because the items we want to retrieve from local DB can be variant every time, so it could be nice if select statement can be parameterized. How can we achieve this? Is there any way of doing this with psycopg2 in python? How to make this happen? Any possible thoughts?

db table:

here is the example db table for reproducible purposes:

CREATE TABLE trans_tbl(
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,
);

And, here is the list of example queries where I need one parameterized SQL query statement (select, where clause should be parameterized):

example query 1

SELECT
 date_trunc('week', date_received::date) AS received_week,
 cl_val,
 item_type,
 ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS price_1,
 ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS dv_price,
FROM trans_tbl
GROUP BY received_week,cl_val,item_type
ORDER BY received_week;

example query 2:

SELECT
 date_trunc('month', date_received) AS received_month,
 ROUND(ROUND(SUM(quant_received * standard_price)::numeric,4) / SUM(quant_received),4) AS mk_price,
 ROUND(ROUND(SUM(quant_received * mg_fb_price)::numeric,4) / SUM(quant_received),4) AS price,
 ROUND(ROUND(SUM(quant_received * mk_price_variance)::numeric,4) / SUM(quant_received),4) AS fob_market_price_variance,
 ROUND(ROUND(SUM(quant_received * grd_delv_cost)::numeric,4) / SUM(quant_received),4) AS dv_cost,
 ROUND(ROUND(SUM(quant_received * freight)::numeric,4) / SUM(quant_received),4) AS weight_avg,
FROM trans_tbl

example query 3:

SELECT
 date_trunc('week', date_received::date) AS received_week,
 grd_name,
 pk_name,
 pk_est,
 TO_CHAR(SUM(quant_received), '999G999G990D') AS received_amt
FROM trans_tbl

what I want to do this I want to have one common parameterized SQL statement so I could run SQL statements by arbitrarily passing parameter values so it could be as same as running above three SQL statements separately. Is there any way of accomplish this with psycopg2 in python? Is that doable to make this happen? Any idea?

update:

perhaps my attempt is not quite feasible, so I am open to possible doable, feasible approach to lease the pain at least. If what I want to achieve is not quite doable, what's efficient approach I could do about it? Any idea?

• Brian: Does this answer your question? Parameterized queries with psycopg2 / Python DB-API and PostgreSQL
• Hamilton: @Brian I looked into this post as well, but my question is certainly different. I want to parameterize dynamic SQL select statements like what I said in my post. Any further thoughts? Thanks
• Hamilton: @Brian we could do parameterize where clause body, but not sure how to do for select clause. I am wondering you might have possible approach for that. Thanks
• Mike Organek: The short answer is, "no." By the time you create a custom DSL (domain-specific language) that can handle your use cases, you will end up with far more complexity than managing separate queries as strings. I often use pypi.org/project/anosql to manage SQL outside of my actual code if mixing SQL and python is your concern.
• Hamilton: @MikeOrganek I see. Could you elaborate on your point with a possible attempt we could try? What's the best possible things we could do? Any further thoughts? Thanks

Answer 1

First this is a duplicate of your previous question Previous. You should have just continued the discussion there. As I stated there it is possible to do what you using the sql module from psycopg2. As example from one of my applications:

class NotificationReport():
 """Builds a query for finding task notifications.
 Use form_choices passed in to modify the select query for task
 notifications using psycopg2.sql module. Filter on status which is some
 combination of notify_expired and notify_cancelled.
 """
 def __init__(self, form_choices):
 self.id = "notification_report"
 self.form_choices = form_choices
 def returnQuery(self):
 flds, defaults = data.fetchFields(data.TaskNotification)
 base_sql = sql.SQL("""SELECT
 task_title, {}
 FROM
 tasks
 JOIN
 task_priority AS tp
 ON
 tasks. task_priority_fk= tp.priority_id
 JOIN
 task_type AS tt
 ON
 tasks.task_type_fk = tt.task_type_id
 LEFT JOIN
 task_notification AS tn
 ON
 tasks.task_id = tn.task_id_fk
 """).format(sql.SQL(",").join(map(sql.Identifier, flds)))
 f_choices = self.form_choices
 and_sql = None
 ops_list = []
 if f_choices:
 for choice in f_choices:
 if choice.get("status"):
 status = choice["status"]
 status_dict = {"open": ("notify_expired = 'f' "),
 "expired": ("notify_expired = 't' "),
 }
 if status == "all":
 pass
 else:
 ops = sql.SQL(status_dict[status])
 ops_list.append(ops)
 if ops_list:
 and_sql = sql.Composed([base_sql, sql.SQL(" AND ")])
 additional_and = sql.SQL(" AND ").join(ops_list)
 ops_sql = sql.Composed([and_sql, additional_and])
 orderby_sql = sql.SQL("""ORDER BY
 task_title""")
 if and_sql:
 combined_sql = sql.Composed([ops_sql, orderby_sql])
 else:
 combined_sql = sql.Composed([base_sql, orderby_sql])
 return combined_sql

Output. First no parameters supplied to report:

SELECT
 task_title, "task_id_fk","before_value","before_interval","every_value","every_interval","notify_note","notify_id","notify_expired"
FROM
 tasks
JOIN
 task_priority AS tp
ON
 tasks. task_priority_fk= tp.priority_id
JOIN
 task_type AS tt
ON
 tasks.task_type_fk = tt.task_type_id
LEFT JOIN
 task_notification AS tn
ON
 tasks.task_id = tn.task_id_fk
ORDER BY
 task_title

Then with status:

SELECT
 task_title, "task_id_fk","before_value","before_interval","every_value","every_interval","notify_note","notify_id","notify_expired"
FROM
 tasks
JOIN
 task_priority AS tp
ON
 tasks. task_priority_fk= tp.priority_id
JOIN
 task_type AS tt
ON
 tasks.task_type_fk = tt.task_type_id
LEFT JOIN
 task_notification AS tn
ON
 tasks.task_id = tn.task_id_fk
 AND notify_expired = 'f' ORDER BY
 task_title

• Hamilton: how about we pass a parameter to select clause? In above attempt, why do we have "task_title","task_desc", is it parameter? No doubt your attempt is workable, but I am wondering how I am gonna apply this to my case. Your further instruction would be appreciated. Thanks
• user7070613: Corrected SQL output, I grabbed the wrong report to run. Yes the '' " fields are parameters, they fill in the '{}' in base_sql. At this point you need to go the psycopg2 link I sent, look at the examples there and the one I show above and start experimenting with your database and code. That is the only way you will really understand how all this works.
• Hamilton: I think this is better fit for my needs, query = sql.SQL("select {fields} from {table}").format(fields=sql.SQL(',').join([sql.Identifier('field1'),...,]),table=sql.Identifier('some_table')), but how do we handle where clause that needs to be parameterized? can we do sql.identifier(where=sql.SQL(',).join(cond1, cond2))? Any possible thoughts? thanks a lot !!!
• user7070613: No. Read the docs, they tell you what the various objects do. FYI, it is not sql.identifier it is sql.Identifier. Hint, that won't work. Look at Composed and SQL
• Hamilton: could you simplify your above example align with my above table and queries in the post? without knowing your data, I don't know how to use and adapt your answer for my experiments? Thank you!!!