python - Python中的Mysqldb INSERT INTO语法错误

Question

我有一个要放入mysql的Excel文件。我想使用python插件xlrd提取32个字段并放入mysql中。数据布局如下:

我的方法是遍历行和列，并在需要时添加字段名称。该代码添加了字段名称，但是添加数据时出现错误。 (我用索引作为唯一字段创建了表“OilProvedReservesHistory”):

def get_data_from_sheet(sheet_index, table, start_row, end_row, end_col):

for row in range(start_row - 1, end_row - 1):
    for col in range(end_col):
        cell = sheet.cell(row,col)
        if col == 0:        
            fieldname = cleanup_field(cell.value)
            if fieldname != ("Blank Cell"):
                sql = "ALTER TABLE OilProvedReservesHistory ADD %s VARCHAR(20)" % fieldname
                c.execute(sql)                  
        else:
            data = cleanup_data(cell)
            if data != ("Blank Cell"):
                postToMySQL(data,fieldname,table)


def postToMySQL(data,fieldname,table):
#Generate sql query string                
sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES %s"
c.execute(sql, data)
# Executes: ("""INSERT INTO OilProvedReservesHistory (US) VALUES 36.533""")

table = create_table_from_sheet_name()

sheet_index = 0
start_row = 5
end_row = 68
end_col = 32

get_data_from_sheet(sheet_index, table, start_row, end_row, end_col)

我得到的错误是:

_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 ''36.533'' at line 1")

您能帮我解决这个问题吗？非常感谢!

Answer 1

我的猜测是，您只是忘记了VALUES子句的括号。

INSERT INTO OilProvedReservesHistory (US) VALUES (36.533)

尝试将代码更改为:

sql = "INSERT INTO " + table + " ("+ fieldname + ") VALUES (%s)"