时间戳

首页 > 解决方案 > 通过python将csv数据导入mysql

python - 通过python将csv数据导入mysql

问题描述

我正在尝试使用以下代码在 mysql 数据库中插入 csv 数据。

import mysql.connector
import csv

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="***",
    database="celldb"
)

mycursor = mydb.cursor()

sql = "DROP TABLE enodeb"
mycursor.execute(sql)

mycursor.execute("CREATE TABLE enodeb (id int(10) AUTO_INCREMENT PRIMARY KEY, enodeb_id VARCHAR(255), enodeb_name VARCHAR(255), cabinet_type VARCHAR(255), du_type VARCHAR(2505), mcc int(3), mnc int(3), ne_type VARCHAR(255), oam_ip VARCHAR(255), s1_ip VARCHAR(25005), nw_type VARCHAR(255))")


with open('site_details.csv', 'r') as csvfile:
    csvreader = csv.reader(csvfile)
    next(csvreader)
    for row in csvreader:
        print (row)
        mycursor.execute('INSERT INTO enodeb(enodeb_id, enodeb_name, cabinet_type, du_type, mcc, mnc, ne_type, oam_ip, s1_ip, nw_type)' 'VALUES(%s %s %s %s %s %s %s %s %s %s)',row)

我的 CSV 文件如下所示 -

enodeb_id,enodeb_name,cabinet_type,du_type,mcc,mnc,ne_type,oam_ip,s1_ip,nw_type
936233,936233_LITTLE_STURGEON,RBS 6201,DUS3102,311,850,ERBS,173.254.129.189,2001:4888:2e30:1100:0343:0411:0000:0000,OMS

当我运行此代码时，我收到以下错误 -

celldb@lteadmin:~/celldb/python_enb_dump/my_sql_update$ python db_connect.py  
['936233', '936233_LITTLE_STURGEON', 'RBS 6201', 'DUS3102', '311', '850', 'ERBS', '173.254.129.189', '2001:4888:2e30:1100:0343:0411:0000:0000', 'OMS']
Traceback (most recent call last):
  File "db_connect.py", line 26, in <module>
    mycursor.execute('INSERT INTO enodeb(enodeb_id, enodeb_name, cabinet_type, du_type, mcc, mnc, ne_type, oam_ip, s1_ip, nw_type)' 'VALUES(%s %s %s %s %s %s %s %s %s %s)',row)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 553, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 442, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1

请告知我所缺少的。

标签： pythonmysql

解决方案

您的 insert 语句和 Values 语句之间没有空格。此外，您的值应根据文档以逗号分隔。

我认为要使它起作用的另一件事是 row 是一个元组。

它应该是：

mycursor.execute('INSERT INTO enodeb (enodeb_id, enodeb_name, cabinet_type, du_type, mcc, mnc, ne_type, oam_ip, s1_ip, nw_type) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)', tuple(row))

推荐阅读