MySQL needs REPLACE and not INSERT OR REPLACE (though it does use INSERT IGNORE)

[HappyChews]

When using csvsql to insert csv files to MySQL database, the REPLACE prefix generates incorrect SQL and fails with error.

csvsql --version csvsql 1.0.5 python3 --version Python 3.7.3 python --version Python 2.7.16

Tested on Debian WSL.

Test file attached but can be anything. test.txt

Commands tried: csvsql --db mysql+mysqlconnector://$dbUser:$dbPass@$dbString:$dbPort/$dbSchema --tables test --unique-constraint Id --create-if-not-exist --prefix REPLACE --insert test.csv

csvsql --db mysql+mysqlconnector://$dbUser:$dbPass@$dbString:$dbPort/$dbSchema --tables test --unique-constraint Id --create-if-not-exist --prefix IGNORE --insert test.csv

When using IGNORE prefix the command finishes without any error, but with REPLACE it throws an ProgrammingError:

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): 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 'REPLACE INTO test (Name, Id, Width) VALUES ('Charles', '1', '1')' at line 1 [SQL: INSERT REPLACE INTO test (Name, Id, Width) VALUES (%(Name)s, %(Id)s, %(Width)s)]

As you can see on the bottom line, I have tried to highlight the error. It should have been only "REPLACE INTO" MySQL Documentation states that the correct syntax for REPLACE INTO does not have INSERT in front. https://dev.mysql.com/doc/refman/8.0/en/replace.html

As the syntax for IGNORE actually is "INSERT IGNORE INTO", this prefix does not throw an error.

[jpmckinney]

I looked into this and it's not terribly obvious how to do it. We use Table from SQLAlchemy, which has an insert method but not a replace method.

The only solution I could find is to switch to using Sessions, but that's quite different from our approach. https://stackoverflow.com/questions/708762/sqlalchemy-insert-or-replace-equivalent