python - 如何使用python将数据列表、数组插入数据库MySQL？

Question

我想将其插入数据库，请帮我解决。

    import numpy as np
    import sys
    import mysql.connector
    from mysql.connector import Error

    id = 'PEL0005,PEL0006,PEL0007,PEL0008,PEL0009,PEL0010,PEL0011,PEL0012,PEL0013,PEL0014'
    p = [[9,1,7,3,14,3,18,2,22,2],[2,2,6,4,4,9,3,3,3,7],[13.9769,12.1656,40.047,28.287,25.1415,47.6875,34.269,38.3822,47.6875,19.575]]
    result = ['C2', 'C2', 'C1', 'C2', 'C2', 'C1', 'C3', 'C1', 'C3', 'C2']
    def insertData(id, recency, frequency, monetary, result):
        try:
            connection = mysql.connector.connect(host='localhost', database='myDB', user='root', password='')
            cursor = connection.cursor()
            query = """INSERT INTO clus_result (id, recency, frequency, monetary, result) 
                                    VALUES (%s, %s, %s, %s, %s) """

            recordTuple = (id, recency, frequency, monetary, result)
            cursor.execute(query, recordTuple)
            connection.commit()
            print("Inserted successfully")

        except mysql.connector.Error as error:
            print("Failed to insert into MySQL table {}".format(error))

        finally:
            if (connection.is_connected()):
                cursor.close()
                connection.close()
                print("MySQL connection is closed")

    insertData(id,p[0],p[1],p[2],result)

错误:无法插入MySQL表失败处理格式参数； Python“列表”无法转换为 MySQL 类型

我期望结果:编号 |近期 |频率|货币|结果

PEL0005 | 2 | 13.9789 | C2

PEL0006 | 2 | 12.1656 | 12.1656 C2

等等..

Answer 1

你应该这样做:-

import numpy as np
import sys
import mysql.connector
from mysql.connector import Error

id = 'PEL0005,PEL0006,PEL0007,PEL0008,PEL0009,PEL0010,PEL0011,PEL0012,PEL0013,PEL0014'
p = [[9,1,7,3,14,3,18,2,22,2],[2,2,6,4,4,9,3,3,3,7],[13.9769,12.1656,40.047,28.287,25.1415,47.6875,34.269,38.3822,47.6875,19.575]]
result = ['C2', 'C2', 'C1', 'C2', 'C2', 'C1', 'C3', 'C1', 'C3', 'C2']
def insertData(id, recency, frequency, monetary, result):
    try:
        connection = mysql.connector.connect(host='localhost', database='myDB', user='root', password='')
        cursor = connection.cursor()
        query = """INSERT INTO clus_result (id, recency, frequency, monetary, result) 
                                VALUES (%s, %s, %s, %s, %s) """

        records = zip(id.split(','), p[0], p[1], p[2], result)
        cursor.executemany(query, records)
        connection.commit()
        print("Inserted successfully")

    except mysql.connector.Error as error:
        print("Failed to insert into MySQL table {}".format(error))

    finally:
        if (connection.is_connected()):
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

insertData(id,p[0],p[1],p[2],result)