mongotop mongotop - 2 months ago 10
MySQL Question

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!

Answer

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.