473,375 Members | 1,321 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,375 software developers and data experts.

Dealing with "funny" characters

Hi, I want to store python text strings that characters like "é" "Č"
in a mysql varchar text field. Now my problem is that mysql does not
seem to accept these characters. I'm wondering if there is any way I
can somehow "encode" these characters to appear as normal characters
and then "decode" them when I want to get them out of the database
again?

-Thanks.

Oct 20 '07 #1
3 2249
On Oct 20, 2:13 pm, sophie_newbie <paulgeele...@gmail.comwrote:
Hi, I want to store python text strings that characters like "é" "Č"
in a mysql varchar text field. Now my problem is that mysql does not
seem to accept these characters. I'm wondering if there is any way I
can somehow "encode" these characters to appear as normal characters
and then "decode" them when I want to get them out of the database
again?
Use unicode strings, and use the 'encode' and 'decode' methods. Mysql
has utf8 support for varchars, so that seems a likely encoding to use.
If you're stuck with ascii, you can use 'unicode_escape' if you're
only accessing it from python.

But first read this excellent article on 'funny' characters:
http://www.joelonsoftware.com/articles/Unicode.html

--
Paul Hankin

Oct 20 '07 #2
Gert-Jan wrote:
sophie_newbie schreef:
>Hi, I want to store python text strings that characters like "é" "Č"
in a mysql varchar text field. Now my problem is that mysql does not
seem to accept these characters. I'm wondering if there is any way I
can somehow "encode" these characters to appear as normal characters
and then "decode" them when I want to get them out of the database
again?


It seems you'll have to use Unicode in your program rather than 'plain'
strings.

Before storing an unicode textstring in a database or a file, you must
encode it using an appropriate encoding/codepage, for example:

outputstring = unicodeobject.encode('utf-8')
No, no, that's wrong. MySQL and the Python interface to it understand
Unicode. You don't want to convert data to UTF-8 before putting it in a
database; the database indexing won't work.

Here's how to do it right.

First, tell MySQL, before you create your MySQL tables, that the tables are
to be stored in Unicode:

ALTER database yourdatabasename DEFAULT CHARACTER SET utf8;

You can also do this on a table by table basis, or even for single fields,
but you'll probably get confused if you do.

Then, when you connect to the database in Python, use something like this:

db = MySQLdb.connect(host="localhost",
use_unicode = True, charset = "utf8",
user=username, passwd=password, db=database)

That tells MySQLdb to talk to the database in Unicode, and it tells the database
(via "charset") that you're talking Unicode.

Within Python, you can use Unicode as well. If you have a Unicode text
editor, you can create Python source files in Unicode and have Unicode text
constants in quotes. If you do this, you should put

# -*- coding: UTF-8 -*-

as the first line of Python files. Quoted constants should be written
as

s = u'Test'

rather than

s = 'Test'

Instead of "str()", use "unicode()".

Once everything is set up like this, you can pass Unicode in and out of MySQL
databases freely, and all the SQL commands will work properly on Unicode data.

John Nagle

Oct 20 '07 #3
No, no, that's wrong. MySQL and the Python interface to it understand
Unicode. You don't want to convert data to UTF-8 before putting it in a
database; the database indexing won't work.
I doubt that indexing has anything to do with it whatsoever.
Here's how to do it right.

First, tell MySQL, before you create your MySQL tables, that the
tables are
to be stored in Unicode:

ALTER database yourdatabasename DEFAULT CHARACTER SET utf8;

You can also do this on a table by table basis, or even for single fields,
but you'll probably get confused if you do.

Then, when you connect to the database in Python, use something like
this:

db = MySQLdb.connect(host="localhost",
use_unicode = True, charset = "utf8",
user=username, passwd=password, db=database)

That tells MySQLdb to talk to the database in Unicode, and it tells the
database
(via "charset") that you're talking Unicode.
You confuse unicode with utf-8 here. And while this appears to be
nitpicking, it is important to write this small program and meditate the
better part of an hour in front of it running:

while True:
print "utf-8 is not unicode"
You continue to make that error below, so I snip that.

The important part is this: unicode is a standard that aims to provide a
codepoint for each and every character that humankind has invented. And
python unicode objects can also represent all characters one can imagine.

