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?

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
– lenz, Commented Jan 26, 2018 at 7:48
See "question mark" in stackoverflow.com/questions/38363566/… Also, check out Python notes in mysql.rjweb.org/doc.php/charcoll#python — Rick James
– Rick James, Commented Jan 29, 2018 at 21:39

takeshin · Accepted Answer · 2018-01-26 09:21:40Z

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, 2018 at 9:21

takeshin

51k32 gold badges125 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Over a year ago

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

Collectives™ on Stack Overflow

Set encoding properly in python script

The question is:

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The question is:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related