A problem encountered in the morning , Now there is a need , Will upload excel The file is parsed and the parsed field information is saved to mysql database . With pandas analysis , A string of format Method splicing sql, Like this ：
sql = f""" insert into xxx(c1, c2, c3) values ("{
v1}", "{
v2}", "{
v3}"); """
excel Fields in may have values or may not have values , for instance v1 Field , The type is date type , If it's worth it c1 Save date value , If not c1 Just keep it null. One of the problems we are facing now is ,{v1} Handling of outside quotation marks , If not quoted ,v1 If there is a value, an error will be reported when inserting a date , If you use quotation marks ,v1 Insert when there is no value "null" You will also report mistakes. , because c1 Column only accept null Or date .
After thinking for a long time, I didn't think of a good solution in the code , Even thought of using triggers to solve , Later, I consulted my colleagues , I was immediately impressed , It can only be said , What a long long road! ！ My colleague just added a code ：sql.replace('"null"', 'null'), It's so simple and elegant .
