2

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
  • 1
    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, 2011 at 19:27

3 Answers 3

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.

3
  • 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
    – toom
    Jun 11, 2011 at 20:48
  • @toom: So what does it say? If that line fails your data is not utf-8 encoded. Jun 11, 2011 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.
    – toom
    Jun 12, 2011 at 10:55
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.

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. Jun 11, 2011 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] )
    – toom
    Jun 12, 2011 at 7:28
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?

5
  • 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, 2011 at 8: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?
    – toom
    Jun 12, 2011 at 10:47
  • Are you sure that string in the database is UTF-8?
    – Keith
    Jun 12, 2011 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
    – toom
    Jun 12, 2011 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
    – Hoons
    Jun 12, 2011 at 14:51

You must log in to answer this question.