Re: Character Encoding Question

Thanks Daniele,

I'm using psycopg 2.4.5 with Python 3.2.3 on Kubuntu 12.10.

This is the connection encoding based on a quick check at the Python
console:
>>>print(con.encoding)
SQLASCII

I honestly don't know where the error actually is - not a very advanced
programmer by any means. All I really know is that the program breaks when
I use fetchall(), but it works with fetchone() and fetchmany(). It could
have something to do with the way my main program module imports the module
that calls the function, for all I know. Here is the traceback:
File "main_program_file.py", line 63, in <module>
rpt_payee_list()
File "imported_sqlcommand_module.py", line 50, in rpt_payee_list
rows = cur.fetchall()
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 9:
ordinal not in range(128)

Here is some fake data retrieved by the query (when specifying the UTF-8
client encoding):
(79, 'Payee', "Bob's Restaurant", '123 Some St.', 'Big City', 'STATE',
'USA', '337709', 'Restaurant')
(80, 'Payee', 'Tony's Fancy Restaurant', '34 Long Rd', 'Big City', 'STATE',
'USA', 'NA', 'Restaurant')
(81, 'Payee', 'Piña Colada Resort', 'Resort Avenue', 'Resort Town', 'Lima',
'Perú', 'NA', 'Resort Hotel')
(82, 'Payee', "Great Foods", '45 Eating Way', 'Big City', 'STATE', 'USA',
'2028', 'Grocery Store')
(83, 'Payee', 'The Home Store', '25 Shopping Plaza', 'Big City', 'STATE',
'USA', '17', 'Home Improvement Store')

Here is the actual function in all its glory (try not to laugh too hard -
I'm still learning):
def rpt_payee_list():
con = None
con = psycopg2.connect(database='financedb_dev', user='donp')
con.set_client_encoding('UTF-8')
cur = con.cursor()
try:
cur.execute("""SELECT * FROM entity
ORDER BY entity_name""")
except:
print('It ain\'t workin')

rows = cur.fetchall()

for row in rows:
print(row)

if con:
con.close()

A little later, I will likely create a connection function that I can call
when I want to run a specific query, but for the initial steps, I've
included the connection as part of the function that creates/runs the query.

On Thu, Mar 28, 2013 at 11:49 AM, Daniele Varrazzo <
daniele(dot)varrazzo(at)gmail(dot)com> wrote:

> On Thu, Mar 28, 2013 at 3:35 PM, Don Parris <parrisdc(at)gmail(dot)com> wrote:
> > Hi all,
> >
> > I am using Psycopg2 with Python3 & PostgreSQL 9.1 with a database
> encoded as
> > UTF-8 (Kubuntu 12.10). I have a question about the encoding and
> decoding,
> > and why some fetch calls did what I wanted while fetchall() seemed to
> choke.
> > I got the following error message when calling fetchall():
> > UnicodeDecodeError: 'ascii' codec can't decode byte 0xe2 in position 9:
> > ordinal not in range(128)
> >
> > The traceback pointed to the fetchall() statement.
> >
> > First, I was able to do a pattern matching search on a specific field in
> my
> > payee table and could get back whatever records matched the user input
> in my
> > script. Then I ran a simple SELECT on the same table, using fetchall(),
> but
> > that choked.
> >
> > I decided to experiment some before asking questions here on the list,
> and
> > discovered that, if I call fetchone(), I got a single record with the
> fields
> > presented as rows in my Bash console. Then I tried fetchmany() and got
> the
> > same record presented as a tuple.
> >
> > In the psycopg usage doc
> > (http://www.initd.org/psycopg/docs/usage.html#unicode-handling) I found
> > this reference:
> >
> > conn.set_client_encoding('LATIN9')
> >
> > I tried that, but set the encoding to UTF-8 instead of the LATIN9,
> supposing
> > that might be useful. Given the successful result, it appears that was
> just
> > what the doctor ordered!
>
> Yes, that should be the case. What was the original encoding? Can you
> check "conn.client_encoding" (to get what psycopg thinks the encoding
> is) and get the result from the query "show client_encoding" (to get
> what the database say it is)? It's strange your connection is not in
> utf8 encoding by default.
>
>
> > My first question is, is this really all I need to do - just set the
> client
> > encoding? Or is there something else I need to do or be aware of?
>
> Yes, but as said it is suspicious it wasn't already in utf8.
>
>
> > My second question is why does fetchall() fail without setting the client
> > encoding (while the other fetch calls work)?
>
> Are you sure the error is in fetchall itself and not somewhere
> downstream? The decoding code path should be common for all the
> fetch*() methods. Can you produce a synthetic test case with a couple
> of rows in a table to trigger the error?
>
> What psycopg version are you using?
>
> Thanks,
>
> -- Daniele
>

--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE