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

I have a SQL query which opens up a csv file and dumps it in a table of a database. I am trying to dump multiple files at once using a python script to iterate among the files. I tried embedding the same SQL query inside the script, but it throws out an error.

This is the script I have.

import csv
import MySQLdb

connection = MySQLdb.connect(host='localhost',
    user='root',
    passwd='password',
    db='some_db')


cursor = connection.cursor()

query = """ LOAD DATA INFILE 'c:\\example.csv' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES """

cursor.execute(query)

conenction.commit()
cursor.close()

And for some reason the python script looks up example.csv at a different location

This is the error that is thrown out :

raise errorclass, errorvalue
InternalError: (29, "File 'C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.5\\data\\example.csv' not found (Errcode: 2)")

Any help would be greatly appreciated. I am also searching on stackoverflow for help to dump the scv files into differnt tables of a database. Any ideas on that?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You probably need the load data local syntax to make sure the data is being read relative to the client and not the server. Change

query = """ LOAD DATA INFILE 'c:\\example.csv' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES """

to

query = """ LOAD DATA LOCAL INFILE 'c:\\example.csv' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES """
share|improve this answer
    
Worked Fine. Thank you. –  Kumaran Senapathy Feb 12 '13 at 15:18
    
For some reason there is an error when I give your answer a thumbs up. –  Kumaran Senapathy Feb 12 '13 at 15:19
    
@KumaranSenapathy Glad it worked. BTW, you could just accept the answer instead of up-voting. –  rjf Feb 12 '13 at 15:29
up vote 0 down vote

Watch your spelling! conenction.commit() should be connection.commit()

share|improve this answer

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.