[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-28
7
Medium Priority
?
1,041 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:fasse
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:fasse
ID: 40294413
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
 
LVL 29

Expert Comment

by:fibo
ID: 40295146
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 40297211
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:fasse
ID: 40299502
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
 
LVL 25

Expert Comment

by:clockwatcher
ID: 40300227
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
 
LVL 29

Accepted Solution

by:
fibo earned 2000 total points
ID: 40300917
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
 
LVL 29

Expert Comment

by:fibo
ID: 40490071
B-) glad we could help. Thx for the grade and points
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question