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
– syntonym, Commented Jun 7, 2016 at 15:24
You should use cursor.execute instead of doing manual formatting. See pep249 for information. — syntonym
– syntonym, Commented Jun 7, 2016 at 15:28
This same issue was discussed here: stackoverflow.com/questions/22372359/… — Vera
– Vera, Commented Mar 13, 2018 at 19:56

ryanmc · Accepted Answer · 2016-06-07 15:24:02Z

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 7, 2016 at 15:24

ryanmc

1,88113 silver badges14 bronze badges

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

1 Comment

syntonym Over a year ago

Instead of formatting sql query manually one should use cursor.execute. That way you are save from SQLinjections (even if in this case it doesn't matter) and there are no real downsides.

shayms8 · Accepted Answer · 2016-06-07 15:32:23Z

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?

Collectives™ on Stack Overflow

SQL in Python - using global variables

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related