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 wanted a script that iterates through csv files in a folder and dump them into a MySQL database. I was able to dump one csv file into it.. But have troubles passing the file name in to the SQL script.

This is the code I use

file_path="C:\csv-files"
files=os.listdir(file_path)
files.sort()
for n in files:

    cursor.execute(" LOAD DATA LOCAL INFILE '%s' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES ",(n))

And I get the following error

raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'file1.csv'' INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY' at line 1")

If I use the file name directly instead of passing it, it works fine.

If you can see in the error thrown out, there seems to be an error in the SQL Script.

This would be the whole code

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


cursor = connection.cursor()

file_path="C:\csv-files"
files=os.listdir(file_path)
files.sort()
for n in files:
    print n



    cursor.execute(" LOAD DATA LOCAL INFILE %s INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' Lines terminated by '\n' IGNORE 1 LINES " %n)



connection.commit()
cursor.close()
share|improve this question

1 Answer 1

up vote 0 down vote accepted

First, replace '%s' with %s in the query. MySQLdb handles any quoting automatically.

Here's the code with some corrections and changes:

import MySQLdb
import os

CSV_DIR = "C:\csv-files"

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

cursor = connection.cursor()

try:
    for filename in sorted(os.listdir(CSV_DIR)):
        cursor.execute("""LOAD DATA LOCAL INFILE %s
                          INTO TABLE new_table
                          FIELDS
                          TERMINATED BY ','
                          OPTIONALLY ENCLOSED BY '"'
                          ESCAPED BY '"'
                          LINES TERMINATED BY '\n'
                          IGNORE 1 LINES""",
                      (os.path.join(CSV_DIR, filename),))

    connection.commit()
finally:
    cursor.close()

NOTE: I set local_infile parameter to 1 in MySQLdb.connect and pass filename in tuple to execute.

Works for me.

share|improve this answer
    
It still throws out an error ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'file1.csv INTO TABLE new_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' at line 1") If u notice there is a change in the error now near "optionally enclosed by" –  Kumaran Senapathy Feb 13 '13 at 16:40
    
I see, thanks. Please provide the whole python script (with MySQLdb.connect), versions of python, MySQLdb and mysql itself. –  alecxe Feb 13 '13 at 16:53
    
I have edited the question with the complete code –  Kumaran Senapathy Feb 13 '13 at 18:04
    
See changes in the answer, hope it helps. –  alecxe Feb 13 '13 at 18:36
    
InternalError: (2, "File 'file2.csv' not found (Errcode: 2)") I get this error. I have two files in the directory. And the fact that I used the script to list the directory. –  Kumaran Senapathy Feb 13 '13 at 18:59

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.