sqlite3 question

[Jorgen Bodde]

Hi all,

I am using sqlite3 in python, and I wonder if there is a way to know
if there are valid rows returned or not. For example I have a table
song with one entry in it. The ID of that entry is 1, so when I do;
.... 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.

All I can think of is a 'crappy' construction where I use the iterator
to see if there was something in there, but surely, there must be a
better way to know?
.... notfound = False
.... print s.... print 'No rows'

I am pretty new with Python, maybe there are some properties of an
iterator / sqlite3 I am not aware of that can tell me how many rows
are there?

With regards,
- Jorgen

 

[Rob Wolfe]

All I can think of is a 'crappy' construction where I use the iterator
to see if there was something in there, but surely, there must be a
better way to know?

... notfound = False
... print s
... print 'No rows'

I am pretty new with Python, maybe there are some properties of an
iterator / sqlite3 I am not aware of that can tell me how many rows
are there?

What about this:

if not c.fetchone():
print "No rows"

or

print "rowcount=", len(cur.fetchall())

 

[Marc 'BlackJack' Rintsch]

... 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".

Ciao,
Marc 'BlackJack' Rintsch

 

[Carsten Haese]

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.

 

[Jorgen Bodde]

Thanks,

This is how I did it in the end as well. Yes i use the connection
object, abbreviated as 'c' for ease of typing.

In my real app the connection is kept inside a singleton object and I
use the DB like

result = GuitarDB().connection.execute('select * from song where id =
1').fetchone()
if result:
print 'Found a song'
else:
print 'Found nothing'

I know there will always be a cursor object back from
connection.execute, so for ease of use and sparing a temp var, I put
the .fetchone() behind the connection.execute()

Thanks everyone for their help!
- Jorgen

 

[Gabriel Genellina]

En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch

This should not work because `r` should not be a `Cursor` object. The
`execute()`-Method returns an integer with the number of "affected rows".

Actually DBAPI 2.0 says the return value is undefined.

 

[Marc 'BlackJack' Rintsch]

En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch



Actually DBAPI 2.0 says the return value is undefined.

I just remembered the number of affected rows, but that's just for data
manipulation statements like ``UPDATE`` or ``INSERT``. For ``SELECT`` the
method should return `None`. My bad.

Ciao,
Marc 'BlackJack' Rintsch