escaping single quotes in python mysqldb

It seems like a typical issue in programming, but i didn't find nothing usefull in google. This is the code:

file=open('list.txt'开发者_如何学运维,'r')
    for line in file:
        cursor.execute("CREATE TABLE IF NOT EXISTS \
        %s(id INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT, \
        entry TEXT NOT NULL)" % line)
    file.close()
    cursor.close()
    db.close()

And this is the error when I try to create a table with single quotes:

Traceback (most recent call last):
  File "test.py", line 104, in <module>
    entry TEXT NOT NULL)" % line)
  File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.7/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Hi 'Buddy!(id INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT,         entry ' at line 1")

MySQLdb.escape_string(line) dont fix it by the way

afaik (see MySQL 5.0 Reference here) you aren't allowed to have single quotes in a table name:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended: U+0080 .. U+FFFF

I'm not sure why you're doing that and I think I should suggest re-thinking your schema, but you can escape a table name with backticks `.

# notice the `'s surrounding %s
for line in file:
    cursor.execute("CREATE TABLE IF NOT EXISTS \
    `%s`(id INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT, \
    entry TEXT NOT NULL)" % line)

Try this -

cursor.execute("CREATE TABLE IF NOT EXISTS '%s'(id INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT, entry TEXT NOT NULL)" % line)

Instead of using % formatting to put in the value, pass in your statement to cursor.execute() with %s still in it for each value you need to replace, and a list or tuple of all replacement values as your second argument, for example:

stmt = "CREATE TABLE IF NOT EXISTS %s(id INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT, entry TEXT NOT NULL)"
value_list = [line]
cursor.execute(stmt, value_list)

This will perform all of the necessary escaping for you because it will turn your string into a valid SQL literal. This comes straight from the MySQLdb User's Guide, here is the relevant example:

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".

Generally, if you're trying to make table names programmatically like this, you'll be happier using a different schema. I would suggest making one table like this, with an additional column that will contain the string you are now using for the table name.