Someone_Else Someone_Else - 5 months ago 8
SQL Question

What do SQL queries Parameterized with '%s' look like?

Consider a particular SQL query in the form

cursor.execute(string, array)


Where string is some string containing
'%s'
and array is some array satisfying
len(array) == string.count("%s")
, not necessarily containing only strings.

For example:

cursor.execute("INSERT INTO tablename(col_one, col_two, col_three) VALUES (%s,%s,%s)",("text", 123, datetime.time(12,0)))


When I run this, I get an unhelpful error message about 'You have an error in your SQL syntax...' and then a partial text of the query. However, to debug this, I want to know the full text of the query.

When the query
cursor.execute(string, array)
is run, what is the actual text of the query the the cursor executes?

Answer

As you can read here:

Syntax:

cursor.execute(operation, params=None, multi=False)

iterator = cursor.execute(operation, params=None, multi=True)

This method executes the given database operation (query or command). The parameters found in the tuple or dictionary params are bound to the variables in the operation. Specify variables using %s or %(name)s parameter style (that is, using format or pyformat style). execute() returns an iterator if multi is True.

So when you use %s, it will replace that value with the one in the params list.


In case you want to debug your statement, you can print the last executed query with: cursor._last_executed:

try:
    cursor.execute(sql, (arg1, arg2))
    connection.commit()
except:
    print("Error: "+cursor._last_executed)
    raise
finally :
    print(cursor._last_executed)

source

Comments