在Python中将数据从MySQL导出到CSV

Question

import mysql.connector

sql = "SELECT r.sensorID, s.area, r.date, r.Time, r.waterLevel, r.redalert FROM sensor s INNER JOIN rainfall r ON s.sensorID = r.sensorID ORDER BY r.date, Time INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/rainDB.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';"

mycursor.execute(sql)

我想在PythonIDE中执行上述代码，将数据导出到CSV文件中。select语句在MySQL工作台中运行良好。但是，在Pycharm中尝试时，我不断遇到以下错误：

File "C:/Users/user/PycharmProjects/pythonProject1/BIrainSensor.py", line 106
    sql = "SELECT r.sensorID, s.area, r.date, r.Time, r.waterLevel, r.redalert FROM sensor s INNER JOIN rainfall r ON s.sensorID = r.sensorID ORDER BY r.date, Time INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/rainDB.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';"
                                                                                                                                                                                                                                                                                                                         ^
SyntaxError: unexpected character after line continuation character

我认为这是由于引号中的反斜杠n（\n）。有没有办法解决这个问题，或者我应该找到另一种方法将数据导出到CSV文件中

Answer 1

您需要添加\（反斜杠）以转义"（因为您使用它来定义查询字符串）和\字符

请参阅Python字符串和字节文本https://docs.python.org/3/reference/lexical_analysis.html#string-and-bytes-literals

例如：

sql = "SELECT r.sensorID, s.area, r.date, r.Time, r.waterLevel, r.redalert FROM sensor s INNER JOIN rainfall r ON s.sensorID = r.sensorID ORDER BY r.date, Time INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/rainDB.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\n'"

甚至更好的是，您可以使用带有前缀字母“r”的三重引号字符串将其标记为原始字符串。这样可以避免转义字符：

sql = r"""SELECT r.sensorID, s.area, r.date, r.Time, r.waterLevel, r.redalert
FROM sensor s
INNER JOIN rainfall r ON s.sensorID = r.sensorID
ORDER BY r.date, Time
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/rainDB.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
"""