如何在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

你不能把表名当作参数来使用，应该用字符串格式化的方法，并手动处理这个值：

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