python - 如何在 Python MySQLdb 中传递用于删除表执行的变量

Question

使用这段代码，我尝试删除一个表(如果它存在)。但我需要通过传递来做到这一点一个变量。

import MySQLdb as mdb
conn = mdb.connect(host='db01.myhost.co.nl,
                        user='pdbois',
                        passwd='triplex',
                        db='myxxx')
cursor = conn.cursor()

# Without passing variables this works OK!
#cursor.execute("""drop table if exists testtable""")

# But this break
table_name = "testtable"
cursor.execute("""drop table if exists %s""",(table_name))
conn.close()

但为什么我上面的方法会因出现此错误而中断？

  File "test_mysql.py", line 63, in <module>
    main()
  File "test_mysql.py", line 59, in main
    create_table()
  File "test_mysql.py", line 25, in create_table
    cursor.execute("""drop table if exists %s""",(table_name))
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 174, in execute
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
_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 ''testtable'' at line 1")

正确的做法是什么？

更新:另一个问题是通过参数创建表。

 sql = "create table %s(
             first_name char(20) not null,
             last_name char(20))" % mdb.escape_string(table_name)

 cursor.execute(sql)

 It gives `SyntaxError: EOL while scanning string literal`.

Answer 1

不能参数化表名，使用string formatting并手动转义该值:

cursor.execute("drop table if exists %s" % mdb.escape_string(table_name))