python replace placeholder values in a string

Question

I have below requirement in python,where I need to pass date and id at run time.

query_str="select * from test_table where data_date = '${data_date}' and id = ${id}"
Where data_date is string column and id is big int column. 

If i'm passing data_date as 2015年01月01日 and id as 1, it should replace the parameters as below.

Output : select * from test_table where data_date = '2015-01-01' and id = 1

• Morgan Thrapp: This needs a minimal reproducible example.

Answer 1

What you are actually asking about is called query parameterization and it has to be done right relying on what your database driver provides. This way you let your driver handle the proper escaping and python-to-database type conversions.

Depending on the driver, the placeholder style can differ, but %s is the most common:

query_str = """
 select * 
 from test_table 
 where data_date = %s and id = %s
"""
params = ('2015-01-01', 1)
cursor.execute(query_str, params)

where cursor is your database connection cursor instance.

• Satish Kumar Reddy: This query parameterization is for pyspark SQL execution. I couldn't find any database driver for this purpose. That's the reason I'm looking for parameterization using python functions and execute the parameterized query.