0

I want to store an address (type string) into a database field (type VARCHAR). The problem is that sometimes the address has Non-ASCII characters, so I did the next to take them:

in my .py file

import sys
reload(sys)
sys.setdefaultencoding('utf8')

# Other imports...

# The connection with the DB
conn = MySQLdb.connect(host="127.0.0.1", user="myUser", passwd="myPass", db="myDB", charset='utf8', init_command='SET NAMES UTF8')

# More code here...

address2 = address.encode('utf-8')
print "\nTHE ADDRESS TYPE IS -> ", type(address), "\n"
print "\nTHE ADDRESS IS -> ", address, "\n"
print "\nTHE ADDRESS2 IS -> ", address2, "\n"

self.conn.cursor().execute("""INSERT INTO myTable (BLA, BLA, ADDRESS) VALUES (%s, %s, %s)""", (bla, bla, address2))
# The rest of the code...

So when I run the code, I get the following after the prints:

THE ADDRESS TYPE IS ->  <type 'str'>
THE ADDRESS IS -> Francesc Aragó, 2, 07872 Es Caló, España
THE ADDRESS2 IS -> Francesc Aragó, 2, 07872 Es Caló, España

but when I go to my table in the db, and access that field, I get this

Francesc Arag?, 2, 07872 Es Cal?, Espa?a

I've tried changing the address2 for addressin the mysql query, along with many other tries, but I've got nothing so far...

Rick James
  • 135,179
  • 13
  • 127
  • 222
wj127
  • 118
  • 1
  • 12
  • Perhaps utf-8 is not the default character set in your mysql database. if you start a mysql console and type "show variables like 'char%';", what result do you get? – Göran Paues Apr 16 '17 at 15:30
  • What is the collation for the VARCHAR column? Can you alter that column to use a different collation or replace it with an NVARCHAR column? – Tom Blodget Apr 16 '17 at 15:31
  • @GöranPaues : I've run the command, and this is what I got: `character_set_client -> latin1; character_set_connection -> latin1; character_set_database -> latin1; character_set_filesystem -> binary; character_set_results -> latin1; character_set_server -> latin1; character_set_system -> utf8; character_sets_dir -> /usr/share/mysql/charsets/` – wj127 Apr 16 '17 at 15:39
  • @TomBlodget : The collation for the varchar column is this one `ADDRESS varchar(255) DEFAULT ''` I didn't create the table, and altering that column might be a difficult option... – wj127 Apr 16 '17 at 15:45
  • @TomBlodget : But anyway, think like if I could alter the column! What do you mean with NVARCHAR ? sorry, I'm almost new to DataBases... – wj127 Apr 16 '17 at 15:49
  • `NVARCHAR` is not sufficient. – Rick James Apr 16 '17 at 17:18
  • `The init_command should have overridden the output from `SHOW VARIABLES LIKE 'char%'`, so that output is not relevant. – Rick James Apr 16 '17 at 17:19

2 Answers2

1

Your database is using latin1, so a solution for your problem is to use UTF-8 in your MySQL database. See this post on how to convert your existing database to UTF-8: How to convert an entire MySQL database characterset and collation to UTF-8?

To make sure that your future databases also use utf-8, check this post out on how to change that: Change MySQL default character set to UTF-8 in my.cnf?

You could also use latin1 instead of utf-8 in your Python code, but UTF-8 has become a de facto standard so it's best if your database uses that.

Community
  • 1
  • 1
Göran Paues
  • 73
  • 1
  • 8
1

See "question marks" in Trouble with utf8 characters; what I see is not what I stored :

  • The bytes to be stored are not encoded as utf8/utf8mb4. -- Presumably the init_command fixes this.
  • The column in the database is CHARACTER SET utf8 (or utf8mb4). -- This seems to be broken.
  • Also, check that the connection during reading is UTF-8.

If you have further troubles follow "Test the data" in the above link.

Python tips here: http://mysql.rjweb.org/doc.php/charcoll#python

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222