pypgsql-users

[Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Gerhard <ger...@gm...>]

CC-ing to pypgsql-users list, maybe other users have comments on this issue,
too.

* Paul Boddie <pa...@bo...> [2002-06-05 08:21 -0700]:
> Gerhard <gh_...@gm...> wrote:
> > 	
> > * Fixed the array parsing so it also works with PostgreSQL versions 7.2.x.
> 
> I doubt that this is related, but does the 'IN' operator work with
> statement parameters (bind variables) in pyPgSQL yet? I recently found
> that doing something like this...
> 
>   cursor.execute("SELECT * FROM CAGES WHERE NAME IN %s",
>     (("mammals", "apes"),))
> 
> ...gives some kind of parsing error inside pyPgSQL.

It is indeed a related issue. If you want to show which queries pyPgSQL
produces, the trick is to access a special attribute like this:

conn.conn.toggleShowQuery

This would show the following query is executed:

QUERY: DECLARE PgSQL_08120A6C CURSOR FOR select * from cages where name in '{"mammals","apes"}'

... which obviously cannot work. The reason is that if pyPgSQL encounters a
tuple or a list in the statement parameters, it will quote them suitable for
use as an ARRAY. Unfortunately, the ARRAY syntax is different from what you
would need for the IN operator in SQL.

I'm not sure if something can be done about this problem. It seems to me that
it's an either/or with ARRAY vs. your desired conversion for the IN operator.

> [...] Another thing: pyPgSQL seems to be fine with Unicode query strings
> (unlike psycopg, apparently), but it doesn't like Unicode parameter values.
> Is there some explicit encoding step I should be performing?

I've fiddled with adding Unicode support for some time now, but I didn't get
enough testers yet to make me comfortable enough with it for adding it to a
pyPgSQL release. On the Sourceforge patches section, I've uploaded a patch for
full Unicode support, including getting Unicode results back from the database,
insert Unicode strings, and automatic encoding conversions. I'll try to update
this patch today to work with the latest pyPgSQL 2.1 release and drop you a
note when it's ready.

Gerhard
-- 
This sig powered by Python!
Außentemperatur in München: 25.7 °C      Wind: 4.5 m/s

[Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[<pa...@bo...>]

On Wed, 5 Jun 2002 18:40:11 +0200 Gerhard Häring <ger...@gm...> wrote:
>CC-ing to pypgsql-users list, maybe other users have comments on this issue,
>too.

[Arrays vs. 'IN']

>QUERY: DECLARE PgSQL_08120A6C CURSOR FOR select * from cages where name
>       in '{"mammals","apes"}'
>
>... which obviously cannot work. The reason is that if pyPgSQL encounters a
>tuple or a list in the statement parameters, it will quote them suitable for
>use as an ARRAY. Unfortunately, the ARRAY syntax is different from what you
>would need for the IN operator in SQL.

Right, and there's no way of knowing what the database engine expects for that 
parameter until it has parsed the statement. I suppose there isn't a way of 
getting type information from PostgreSQL about parameters - indeed, does 
PostgreSQL actually support parameters in the way that other database systems 
do? I got the impression that PostgreSQL database modules have to bundle the 
parameter values into the query as shown above, rather than passing those 
values separately.

>I'm not sure if something can be done about this problem. It seems to me that
>it's an either/or with ARRAY vs. your desired conversion for the IN operator.

That's an unfortunate limitation, but thankfully I didn't need to use 'IN' 
after all. :-)

>I've fiddled with adding Unicode support for some time now, but I didn't get
>enough testers yet to make me comfortable enough with it for adding it to a
>pyPgSQL release. On the Sourceforge patches section, I've uploaded a patch for
>full Unicode support, including getting Unicode results back from the database,
>insert Unicode strings, and automatic encoding conversions. I'll try to update
>this patch today to work with the latest pyPgSQL 2.1 release and drop you a
>note when it's ready.

That would be great. Where XML data and PostgreSQL come together, it quickly 
becomes a requirement to handle the data gracefully or transparently, although 
I suspect that many database systems still struggle with Unicode.

Thanks for the explanation and the great work!

Paul

Re: [Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Gerhard <ger...@gm...>]

* pa...@bo... <pa...@bo...> [2002-06-05 17:08 -0000]:
> On Wed, 5 Jun 2002 18:40:11 +0200 Gerhard Häring wrote:
> >CC-ing to pypgsql-users list, maybe other users have comments on this issue,
> >too.
> 
> [Arrays vs. 'IN']
> 
> >QUERY: DECLARE PgSQL_08120A6C CURSOR FOR select * from cages where name
> >       in '{"mammals","apes"}'
> >
> >... which obviously cannot work. The reason is that if pyPgSQL encounters a
> >tuple or a list in the statement parameters, it will quote them suitable for
> >use as an ARRAY. Unfortunately, the ARRAY syntax is different from what you
> >would need for the IN operator in SQL.
> 
> Right, and there's no way of knowing what the database engine expects for
> that parameter until it has parsed the statement.

Unless you also parse at the client side, which I'd rather not ;-)

There might be a solution with controlling the behaviour using a flag in the
cursor object, for example.

> I suppose there isn't a way of getting type information from PostgreSQL about
> parameters - indeed, does PostgreSQL actually support parameters in the way
> that other database systems do?

You mean prepared statements? No, PostgreSQL doesn't currently support these.
All it gets is a query string. From what I hear on IRC and on
postgresql-hackers, people are working on prepared statements.

