More DBI question

[Gordon McMillan]

Timothy Grant writes:

> I guess I have a misunderstanding of how cursors are supposed work, or
> more likely how I am supposed to work with cursors.

Different databases have different concepts of cursors. 
Different drivers may warp those concepts in unexpected 
ways. The least common denominator is forward only.
 
Most DBs use the word cursor to mean "result set iterator", 
but the DBI spec uses the term a bit more loosely.

> Current understanding:
> 
> Open Database Connection
> Create a Cursor
> Execute SQL queries using that cursor
> Process  results
> 
> Now, I need to make all sorts of different queries using that cursor,
> some are selects, some are updates, some are deletes, some deal with
> functions
> 
> Now my misunderstanding comes when I do something like this
> 
> c.execute("""SELECT NEXTVAL ('id')""")
> 
> c.execute("""INSERT INTO mytable ( name, rank, serialno)
> 		VALUES( ?, ?, ?""", (namevar, rankvar, serialnovar))
> 
> This works as advertised
> 
> Howevever, the second time through this sequence, the first execute
> returns an error along the lines of "Expecting 3 arguments received 1"
> 
> Now I understand where the three arguments part comes from, the cursor
> just did something that required three arguments, but how do I "reset"
> what the cursor expects, it seems clunky to call a db.commit() after
> each execute.

As you might expect by now, much depends on what DB, 
what driver and what DBI implementation you're using. I 
suggest, however, closing a cursor once you're done with an 
execute and using a new cursor.

If your DBI implementation is smart enough, it can be much 
more efficient to associate a cursor with an SQL string.

cust_cursor = conn.cursor()
while something:
  cust_cursor.execute(
          'select nm from customer where id = ?', id)
  # other stuff

If you're lucky, the statement will only get parsed once.



- Gordon