Escape string Python for MySQL

Question

I use Python and MySQLdb to load web pages and store them in a database. The problem is that I cannot save complex rows in the database because they were not escaped.

Is there a function in Python that I can use to avoid a string for MySQL? I tried with ''' (triple simple quotes) and """ , but that didn't work. I know that PHP has mysql_escape_string() , something similar in Python?

Thank.

Answer 1

 conn.escape_string() 

See the MySQL C API Feature Mapping: http://mysql-python.sourceforge.net/MySQLdb.html

Answer 2

The MySQLdb library will actually do this for you if you use their implementations to build the SQL query string instead of trying to create your own.

Do not execute:

 sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)" % (val1, val2) cursor.execute(sql) 

do:

 sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)" cursor.execute(sql, (val1, val2)) 

Answer 3

 >>> import MySQLdb >>> example = r"""I don't like "special" chars ¯\_(ツ)_/¯""" >>> example 'I don\'t like "special" chars \xc2\xaf\\_(\xe3\x83\x84)_/\xc2\xaf' >>> MySQLdb.escape_string(example) 'I don\\\'t like \\"special\\" chars \xc2\xaf\\\\_(\xe3\x83\x84)_/\xc2\xaf' 

Answer 4

Use the sqlalchemy text function to remove the interpretation of special characters:

Note the use of the text("your_insert_statement") function text("your_insert_statement") below. What he does tells sqlalchemy that all question marks and percent signs in the passed string should be treated as literals.

 import sqlalchemy from sqlalchemy import text from sqlalchemy.orm import sessionmaker from datetime import datetime import re engine = sqlalchemy.create_engine("mysql+mysqlconnector://%s:%s@%s/%s" % ("your_username", "your_password", "your_hostname_mysql_server:3306", "your_database"), pool_size=3, pool_recycle=3600) conn = engine.connect() myfile = open('access2.log', 'r') lines = myfile.readlines() penguins = [] for line in lines: elements = re.split('\s+', line) print "item: " + elements[0] linedate = datetime.fromtimestamp(float(elements[0])) mydate = linedate.strftime("%Y-%m-%d %H:%M:%S.%f") penguins.append(text( "insert into your_table (foobar) values('%%%????')")) for penguin in penguins: print penguin conn.execute(penguin) conn.close()