473,287 Members | 1,978 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,287 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 2244
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.