load Local infile csv to mysql db fails

Question

I trying to import my csv file into database. but it fails.

    # -*- coding: utf-8 -*-
import MySQLdb

class Database:

    def __init__(self):

        self.host = 'localhost'
        self.user = 'root'
        self.port =  3306
        self.password = 'root'
        self.db = 'test'
        self.connection = MySQLdb.connect(self.host, self.user, self.password, self.db, self.port, local_infile = 1)
        self.cursor = self.connection.cursor()


    def insert_csv_test(self):

        query = "LOAD DATA LOCAL INFILE ‘/Users/ankr/Desktop/output’ INTO TABLE details FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’"
        self.cursor.execute(query)
        self.connection.commit()
        self.connection.close()
        print("Done")

    def close_connection(self):
        self.connection.close()

database = Database()
database.__init__()
database.insert_csv_test()
database.close_connection()

It fails. Seeing this below.

Traceback (most recent call last): File "test.py", line 30, in database.insert_csv_test() File "test.py", line 20, in insert_csv_test self.cursor.execute(query) File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.12-intel.egg/MySQLdb/cursors.py", line 202, in execute self.errorhandler(self, exc, value) File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.12-intel.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\xe2\x80\x98/Users/ankr/Desktop/output\xe2\x80\x99 INTO TABLE details FIELDS TERMINATED BY \xe2\x80\x98,\xe2\x80\x99 LI' at line 1")

Any Help would be appreciated.

Looks like you have curly quotes around your csv path.

Daniel Roseman
– Daniel Roseman

2017-10-18 10:18:37 +00:00
Commented Oct 18, 2017 at 10:18 — Daniel Roseman
– Daniel Roseman, Commented Oct 18, 2017 at 10:18

Artsiom Vahin · Accepted Answer · 2017-10-18 10:24:57Z

1

This may be a little naive answer, but I think the problem lies in ‘ character. It's getting interpreted as a UTF-8 character. Try replacing it with a regular single quote - ' .

answered Oct 18, 2017 at 10:24

Artsiom Vahin

691 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eamonn Kenny · Accepted Answer · 2017-10-18 11:26:02Z

1

Looks like you at least have a problem in the call. You are connecting to the database twice:

database = Database()
database.__init__()

You should just run:

database = Database()

You should be using \' inside the SQL query (not ') since you want to avoid them being directly interpreted as mentioned already in another comment.

edited Oct 18, 2017 at 11:26

answered Oct 18, 2017 at 10:38

Eamonn Kenny

2,11221 silver badges21 bronze badges

1 Comment

Anjanaa Over a year ago

Thanks. I got it. It is becoz of quotes(') not (‘)

Collectives™ on Stack Overflow

load Local infile csv to mysql db fails

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related