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)"
It should be noted that MySQL connections are not thread-safe, so you cannot directly call ping from an another thread.
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.
disconnect() Method. This method tries to send a QUIT command and close the socket. It raises no exceptions.
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")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With