I want to create a SQL file with python to be executed (later) by a mysql client. How can I escape the queries string correctly?

While working on a database, I would use the connections statement function - but how can I use them for creating escaped queries in sql files?

Edit: this is an example:

from datetime import datetime

now = datetime.now().strftime("%Y%m%d%H%M%S")
user = 'johndoe'
logentry = "DELETE FROM test WHERE id='5';"

with open('script.sql', 'w') as script:
    eventlog = f"INSERT INTO eventlog "\
               f"(user, timestamp, sqlstatement) VALUES "\
               f"('{user}','{now}','{logentry}');"
    script.writelines(eventlog   '\n')

It creates a file with the - invalid - statement: INSERT INTO eventlog (user, timestamp, sqlstatement) VALUES ('johndoe','20220810145227','DELETE FROM test WHERE id='5';');

As you can see, the " ' " aren't escaped - how can I get the string escaped?

CodePudding user response：

The Python connector has a filter function for escaping quotes and other characters that need to be escaped.

Assuming cnx is a connection to your database opened by the Python Connector for MySQL:

logentry = cnx.converter.escape("DELETE FROM test WHERE id='5';")

It needs a connection to your database, because the escaping has to know about your database server's character set.

CodePudding user response：

Since Python strings are in unicode, first you need to set the correct character set in your sql file:

SET NAMES utf8mb4.

For escaping your unicode SQL statement you have two options:

1. The easy way - establish a connection to your database server and use driver escape function, e.g.

   import mariadb conn= mariadb.connect(host=localhost, .....)

   escaped_eventlog= conn.escape_string(eventlog)

In PyMySQL it is the literal method.

2. If you don't want to use a database driver or if you can't connect to a database write your own method for escaping, with the rules described in section Escape Sequences of MariaDB's string literal documentation