python - 使用列表创建 MySQL 表的列

Question

对 SO 不熟悉，对编码也相当陌生，所以我会尽力遵循适当的协议(protocol)。

在我的 python 脚本中，我正在创建一个新表并从名为“dups”的列表中填充列名称。

dups = ['Id', 'Name', 'Price', 'Rating']

我通过 for 循环输入此列表作为新表(称为“SuperTable”)的列。请参阅下面的代码:

with new_db:

    cur = new_db.cursor()
    cur.execute("DROP TABLE IF EXISTS SuperTable")
    for i in dups:
        if i == dups[0]:
            new_col = i.replace("'","")
            cur.execute("CREATE TABLE SuperTable(%s)" % (new_col))
    else:
        cur.execute("ALTER TABLE SuperTable ADD COLUMN %s" % i)

我环顾四周，似乎无法确定我做错了什么。这种方法适用于 Sqlite，但我在 MySQLdb 中不断收到同样的错误:

Traceback (most recent call last):
  File "MySQL_SuperTable.py", line 125, in <module>
  cur.execute("CREATE TABLE Super(%s)" % (new_col))
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/cursors.py", line 174, in execute
  self.errorhandler(self, exc, value)
  File "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, 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 ')' at line 1")

Answer 1

感谢蛋黄!他指出 MySQL 列需要数据类型。这就是代码现在的样子(我创建了一个元组列表来通过 for 循环输入数据类型+列名称):

with new_db:

cur = new_db.cursor()
cur.execute("DROP TABLE IF EXISTS SuperTable")
for i in col_namestypes:
    if i == col_namestypes[0]:
        cur.execute("CREATE TABLE SuperTable(%s %s)" % (i))
    else:
        cur.execute("ALTER TABLE SuperTable ADD COLUMN %s %s" % i)
for i in new_table:
    count = len(i)
question_marks = []
while a < count:
    question_marks.append('%s')
    a += 1
quests = ','.join(question_marks)
cur.executemany("INSERT INTO SuperTable VALUES(%s)" % quests, new_table)