dealing with special characters in Python and MySQL

[ronrsr]

I have an MySQL database called zingers. The structure is:

zid - integer, key, autoincrement
keyword - varchar
citation - text
quotation - text

the encoding and collation is utf-8


I am having trouble storing text, as typed in last two fields. Special
characters and punctuation all seem not to be stored and retrieved
correctly.

Special apostrophes and single quotes from Microsoft Word are causing a
special problem, even though I have ''ed all 's

error messages, when trying to save to database:

UnicodeDecodeEr ror: 'ascii' codec can't decode byte 0xe2 in position
71: ordinal not in range(128)
args = ('ascii', "update zingers set keywords = ' aaaaaa;Action',
....ation = '''''''''''''\n \n ' where zid = 422", 71, 72, 'ordinal not
in range(128)')
encoding = 'ascii'
end = 72
object = "update zingers set keywords = ' aaaaaa;Action',
....ation = '''''''''''''\n \n ' where zid = 422"
reason = 'ordinal not in range(128)'
start = 71


I think my problem may be that I need to encode the string before
saving it in the databse. Can anyone point me in the right direction
here?




Thanks so much,

[Fredrik Lundh]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

I have an MySQL database called zingers. The structure is:
>
zid - integer, key, autoincrement
keyword - varchar
citation - text
quotation - text
>
the encoding and collation is utf-8
>
I am having trouble storing text, as typed in last two fields. Special
characters and punctuation all seem not to be stored and retrieved
correctly.

are you passing in the strings as Unicode strings, or as something else?
if you're using something else, what have you done to tell the
database what it is?

</F>

[ronrsr]

Re: dealing with special characters in Python and MySQL

structure for the DB:

CREATE TABLE `zingers` (
`zid` int(9) unsigned NOT NULL auto_increment,
`keywords` varchar(255) default NULL,
`citation` text,
`quotation` text,
PRIMARY KEY (`zid`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8 AUTO_INCREMENT= 422 ;




code for storing to database:

querystring = "update zingers set keywords = '%s', citation =
'%s', quotation = %s' where zid = %d" %
(keywords,citat ion,quotation,z id)


;

cursor.execute( querystring)



at this point, querystring = update zingers set keywords = '
aaaaaa;Action', citation = '''''''''
', quotation = '''''''''''''

' where zid = 422

where '''' are an assortment of apostrophes and single quotes copied
and pasted from a Word Document.

>
And where is the actual code... What you've supplied doesn't show
how you are generating the SQL...
--

[ronrsr]

Re: dealing with special characters in Python and MySQL

>
are you passing in the strings as Unicode strings, or as something else?
if you're using something else, what have you done to tell the
database what it is?
>

not at all sure what I'm passing it as.

The database default encoding is utf-8
the database collation is utf-8
the page encoding of the page is utf-8

what else do I have to tell the database?

[John Nagle]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

>>are you passing in the strings as Unicode strings, or as something else?
> if you're using something else, what have you done to tell the
>>database what it is?
>>

>
>
>
not at all sure what I'm passing it as.
>
The database default encoding is utf-8
the database collation is utf-8
the page encoding of the page is utf-8
>
what else do I have to tell the database?
>

Try putting "use_unicode=Tr ue" in the MySQLdb "connect" call.

See

http://sourceforge.net/tracker/index.php?func=detail&aid=1559350&group_id=22307&atid=374932

and look at other charset related bugs at

http://sourceforge.net/tracker/?atid=374932&group_id=22307&func=browse

Also note that MySQLdb didn't support this until recently, so check
your version of MySQLdb. There still seem to be problems in that area.

John Nagle
Animats

[ronrsr]

Re: dealing with special characters in Python and MySQL

version of python is 2.2 -

[ronrsr]

Re: dealing with special characters in Python and MySQL

version of python is either 2.2 or 2.4

bests,

-rsr-

John Nagle wrote:

ronrsr wrote:

[fumanchu]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

code for storing to database:
>
querystring = "update zingers set keywords = '%s', citation =
'%s', quotation = %s' where zid = %d" %
(keywords,citat ion,quotation,z id)

You're missing a single quote in there around the quotation %s.

Are you also replacing "\\" with r"\\" ? You should be.


Robert Brewer
System Architect
Amor Ministries
fumanchu@amor.o rg

[ronrsr]

Re: dealing with special characters in Python and MySQL

Try putting "use_unicode=Tr ue" in the MySQLdb "connect" call.

tried that, and also added charset="utf8" -

now, I can't do any string operations, I get the error msg:

descriptor 'lower' requires a 'str' object but received a 'unicode'
args = ("descriptor 'lower' requires a 'str' object but received
a 'unicode'",)


or similar, on every string operation.

many thanks,

-0rsr-

>
See
>

http://sourceforge.net/tracker/index.php?func=detail&aid=1559350&group_id=22307&atid=374932

>
and look at other charset related bugs at
>

http://sourceforge.net/tracker/?atid=374932&group_id=22307&func=browse

>
Also note that MySQLdb didn't support this until recently, so check
your version of MySQLdb. There still seem to be problems in that area.
>
John Nagle
Animats

[Fredrik Lundh]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

now, I can't do any string operations, I get the error msg:
>
descriptor 'lower' requires a 'str' object but received a 'unicode'
args = ("descriptor 'lower' requires a 'str' object but received
a 'unicode'",)

what's a "string operation" in this context? are you trying to call
string methods by explicit calls to class methods in the str class:

>>str.upper(u"f oo")

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: descriptor 'upper' requires a 'str' object but received a
'unicode'

instead of calling methods on the string object:

>>u"foo".upper( )

u'FOO'

? if so, what did you get that idea from?

</F>

[Fredrik Lundh]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

querystring = "update zingers set keywords = '%s', citation =
'%s', quotation = %s' where zid = %d" %
(keywords,citat ion,quotation,z id)

that's not a good way to pass strings to the database. for the right
way to do this, see:

http://effbot.org/pyfaq/how-do-i-escape-sql-values-when-using-the-db-api

</F>

[Leo Kislov]

Re: dealing with special characters in Python and MySQL

ronrsr wrote:

>

Try putting "use_unicode=Tr ue" in the MySQLdb "connect" call.

>
tried that, and also added charset="utf8" -
>
now, I can't do any string operations, I get the error msg:
>
descriptor 'lower' requires a 'str' object but received a 'unicode'
args = ("descriptor 'lower' requires a 'str' object but received
a 'unicode'",)
>
>
or similar, on every string operation.

What is string operation? Every time you say "I get error" please
provide source code where this error occurs. And by the way, do you
know that for non-ascii characters you should use unicode type, not str
type?

-- Leo