current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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!

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
That fixed the Mysql Query, but that gave me an error Unexpected Character after line continuation character { ' } –  mongotop Mar 4 '13 at 21:53
    
Made a change, try it now. (encapsulate the whole string by an extra set of single quotes) –  David Marx Mar 4 '13 at 22:25
    
Thanks a lot for your quick replay!! Still Same Error Message. –  mongotop Mar 4 '13 at 22:41
    
The problem is that python thinks that you are terminating your string prematurely. The error message means the interpreter is reading the backslash as a "line continuation" character. Try encapsulating this line using different combinations of single and double quotes. –  David Marx Mar 4 '13 at 22:58
    
Thank you so much David, I made some changes and I tried a new way but no luck. –  mongotop Mar 5 '13 at 15:56
up vote 0 down vote

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.

share|improve this answer
    
It would be nice if you explain why the error occurred and how you solved it. –  jurgenreza Mar 5 '13 at 18:12
2  
I can explain the error if you want. He solved the error be encapsulating all of his text in triple quotes instead of building it up peicewise, which is really much much better. His original error, which I had missed as well, was that he only terminated his first and fourth line with a white space character, but his second and third lines needed them as well. I thoguht his second line had a new line character at the end, but it actually just had a new line character contained in a command "lines terminated by" command. –  David Marx Mar 5 '13 at 19:23
1  
Do vote up any answer that is accepted, aye? –  hd1 Nov 4 '13 at 19:00
    
I voted up David answer and I marked it as accepted answer! thanks for the reminder hd1 !! –  mongotop Nov 5 '13 at 3:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.