Dumping CSV files to MySQL database using embedded query in python script

Question

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?

rjf · Accepted Answer · 2013-02-12 15:07:31Z

1

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 """

answered Feb 12, 2013 at 15:07

rjf

1,0073 gold badges13 silver badges20 bronze badges

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

2 Comments

Kumaran Senapathy Over a year ago

For some reason there is an error when I give your answer a thumbs up.

rjf Over a year ago

@KumaranSenapathy Glad it worked. BTW, you could just accept the answer instead of up-voting.

Brandon Bass · Accepted Answer · 2014-10-02 16:21:04Z

0

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

answered Oct 2, 2014 at 16:21

Brandon Bass

11 bronze badge

Collectives™ on Stack Overflow

Dumping CSV files to MySQL database using embedded query in python script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related