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
Thanks
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
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";
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:
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'.
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;"""
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'.
ASKER
Most of the bugs worked out with the code. One last problem. I receive _mysql_exceptions.Operatio nalError: (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
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
B-) glad we could help. Thx for the grade and points
ASKER
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.
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()