0

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.

AK91
  • 671
  • 2
  • 13
  • 35

1 Answers1

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 %

tomgalpin
  • 1,943
  • 1
  • 4
  • 12
  • Tried that too unfortunately...https://stackoverflow.com/questions/59543291/python-mysql-parameters-and-load-data-command – AK91 Dec 31 '19 at 13:34