CSV - MYSQL Using Python

Question

After reading several inputs I still can't get this to work. Most likely I'm doing it all wrong but I've tried several different approaches

What I'm trying to do is extract data from a CSV and add it into my newly created database/table

My csv input look like this

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,-

The mysql table is created but possibly that might be the issue as some ar varchar columns and some are integer columns

My server is a Ubuntu if that is of any use

My Code

        # -*- 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()

Googe "mysql csv" and look around. MySQL has built in functionality specifically for loading CSVs, you may not need to write any code at all and it will probably run faster. — Alex Hall, May 17 '16 at 18:21

score 0 · Answer 1 · edited May 23 '17 at 12:31

0

You are missing Percent_0-5 from your Insert

Remove the quotes from the %s references, this needs to be in String format, but the underlying data type will be passed.

There may be issues with datatype resulting from the csv reader. Have Python eval() the csv data to alter type as an INT. Here is some more information from another post: 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]))

edited May 23 '17 at 12:31

Community

1
1

answered May 17 '16 at 17:10

EoinS

5,405
1
19
32

Ah, THX that however didn't fix all of the issues I'm still not populating the table, Could it be an issue with usig integer but sending strings? – Hunken84 May 17 '16 at 17:15
"As the whole query needs to be in a string format while execution of query so %s should be used..." Try and manually insert one row of data into your DB, check if it passes. You may have datatyping issues with the data and your table. Python uses %s but if the item being passed is INT it will flow through as an INT. http://stackoverflow.com/questions/20463333/mysqldb-python-insert-d-and-s – EoinS May 17 '16 at 17:30
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 (2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3) – Hunken84 May 17 '16 at 17:51
This is probably an issue with the CSV reader. You can manipulate the data after reading it to pass it through to your insert statement. Here is some more information: http://stackoverflow.com/questions/11665628/read-data-from-csv-file-and-transform-to-correct-data-type – EoinS May 17 '16 at 17:58
Sounds likely but I've changed all columns to varchar so it should'nt give me any issues? Ofcourse I'd like to have it in correct format but until then it should work if the columns are varchar 11-32 ? – Hunken84 May 17 '16 at 18:05
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 ('X13146PAZ','5002','1/11/106','2016-05-16 00:00:00','24','Near End','... – Hunken84 May 17 '16 at 18:11
Added an actual line from the CSV without any issues, I'll see if I can wrap my head around the csv-issue – Hunken84 May 17 '16 at 18:12
I was just writing to ask you to do that. Unfortunate. Try importing the first row into python separately and analyze the list for values that look incorrect. – EoinS May 17 '16 at 18:17
*Solved*, Thx, I believe it was as you said a combination of CSV always being Strings and a missed %s on my part. Big thx for all your help it saved a long night och head-scratching. Upvoted but my rank is to low to be seen If ever in Gothenburg/Sweden send me a PM and I'll gladly repay you – Hunken84 May 17 '16 at 18:19
Scratch that I was a bit fast =P It is'nt solved the beer offer still stands though – Hunken84 May 17 '16 at 18:22
cur.execute('INSERT INTO RX_UTIL(NodeName, NeId, Object, Time, Interval1,Direction,NeAlias,NeType,Position,AVG,MAX,MIN,percent_0-5,percent, 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(6,9,9,9,9,0,0,9,8,7,6,4,4,4,4,4,4,4,4,4,4,4,4,2,2,2,2,2,1,1,1,1,1,1)',tuple(row[:34])) Is this the correct format? – Hunken84 May 17 '16 at 18:44
like this when you feed values: cur.execute('INSERT INTO RX_UTIL(NodeName, NeId, Object, Time, Interval1,Direction,NeAlias,NeType,Position,AVG,MAX,MIN,percent_0-5,percent, .....,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)',(6,9,9,9,9,0,0,9,8,7,6,4,4,4,4,4,4,4,4,4,4,4,4,2,2,2,2,2,1,1,1,1,1,1)) – EoinS May 17 '16 at 19:56
It looks like you added Interval1 to your list of headers? If this is necessary you should add data for it, otherwise remove from the list of column headers. – EoinS May 17 '16 at 20:04

score 0 · Answer 2 · answered May 17 '16 at 17:16

You should not put the placeholder %s in quotes ":

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]))

WOW you guys are fast, That however did not solve the issue could it be that my columns in MYSQL are INT instead of varchar — Hunken84, May 17 '16 at 17:21

CSV - MYSQL Using Python

2 Answers2