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@example.com |       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?

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. — lenz, Jan 26 '18 at 07:48
See "question mark" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored Also, check out Python notes in http://mysql.rjweb.org/doc.php/charcoll#python — Rick James, Jan 29 '18 at 21:39

score 1 · Accepted Answer · answered Jan 26 '18 at 09:21

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;')

answered Jan 26 '18 at 09:21

takeshin

49,108
32
120
164

1

`SET NAMES` is the only one needed, and it is needed only once -- just after connecting, not once per `open_cursor`. – Rick James Jan 29 '18 at 21:40

Set encoding properly in python script

The question is:

1 Answers1