SQL in Python - using global variables

Question

I'm using pymysql package and need to write a query that will get 3 numeric variables that represent the specific date as: year month and day - and use them.

This will look like this:

INSERT INTO tbl1 

SELECT id, user_id, search_term
FROM main_table 
WHERE year = (var1) AND month = (var2) AND day = (var3);

INSERT INTO tbl2 

SELECT id,user_id, find_term
FROM main_table 
WHERE year = (var1) AND month = (var2) AND day = (var3);

I need that the 2 insert clauses will use the same var1, var2, var3 that i will define before executing the query.

The thing is - in order to be effcient - i need to do it in the same run. I tried to use (%s) but the problem is the python asks me for 6 arguments and not only 3...

Anyone know what could be helpful here? Thank you!!!

Please update your question with the code and properly formatted as code. — syntonym, Jun 07 '16 at 15:24
You should use cursor.execute instead of doing manual formatting. See [pep249](https://www.python.org/dev/peps/pep-0249/#id15) for information. — syntonym, Jun 07 '16 at 15:28
This same issue was discussed here: https://stackoverflow.com/questions/22372359/how-to-declare-global-variable-in-sql-server — Vera, Mar 13 '18 at 19:56

score 2 · Answer 1 · answered Jun 07 '16 at 15:24

2

You can use python's string interpolation:

v1 = 2016
v2 = 6
v3 = 17

query = """INSERT INTO tbl1 

SELECT id, user_id, search_term
FROM main_table 
WHERE year = {var1} 
AND month = {var2} 
AND day = {var3};

INSERT INTO tbl2 

SELECT id,user_id, find_term
FROM main_table 
WHERE year = {var1} 
AND month = {var2}
AND day = {var3};""".format(var1=v1, var2=v2,var3=v3)

print(query)

answered Jun 07 '16 at 15:24

ryanmc

1,821
12
14

Instead of formatting sql query manually one should use [cursor.execute](https://www.python.org/dev/peps/pep-0249/#id15). That way you are save from SQLinjections (even if in this case it doesn't matter) and there are no real downsides. – syntonym Jun 07 '16 at 15:31

score 0 · Answer 2 · answered Jun 07 '16 at 15:32

First of all - i want to say that it works fine with 1 query. the problems begins when i have 2 (or more) queries and python think its too much variables.

This is the code.

This part is for getting the 3 variables:

yesterday = datetime.date.fromordinal(datetime.date.today().toordinal()-1) year_var = yesterday.year month_var = yesterday.month day_var = yesterday.day

This part is executing the query with 3 given paramaters:

query = INSERT INTO tbl1 SELECT id, user_id, search_term FROM main_table WHERE year = %s AND month = %s AND day = %s ; INSERT INTO tbl2 SELECT id,user_id, find_term FROM main_table WHERE year = %s AND month = %s AND day = %s ;

cursor.execute(query.value,(year_var, month_var, day_var ))

When i try to run the script with the 2 insert clauses - it says i have to many variables.

I need to define some kind of global variable instead of using the %s thing...

Wdya think might?

SQL in Python - using global variables

2 Answers2