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 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

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.