Subject: Re: Minor issue
To: Rory Campbell-Lange <rory@campbell-lange.net>
Cc: psycopg@postgresql.org
References: <92a25cd6-5b50-3074-0da6-7394db9e0f0a@chagford.com>
 <20200526114541.GA21741@campbell-lange.net>
From: Frank Millman <frank@chagford.com>
Message-ID: <372a461a-6b6d-b499-db42-dfd045b76720@chagford.com>
Date: Tue, 26 May 2020 14:00:56 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:68.0) Gecko/20100101
 Thunderbird/68.8.1
MIME-Version: 1.0
In-Reply-To: <20200526114541.GA21741@campbell-lange.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


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