Re: Python DB-API 2.0 oddity (was: I receieved an example of

On Mon, 2005-05-02 at 11:56 +0200, Karsten Hilbert wrote:
> A note on what I think is a strangeness in the Python DB-API 2.0.
> Please correct me if I am wrong.
>
> (Note that I am not trying to throw off the OP but simply use
> his example to point out an oddity about that API. The point
> is to make sure it *is* an oddity so I can raise it with the
> appropriate forum, eg the Python community.)
>
> Observe the following notes by someone learning the DB-API:
>
> > # Get a "cursor". We do this by connecting to the database: the
> > # (button,"") arguments just connect to the database that the form is
> > # running in.
> > #
> > cursor = RekallPYDBI.connect (button, '').cursor()
> So far so good... But - getting a cursor without knowing the
> SELECT query the results of which it stands for ?

AFAIK cursors are not limited to SELECTs.

> > # Execute a query. This only gets people whose ages are 21 or above.
> > # This is the important bit, we are accessing the database directly.
> > #
> > cursor.execute ("select surname from users where age >= ?", [21])
> Ah, the query is set *after* getting a cursor for it - seems
> odd, but hey, as long as it's set before retrieving rows ...
>
> > The key is getting the "cursor". Once you have a cursor you can do inserts,
> > updates and deletes, like
> Huh ? Pardon me ? Doing inserts, updates and deletes via a
> cursor ? The PostgreSQL documentation clearly says that the
> <query> part of a cursor definition must be a SELECT:
>
> http://www.postgresql.org/docs/7.4/static/sql-declare.html

But what makes you think that Python DBI was designed to be PostgreSQL
specific?

http://www.python.org/peps/pep-0249.html

.cursor()

Return a new Cursor Object using the connection. If the
database does not provide a direct cursor concept, the
module will have to emulate cursors using other means to
the extent needed by this specification.

It's up to the module implementation to use real SQL cursors when
possible. AFAIK, it's not done automagically for PostgreSQL.
In practice, DBI cursor objects and SQL cursors have little in common
in the PostgreSQL drivers I'm aware of (PygreSQL and psycopg). A DBI
cursor is just an handle to execute SQL commands.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it