How can i Pass variable into a sql file while running through python

Question

I have a Python wrapper which reads an SQL file and executes it. Example:

query=job.get_sql_from_file("$DIR/file_path/file_name.sql")
job.execute(query)
job.query_desc()

$DIR : is default path declared in bashrc file

FILE_NAME.sql : contains schema name and path to read the input_file to load table: I want that path to be dynamic and pass it as parameter. How can i do that?

What I am aware of is:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

...but it will not work in my case because my SQL statement is in a .sql file and I have to pass argument in that file ITSELF.

Answer 1

Given a SQL file

mysql_path.sql

SELECT * FROM users WHERE name = '$name' AND age = $age;

From python you can do:

from string import Template

sql_path = 'dir/mysql_path.sql'

with open(sql_path, 'r') as fp:
    sql = fp.read()

query = Template(sql).substitute(
    name = 'Charles Darwin',
    age = 73,
)

That will give you:

SELECT * FROM users WHERE name = 'Charles Darwin' AND age = 73;

Answer 2

What I can suggest is you can read the file and then replace.

Example: my test.sql file

select * from xxxxxx

now replace 'xxxxxx' with your table

table = 'my_table'
sql_statement = open('test.sql').read().replace('xxxxxx', table)

you can also use format:

Example: my test.sql file

select * from {}

now use format

sql_statement = open('test.sql').read().format('my_table')