Creating an insert query with escape characters in MySQL/Python

Question

I am writing a script that transfers data from an Access database to a MySQL database. I'm am trying to generate a query similar to below:

INSERT into customers (firstname, lastname) value ('Charlie', "D'Amelio");

However, MySQL does not like double quotes like those listed above. I wrote a clunky function to try to replace the ' in D'Amelio with a '. Here is the whole function to create the SQL statement below:

def dictionary_output(dict):

    output = "INSERT into lefm_customers "
    fields = "(id, "
    vl =  "('" + id_gen() + "', "
    for key in dict.keys():
        # print(dict[key])
        if str(dict[key]) == 'None' or str(dict[key]) == "":
            pass
        elif "'" in str(dict[key]):
            fields = fields + str(key) + ", "
            string = ""
            for character in string:
                if character == "'":
                    string += r"\'"
                else:
                    string += character
            vl = "'" + string + "', "
        else:
            fields = fields + str(key) + ", "
            vl = vl + "'" + str(dict[key]) + "', "
            
    fields = fields[:-2] + ")"
    vl = vl[:-2] + ");"
    return "INSERT into lefm_customers " + fields + " values " + vl

Currently it is just ignoring that value altogether. Any tips on what to replace ' with or how I can improve my function? Thank you!

Use a prepared statement with parameters, then you don't have to worry about escaping. — Barmar, Aug 31 '21 at 18:27
You must double quote char in the value. `INSERT into customers (firstname, lastname) value ('Charlie', 'D''Amelio');` — Akina, Aug 31 '21 at 18:43
@nbk *Any tips on what to replace ' with* - doubled quote char. *or how I can improve my function?* - use prepared statements. — Akina, Aug 31 '21 at 18:54
Use the techniques described in [this Q&A](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — snakecharmerb, Aug 31 '21 at 19:39

score 0 · Answer 1 · edited May 20 '22 at 14:17

This fixed it:

def dictionary_output(dict):
lst = []
output = "INSERT into lefm_customers "
fields = "(id, "
vl =  "('" + id_gen() + "', "
for key in dict.keys():
    # print(dict[key])
    if str(dict[key]) == 'None' or str(dict[key]) == "":
        pass
    
    else:
        fields = fields + str(key) + ", "
        vl = vl + "%s, "
        lst.append(dict[key])
fields = fields[:-2] + ")"
vl = vl[:-2] + ");"
return ("INSERT into lefm_customers " + fields + " values " + vl, lst)



for name in access_dict:
if str(name) not in mysql_dict.keys():
    try:
        statement = dictionary_output(access_dict[name])
        mysql_cursor.execute(statement[0], statement[1]) 
        print('attempting ' + str(name))
        db_connection.commit()
        print("Success!")
    except:
        print('something went wrong')

score -2 · Answer 2 · answered Aug 31 '21 at 18:33

-2

You can just call Python's replace. Example in the terminal:

>>> s = "D'Amelio"
>>> s.replace("'", "'")
"D'Amelio"

In this case the first argument is the single quote ' and the second is the acute accent '.

answered Aug 31 '21 at 18:33

bwdm

793
7
17

Creating an insert query with escape characters in MySQL/Python

2 Answers2