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!

what is the data type of first column of your searches table? — thavan
– thavan, Commented Apr 23, 2012 at 13:16
It is just a normal varchar. Some characters is Norwegian though, so that may cause some problems, perhaps? — olovholm
– olovholm, Commented Apr 23, 2012 at 19:17

thavan · Accepted Answer · 2012-04-23 13:51:00Z

1

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.

answered Apr 23, 2012 at 13:51

thavan

2,48924 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jojo · Accepted Answer · 2012-04-23 18:37:51Z

1

I'm not familiar with MySQLdb but it should be something like this

conn = MySQLdb.connect (unix_socket = '/opt/local/var/run/mysql5/mysqld.sock',host = "localhost", user = "username", passwd= "passwd", db="db",charset="utf-8")

make sure that your Database is also work with utf-8

using pypdbc this would look like

import pyodbc
con = pyodbc.connect('DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=db;UID=user;PWD=blabla',charset='utf8', init_command='SET NAMES UTF8')
cursor = con.cursor()
str=u'''INSERT INTO migdal_hist VALUES("","2011/03/01","0","בלהבלה","0",","0","0")'''
cursor.execute(str.encode('utf-8'))
con.commit()

edited Apr 23, 2012 at 18:37

answered Apr 23, 2012 at 15:42

jojo

3,6291 gold badge29 silver badges21 bronze badges

Comments

airween · Accepted Answer · 2012-04-24 20:22:25Z

0

Perhaps you need to something like this:

mysql = MySQLdb.connect(host = '...', [...] use_unicode = True)
cursor = mysql.cursor()
cursor.execute("SET NAMES 'utf8'")

Let's try it :)

answered Apr 24, 2012 at 20:22

airween

6,3231 gold badge16 silver badges23 bronze badges

Collectives™ on Stack Overflow

MySQL and Python charset error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related