However, unicode as such is an abstraction. Harddisks, network sockets,
databases and the like don't deal with abstractions though - the eat
bytes. Which makes it necessary to encode unicode objects to
byte-strings when serializing them. Thus there are the thingies called
encodings: latin1 for most characters used in westen europe for example.
But it is limited to 256 characters (actually, even less), chinese or
russian customers won't get too happy with them.

So some encodings are defined that are capable of encoding _ALL_ unicode
codepoints. Either by being larger than one byte for each character. Or
by providing escape-mechanisms. The former are e.g. UCS4 (4 bytes per
character), the most important member of the latter is utf-8. Which uses
ascii + escapes to encode all codepoints.

Now what does that mean in python?

First of all, the coding:-declaration: it tells python which encoding to
use when dealing with unicode-literals, which are the

u"something"

thingies. If you use a coding of latin1, that means that the text

u"ö"

is expected to be one byte long, with the proper value that depicts the
german umlaut o in latin1. Which is 0xf6.

If coding: is set to utf-8, the same string has to consist not of one,
but of two bytes: 0xc3 0xb6.

So, when editing files that are supposed to contain "funny" characters,
you have to

- set your editor to save the file in an appropriate encoding

- specify the same encoding in the coding:-declaration

Regarding databases: they store bytes. Mostly. Some allow to store
unicode by means of one of the fixed-size-encodings, but you pay a
storage-size penalty for that.

So - you we're right when you said that one can change the encoding a db
uses, on several levels even.

But that's not all that is to it. Another thing is the encoding the
CONNECTION expects byte-strings to be passed, and will use to render
returned strings in. The conversion from and to the used storage
encoding is done automagically.

It is for example perfectly legal (and unfortunately happens
involuntarily) to have a database that internally uses utf-8 as storage,
potentially being able to store all possible codepoints.

But due to e.g. environmental settings, opened connections will deliver
the contents in e.g. latin1. Which of course will lead to problems if
you try to return data from the table with the topmost chines first names.

So you can alter the encoding the connection delivers and expects
byte-strings in. In mysql, this can be done explcit using

cursor.execute("set names <encoding>")

Or - as you said - as part of a connection-string.

db = MySQLdb.connect(host="localhost",
use_unicode = True, charset = "utf8",
user=username, passwd=password, db=database)
But there is more to it. If the DB-API supports it, then the API itself
will decode the returned strings, using the specified encoding, so that
the user will only deal with "real" unicode-objects, greatly reducing
the risk of mixing byte-strings with unicode-objects. That's what the
use_unicod-parameter is for: it makes the API accept and deliver
unicod-objects. But it would do so even if the charset-parameter was
"latin1". Which makes me repeat the lesson from the beginning:

while True:
print "utf-8 is not unicode"
Diez
Oct 20 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Martin Goldman | last post by:
Hello all, I've been struggling for a few days with the question of how to convert "smart" (curly) quotes into straight quotes. I tried playing with the htmlentities() function, but all that is...
7
by: Barry | last post by:
Hi all, I've noticed a strange error on my website. When I print a capital letter P with a dot above, using & #7766; it appears correctly, but when I use P& #0775 it doesn't. The following...
24
by: hjbortol | last post by:
Hi! Is the expression "a >= b" equivalent to "a - b >= 0" in C/C++? Is this equivalence an IEEE/ANSI rule? Or is this machine/compiler dependent? Any references are welcome! Thanks in...
17
by: G Fernandes | last post by:
Can someone explain what is meant by "directory order" in the questoin for K and R 2 exercise 5-16? I can't seem to find a solution for this exercise on the main site where clc goers have posted...
4
by: wob | last post by:
Many thanks for those who responded to my question of "putting greek char into C string". In searching for an solution, I noticed that there are more than one version of "Extended ASCII...
3
by: Mange | last post by:
I'm using WebRequest class to access a PHP script, but keep getting a 400 error from the server when calling the GetResponse() method. I have added the url to the script in the bypass list in...
14
by: arnuld | last post by:
i have slightly modified the programme from section 1.5.1 which takes the input frm keyboard and then prints that to the terminal. it just does not run and i am unable to understand the error...
92
by: ureuffyrtu955 | last post by:
Python is a good programming language, but "Python" is not a good name. First, python also means snake, Monty Python. If we search "python" in google, emule, many results are not programming...
18
by: Jeff Bigham | last post by:
Hi, I'm getting an "Access is denied" error in IE when I try to focus an input box with node.focus() My understanding is that should only happen when the domains are different of the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.