1

I have a python code which adds rows to the MySQL database which runs on the same machine. The code is as follows:

import mysql.connector

config = {
  'user': 'root',
  'password': 'a$$enjoyer',
  'host': 'localhost',
  'database': 'db',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)
con = cnx.cursor()

Now, I have a table which has a MEDIUMTEXT column. The issue is that whenever I try to insert texts with emojis (yes, I need them), I get the error like: 1366 (HY000): Incorrect string value: '\xF0\x9F\xA5\xB6' for column 'epictext' at row 1.

The code is as follows:

con.execute('INSERT INTO nice VALUES(%s,%s,%s)', [1,2,''])

I have found a few questions here which addressed this error like, for example this one, but they didn't fix the issue.

The weird thing is that simply running INSERT INTO nice VALUES(1,2,''); directly in MySql console works fine. Moreover adding the 'collation': 'utf8mb4_general_ci' to config fixes the issue entirely, but when I last tried doing this, this line was not necessary, and I would really like to avoid using it if it is possible.

user9102437
  • 600
  • 1
  • 10
  • 24
  • You could try to [set the encoding of the python script](https://stackoverflow.com/questions/6289474/working-with-utf-8-encoding-in-python-source) to see if that works. Also lol at your db password – IsThisJavascript Jan 03 '23 at 10:35

1 Answers1

1

You need to specify 'charset' and 'collation' like below in your config

config = {
  'user': 'root',
  'password': 'a$$enjoyer',
  'host': 'localhost',
  'database': 'db',
  'raise_on_warnings': True,
  'charset': 'utf8mb4',
  'collation': 'utf8mb4_unicode_ci'
}

cnx = mysql.connector.connect(**config)

hope it helps

Joe S
  • 47
  • 2
  • Thanks, but this is exactly what I have described in the question. I know that this is the solution. The question is rather how to make it so that I don't have to add this line. – user9102437 Jan 03 '23 at 14:53