Python 3 + Mysql: Incorrect string value '\xF0\x9F\x85\x97\xF0\x9F...'

Question

I have found other questions/answers about "Incorrect string value" here on stack but none of the answers are working so maybe something is different about my case.

try:
    self.cnx = mysql.connector.connect(host='localhost', user='emails', password='***',
                                               database='extractor', raise_on_warnings=True)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
self.sql = self.cnx.cursor()

biography = str(row[8])
self.sql.execute("""insert into emails (biography)
                            values(%s)""",
                                         (biography,))

where biography is a utf8mb4_general_ci TEXT column of:

< Living the > Azofra & Clifford Travel Food Fashion

I get:

mysql.connector.errors.DataError: 1366 (22007): Incorrect string value: '\xF0\x9F\x85\x97\xF0\x9F...' for column `extractor`.`emails`.`biography` at row 1

Output of show create table emails:

show create table emails;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emails | CREATE TABLE `emails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ufeff_user_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `username` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `full_name` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT NULL,
  `follower_count` int(10) DEFAULT NULL,
  `following_count` int(10) DEFAULT NULL,
  `media_count` int(10) DEFAULT NULL,
  `biography` text CHARACTER SET utf8mb4 DEFAULT NULL,
  `has_profile_pic` tinyint(1) DEFAULT NULL,
  `external_url` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `public_email` varchar(320) CHARACTER SET utf8 NOT NULL,
  `contact_phone_number` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `address_street` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `is_business` tinyint(1) DEFAULT NULL,
  `engagement` int(10) DEFAULT NULL,
  `recent_post_date` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `category` varchar(75) COLLATE utf8_bin DEFAULT NULL,
  `avg_likes` int(10) DEFAULT NULL,
  `avg_comments` int(10) DEFAULT NULL,
  `business_join_date` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `business_count` int(5) DEFAULT NULL,
  `business_ads` tinyint(1) DEFAULT NULL,
  `country_code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `emailscol` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `city_name` varchar(75) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`public_email`)
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

`'< Living the > Azofra & Clifford Travel Food Fashion'` – xendi Oct 24 '19 at 20:38 — xendi, Oct 24 '19 at 20:38
Let's see the connection parameters in python. – Rick James Oct 25 '19 at 00:15 — Rick James, Oct 25 '19 at 00:15

Eugene · Answer 1 · 2019-10-28T15:46:12.477

6

I'm assuming the column emails.biography is of type VARCHAR, and that the CHARSET for the table emails is utf8mb4. If not, you'd want to execute:

 ALTER TABLE `emails` CONVERT TO CHARACTER SET utf8mb4;

Then, if that doesn't solve the issue, try executing the following directly after you create your MySQL cursor in Python (assuming self.sql is your cursor):

 self.sql.execute('SET NAMES utf8mb4;')
 self.sql.execute('SET CHARACTER SET utf8mb4;')
 self.sql.execute('SET character_set_connection=utf8mb4;')

And if that doesn't work, try setting the character set immediately after you create the MySQL connection in Python, for example:

 self.connection.set_character_set('utf8mb4')

If you're still out of luck at this point, we can debug further :)

Update:

Try:

ALTER TABLE `emails` CONVERT TO CHARACTER SET utf8;
ALTER TABLE `emails` CHANGE COLUMN `biography` TEXT CHARACTER SET 'utf8';

Note that utf8mb4_general_ci is the collation of the table, not the encoding. Ideally, you should be using COLLATE utf8_unicode_ci.

edited Oct 28 '19 at 15:46

answered Oct 24 '19 at 22:05

Eugene

1,539
12
20

It is a uft8bm4 field already of type TEXT. Question updated. – xendi Oct 25 '19 at 06:03
`self.cnx.set_character_set('utf8mb4')` produces: `AttributeError: 'MySQLConnection' object has no attribute 'set_character_set'` so I had to do: `self.cnx.set_charset_collation('utf8bm4')` which produces: `Character set 'utf8bm4' unsupported.`. I also tried `utf8bm4_general_ci` with same result. – xendi Oct 25 '19 at 06:17
Try changing all `utf8mb4` to `utf8`. `utf8mb4` was introduced in MySQL version 5.5, so presumably you're using a rather old MySQL installation. Also, I have updated my answer. – Eugene Oct 28 '19 at 15:25
Are you able to paste your `my.cnf`? You probably should update it if you haven't already. See here: https://stackoverflow.com/a/20429481/2178980. I could do it for you if you send me what you have currently. – Eugene Oct 28 '19 at 15:32
Also, can you paste the result of `SHOW CREATE TABLE emails;`? – Eugene Oct 28 '19 at 15:38
@Eugene `show create table emails` https://hastebin.com/nefemegugu.sql – xendi Nov 05 '19 at 19:14
@Eugene also it gives syntax error for the `CHANGE COLUMN` command. – xendi Nov 05 '19 at 19:27
I added the lines to `my.cnf` from https://stackoverflow.com/a/20429481/2178980 . Still getting the same error when I run my script. – xendi Nov 05 '19 at 20:21
You still have `utf8` stuff. Try this https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4 – Eugene Nov 06 '19 at 16:02
2

Great! This saved my day. I have been trying to put Emojis into the MySQL using Python, but it didn't work. Second solution (of executing `SET names utf8mb4` and other queries) worked for me properly. By the way, this should be accepted answer. I don't know why it isn't. – Coder Amogh Sep 05 '20 at 11:20

Python 3 + Mysql: Incorrect string value '\xF0\x9F\x85\x97\xF0\x9F...'

1 Answers1