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

Question

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?

Is this using the [Python API](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html)? — gen_Eric, Jun 09 '16 at 14:20
What is the *value* of `string`? What elements are in `array`? — gen_Eric, Jun 09 '16 at 14:20
Can you give an example. Currently it is not very clear to me what you want... — Willem Van Onsem, Jun 09 '16 at 14:23
In this particular case, string looks like "INSERT INTO tablename(col_one, col_two, col_three) VALUES (%s,%s,%s)" and array looks like ["text", 123, datetime.time(12,0)]. That said, I'm looking for more of a general case solution. — Everyone_Else, Jun 09 '16 at 14:23
Willem, I'm looking to learn how the elements of `array` are combined with `%s` to generate the actual text of the query. — Everyone_Else, Jun 09 '16 at 14:24
So, using another terminology, `string` is the parametrized SQL query and `array` are the params? — Álvaro González, Jun 09 '16 at 14:25
I know nothing about Python but that's not how parametrized queries normally work. Parameter values are sent to MySQL separately, they are not injected into the SQL string. For that reason, parameter values can never cause a syntax error (that's the whole point). — Álvaro González, Jun 09 '16 at 14:29

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

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

edited May 23 '17 at 11:52

Community

1
1

answered Jun 09 '16 at 14:32

Willem Van Onsem

443,496
30
428
555

How does the claim that 'when you use %s, it will replace that value with the one in the params list' reconcile with Álvaro's comment that parameter values are not injected into the SQL string? – Everyone_Else Jun 09 '16 at 14:39
@Someone_Else: It will escape the sequence to prevent SQL injection. That's why it behaves differently than a simple `.format` call. – Willem Van Onsem Jun 09 '16 at 14:41
So it's like a .format call, but it automatically escapes the characters it needs to? – Everyone_Else Jun 09 '16 at 14:43
1

@Everyone_Else As I said, I'm not a Python coder. Actual implementations for parametrized queries can use different approaches, from real server-side bind parameters to client-side emulations. – Álvaro González Jun 13 '16 at 08:25

score 2 · Answer 2 · answered Jun 09 '16 at 14:45

Your string is actually your parameterized query, where you should pass your elements to match your %s.

You can get examples in the mySql documentation

Note in there that the parameters are not in an array but in a tuple.

Your example becomes :

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

I also changed your " to 'as I doubt it liked it too much.

I'm also never sure of the date format, try without a date if you still have trouble (then fix the date format if needed).

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

2 Answers2