MySQL and Python charset error
Question: I am currently working on formatting data between CSV files and an mySQL
database. I am using the MySQLdb library to manage the connection, but it
seems to be some problems with formatting. I have to admit that I'm not a very
experienced in neither mySQL or Python, but with a pragmatic approach most
have been working out great until now.

    
    
    #!/usr/bin/python
    # -*- coding: utf-8 -*-
    
    import MySQLdb 
    
    QUERY = "SELECT * FROM searches WHERE searchdate BETWEEN '2011-08-08' AND '2011-08-14';"
    conn = MySQLdb.connect (unix_socket = '/opt/local/var/run/mysql5/mysqld.sock',host =      "localhost", user = "username", passwd= "passwd", db="db")
    c = conn.cursor()
    c.execute(QUERY)
    for row in c.fetchall():
        print row
    

This is the script which extracts the records from the database. Later in the
process I want to extract the data from each of the line and format this into
a CSV, but for the moment my problem is that the data printed to screen looks
like this:

    
    
    ('\xc3\xa6nima', ' 1', ' 12782027', ' 35', datetime.date(2011, 8, 13))
    ('\xc3\xa6nima', ' 1', ' 12823616', ' 59', datetime.date(2011, 8, 10))
    ('\xc3\xa6oc', ' 1', ' 13078573', ' 55', datetime.date(2011, 8, 14))
    ('\xc3\xa6re', ' 1', ' 12516300', ' 35', datetime.date(2011, 8, 8))
    ('\xc3\xa6re v\xc3\xa6re deg', ' 1', ' 13145801', ' 59', datetime.date(2011, 8, 13))
    ('\xc3\xa6re v\xc3\xa6re deg og lammet', ' 1', ' 13145801', ' 59', datetime.date(2011, 8, 13))
    ('\xc3\xa6re v\xc3\xa6re jesu navn', ' 1', ' 13136667', ' 59', datetime.date(2011, 8, 11))
    ('\xc3\xa6rlig vuggevise', ' 1', ' 12386933', ' 35', datetime.date(2011, 8, 12))
    ('\xc3\xa6ror aleina', ' 1', ' 12867037', ' 35', datetime.date(2011, 8, 12))
    ('\xc3\xa6sj', ' 1', ' 13130891', ' 59', datetime.date(2011, 8, 8))
    ('\xc3\xa6thenor', ' 1', ' 12555673', ' 35', datetime.date(2011, 8, 10))
    

What I'm now having problems to understand is how I should get the data in a
compatible format. So I guess I want to know how I can access and alter the
charset in the database to UTF-8, and whether I need to rebuild all the data
or if there is an automatic way of dealing with this issue. I would also be
greatfull if anyone could point me in a direction of how I could format the
datatime.date with a built-in function (I know I could regex and rebuild, but
there is probably a more elegant solution).

In advance thank you for your help!


Answer: In your first column, some of the characters are not printable, so it is
converted into hex chars. The last column in a datetime object. Python
provides strftime function to convert it into string.

    
    
    for row in c.fetchall():
        print row[0], row[1], row[2], row[3], row[4].strftime('%Y-%m-%d')
    

will work.

Also, you can write to a file using

    
    
    file.write(",".join((row[0], row[1], row[2], row[3], row[4].strftime('%Y-%m-%d'))))
    

where, file is file object. It will write as comma separated column. Here you
can see the original characters in file when you open it.

