Feb-24-2017, 07:28 AM
Thank you for all the replies so far!
I finally figured out what problem was and how to solve it. I write this self-reply post to share my solution, and hope it be helpful to guys who have similar problem with me.
I finally figured out what problem was and how to solve it. I write this self-reply post to share my solution, and hope it be helpful to guys who have similar problem with me.
- problem
The reason why python can not correctly retrieve records from mysql is because my mysql database was not properly configured for utf-8 characters, but used default latin1 encoding. (I still don't know why one of my PHP program can write and read utf-8 character to/from the problematic database without any problems, it is mysterious! Anyway...)
- solution
mysql config: edit my.cnf
Quote:[mysqld]
restart mysql: /etc/init.d/mysql restart
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysqlold_passwords=1
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
[mysql]
default-character-set = utf8
[mysqldump]
default-character-set = utf8
To confirm the above configs works well, connect mysql: mysql -u user -p database_name
Quote:mysql> show variables like 'char%';
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/
mysql> show variables like 'coll%';
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci
Config phpmyconfig ( if necessary)
edit config.inc.php
Quote:$cfg['DefaultLang'] = 'utf-8';
reload apache: # /etc/rc.d/init.d/apache restart
$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';
$cfg['FilterLanguages'] = '';
$cfg['DefaultCharset'] = 'utf-8';
$cfg['AllowAnywhereRecoding'] = TRUE;
For old database which was not correctly configured, modify it by mysqldump. Dump it
Quote:mysqldump -u user -p database_name > database.sql
check database.sql, the utf-8 characters should be garbled. Dump the database again by specifying latin1 coding
Quote:# mysqldump --default-character-set=latin1 -u root -p database_name > database.dump
check database.dump, the garbled characters should have gone. :-)
Then edit new dumped database.dump
Quote:modify line 9, from
/*!40101 SET NAMES latin1
to
/*!40101 SET NAMES utf8
Restore the database by
Quote:mysql -u user -p database_name < database.dump
Restart mysql: /etc/init.d/mysql restart
- PHP
Add the below code right after "mysql_select_db"
Quote:mysql_set_charset('utf8');
- python
Add the below code before query
Quote:cur.execute("SET NAMES utf8")