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
– Alex Hall, Commented May 17, 2016 at 18:21

Daniel · Accepted Answer · 2016-05-17 17:16:12Z

0

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

answered May 17, 2016 at 17:16

Daniel

42.9k4 gold badges57 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hunken84 Over a year ago

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 Over a year ago

Hoe come you leave out the "citations" on som columns?

Community · Accepted Answer · 2017-05-23 12:31:48Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered May 17, 2016 at 17:10

EoinS

5,4901 gold badge21 silver badges33 bronze badges

13 Comments

Hunken84 Over a year ago

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?

EoinS Over a year ago

"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. stackoverflow.com/questions/20463333/…

Hunken84 Over a year ago

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)

EoinS Over a year ago

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: stackoverflow.com/questions/11665628/…

Hunken84 Over a year ago

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 ?

|

Collectives™ on Stack Overflow

CSV - MYSQL Using Python

2 Answers 2

2 Comments

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related