On 2020-05-26 1:45 PM, Rory Campbell-Lange wrote:
On 26/05/20, Frank Millman (fr...@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
