时间戳

首页 > 解决方案 > 使用python将数据从文件插入到mysql时出错

python - 使用python将数据从文件插入到mysql时出错

问题描述

有一个文件包含我想使用 python 插入到 mysql 中的表中的数据。我使用“with open”从文件中检索数据并将其转换为列表。所以现在列表中的元素是字符串，我想更改字符串到元组，以便我可以从中检索数据到 mysql。 

    import mysql.connector


    with open("/home/ninky/com.csv", "r") as fp:
        content = fp.read()
    lines = content.split("\n")
    print(lines)

        myconn = mysql.connector.connect(host="localhost", user="root", database="EMPSALARY" 
        cur = myconn.cursor()
        db = "insert into PERSON(name,age,year)values(%s,%s,%s)"
        myconn = cur.execute(db, lines)
        myconn.close()

result:
-----------
['deepak,29,2019', 'ninky,29,2010', 'suraj,29,2020', 'pratap,30,2018', '']
deepak,29,2019
ninky,29,2010
suraj,29,2020
pratap,30,2018

Traceback (most recent call last):   File
"/home/ninky/PycharmProjects/new/csv_db.py", line 23, in <module>
    cur.execute(db, lines)   File "/usr/local/lib/python3.6/dist-packages/mysql/connector/cursor.py",
line 543, in execute
    "Not all parameters were used in the SQL statement") mysql.connector.errors. ProgrammingError: Not all parameters were used
in the SQL statement

Process finished with exit code 1

标签： pythonmysql

解决方案

在行中，您拥有的是一个包含 CSV 文件每一行的列表。然后，您需要遍历所有行并在分隔符 (',') 上再次进行拆分：

import mysql.connector

with open("/home/ninky/com.csv", "r") as fp:
    content = fp.read()
lines = content.split("\n")
print(lines)


myconn = mysql.connector.connect(host="localhost", user="root", database="EMPSALARY")
cur = myconn.cursor()

db = "insert into PERSON (name,age,year) values (%s,%s,%s)"
for l in lines:
    data=l.split(',')
    cur.execute(db, data)
myconn.close()

推荐阅读