CSV MYSQL使用Python

Question

在阅读了几个输入之后，我仍然不能让它工作。 很可能我做错了，但我试过几种不同的方法

我要做的是从CSV中提取数据并将其添加到新创建的数据库/表中

我的csv输入是这样的

NodeName,NeId,Object,Time,Interval,Direction,NeAlias,NeType,Position,AVG,MAX,MIN,percent_0-5,percent_5-10,percent_10-15,percent_15-20,percent_20-25,percent_25-30,percent_30-35,percent_35-40,percent_40-45,percent_45-50,percent_50-55,percent_55-60,percent_60-65,percent_65-70,percent_70-75,percent_75-80,percent_80-85,percent_85-90,percent_90-95,percent_95-100,IdLogNum,FailureDescription
X13146PAZ,5002,1/11/100,2016-05-16 00:00:00,24,Near End,GE0097-TN01.1,AMM 20PB,-,69684,217287,772,10563,8055,10644,15147,16821,13610,7658,2943,784,152,20,3,0,0,0,0,0,0,0,0,0,-
...
X13146PAZ,5002,1/11/102,2016-05-16 00:00:00,24,Near End,GE0097-TN01.1,AMM 20PB,-,3056,28315,215,86310,90,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-
...
X13146PAZ,5002,1/11/103,2016-05-16 00:00:00,24,Near End,GE0097-TN01.1,AMM 20PB,-,769,7195,11,86400,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-

mysql表已经创建，但可能是因为有些ar varchar列和有些是整数列

我的服务器是一个Ubuntu如果这是任何用途

我的代码

        # -*- coding: utf-8 -*-
#Imports
from datetime import date, timedelta
import sys
import MySQLdb as mdb
import csv
import os

#Vars
Yesterday = date.today() - timedelta(1)

#Opening document
RX_Document = open('./reports/X13146PAZ_TN_WAN_ETH_BAND_RX_' + Yesterday.strftime("%Y%m%d") + "_231500.csv" , 'r')
RX_Document_Str = './reports/X13146PAZ_TN_WAN_ETH_BAND_RX_' + Yesterday.strftime("%Y%m%d") + "_231500.csv"

csv_data = csv.reader(file(RX_Document_Str))

con = mdb.connect('localhost', 'username', 'password','tn_rx_utilization');


counter = 0
for row in csv_data: 
    if counter == 0:
        print row
        continue


    counter = 1
    if counter == 1:
        cur = con.cursor()
        cur.execute('INSERT INTO RX_UTIL(NodeName, NeId, Object, Time, Interval1,Direction,NeAlias,NeType,Position,AVG,MAX,MIN,percent_5-10,percent_10-15,percent_15-20,percent_20-25,percent_25-30,percent_30-35,percent_35-40,percent_40-45,percent_45-50,percent_50-55,percent_55-60,percent_60-65,percent_65-70,percent_70-75,percent_75-80,percent_80-85,percent_85-90,percent_90-95,percent_95-100,IdLogNum,FailureDescription)' 'VALUES("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")',tuple(row[:34]))
        con.commit()

        #cur.execute("SELECT VERSION()")

        #ver = cur.fetchone()




con.commit()
con.close()

Answer 1

插入内容中缺少%0-5

从%s引用中删除引号，这需要采用字符串格式，但将传递基础数据类型。你知道吗

csv读取器可能导致数据类型出现问题。让Python eval（）将csv数据改为INT类型。下面是另一篇文章中的更多信息： Read data from csv-file and transform to correct data-type

cur.execute('INSERT INTO RX_UTIL(NodeName, NeId, Object, Time, Interval1,Direction,NeAlias,NeType,Position,AVG,MAX,MIN,percent_0-5,percent_5-10,percent_10-15,percent_15-20,percent_20-25,percent_25-30,percent_30-35,percent_35-40,percent_40-45,percent_45-50,percent_50-55,percent_55-60,percent_60-65,percent_65-70,percent_70-75,percent_75-80,percent_80-85,percent_85-90,percent_90-95,percent_95-100,IdLogNum,FailureDescription)' 'VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',tuple(row[:34]))

Answer 2

不应将占位符%s放在引号"：

cur.execute('''INSERT INTO RX_UTIL(NodeName, NeId, Object, Time, Interval1,Direction,
    NeAlias,NeType,Position,AVG,MAX,MIN,"percent_5-10","percent_10-15",
    "percent_15-20","percent_20-25","percent_25-30","percent_30-35",
    "percent_35-40","percent_40-45","percent_45-50","percent_50-55",
    "percent_55-60","percent_60-65","percent_65-70","percent_70-75",
    "percent_75-80","percent_80-85","percent_85-90","percent_90-95",
    "percent_95-100",IdLogNum,FailureDescription)
    VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
        %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''', tuple(row[:33]))