I tried to load a csv file into database using Python:
SQLCMD = "LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv' \
INTO TABLE salaries.salaries \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' \
LINES TERMINATED BY '\\n' \
IGNORE 1 ROWS"
When I run it, I got the error in line 4:
SyntaxError: unexpected character after line continuation character
I know the error is related to \\n
because it's black(I use Jupyter notebook) and other parts are all red.
I think the reason is because the double quote in this part:
OPTIONALLY ENCLOSED BY '"'
This makes Python think the entire part ends here:
"LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv' \
INTO TABLE salaries.salaries \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"
But OPTIONALLY ENCLOSED BY '"'
is a must, I need this part because some columns contain commas.
How can I solve this problem?
CodePudding user response:
This issue is caused by the "
in OPTIONALLY ENCLOSED BY '"'
ending the SQLCMD
string. You could fix this by using a backslash to escape the quote, but it'd be easier to use a triple-quoted string to avoid having to escape the quotes altogether:
SQLCMD = """LOAD DATA INFILE '/Users/v***/MySQLfiles/salaries.csv'
INTO TABLE salaries.salaries
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS"""