0

My script gets a value from a JSON object and then inserts the value into a MySQL database table. Some values have special characters which appear to be from a foreign language. If I print the value of the variable, the special characters display properly. But when I insert the contents of the variable into the MySQL table, the special characters are removed. I need to preserve them.

import mysql.connector
import json

connection = mysql.connector.connect(host=dbHost,
                                     database=dbName,
                                     user=dbUser,
                                     password=dbPassword)

      with open(json_file_item, encoding="utf-8") as fp:
        line = fp.readline()
        json_item = json.loads(line)
        source_value = json_item['source_value']
        print(source_value)
        cur = connection.cursor()
        cur.execute("""Insert into my_table (col_val_txt) values (%s)""", (source_value))
        connection.commit()

The print displays the following value: Â Optometrist

However when inserted into the database, the A character with the accent on top is removed and is converted to this: Optometrist

Interestingly, if I hard code the value in the insert like so: curr.execute("""Insert into my_table (col_val_txt) values (%s)""", ('Â Optometrist')), I get the desired result, i.e. the entire value gets inserted into the table

How can I preserve the original string while doing the insert using a variable?

Whitey Winn
  • 306
  • 1
  • 6
  • 19
  • Bump. Does anyone have any insight into this? I thought the solution was to change how I open the file, i.e. using latin1 instead of utf-8, but that doesn't appear to help. – Whitey Winn Jul 01 '20 at 14:11
  • See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Jul 01 '20 at 23:28

0 Answers0