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?

Does this answer your question? [Parameterized queries with psycopg2 / Python DB-API and PostgreSQL](https://stackoverflow.com/questions/1466741/parameterized-queries-with-psycopg2-python-db-api-and-postgresql) — Brian, Jul 21 '20 at 17:29
@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, Jul 21 '20 at 17:31
@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 — Hamilton, Jul 21 '20 at 17:33
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 https://pypi.org/project/anosql/ to manage SQL outside of my actual code if mixing SQL and python is your concern. — Mike Organek, Jul 21 '20 at 17:35
@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 — Hamilton, Jul 21 '20 at 17:39
@MikeOrganek what is a good idea? I am not saying I going to strictly follow what I said above. I am open to any feasible solution. — Hamilton, Jul 21 '20 at 18:05

Adrian Klaver · Accepted Answer · 2020-07-21T19:48:06.217

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

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 — Hamilton, Jul 21 '20 at 19:23
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. — Adrian Klaver, Jul 21 '20 at 19:53
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 !!! — Hamilton, Jul 21 '20 at 21:34
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``` — Adrian Klaver, Jul 21 '20 at 21:52
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!!! — Hamilton, Jul 21 '20 at 21:55
I can't because: 1) I don't know what conditions lead to the various query forms. 2) You experimenting will teach you more then I can. — Adrian Klaver, Jul 21 '20 at 22:01
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/218317/discussion-between-hamilton-and-adrian-klaver). — Hamilton, Jul 21 '20 at 22:04

make parameterized SQL select statement with psycopg2 in python

1 Answers1