Mysql/connector Python Query Works In Mysql But Not Python

I've seen the following two answers: python - mysql query not working SQL query works in console but not in python And neither of the solutions work for me. I'm running the code

Solution 1:

MySQL doesn't accept a bind placeholder for an identifier. In this case, the name of the table. That has to be part of the SQL text. You can only supply values using bind placeholders.

You can do this:

SELECT t.id FROM mytable t WHERE t.foo = :val
                                         ^^^^

But this is not valid:

SELECT t.id FROM :mytable t WHERE t.foo = 'bar'
                 ^^^^^^^^ 

You could do a an sprintf-type operation as a prior step, to generate SQL text (a string) that contains the actual table name, and then submit that SQL text (string) to MySQL.

When you get to that cursor.execute, you need the SQL text to include the table name as part of the string. (It's also likely that the name of the file has to be specified as a literal, and can't be a placeholder, but I'm not sure about that. What I am sure about is the table name.)

---

As a test, try hardcoding the table name "Compounds" and hardcoding the filename into the SQL text...

cursor.execute = (
    "LOAD DATA LOCAL INFILE '/opt/data/compounds/parsed/fullData.csv'"
    " REPLACE"
    " INTO TABLE `Compounds`"