Re: [RFC] ideas for a new Python DBAPI driver (was Re: libpq test suite)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Il 14/02/2013 20:01, Peter Eisentraut ha scritto:
> On 2/14/13 9:23 AM, Manlio Perillo wrote:
>> 1) always use PQsendQueryParams functions.
>>
>> This will avoid having to escape parameters, as it is done in
>> psycopg2
>> (IMHO it still use simple query protocol for compatibility purpose)
>
> I think the reason this doesn't work is that in order to prepare a query
> you need to know the parameter types, but you don't know that in Python,
> or at least with the way the DB-API works.

Hint: .setinputsizes.

In my implementation, prepared queries are **only** used if user calls
setinputsizes; if setinputsizes is not called, preparing a query can
cause performance loss, so it is better to not prepare one.

> For example, if you write
>
> cur.execute("SELECT * FROM tbl WHERE a = %s AND b = %s", (val1, val2))
>
> what types will you pass to PQsendQueryParams?
>

1) if setinputsizes is used, use the type specified here.

2) if setinputsizes is not used, query the driver's type catalog, to
get the postgresql type oid associated with a Python object; e.g.:

pg_type = connection.get_type_by_object(val1)
buf, format = pg_type.output_function(val1)
param_oid = pg_type.oid

> You can make some obvious heuristics, but this sort of thing can get
> complicated pretty quickly.

A non trivial case if when val is a list, that should be mapped to a
PostgreSQL array.

However, you can always set the oid to 0, and let PostgreSQL deduce the
type, as it is done in psycopg2. If user called setinputsizes, we are
happy.

Regards Manlio
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlEdO54ACgkQscQJ24LbaURFWACgkG3dkDYUA3tHooiXpGDf8hm0
Fz0AnjhFDwJU/XSqtgPyHwuHw9+GiRlv
=m68+
-----END PGP SIGNATURE-----