Unable to load data file into MySQL using pymysql - Can't find file

Question

I am trying to load a delimited file into my MariaDB database, but I keep getting the following error:

pymysql.err.OperationalError: (1017, "Can't find file 'b'/tmp/altered.txt''")

Where is the 'b' coming from?

When I run LOAD DATA command from the MariaDB prompt, it works fine.

Here is my code:

    print("Prepare to import file")
    cnopts=pysftp.CnOpts()
    cnopts.hostkeys = None

    with pysftp.Connection("8.8.8.8", username="osuser", password="ospassword", cnopts=cnopts) as sftp:
        with sftp.cd("/tmp"):
            sftp.put("C:/mydirectory/altered.txt")
    db = pymysql.connect("8.8.8.8", "dbuser", "dbpassword", "database", local_infile=True)
    cursor = db.cursor()
    print("importing file")
    cursor.execute('LOAD DATA LOCAL INFILE "/tmp/altered.txt" INTO TABLE daily FIELDS TERMINATED BY "|" LINES STARTING BY "148521" TERMINATED BY "\n" ')

    db.commit()
    cursor.close()

Answer 1

LOAD DATA LOCAL INFILE is for a file that is on the client (remote) machine.

By using LOAD DATA LOCAL INFILE, there is no need to sFTP the file.

print("Prepare to import file")

db = pymysql.connect("serverIP", "user", "password", "database", local_infile=True)
cursor = db.cursor()
print("importing file")

load_sql = "LOAD DATA LOCAL INFILE 'C:/directory/file.txt' INTO TABLE table FIELDS TERMINATED BY '|' LINES STARTING BY '11111' TERMINATED BY '\n';"
print("SQL Statement loaded")

cursor.execute(load_sql)

db.commit()
cursor.close()