473,846 Members | 1,975 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

dealing with special characters in Python and MySQL

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,

Dec 18 '06 #1
11 10270
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>

Dec 18 '06 #2
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...
--
Dec 18 '06 #3
>
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?

Dec 18 '06 #4
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...07&atid=374932

and look at other charset related bugs at

http://sourceforge.net/tracker/?atid...07&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
Dec 18 '06 #5
version of python is 2.2 -

Dec 18 '06 #6
version of python is either 2.2 or 2.4

bests,

-rsr-

John Nagle wrote:
ronrsr wrote:
Dec 18 '06 #7
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
fu******@amor.o rg

Dec 18 '06 #8
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...07&atid=374932

and look at other charset related bugs at

http://sourceforge.net/tracker/?atid...07&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
Dec 18 '06 #9
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>

Dec 18 '06 #10

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

Similar topics

12
28596
by: Mosher | last post by:
Hi all, I have an issue with php and/or mysql. I have a php form that writes "items" to a mysql database, including a description of the item. On the mysql server, "magic_quotes_gpc" is ON. I am testing it now by putting special characters in the description field, this is what I am entering: O'Leary "special edition"
3
2577
by: Ruby Tuesday | last post by:
How do I transfer my MS Access DB to MySQL? One issue though, in Access I have some special character such fraction, backquote, etc ... how do I replace those characters to ASCI equivalent when transfering to MySQL, or do I have to do it outside MySQL? Thanks
25
5409
by: Wim Cossement | last post by:
Hello, I was wondering if there are a few good pages and/or examples on how to process form data correctly for putting it in a MySQL DB. Since I'm not used to using PHP a lot, I already found out that addslashes() can be used escape some characters, but I'm having some more problems with for instance ä, å and µ (since the text is scientifical) Now some people also throw in htmlspecialchars() to convert those to HTML entities, but some...
1
3049
by: ronrsr | last post by:
I have an MySQL database called zingers. The structure is: zid - integer, key, autoincrement keyword - varchar citation - text quotation - text I am having trouble storing text, as typed in latter two fields. Special characters and punctuation all seem not to be stored and retrieved correctly.
1
1786
by: ronrsr | last post by:
I have an MySQL database called zingers. The structure is: zid - integer, key, autoincrement keyword - varchar citation - text quotation - text I am having trouble storing text, as typed in latter two fields. Special characters and punctuation all seem not to be stored and retrieved correctly.
6
3883
by: TheRealDan | last post by:
Hi all. I'm having a problem with a special characters. I have php script that reads from an xml file and writes to a mysql db. It's a script called phptunest that I found on the net, although the original website for the author appears to be gone. It works really nicely except when it hits special characters. Everything from a sp char forward just gets lost. It is using mysql_real_escape_string, but that doesn't seem to help with the ...
2
3333
by: thisisazam | last post by:
Hi I am picking records from xml file and saving them into mysql database. I am When I insert records, the special characters like ö,å,ä are being transformed into some unwanted values. I tried to insert those values manualy by giving insert into table_name...... this command on command line. It works fine and store even special characters as well. But when I Pick the record from xml and insert them into mysql it does not insert ö,ä,å...
3
2284
by: sophie_newbie | last post by:
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.
2
3592
by: matech | last post by:
I have a problem with uploading special characters from excel files to mysql 5. It doesn't matter if I use UTF-8 or iso-8859-1 when uploading the trademark ™ symbol. htmlspecialchars() or htmletities() doesn't help? the database doesn't show the data in the field but replaces it with the binary/Image information. the following are examples of how I've tried loading the data along with UTF-8 or iso-8859-1:
0
9725
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10976
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10705
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5714
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5906
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4111
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.