Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Escaping strings with python mysql.connector

Tags:

python

mysql

I am trying to insert a bunch of strings into 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 should I go about escaping my strings? I could try doing it in python but I know this isn't the right way.

Note: I realise mysql.connector is still in development.

update:

Line 4 should read:

sql = "INSERT INTO mytable (col1, col2) VALUES (%s, %s)"
like image 371
Mr_Chimp Avatar asked Sep 24 '11 17:09

Mr_Chimp


People also ask

Is python MySQL connector thread safe?

It should be noted that MySQL connections are not thread-safe, so you cannot directly call ping from an another thread.

How do I close MySQL connection in python?

Practical Data Science using Python To disconnect Database connection, use close() method. If the connection to a database is closed by the user with the close() method, any outstanding transactions are rolled back by the DB.

Which method is used to disconnect connection with MySQL?

disconnect() Method. This method tries to send a QUIT command and close the socket. It raises no exceptions.


1 Answers

The answer from infrared is the best approach.

But, if you really need to escape some 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 lowlevel C-API):

db = mysql.connector.connect(......)

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

Another option is to use mariadb python connector (pip install mariadb).

db = mariadb.connector(....)
new_str = db.escape_string("quote ' this")
like image 168
Luciano Barcaro Avatar answered Sep 21 '22 09:09

Luciano Barcaro