ASCII error when fetching results from database with pyodbc and sql server 2005

Question

I have this error when fetching results from the database.

UnicodeEncodeError: 'ascii' codec can't encode character u'\xf1' in position 3: ordinal not in range(128)

Upon closer inspection, I found out that several of the records have ñ and Ñ in them. And I cannot just replace them or ignore them because my db are records for personal information and cannot be changed. I have read many links regarding this and most of it says that I have to replace them or remove them. I have also tried using what's in here but it's not applicable when database is involve. Also, I tried this one, and this one but nothing seems to answer my problem. I am formatting the fetched results like this:

cursor.execute("select * from dummy")
str1=''
for row in cursor:
    str1 = str1 + ''.join(map(str,row)) + "\n" 
print str1

and it outputs the error above. However, when I try this one:

for row in cursor:
    str1 = str(row)
    print str1.decode('latin-1')

It display NO errors but the results were:

(u'Ara\xf1as    ', )
(u'Para\xf1aque ', )
(u'Ma\xf1a\xf1a    ', )
(u'Di\xd1a      ', )

instead of

Arañas
Parañaque
Mañaña
DiÑa

How can I solve this ascii error?

jhinghaus · Accepted Answer · 2015-05-13 13:13:01Z

1

You cast the whole row to string. Try something like:

print row[0]
print str(row[0])
print row[0].encode('latin-1')
print row[0].encode('utf-8')

That will bring you closer to your solution.

edited May 13, 2015 at 13:13

answered May 13, 2015 at 7:12

jhinghaus

86511 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

predator Over a year ago

Hi there! thank you for replying! What if I have several columns?

jhinghaus Over a year ago

row[0], row[1], ... your row is just a python-tuple.

predator Over a year ago

I have thought about that but it will not be very practical to my situation since I have more than 80 columns..

jhinghaus Over a year ago

Ok, let's solve your encoding problem first. Getting the columns by name is another thing.

predator Over a year ago

The one that worked for me was the first one. Decode got an error.

|

Collectives™ on Stack Overflow

ASCII error when fetching results from database with pyodbc and sql server 2005

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related