Python SQL variable

Question

def copy_sql_db(storage_connection, mysql_connection, date):
    cursor = storage_connection.cursor()
    mysql_cursor = mysql_connection.cursor()

    if date == "ALL":
      cursor.execute("""select name, test_time, id from test_table""")
    else:
      cursor.execute("""select name, test_time, id from test_table where test_time > (CURDATE() - INTERVAL date day)""")
    ...

I do not know what to search for to get the variable date that I pass into my function, copy_sql_db into my else: cursor.execute...when I run the script created I get there is an error in the SQL syntax near where test_time > date.

@khelwood edited my mistake in the question – user20929302 Jun 05 '18 at 13:58 — user20929302, Jun 05 '18 at 13:58

score 0 · Accepted Answer · edited Jun 05 '18 at 14:17

0

You need to use a placeholder

try this:

    cursor.execute("""select name, test_time, id from test_table where test_time > (CURDATE() - INTERVAL {0} day)""".format(date))

or SAFE:

    cursor.execute("""select name, test_time, id from test_table where test_time > (CURDATE() - INTERVAL %s day)""", (date,))

edited Jun 05 '18 at 14:17

Ilja Everilä

50,538
7
126
127

answered Jun 05 '18 at 13:58

Dataichou

317
1
6

That worked perfectly, I will accept as answer when I can, thanks. – user20929302 Jun 05 '18 at 14:01
2

Please don't promote string formatting as a solution to passing variables to queries. Using the *DB-API driver's* placeholders is the safe solution. – Ilja Everilä Jun 05 '18 at 14:05
I have added a safer way in order to promote the best possible solution – Dataichou Jun 05 '18 at 14:10

Python SQL variable

1 Answers1