CODEWITHSUNDEEP
pythonmysqlcsv
mongotop
mongotop

Reputation: 5774

import csv file into Mysql Database using python

This is my code:

#!/usr/bin/python 
import MySQLdb
import csv

db = MySQLdb.connect(host="host", # The Host
                     user="username", # username
                     passwd="pwd", # password
                     db="databasename") # name of the data base

sqlLoadData = 'LOAD DATA LOCAL INFILE "csv?_file_name.csv" INTO TABLE tablename '   
sqlLoadData += 'FIELDS TERMINATED BY "," LINES TERMINATED BY "\n"'     
sqlLoadData += 'IGNORE 1 LINES'     
sqlLoadData += 'ENCLOSED BY '"' ESCAPED BY "\\" '

try:
    curs = db.cursor()   
    curs.execute(sqlLoadData)    
    resultSet = curs.fetchall()    
except StandardError, e:    
    print e    
    db.rollback()
    db.close()

I recieve the error Message : You have an error in your SQL Syntax; chekc the manual that correcpond to your Mysql Server.

When I remove the part sqlLoadData += 'ENCLOSED BY '"' ESCAPED BY "\\" ' everything work perfect. I used the last part just to remove the quote from the values.

I also tried:

cursor = mydb.cursor()

reader = csv.reader(open('Cumulative.csv', 'rb'))

reader.next() for row in reader[1:]: cursor.execute('INSERT INTO Cumulative (C1, C2, C3, C4, C5, C6) VALUES(%s, %s, %s, %s, %s, %s)', row)
cursor.commit()

close the connection to the database.

cursor.close()

I want just to remove the quote so the integer field will support the data. so with quote "1" will be considered as a String instead of integer

Can Anyone please help me to understand this?

Thanks!

Upvotes: 2

Views: 17863

Answers (3)

Janojeps
Janojeps

Reputation: 21

After days and hours of searching the internet and running into all sort of errors and warnings, this worked perfectly. I hope this saves someone some time 

import MySQLdb
import os
import string

db = MySQLdb.connect (host="host",
    user="user",
    passwd="pwd",
    db="database_name",
    local_infile = 1) #Grants permission to write to db from an input file. Without this you get sql Error: (1148, 'The used command is not allowed with this MySQL version')

print "\nConnection to DB established\n"

#The statement 'IGNORE 1 LINES' below makes the Python script ignore first line on csv file
#You can execute the sql below on the mysql bash to test if it works
sqlLoadData = """load data local infile 'file.csv' into table table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;"""

try:
    curs = db.cursor()   
    curs.execute(sqlLoadData)
    db.commit()   
    print "SQL execution complete"   
    resultSet = curs.fetchall()   
except StandardError, e:    
    print "Error incurred: ", e    
    db.rollback()
    db.close()

print "Data loading complete.\n"

Thanks, I hope this helps :)

Upvotes: 2

mongotop
mongotop

Reputation: 5774

After 2 days worth of research I found the answer:

!/usr/bin/python  
import MySQLdb 
import csv

db = MySQLdb.connect(host="host", # The Host
                      user="username", # username
                      passwd="pwd", # password
                      db="databasename") # name of the data base

 cursor = connection.cursor() 
 Query = """ LOAD DATA LOCAL INFILE 'usrl to csv file' INTO TABLE
 table_nameFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED
 BY '"' Lines terminated by '\n' IGNORE 1 LINES """

cursor.execute(Query)   
connection.commit()   
cursor.close()

hope it will help somebody out there.

Upvotes: 2

David Marx
David Marx

Reputation: 8558

looks like you forgot to terminate the preceding line with a space or newline character. Thi sis causing a syntax error when the parser tries to understand LINESENCLOSED which obviously isn't a keyword.

 sqlLoadData += 'IGNORE 1 LINES \n'
 sqlLoadData += ''ENCLOSED BY '"' ESCAPED BY "\" ''

As a rule of thumb: when you're debugging, and you're able to fix you're code by removing a line, don't rule out the line immediately above

EDIT: Modified the quotes around the second line. I think it was breaking in the "enclosed by" statement.

Upvotes: 4

Related Questions