Run MySQL to load CSV to MySQL in Python 2.7 -

Question

I am trying to upload a csv table into a MySQl table using MySQLdb module in python 2.7.

import MySQLdb

connection = MySQLdb.connect(host="localhost", user="root", passwd="password", db="test")

cursor = connection.cursor()
query = '''USE test; DELETE FROM test2; LOAD DATA LOCAL INFILE "C:/data/data.csv" INTO TABLE    test2 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;'''
cursor.execute(query)

connection.close()

I have tried the query on phpmyadmin and it goes through, it allows me to transfer all the rows (excluding the top row which are just the columns names) but when I try and run the query in python 2.7 I get multiple error messages.

Error 1:

Syntax error: Invalid Syntax pointing to the ENCLOSED BY section.

When I add 3 single quotes around the query, I get an error message"

Error 2:

Exception _mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL s erver during query') in (bound method Cursor__del__ of MySQLdb.cursors.Cursor object at 0x0233AAF0) ignored

I have no idea what to do. I've done research for the better part of the day and have tried different variations but I've got nothing. You're help would be greatly appricaited.

Answer 1

For Error 2, instead of putting all the queries in a single string, you should try splitting them into multiple queries and run them separately. Like this -

query1 = '''DELETE FROM test2;'''
cursor.execute(query1)
query2 = '''LOAD DATA LOCAL INFILE "C:/data/data.csv" INTO TABLE    test2 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;'''
cursor.execute(query2)

I do not think you need to execute use test as that would already be used when creating the connection.