1

I'm trying to add new entries to a (MySQL) Database using manipulation of some data in python.

I'm basically parsing the names of some files into a given column of a DB's table. The code works fine and the names are added to the DB.

Some files contain 'special characters' that are part of the German language, such as 'ü', 'ä', 'ö' (known as umlaut). The words with these special character are inserted as well and can be perfectly observed in the tables after the code has been executed (both in phpMyAdmin/terminal).

Problem: these words with these special characters, that have been successfully entered are not appearing in the search when I search specifically for them. I get the message an empty set -

mysql> SELECT * from table1 where word='Künstler';  
Empty set (0.00 sec)

same goes to when using the search phpMyAdmin.

But, say I search for the word by it's id, find it and replace the word manually (rewrite 'ü' inside the programm) then it gets recognized in the search!

My assumption is that this lies on Python's Unicode. Therefore, I tried this in python:

i = 'Künstler'
word = i.encode('utf-8')

produced an error message:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xcc in position 10: ordinal not in range(128)

replacing the 'decode' function instead of encode didn't produce an error but it didn't solve the problem in the DB.

Does anyone know how to solve this problem?

Also, all my columns are defined as collation = utf8_general_ci

The column of the 'words' in the database is defined as VARCHAR(128) because some words are very long. I tried to change it to 8bits but that also didn't work.
I'm using Python 2.7.11

*I believe this question is different than the other questions that I've seen here because it involves Python and the others didn't.

EDIT: after my question is tagged over and over as duplicate (although it isn't) I see I have to make some things clear. The words with 'ü', 'ä', 'ö' are inserted CORRECTLY into the table. They appear CORRECTLY when showing the entries. Only when I search for them specifically they don't appear.

Francisco
  • 10,918
  • 6
  • 34
  • 45
Apython
  • 453
  • 6
  • 19
  • This question is NOT a duplicate. And the link that is claimed to have the answer mentioned above (What is the best MySQL collation for German language) is not giving a proper answer! because the problem is not changing uff8_general_ci to utf8_bin. The problem is probably in PYTHON! please treat each question individually, don't generalize and read my question carefully! – Apython Oct 23 '16 at 18:54
  • I do. Shall I blow an hour on this one too like the Turk one here http://stackoverflow.com/questions/40206142 or will you be blind also to all that is said (hugs) – Drew Oct 23 '16 at 18:58
  • I tried the links offered by @Drew. [link](http://stackoverflow.com/questions/2344118/utf-8-general-bin-unicode), [link](http://stackoverflow.com/questions/5526169/what-is-the-best-mysql-collation-for-german-language) and [link](http://stackoverflow.com/questions/40206142/how-to-add-turkish-character-to-mysql-without-char-transformationhttp://stackoverflow.com/questions/40206142/how-to-add-turkish-character-to-mysql-without-char-transformation) did't work! perhaps the UNICODE of str should be edited in python before inserting to the DB? – Apython Oct 24 '16 at 05:05

0 Answers0