Parameter name is changed in bin_params using JinjaSql

Question

Imagine this is my query :

query = '''
        SELECT *
        FROM table
        WHERE id = {{myid}}'''
params = {'myid':3}

j= JinJaSql(param_style='pyformat'}

myquery, bind_params = j.prepare_query(query,params)

when I print bind_params I would get

{'myid_1':3}

why my parameter name was changed to myid_1 while I named it myid. Is there anything wrong with my code? How can I fix it?

Answer 1

According to the readme on the JinjaSql github page, if using the "pyformat" or "named" param style, the bound parameters returned by prepare_query are guaranteed to be unique. I suspect that this is why myid gets changed to myid_1.

When calling read_sql you should make sure to use the myquery and bind_params returned by prepare_query and not the query and params used in the prepare_query call, i.e.

# ...
# your code above
# ...
myquery, bind_params = j.prepare_query(query, params)
result = pd.read_sql(myquery, conn, params=bind_params)

Note that the format of your params for pd.read_sql depends on the SQL dialect you're using (docs). If you're using postgres for example, dictionary params are the way to go.

Hope this helps!