Python mysql parameters and load data command (still not working)

Question

Based on Python MySQLdb execute table variable and MySQL LOAD DATA LOCAL INFILE example in python? this should work:

import pymysql, os

directory = os.path.join('path', 'to', 'directory')
filename = 'my_filename.csv'
filepath = os.path.join(directory, filename)
to_table_name = "my_table"

connection = pymysql.connect(..., local_infile=True)
with connection.cursor() as cursor:
    load_statement = """
    load data local infile %s
    into table %s
    fields terminated by ','
    optionally enclosed by '"'
    lines terminated by '\\n'
    ignore 1 lines
    """
    cursor.execute(load_statement % (filepath, to_table_name, ))
connection.commit()
connection.close

But I'm still seeing this error:

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 '/path/to/directory/my_filename.csv\n    into ' at line 1")

When I run this without the parameters i.e. writing the actual filepath and table name it works.

Any help would be much appreciated.

Have you tried double quotes around the filepath ?

tomgalpin
– tomgalpin

2019-12-31 13:02:26 +00:00
Commented Dec 31, 2019 at 13:02 — tomgalpin
– tomgalpin, Commented Dec 31, 2019 at 13:02
@tomgalpin tried it just now, unfortunately same error.

AK91
– AK91

2019-12-31 13:07:38 +00:00
Commented Dec 31, 2019 at 13:07 — AK91
– AK91, Commented Dec 31, 2019 at 13:07

tomgalpin · Accepted Answer · 2019-12-31 13:29:10Z

0

You should use the built in ability of Execute to do your string formatting also (this avoids MYSQL Injection attacks and errors) ... Rather than passing the parameters to the load_statement using % (String Interpolation) , pass that as parameters to execute

 cursor.execute(load_statement , (filepath, to_table_name ))

Notice the comma instead of a %

answered Dec 31, 2019 at 13:29

tomgalpin

2,1071 gold badge7 silver badges12 bronze badges

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

2 Comments

AK91 Over a year ago

Tried that too unfortunately...stackoverflow.com/questions/59543291/…

sureshvv Over a year ago

@AK91 Did you find a solution? Not able to get past the "ENCLOSED BY'"

Collectives™ on Stack Overflow

Python mysql parameters and load data command (still not working)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related