import pandas as pd
q = """
select *
from tbl
where metric = %(my_metric)s
;
"""
params = {'my_metric':'sales'}
pd.read_sql(q, mysql_conn, params=params)
Im using pandas read_sql function to safely pass arguments to my query string. I would like to return the final query string with the arguments replaced as well as the results. So for example, return the string:
select *
from tbl
where metric = 'sales'
;
Any way to do this?
OK, in that case try this:
import pandas as pd
q = """
select *
from tbl
where metric = %s
;
"""
params = {'my_metric': 'sales'}
df = pd.read_sql(q, mysql_conn, params=[params['my_metric']])
query_string = q % params['my_metric']
If you are using psycopg2, then you could try using your mysql_conn instance to create a cursor, then mogrify q.
This worked for me:
import pandas as pd
q = """
select *
from tbl
where metric = %(my_metric)s
;
"""
params = {'my_metric':'sales'}
cur = mysql_conn.cursor()
print cur.mogrify(q, params)
pd.read_sql(q, mysql_conn, params=params)
I don't think read_sql has that option but you can reconstitute what the query would look like with regular expressions.
import re
actual_q = re.sub('%\(.*\)s',params[re.findall('%\(.*\)s',q)[0].strip('%(').strip(')s')],q)
print(actual_q)
select *
from tbl
where metric = sales
;
This only works with one parameter per query. If you have more you could create a list of params found using re.findall as above and iterate over them to replace
q_params = re.findall('%\(.*\)s',q)
for param in q_params:
actual_param = param.strip('%(').strip(')s')
actual_q = re.sub('%\({}\)s'.format(actual_param), params[actual_param],q)
print(actual_q)
select *
from tbl
where metric = sales
;
To extend and fix some issues in @tvashtar 's answer
actual_q = query_string
for param in q_params:
actual_param = param.strip('%(').strip(')s')
# 'param1'
form_param = '%\({}\)s'.format(actual_param)
# '%s\\(param1\\)s'
value = str(params[actual_param]) if params[actual_param] else "\'\'"
# '' if no value else the str of param1
actual_q = re.sub(form_param, value, actual_q)
print(actual_q)
After processing through the list of params, actual_q will have the value replaced query string
