Link to home
Start Free TrialLog in
Avatar of fasse
fasse

asked on

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
Avatar of fasse
fasse

ASKER

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()
Avatar of Bernard Savonet
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.
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'.
Avatar of fasse

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Bernard Savonet
Bernard Savonet
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
B-) glad we could help. Thx for the grade and points