Subject: Minor issue

From: Frank Millman <[email-masked]>
Date: Tue, 26 May 2020 13:42:56 +0200

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

---

From: Rory Campbell-Lange <[email-masked]>
Date: Tue, 26 May 2020 12:45:41 +0100

On 26/05/20, Frank Millman ([email-masked]) wrote:
> 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?

---

From: Frank Millman <[email-masked]>
Date: Tue, 26 May 2020 14:00:56 +0200

> 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

---

From: Daniele Varrazzo <[email-masked]>
Date: Wed, 27 May 2020 00:11:34 +1200

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

---

From: Frank Millman <[email-masked]>
Date: Tue, 26 May 2020 14:15:29 +0200

Ok, thanks.

Frank

---

From: Rory Campbell-Lange <[email-masked]>
Date: Tue, 26 May 2020 14:08:01 +0100

> 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?

---

From: Frank Millman <[email-masked]>
Date: Tue, 26 May 2020 15:16:03 +0200

> 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

---

From: Rory Campbell-Lange <[email-masked]>
Date: Tue, 26 May 2020 15:09:36 +0100

On 26/05/20, Frank Millman ([email-masked]) wrote:
> To reproduce my situation, you should place the '--' at the beginning of the
> following line (',%s as d').
> 

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
	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