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.

can you show me what ur sql file look line and what you have to pass to that file? — Hackaholic
– Hackaholic, Commented Feb 5, 2016 at 5:47

Matheus Torquato · Accepted Answer · 2024-01-26 08:41:08Z

7

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;

edited Jan 26, 2024 at 8:41

answered Dec 20, 2022 at 17:04

Matheus Torquato

1,66924 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Sheard Over a year ago

Nice. I've never heard of the Template class before so thanks for this smart solution.

Vzzarr · Accepted Answer · 2024-01-18 13:50:46Z

5

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')

edited Jan 18, 2024 at 13:50

Vzzarr

5,8924 gold badges61 silver badges103 bronze badges

answered Feb 5, 2016 at 5:53

Hackaholic

19.8k6 gold badges59 silver badges77 bronze badges

3 Comments

Hdk Over a year ago

BUt my sql file is in path:.. Can i give like this: query=job.get_sql_from_file("$DIR/file_path/open('file_name.sql').read().replace('xxxxxx',table)")

CathyQian Over a year ago

How about passing datetime parameters instead of table names? If use .replace('xxxxx', param), how can I replace many parameters?

Vzzarr Over a year ago

I find the format method of string more flexible for combining for example env name in the schema name. Edited to highlight the 2 alternatives in the answer as I missed format

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related