How to Import a csv file into mysql using a python script.

Everyday we download a file with shipping information from UPS.  I want to be able to just upload the csv file into a mysql table.   I was hoping to make this work with python.  Can anyone supply me with an example python script to do this with.

Thanks
fasseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fasseAuthor Commented:
Here is my code but i get an error when I run it.

Here is the error I get when I run it.
  File "test.py", line 11
    Query = LOAD DATA LOCAL INFILE 'home\python\ship.csv',
                    ^
SyntaxError: invalid syntax

-----------------------------------------------------------------

import MySQLdb
import csv

db = MySQLdb.connect(host="192.168.100.163", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="CSV_DB") # name of the data base

cursor = connection.cursor()

Query = LOAD DATA INFILE '\home\python\ship.csv'
        INTO TABLE TBL_NAME
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        ESCAPED BY '"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES;

cursor.execute(Query)
connection.commit()
cursor.close()
0
Bernard S.CTOCommented:
Query is a normal string, which once complete can be used as such, eg for display or, like here, to be sent to mysql server

Syntax for Python string values is that they are enclosed inside matching single quotes ' or double quotes "

So Query = LOAD... is recognized as a syntax error immediately.

Your string is however slightly more complicated, since it contains both single and double qoites, and so needs some tricks to handle that
A possible change could be
Query = "LOAD DATA INFILE '\home\python\ship.csv'
        INTO TABLE TBL_NAME
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '" 
        + '"'
        + "' ESCAPED BY '"
        + '"'
        + "' LINES TERMINATED BY '\n'
        IGNORE 1 LINES";

Open in new window

Note that at lines 5 and 7 '"' is one double quote enclosed by a pair of single quotes.
0
clockwatcherCommented:
Python supports triple quoted strings which would probably be a tad easier to read:
Query = r"""LOAD DATA INFILE '\home\python\ship.csv'
         INTO TABLE TBL_NAME
         FIELDS TERMINATED BY ','
         OPTIONALLY ENCLOSED BY '"'
         ESCAPED BY '"'
         LINES TERMINATED BY '\n'
         IGNORE 1 LINES;"""

Open in new window


You'll also probably want to use a raw string (note the r before the string start) which will cancel the backslash interpretation and help with your '\home\python\ship.csv' and '\n'.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

fasseAuthor Commented:
Most of the bugs worked out with the code.  One last problem.  I receive _mysql_exceptions.OperationalError: (1148, 'The used command is not allowed with this MySQL version')

I have googled and googled this.  I can not make any of the suggestions work.  I am using python 2.7 and mysql version 5.5.38

Any suggestions would be great.

Thanks
0
clockwatcherCommented:
Not sure what you've tried but it appears people have had luck with adding the local_infile parameter to the connect.
db = MySQLdb.connect(host="192.168.100.163", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="CSV_DB", # name of the data base
                      local_infile=1) 

Open in new window

0
Bernard S.CTOCommented:
Since your data is local you might test the impact of using
Query = "LOAD DATA LOCAL INFILE '\home\python\ship.csv'...

Open in new window

and of course check that the access right to the file are OK, eg read by everyone
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bernard S.CTOCommented:
B-) glad we could help. Thx for the grade and points
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.