我试图用文章 Python MySQLdb execute table variable作为例子,但到目前为止还没有快乐.我正在尝试创建一个表,其名称是“archive”和作为变量传入的年份的串联.这是硬编码表名的替代方法,例如“archive_2013”.
这是我的代码片段:
year_string = sys.argv[1]
if int(year_string) < 1999 or int(year_string) > 2014:
print "\n"
print "Year must be between 1999 and 2014\n"
sys.exit(1)
table_name = "archive_" + year_string
# Open database connection
db = MySQLdb.connect("localhost","root","menagerie","haiku_archive" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Create table using execute() method.
sql = ""CREATE TABLE IF NOT EXISTS %s" % table_name
haiku_text VARCHAR(120),
date_written CHAR(22))"
cursor.execute(sql)
这是我得到的错误:
pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
File "./insert_haiku_from_file_into_table.py", line 36
sql = ""CREATE TABLE IF NOT EXISTS %s" % table_name
^
SyntaxError: invalid syntax
任何帮助将不胜感激!
我尝试实施我收到的回复,但到目前为止还没有令人满意的结果.这是我使用三引号SQL的代码段:
sql = """CREATE TABLE IF NOT EXISTS %
haiku_text VARCHAR(120),
date_written CHAR(22))""" % table_name
cursor.execute(sql)
我执行脚本时最终得到以下内容:
pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
Traceback (most recent call last):
File "./insert_haiku_from_file_into_table.py", line 38, in
date_written CHAR(22))""" % table_name
ValueError: unsupported format character '
' (0xa) at index 28
我也尝试过使用占位符表示法,因为我想避免SQL注入的最可能性.这是我的片段:
sql = """CREATE TABLE IF NOT EXISTS ?
haiku_text VARCHAR(120),
date_written CHAR(22))"""
cursor.execute(sql, table_name)
以下是执行时会发生的情况:
pablo@desktop=> ./insert_haiku_from_file_into_table.py 2013 qwert.txt
Traceback (most recent call last):
File "./insert_haiku_from_file_into_table.py", line 39, in
cursor.execute(sql, table_name)
File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting
我会对占位符的语法做一些进一步的研究,但与此同时,任何进一步的建议都会很棒!