[GENERAL] Python DB-API 2.0 oddity (was: I receieved an example of Rekall script)
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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] 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. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Python DB-API 2.0 oddity (was: I receieved an example of
On Mon, 2005-05-02 at 11:56 +0200, Karsten Hilbert wrote: 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.) BTW, look at this page (with the Oracle driver): http://www.zope.org/Members/matt/dco2/dco2doc cursor.execute(INSERT INTO TEST (name, id) VALUES (:name, :id), name=Matt Kromer, id=1) I believe there are databases that allow you to send SQL statements (any kind, not only SELECTs) only in a cursor (either implicit or explicit), hence the name for the cursor object. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
