String exception with python mysql.connector

Question

I am trying to insert a bunch of rows in mysql using python and mysql.connector . My current code looks something like this:

db = mysql.connector.Connect('config blah blah') cursor = db.cursor() data = (somestring1, somestring2) sql = "INSERT INTO mytable (col1, col2) VALUES ('%s', '%s')" cursor.execute(sql, data) 

How do I avoid string escaping? I could try to do this in python, but I know this is the wrong way.

Note. I understand that mysql.connector is still under development.

update:

Line 4 should read:

 sql = "INSERT INTO mytable (col1, col2) VALUES (%s, %s)" 

Answer 1

Since mysql.connector API API v2.0 is compatible, you do not need to delete the data yourself, it does it automatically for you.

Answer 2

The answer from infrared is the best approach.

But, if you really need to escape an arbitrary string, you can do this (before 2.1.6):

 db = mysql.connector.connect(......) new_str = db.converter.escape('string to be escaped') 

Newer versions (use low-level C-API):

 db = mysql.connector.connect(......) new_str = db._cmysql.escape_string('string to be escaped') 

Answer 3

Indeed, the best approach is to allow the module to shield the values ​​on its own. If you absolutely need to do this manually (for example, I want to print SQL only in script debugging mode, and mysql.connector does not seem to implement mogrify() ), there is another option:

 >>>> import mysql.connector >>>> cnx = mysql.connector.connect() >>>> cur = cnx.cursor() >>>> cur._connection.converter.escape("tic ' toc") "tic \\' toc" 

Admittedly, it still uses the "non-public API", but at least it does not contradict the latest versions (for now; tested on 2.0.4, 2.1.3, 2.2.9, 8.0.16).