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 ?
> # 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
(I am well aware that SELECT queries may have side
effects that change data in the backend such as in
"select add_new_customer()" etc.)
The sequence of using a cursor in the Python DB-API is as
follows:
conn = dbapi.connect(...)
curs = conn.cursor()
curs.execute('[select | insert | update | delete] ...')
print curs.fetch(no_of_rows) # if query was a select
This seems mighty odd to me. Should it not be:
# get connection
conn = dbapi.connect(...)
# select w/o cursor so we get entire result set right away
rows = conn.execute('select ...', use_cursor = False)
# select w/ cursor so we can traverse result set as needed
curs = conn.execute('select ...'), use_cursor = True)
rows = curs.fetch(no_of_rows=25)
# insert
conn.execute('insert ...')
# update
conn.execute('update ...')
# delete
conn.execute('delete ...')
Wondering,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346