Set encoding properly in python script

Question

My MySQL database:

mysql> show create database response;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------------------------------------+
| response | CREATE DATABASE `response` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

Variables:

mysql> SHOW variables LIKE '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0,00 sec)

table:

mysql> show create table autoresponse_config;
| Table | Create Table |
+---------------------+--------------------------------------------------------| autoresponse_config | CREATE TABLE `autoresponse_config` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `address` varchar(255) NOT NULL,
 `enabled` tinyint(1) NOT NULL,
 `changed` datetime NOT NULL,
 `expires` datetime NOT NULL,
 `subject` varchar(255) NOT NULL,
 `message` longtext NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='Response - Autoresponse Configurations' 

Sample row (note the message column):

mysql> select * from autoresponse_config;
+----+-----------------------+---------+---------------------+---------------------+---------+---------------------------------------------------------------------------------------------------------+
| id | address | enabled | changed | expires | subject | message |
+----+-----------------------+---------+---------------------+---------------------+---------+---------------------------------------------------------------------------------------------------------+
| 1 | [email protected] | 1 | 2018年01月25日 20:48:19 | 2018年02月24日 00:00:00 | Urlop6 | ąęśćóóŻÓŻAŁÓŁĆGĘŚLĄJAŹŃżŻ |
+----+-----------------------+---------+---------------------+---------------------+---------+---------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

The question is:

Why in this python script
message variable retrieved from database is

????óó?Ó?A?Ó??G??L?JA????

instead of:

ąęśćóóŻÓŻAŁÓŁĆGĘŚLĄJAŹŃżŻ`

How can I set character set properly in this script?

• lenz: How did you determine what the value of message is, through a debugger or a print statement? Note: I'm too lazy to read through 340 lines of code.
• Rick James: See "question mark" in stackoverflow.com/questions/38363566/… Also, check out Python notes in mysql.rjweb.org/doc.php/charcoll#python

Answer 1

Set the connection character encoding in the python script like this:

connection = backend.connect(
 unicode=True,
 cursorclass=backend.CURSOR_DICT
 )
connection.set_character_set('utf8')

If it's not enough, add also:

cursor = backend.open_cursor(connection)
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')

• Rick James: SET NAMES is the only one needed, and it is needed only once -- just after connecting, not once per open_cursor.