title: any way to make parameterized queries and encapsulate it python in function
body: <p>I want to implement a python function which is going to execute <code>SQL</code> query with parameters. To do so, I started to use <code>psycopg2</code> for accessing my local db. However, I have written a bunch of very similar <code>SQL</code> queries whereas each <code>SQL</code> 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 <code>SO</code> post and got some idea but couldn't accomplish compact python function which could execute <code>SQL</code> statements with arbitrary parameters. I know how to write up python function with passing arbitrary parameters by using <code>**kwargs</code>, <code>*args</code>, 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?</p>
  <p><strong>my db schema</strong>:</p>
  <p>here is my table schema in postgresql:</p>
  <pre><code>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,
  );
  </code></pre>
  <p><strong>my example SQL query</strong></p>
  <p>here is one of the example SQL queries that needs to be parameterized:</p>
  <pre><code>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 &gt;= to_date('2010-10-01','YYYY-MM-DD')
      AND date_received &lt;= 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;
  </code></pre>
  <p><strong>my current attempt</strong>:</p>
  <pre><code>import psycopg2
  
  connection = psycopg2.connect(database=&quot;myDB&quot;, user=&quot;postgres&quot;, password=&quot;passw&quot;, host=&quot;localhost&quot;, port=5432)
  cursor = connection.cursor()
  cursor.execute(
      &quot;&quot;&quot;
      select * from mytable where date_received &lt; any(array['2019-01-01'::timestamp, '2020-07-10'::timestamp])
      &quot;&quot;&quot;)
  record = cursor.fetchmany()
  </code></pre>
  <h2>another attempt:</h2>
  <pre><code>cursor.execute(&quot;&quot;&quot;
      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;
  &quot;&quot;&quot;, (item_type_value, order_type_value))
  
  results = [r[0] for r in cursor.fetchall()]
  </code></pre>
  <p>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</p>
  <p><strong>goal</strong></p>
  <p>I am hoping to implement function like this:</p>
  <pre><code>def parameterized_sql(**kwargs, *args):
      connection = psycopg2.connect(database=&quot;myDB&quot;, user=&quot;postgres&quot;, password=&quot;passw&quot;, host=&quot;localhost&quot;, port=5432)
      cursor = connection.cursor()
      cursor.execute(&quot;&quot;&quot;SQL statement with parameter&quot;&quot;&quot;)
      ## maybe more
  </code></pre>
  <p>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</p>
  <p><strong>update</strong>:</p>
  <p>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.</p>