sqlite3 question

[Carsten Haese]

On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
> In <mailman.6384.1176363488.32031.python-list at python.org>, Jorgen Bodde
> wrote:
> 
> >>>> r = c.execute('select * from song where id = 1')
> >>>> for s in r:
> > ... 	print s
> > ... 	
> > (1, u'Spikedrivers Blues', u'Mississippi John Hurt')
> > 
> > That works. But when I can't restore the row by e.g. an ID that does
> > not exist, I cannot see any method in 'r' (which is a SQLite.Cursor)
> > that can tell me if I have rows. As explained in the help, r.rowcount
> > does not give a valid result because it can't determine how many rows
> > are restored in advance.
> 
> This should not work because `r` should not be a `Cursor` object.  The
> `execute()`-Method returns an integer with the number of "affected rows".

It does work if 'c' is a connection object with a poorly chosen name.
According to
http://docs.python.org/lib/sqlite3-Connection-Objects.html , sqlite3
connection objects have a non-standard execute method that creates a
cursor, executes a query on that cursor, and returns that cursor.

Anyway, if you expect a query to return at most one row, such as when
you're filtering on the table's primary key, this is how I would do it:

cur.execute("select * from song where id = ?", (wanted_id,) )
song_row = cur.fetchone()
if song_row:
   # Do something with song_row
else:
   # Song not found

HTH,

Carsten.