python - 尝试在Python中向mysql数据库中的表添加数据时出现未知列错误

Question

向数据库添加数据时出现此错误。我该如何解决这个问题？

错误:

mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'hn' in 'field list'

我知道此列不存在，但我无论如何都不会向此类列发送数据。

我的Python代码:

def addToTable(table_name,connection,column_name_list,*data_list):
 
    if(len(column_name_list) != len(data_list)):
        raise ValueError("'column_name_list' length has to be equal to 'data_list' length. Please check the parameters")
    cursor = connection.cursor()  # initializing a cursor
    for column_data in range(len(data_list[0])):
        addList = list()
        for data in range(len(data_list)):
            added = str(data_list[data][column_data])
            addList.append(added)
        cursor.execute("INSERT INTO " + table_name + " VALUES (" + ", ".join(str(k) for k in addList) + ")")
        mydb.commit()
        print("Added {} in {} ...".format(added, table_name))

从 python 代码发送的示例查询:

插入 deneme 值(hn、1212、asdmailcom)

调用函数:

names = ["hn","ben","alex",]
numbers = [1212,1245,54541]
mails = ["asdmailcom","fghmailcom","xyzmailcom"]

columns = ["de","ne","me"]

mydb = mysql.connector.connect(host="127.0.0.1",
                                   user="root",
                                   passwd="1234",
                                   database="deneme",
                                   auth_plugin='mysql_native_password')

addToTable("deneme",mydb,columns,names,numbers,mails)

我的表名称是“deneme”，数据库名称是“deneme”。列:'de' varchar(45)、'ne' varchar(45)、'me' varchar(45)

Answer 1

我解决了这个问题。我在评论行中解释了。

def addToTable(table_name,connection,column_name_list,*data_list):
    if(len(column_name_list) != len(data_list)):       
        raise ValueError("'column_name_list' length has to be equal to 'data_list' length. Please check the parameters")

    cursor = connection.cursor()  # initializing a cursor
    for column_data in range(len(data_list[0])):
        addList = list()
        for data in range(len(data_list)):
            added = str(data_list[data][column_data])
            added = "'"+added+"'" # the purpose of this line is to convert the data to string
                                  # example: without this line
                                  #          query ---> INSERT INTO table_name (column1, column2, ...) VALUES (lorem,ipsum,sit)
                                  # example: with this line
                                  #          query ---> INSERT INTO table_name (column1, column2, ...) VALUES ('lorem','ipsum','sit')
            addList.append(added)
        cursor.execute("INSERT INTO " + table_name + " VALUES (" + ", ".join(str(k) for k in addList) + ")")
        mydb.commit()
        print("Added {} in {} ...".format(added, table_name))