Re: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Hi John,
Thanks for the suggestions: I have finally been able to get it
working :)
In anyone else runs into the same problem, here is some example code
that works in Python 2.4+:
= Begin Example ==
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import sys
def main():
import MySQLdb, getpass
admin = raw_input("Database admin: ")
pw = getpass.getpass("Password: ")
db = MySQLdb.connect(use_unicode=True, charset = "utf8",
user=admin, passwd=pw)
cursor = db.cursor()
try:
cursor.execute("DROP DATABASE IF EXISTS unicode_test;")
cursor.execute("CREATE DATABASE unicode_test DEFAULT CHARACTER
SET utf8;")
except:
print ""
cursor.execute('''
CREATE TABLE `unicode_test`.`test` (
`id` SMALLINT unsigned NOT NULL AUTO_INCREMENT,
`name`VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`), INDEX (`id`)
) DEFAULT CHARSET=utf8;''')
cursor.execute("INSERT INTO `unicode_test`.`test` VALUES (NULL,
'%s');" % u"Ångström")
# Test 1
print "Just printing: %s" % 'Ångström'
# Test 2
cursor.execute("SELECT name FROM unicode_test.test;")
print "From database: %s" % cursor.fetchone()[0]
# Test 3 (Manual)
print 'To verify manually: mysql -u %s -p -e "SELECT name FROM
unicode_test.test"' % admin
if __name__ == '__main__':
sys.exit(main())
= End Example
Thanks all for taking the time to help!
Best,
Keith
--
http://mail.python.org/mailman/listinfo/python-list
Re: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Keith Hughitt wrote:
Hi all,
I ran into a problem recently when trying to add support for earlier
versions of Python (2.4 and 2.5) to some database related code which
uses MySQLdb, and was wondering if anyone has any suggestions.
With later versions of Python (2.6), inserting Unicode is very simple,
e.g.:
# -*- coding: utf-8 -*-
...
cursor.execute('''INSERT INTO `table` VALUES (0,
'Ångström'),...''')
When the same code is run on earlier versions, however, the results is
either garbled text (e.g. "Ã or "?" instead of "Å" in Python 2.5), or
an exception being thrown (Python 2.4):
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in
position 60: ordinal not in range(128)
So far I've tried a number of different things, including:
1. Using Unicode strings (e.g. u"\u212B")
2. Manually specifying the encoding using sys.setdefaultencoding
('utf-8')
3. Manually enabling Unicode support in MySQLdb
(use_unicode=False, charset = "utf8")
No, that's backwards. Try:
db = MySQLdb.connect(host="localhost",
use_unicode = True, charset = "utf8",
user=username, passwd=password, db=database)
"use_unicode" means that you want MySQLdb to accept and return
Unicode strings. "charset="utf8" means you want MySQLdb to
negotiate with the server to use UTF8 on the socket connecting
it to the database. This works fine in Python 2.4 and 2.5.
Returned strings will be in Unicode.
At the database end, you have to make sure that 1) MySQL was
built with Unicode support (it usually is), 2) the database
fields of interest are in Unicode. I suggest
ALTER DATABASE dbname DEFAULT CHARACTER SET utf8;
before doing any CREATE TABLE operations. Then strings
will be UTF8 in the database.
Read this: http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html
It all works quite well.
John Nagle
--
http://mail.python.org/mailman/listinfo/python-list
Re: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Hello,
Thanks for the suggestions and information Diez!
On Nov 18, 9:38 am, "Diez B. Roggisch" wrote:
>
> You are aware that the coding-declaration only affects unicode-literals (the
> ones like u"i'm unicode")? So the above insert-statement is *not* unicode,
> it's a byte-string in whatever encoding your editor happens to save the
> file in.
Thanks. I didn't know that, but that is helpful.
>
> And that's point two: make sure your editor reads and writes the file in the
> same encoding you specified in the comment in the beginning.
That is taken care of: the files are opened/saved as UTF-8.
>
> Makes sense if the execute tries to encode to unicode first - as you didn't
> give it a unicode-object.
>
In Python 2.6 where I originally developed the code, it wasn't
necessary to explicitly specify the type of
some text: it was basically handled for you. It does make sense
though.
>
>
> > So far I've tried a number of different things, including:
>
> > 1. Using Unicode strings (e.g. u"\u212B")
>
> > 2. Manually specifying the encoding using sys.setdefaultencoding
> > ('utf-8')
>
> > 3. Manually enabling Unicode support in MySQLdb
> > (use_unicode=False, charset = "utf8")
>
> You *disabled* unicode here! Unicode is NOT utf-8!!!
Oops. It was enabled when I ran it, I just copied the above text from
somewhere else and forgot to change it. I am aware that Unicode does
not equal
utf-8, but utf-8 is a Unicode encoding, right?
>
> http://www.joelonsoftware.com/articles/Unicode.html
Thanks!
>
> Try the above, and better yet provide self-contained examples that show the
> behavior.
>
> Diez
Still no luck. I also tried using double-quotes instead of single-
quotes around the relevant strings (as suggested over email by
ThreaderSlash), however, that did
not work for me.
Here is a small example of what I'm trying to do. Notice that if you
run it in Python 2.5-2.6, everything works fine. It is only in Python
2.4 that the
below example doesn't work.
= Begin Example ==
#!/usr/bin/env python
#-*- coding:utf-8 -*-
import sys
def main():
import MySQLdb, getpass
admin = raw_input("Database admin: ")
pw = getpass.getpass("Password: ")
db = MySQLdb.connect(user=admin, passwd=pw)
cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS unicode_test;")
cursor.execute('''
CREATE TABLE `unicode_test`.`test` (
`id` SMALLINT unsigned NOT NULL AUTO_INCREMENT,
`name`VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`), INDEX (`id`)
) DEFAULT CHARSET=utf8;''')
cursor.execute('''
INSERT INTO `unicode_test`.`test` VALUES
(NULL, 'Ångström');
''')
# Test 1
print "Just printing: %s" % 'Ångström'
# Test 2
cursor.execute("SELECT name FROM unicode_test.test;")
print "From database: %s" % cursor.fetchone()[0].decode('utf-8')
# Test 3 (Manual)
print 'To verify manually: mysql -u %s -p -e "SELECT name FROM
unicode_test.test"' % admin
if __name__ == '__main__':
sys.exit(main())
= End Example
Any suggestions?
Thanks!
Keith
--
http://mail.python.org/mailman/listinfo/python-list
Solved: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
-- Forwarded message --
From: Keith Hughitt
To: python-list@python.org
Date: Wed, 18 Nov 2009 06:09:11 -0800 (PST)
Subject: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Hi all,
I ran into a problem recently when trying to add support for earlier
versions of Python (2.4 and 2.5) to some database related code which
uses MySQLdb, and was wondering if anyone has any suggestions.
With later versions of Python (2.6), inserting Unicode is very simple,
e.g.:
# -*- coding: utf-8 -*-
...
cursor.execute('''INSERT INTO `table` VALUES (0,
'Ångström'),...''')
When the same code is run on earlier versions, however, the results is
either garbled text (e.g. "Ã or "?" instead of "Å" in Python 2.5), or
an exception being thrown (Python 2.4):
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in
position 60: ordinal not in range(128)
So far I've tried a number of different things, including:
1. Using Unicode strings (e.g. u"\u212B")
2. Manually specifying the encoding using sys.setdefaultencoding
('utf-8')
3. Manually enabling Unicode support in MySQLdb
(use_unicode=False, charset = "utf8")
...but no combination of any of the above resulted in proper database
content.
To be certain that the issue was related to Python/MySQLdb and not
MySQL itself, I manually inserted the text and it worked just fine.
Furthermore, when working in a Python console, both print "Å" and
print u"\u212B" display the correct output.
Any ideas? The versions of the MySQLdb adapter tested were 1.2.1
(Python 2.4), and 1.2.2-10 (Python 2.5).
Thanks!
Keith
Hello Keithm,
Here is your answer...
I run on Python2.5, this syntax works fine for me -- a piece of my code:
getMaxID_Query = """SELECT MAX(customerID)
FROM customer;"""
self.cursorMySQL.execute(getMaxID_Query)
Try to use: """ instead of ''' . It will solve your problem.
ThreaderSlash
--
http://mail.python.org/mailman/listinfo/python-list
Re: Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Keith Hughitt wrote:
> Hi all,
>
> I ran into a problem recently when trying to add support for earlier
> versions of Python (2.4 and 2.5) to some database related code which
> uses MySQLdb, and was wondering if anyone has any suggestions.
>
> With later versions of Python (2.6), inserting Unicode is very simple,
> e.g.:
>
> # -*- coding: utf-8 -*-
> ...
> cursor.execute('''INSERT INTO `table` VALUES (0,
> 'Ångström'),...''')
You are aware that the coding-declaration only affects unicode-literals (the
ones like u"i'm unicode")? So the above insert-statement is *not* unicode,
it's a byte-string in whatever encoding your editor happens to save the
file in.
And that's point two: make sure your editor reads and writes the file in the
same encoding you specified in the comment in the beginning.
>
> When the same code is run on earlier versions, however, the results is
> either garbled text (e.g. "Ã or "?" instead of "Å" in Python 2.5), or
> an exception being thrown (Python 2.4):
>
> UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in
> position 60: ordinal not in range(128)
Makes sense if the execute tries to encode to unicode first - as you didn't
give it a unicode-object.
>
> So far I've tried a number of different things, including:
>
> 1. Using Unicode strings (e.g. u"\u212B")
>
> 2. Manually specifying the encoding using sys.setdefaultencoding
> ('utf-8')
>
> 3. Manually enabling Unicode support in MySQLdb
> (use_unicode=False, charset = "utf8")
You *disabled* unicode here! Unicode is NOT utf-8!!!
http://www.joelonsoftware.com/articles/Unicode.html
>
> ...but no combination of any of the above resulted in proper database
> content.
>
> To be certain that the issue was related to Python/MySQLdb and not
> MySQL itself, I manually inserted the text and it worked just fine.
> Furthermore, when working in a Python console, both print "Å" and
> print u"\u212B" display the correct output.
>
> Any ideas? The versions of the MySQLdb adapter tested were 1.2.1
> (Python 2.4), and 1.2.2-10 (Python 2.5).
Try the above, and better yet provide self-contained examples that show the
behavior.
Diez
--
http://mail.python.org/mailman/listinfo/python-list
Inserting Unicode text with MySQLdb in Python 2.4-2.5?
Hi all,
I ran into a problem recently when trying to add support for earlier
versions of Python (2.4 and 2.5) to some database related code which
uses MySQLdb, and was wondering if anyone has any suggestions.
With later versions of Python (2.6), inserting Unicode is very simple,
e.g.:
# -*- coding: utf-8 -*-
...
cursor.execute('''INSERT INTO `table` VALUES (0,
'Ångström'),...''')
When the same code is run on earlier versions, however, the results is
either garbled text (e.g. "Ã or "?" instead of "Å" in Python 2.5), or
an exception being thrown (Python 2.4):
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in
position 60: ordinal not in range(128)
So far I've tried a number of different things, including:
1. Using Unicode strings (e.g. u"\u212B")
2. Manually specifying the encoding using sys.setdefaultencoding
('utf-8')
3. Manually enabling Unicode support in MySQLdb
(use_unicode=False, charset = "utf8")
...but no combination of any of the above resulted in proper database
content.
To be certain that the issue was related to Python/MySQLdb and not
MySQL itself, I manually inserted the text and it worked just fine.
Furthermore, when working in a Python console, both print "Å" and
print u"\u212B" display the correct output.
Any ideas? The versions of the MySQLdb adapter tested were 1.2.1
(Python 2.4), and 1.2.2-10 (Python 2.5).
Thanks!
Keith
--
http://mail.python.org/mailman/listinfo/python-list
