Thread: Minor issue
Hi all
This is very minor, but I thought I would mention it.
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.
pyodbc and sqlite3 both work this way, but psycopg2 raises the exception
'tuple index out of range'.
I can live with it, but it means that I have to adjust the parameter
tuple differently depending on which database I am testing with.
If it can be fixed, that would be nice. If it can't, no problem.
Frank Millman
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?
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
No, we don't want to add any intelligence in trying to figure out what
is into a query. If you are comfortable that you will be using always
the same pattern for comments you can easily clean the string yourself
before passing it to psycopg.
A better approach for you I guess would be to use named placeholders,
so that an a missing placeholder wouldn't require you to change the
arguments to execute.
-- Daniele
On Tue, 26 May 2020 at 23:43, Frank Millman <frank@chagford.com> wrote:
>
> Hi all
>
> This is very minor, but I thought I would mention it.
>
> 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.
>
> pyodbc and sqlite3 both work this way, but psycopg2 raises the exception
> 'tuple index out of range'.
>
> I can live with it, but it means that I have to adjust the parameter
> tuple differently depending on which database I am testing with.
>
> If it can be fixed, that would be nice. If it can't, no problem.
>
> Frank Millman
>
>
Ok, thanks.
Frank
On 2020-05-26 2:11 PM, Daniele Varrazzo wrote:
> No, we don't want to add any intelligence in trying to figure out what
> is into a query. If you are comfortable that you will be using always
> the same pattern for comments you can easily clean the string yourself
> before passing it to psycopg.
>
> A better approach for you I guess would be to use named placeholders,
> so that an a missing placeholder wouldn't require you to change the
> arguments to execute.
>
> -- Daniele
>
> On Tue, 26 May 2020 at 23:43, Frank Millman <frank@chagford.com> wrote:
>>
>> Hi all
>>
>> This is very minor, but I thought I would mention it.
>>
>> 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.
>>
>> pyodbc and sqlite3 both work this way, but psycopg2 raises the exception
>> 'tuple index out of range'.
>>
>> I can live with it, but it means that I have to adjust the parameter
>> tuple differently depending on which database I am testing with.
>>
>> If it can be fixed, that would be nice. If it can't, no problem.
>>
>> Frank Millman
>>
>>
On 26/05/20, Frank Millman (frank@chagford.com) wrote:
> On 2020-05-26 2:11 PM, Daniele Varrazzo wrote:
> > No, we don't want to add any intelligence in trying to figure out what
> > is into a query. If you are comfortable that you will be using always
> > the same pattern for comments you can easily clean the string yourself
> > before passing it to psycopg.
> >
> > A better approach for you I guess would be to use named placeholders,
> > so that an a missing placeholder wouldn't require you to change the
> > arguments to execute.
> Ok, thanks.
>
> Frank
I must be missing something, because this works for me:
In [28]: d.query("""
...: select
...: 1 as a
...: /*
...: ,2 as b
...: */
...: -- ,'hi' as c
...: ,%s as d
...: """, ("a string", )).results
Out[28]: [Record(a=1, d='a string')]
(d.query is a wrapper around cursor_create, execute and fetchall).
Does using %s instead of {}.format help solve the issue?
On 2020-05-26 3:08 PM, Rory Campbell-Lange wrote:
> On 26/05/20, Frank Millman (frank@chagford.com) wrote:
>> On 2020-05-26 2:11 PM, Daniele Varrazzo wrote:
>>> No, we don't want to add any intelligence in trying to figure out what
>>> is into a query. If you are comfortable that you will be using always
>>> the same pattern for comments you can easily clean the string yourself
>>> before passing it to psycopg.
>>>
>>> A better approach for you I guess would be to use named placeholders,
>>> so that an a missing placeholder wouldn't require you to change the
>>> arguments to execute.
>
>> Ok, thanks.
>>
>> Frank
>
> I must be missing something, because this works for me:
>
> In [28]: d.query("""
> ...: select
> ...: 1 as a
> ...: /*
> ...: ,2 as b
> ...: */
> ...: -- ,'hi' as c
> ...: ,%s as d
> ...: """, ("a string", )).results
> Out[28]: [Record(a=1, d='a string')]
>
> (d.query is a wrapper around cursor_create, execute and fetchall).
>
> Does using %s instead of {}.format help solve the issue?
>
To reproduce my situation, you should place the '--' at the beginning of
the following line (',%s as d').
As no parameters are now being substituted, I would expect to supply an
empty tuple. In fact, the parameter is still required.
Frank
On 26/05/20, Frank Millman (frank@chagford.com) wrote:
>
>
> On 2020-05-26 3:08 PM, Rory Campbell-Lange wrote:
> > On 26/05/20, Frank Millman (frank@chagford.com) wrote:
> > Does using %s instead of {}.format help solve the issue?
>
> To reproduce my situation, you should place the '--' at the beginning of the
> following line (',%s as d').
>
> As no parameters are now being substituted, I would expect to supply an
> empty tuple. In fact, the parameter is still required.
That makes sense, apologies.
However, this works as you suggest:
In [33]: d.query("""
...: select
...: 1 as a
...: /*
...: ,2 as b
...: */
...: -- ,'hi' as c
...: -- ,%s as d
...: """, ("a string", )).results
Out[33]: [Record(a=1)]
This doesn't work (as you suggest):
In [35]: d.query("""
...: select
...: 1 as a
...: /*
...: ,2 as b
...: */
...: -- ,'hi' as c
...: -- ,%s as d
...: """, ()).results
ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 0))
But this works:
In [34]: d.query("""
...: select
...: 1 as a
...: /*
...: ,2 as b
...: */
...: -- ,'hi' as c
...: -- ,%s as d
...: """).results
Out[34]: [Record(a=1)]
So perhaps simply don't provide the tuple in this case? This will also probably
work for your other backends.
Rory