Re: Minor issue - Mailing list psycopg
From | Frank Millman |
---|---|
Subject | Re: Minor issue |
Date | |
Msg-id | 372a461a-6b6d-b499-db42-dfd045b76720@chagford.com Whole thread Raw |
In response to | Re: Minor issue (Rory Campbell-Lange <rory@campbell-lange.net>) |
List | psycopg |
On 2020-05-26 1:45 PM, Rory Campbell-Lange wrote: > On 26/05/20, Frank Millman (frank@chagford.com) wrote: >> I have a function that returns a complex SQL query and a tuple of >> parameters. The query is stored inside the function as a triple-quoted >> string, and the parameters are derived depending on the input arguments. >> >> Sometimes while testing I will comment out some of the SQL using '--'. If >> those lines happen to contain a parameter placeholder ('%s') I expected to >> remove the parameter from the tuple as well. > > Could you provide an example? > Sure. Here is my function - """ def get_sql(company, conn, bal_date): sql = (""" SELECT {1} AS "[DATE]" , c.gl_code , SUM(COALESCE(b.tran_tot, 0)) AS "[REAL2]" FROM ( -- SELECT ( -- SELECT c.row_id FROM {0}.gl_totals c -- WHERE c.tran_date <= {1} -- AND c.gl_code_id = d.row_id -- AND c.location_row_id = e.row_id -- AND c.function_row_id = f.row_id -- AND c.tran_type_id = g.row_id -- AND c.deleted_id = 0 -- ORDER BY c.tran_date DESC LIMIT 1 -- ) AS cl_row_id -- FROM {0}.gl_codes d, {0}.adm_locations e, {0}.adm_functions f, {0}.gl_tran_types g -- WHERE e.location_type != 'group' -- AND f.function_type != 'group' SELECT a.row_id FROM {0}.gl_codes b CROSS JOIN {0}.adm_locations c CROSS JOIN {0}.adm_functions d CROSS JOIN {0}.gl_tran_types e LEFT OUTER JOIN ( SELECT gl_code_id,location_row_id,function_row_id,tran_type_id,row_id,tran_date, ROW_NUMBER() OVER (PARTITION BY gl_code_id,location_row_id,function_row_id ORDER BY tran_date DESC) row_num FROM {0}.gl_totals WHERE deleted_id = 0 AND tran_date <= {1} ) a ON a.gl_code_id = b.row_id AND a.location_row_id = c.row_id AND a.function_row_id = d.row_id AND a.tran_type_id = e.row_id AND a.row_num = 1 ) AS a LEFT JOIN {0}.gl_totals b on b.row_id = a.row_id JOIN {0}.gl_codes c ON c.row_id = b.gl_code_id GROUP BY b.gl_code_id, c.gl_code HAVING b.gl_code_id IS NOT NULL ORDER BY b.gl_code_id """.format(company, conn.constants.param_style) ) params = (bal_date, bal_date) fmt = '{:%d-%m-%Y} : {:<12}{:>12}' return sql, params, fmt """ You will see 3 occurrences of '{1}'. This is replaced at runtime by the appropriate placeholder, namely '?' for pyodbc and sqlite3, '%s' for psycopg2. One of them is in a line that starts with '--'. The tuple of parameters has 2 items. It works with pyodbc and sqlite3. Using psyocpg2, it only works if I add a third item. Frank