Python encoding problem

Question

I have a python script that exports data from a mysql utf-8 table into an textfile. Here is the code that does the job

csvDatei = codecs.open( csvDateiName, "w", "utf-8" )
...
cursor = db.cursor();
sql = "select * from %s.%s;" % (dbAusgang, tabelle)
cursor.execute(sql);
...
daten = cursor.fetchall();
for i in xrange(len(daten)):
    line = '';
    for j in xrange(len(daten[i])):
        line += '"%s";' % unicode(daten[i][j]);
    line = line[:-1];
    line += '\n';
    csvDatei.write(line);
csvDatei.close();

I have also tried this

line += '"%s";' % str(daten[i][j]);

and

line += '"%s";' % daten[i][j];

And now the part I do not understand:

Normally this script should be invoked by a cron job. But when I read a varchar from a table that contains an umlaut like ä,ö or ü the script simply terminates. I checked that by piping the output of the script into a file.

Therefore I tested the script by invoking it manually on the shell by simply typing "python myscript.py" and it runs perfectly fine without any problems.

So my guess is that the problem does not really lie in the script itself but rather in the cron enviroment somehow.

Hopefully anybody of you can give me advice. I'm completly confused.

Any help is appreciated.

---------------- Answer to comment 1:

Thanks for the hint with the locale.

First I wrote "locale" on the standard shell. It gave me the following output:

dhl@srv1093:~$ locale
LANG=de_DE.UTF-8
LC_CTYPE="de_DE.UTF-8"
LC_NUMERIC="de_DE.UTF-8"
LC_TIME="de_DE.UTF-8"
LC_COLLATE="de_DE.UTF-8"
LC_MONETARY="de_DE.UTF-8"
LC_MESSAGES="de_DE.UTF-8"
LC_PAPER="de_DE.UTF-8"
LC_NAME="de_DE.UTF-8"
LC_ADDRESS="de_DE.UTF-8"
LC_TELEPHONE="de_DE.UTF-8"
LC_MEASUREMENT="de_DE.UTF-8"
LC_IDENTIFICATION="de_DE.UTF-8"
LC_ALL=de_DE.UTF-8

Then I edited the cron file with "crontab -e" and added the folloing line

*/1 * * * * locale > /home/user/locale.ouput

The output of this cronjob is:

dhl@srv1093:~$ cat locale.ouput 
LANG=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

So, this might be the problem? How can I fix that?

1. Make sure cron can send you mail and read it, it probably contains a traceback. 2. Check the output of `locale` is the same inside cron. — Tobu, Jun 11 '11 at 19:27

score 1 · Answer 1 · answered Jun 11 '11 at 20:07

1

You decode the lines to unicode with unicode(daten[i][j]). When you give no encoding then Python uses the system default, which is probably ascii when you run the script through cron.

In either way, you must give the actual encoding used by the database. You can use unicode(daten[i][j], dbencoding) instead, or get your database adapter to give you unicode directly.

Btw: There are probably a million tools that generate cvs files from database queries, MySQL has that even built-in. Your code on the other hand is rather fragile because you do no escaping whatsoever.

answered Jun 11 '11 at 20:07

Jochen Ritzel

85
7

Thanks you for the answer. My script works fine, I'm very satisfied with it and I'm not going to change it. The problem seems to lie in the encoding. Also the change to "unicode(daten[i][j], "utf-8");" crashes the script already on the first piece of data. So I will remove it again – Jun 11 '11 at 20:48
@toom: So what does it say? If that line fails your data is not utf-8 encoded. – Jochen Ritzel Jun 11 '11 at 21:19
Okay, so how do I determine the encoding of the database? Afaik this is utf-8, as "show create table tablename" tells me. And why does my script work in a normal shell. That should not be possible either. – Jun 12 '11 at 10:55

score 0 · Answer 2 · answered Jun 11 '11 at 21:23

0

I'm pretty sure that is the problem. MySQL will look at your locale settings to determine the character encoding to return values in. I also know that latin characters with umlauts when encoded in ISO-8859-1 are not valid UTF-8 characters and any decoder will fail if it tries to decode them (and without a locale set your db client module may be defaulting to that). I haven't tried it and I don't know what version of python you're using but googling python locale returned this link: http://docs.python.org/library/locale.html So. I would try

import locale
locale.setlocale(locale.LC_ALL, 'de_DE.UTF-8')

at the very beginning of your script before importing your db connectivity module and see if that works.

answered Jun 11 '11 at 21:23

Rob Marrowstone

101
2

Yeah i suspect something like this too. The db connectivity module probably has a option to set the encoding for the connection too, so you don't need locale. – Jochen Ritzel Jun 11 '11 at 23:22
Thanks for the answer. Setting the local from within the code didn't yield anything it still crashes when it approaches at the umlaut. If MySQL uses uses an encoding which corresponds to the local then it would be POSIX and when running in the normal shell it would be UTF-8. Anyway I always open a connection to the db by mysql.connect( db='', charset="utf8", use_unicode=True, **v.MySQLServer[server] ) – Jun 12 '11 at 07:28

score 0 · Answer 3 · answered Jun 12 '11 at 08:10

0

Okay, I now found out what the problem is. It has nothing to do with the code, well okay that was already clear before but the local language variables are the problem.

In the cron job the encodings are set to POSIX and in the normal SHELL mode the encodings are set to UTF-8. So I changed the encodings all from UTF-8 to POSIX and ran my script. And suprise, the same error occurs as in the cron environment. So now I change the encoding step by step, I mean variable by variable and check whether my script runs or not.

First I changed

export LANG=de_DE.UTF-8

and runing the script the same error remained. Then after that I changed

export LC_CTYPE="de_DE.UTF-8"

and then the script worked absolutly fine. No problems.

So that's the problem. Now how do I change this variable in my cron environment? I already tried in the code

locale.setlocale(locale.LC_CTYPE, 'de_DE.UTF-8')

But that didn't work.

How do I change that?

answered Jun 12 '11 at 08:10

In your crontab file put the environment variables up at the top. `LANG=de_DE.UTF-8` and `LC_CTYPE="de_DE.UTF-8"`. – Keith Jun 12 '11 at 08:34
Mmmh now the language variables are all set to utf-8 but it still crashes when it comes to an umlaut. What else could be the problem? – Jun 12 '11 at 10:47
Are you sure that string in the database is UTF-8? – Keith Jun 12 '11 at 11:14
Well I checked the database and discovered that the character encoding is indeed not utf8 but latin1. I think I have to check this first with a colleague who is responsible for the database. Actually the encoding should be utf8. What I still do not understand is why the script runs in a normal shell and crashes in the cron environment??? This shouldn't happen if the encoding is wrong it should also crash in a normal shell – Jun 12 '11 at 11:27
You can export variables in your crontab itself, just have the entry be `export LC_CTYPE="de_DE.UTF-8"; myscript.py` – Jun 12 '11 at 14:51

Python encoding problem

3 Answers3