Python Forum

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. 

• 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]     
  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

  restart mysql: /etc/init.d/mysql restart
  
  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';
  $cfg['DefaultConnectionCollation'] = 'utf8_general_ci';
  $cfg['FilterLanguages'] = '';
  $cfg['DefaultCharset'] = 'utf-8';
  $cfg['AllowAnywhereRecoding'] = TRUE;

  reload apache: # /etc/rc.d/init.d/apache restart
  
  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")

DONE!

That's like a rubik's cube procedure!
Glad you figured it out

(Feb-24-2017, 11:24 AM)Larz60+ Wrote: [ -> ]That's like a rubik's cube procedure!
Glad you figured it out

Thank you!