Unfortunately, we'll have a problem with them and pyPgSQL, as as far as I see,
prepared statements don't play nice with the DB-API 2.0 'format' and 'pyformat'
parameter quoting styles. I believe that the 'qmark' or 'numeric' styles are
designed exactly for prepared statement use. It might be possible to rewrite
the query and continue using our 'pyformat' style, but I haven't tried, yet.

> > [Unicode patch] 
> That would be great. Where XML data and PostgreSQL come together, it quickly
> becomes a requirement to handle the data gracefully or transparently,
> although I suspect that many database systems still struggle with Unicode.

Hopefully, pyPgSQL with PostgreSQL works fine. PostgreSQL can even use the
UTF-8 encoding at server side. With the Unicode patch and appropriate
parameters for the connect() call, you should be able to use full Unicode:

>>> from pyPgSQL import PgSQL
>>> conn = PgSQL.connect(client_encoding="utf-8", unicode_results=1)
>>> cursor = conn.cursor()
>>> cursor.execute("insert into test(name) values (%s)", unicode("Österreich", "latin1"))
>>> cursor.execute("select * from test")
>>> res = cursor.fetchone()
>>> print repr(res.name)
u'\xd6sterreich'

Btw. the Unicode support that PySQLite recently gained is based on the same
patch, and it includes an example for mass-importing Freshmeat project data
from XML into a database. As you said, XML is Unicode-based, so this is a
relatively practical example, I hope.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Gerhard <ge...@bi...>]

* Gerhard Häring <ger...@gm...> [2002-06-05 19:55 +0200]:
> >>> from pyPgSQL import PgSQL
> >>> conn = PgSQL.connect(client_encoding="utf-8", unicode_results=1)
> >>> cursor = conn.cursor()

Here I missed something that (still) needs to be done explicitely:

cursor.execute("set client_encoding to UNICODE")

> >>> cursor.execute("insert into test(name) values (%s)", unicode("Österreich", "latin1"))
> >>> cursor.execute("select * from test")
> >>> res = cursor.fetchone()
> >>> print repr(res.name)
> u'\xd6sterreich'

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Adam B. <ad...@po...>]

> > [Arrays vs. 'IN']
> > Right, and there's no way of knowing what the database engine expects for
> > that parameter until it has parsed the statement.
> Unless you also parse at the client side, which I'd rather not ;-)
> There might be a solution with controlling the behaviour using a flag in the
> cursor object, for example.

But what will happen when someone passes many different parameters to
execute() function, some of them being arrays which have to be
converted to "'{one,two,three}'" and some to "('one', 'two', 'three')"?
I think the only good way would be to leave current cursor
object behaviour as it is and add new class (call it PgSet, for
instance), which would be converted to the second form above.  What do
You think?

I agree that it would be useful.  However, there are probably many
more such situations where a programmer would like to convert
automatically a data structure into the SQL expression of his/her
choice.  I am not sure if it is really good for a general library like
pyPgSQL to have so many features not covered by standards.

Regards,

-- 
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

Re: [Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Gerhard <ger...@gm...>]

* Gerhard Häring <ger...@gm...> [2002-06-05 18:40 +0200]:
> * Paul Boddie <pa...@bo...> [2002-06-05 08:21 -0700]:
> > [...] Another thing: pyPgSQL seems to be fine with Unicode query strings
> > (unlike psycopg, apparently), but it doesn't like Unicode parameter values.
> > Is there some explicit encoding step I should be performing?
> 
> I've fiddled with adding Unicode support for some time now, but I didn't get
> enough testers yet to make me comfortable enough with it for adding it to a
> pyPgSQL release. On the Sourceforge patches section, I've uploaded a patch
> for full Unicode support, including getting Unicode results back from the
> database, insert Unicode strings, and automatic encoding conversions. I'll
> try to update this patch today to work with the latest pyPgSQL 2.1 release
> and drop you a note when it's ready.

Done now. I like vimdiff :-)

You can get the experimental Unicode patch that applies cleanly to pyPgSQL 2.1
at the following long URL:

http://sf.net/tracker/index.php?func=detail&aid=484468&group_id=16528&atid=316528

There isn't much docs (read: any) yet, but you can look into the Unicode test
suite (test/unicode_tests.py) that the patch will install for usage of the
various Unicode features.

Gerhard
-- 
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id 86AB43C0
public key fingerprint: DEC1 1D02 5743 1159 CD20  A4B6 7B22 6575 86AB 43C0
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))

Re: [Pypgsql-users] Re: [ANN] pyPgSQL 2.1 released

[Adam B. <ad...@po...>]

> I've fiddled with adding Unicode support for some time now, but I didn't get
> enough testers yet to make me comfortable enough with it for adding it to a
> pyPgSQL release. On the Sourceforge patches section, I've uploaded a patch for
> full Unicode support, including getting Unicode results back from the database,
> insert Unicode strings, and automatic encoding conversions. I'll try to update
> this patch today to work with the latest pyPgSQL 2.1 release and drop you a
> note when it's ready.

I'm very, very sorry I haven't find time yet for testing your Unicode
patch yet :(((  I know that You made a huge effort to bring this
functionality to pyPgSQL and that I should have help You at least with
testing it.  I hope I will manage to do this within two or three days,
but I cannot promise anything, I'm afraid.

I'd like also to ask, why is it still necessary to set the encoding
twice, one time during connect() (as a "client_encoding" parameter),
and the second one using "set client_encoding to ..." command?  Why
doesn't pyPgSQL send this command to PostgreSQL automatically?  Is it
something I don't understand here? :)

-- 
Adam Buraczewski <ad...@po...> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?