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;

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

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?

>>r = c.execute('select * from song where id = 2')
notfound = True
for s in r:

.... notfound = False
.... print s

>>if notfound:

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

Jorgen Bodde wrote:

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?

>r = c.execute('select * from song where id = 2')
notfound = True
for s in r:

... notfound = False
... print s

>if notfound:

... 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())

--
HTH,
Rob

[Marc 'BlackJack' Rintsch]

In <ma***************************************@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".

Ciao,
Marc 'BlackJack' Rintsch

[Carsten Haese]

On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:

In <ma***************************************@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-C...n-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

On 4/12/07, Carsten Haese <ca*****@uniqsys.comwrote:

On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:

In <ma***************************************@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-C...n-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.
--
http://mail.python.org/mailman/listinfo/python-list

On 4/12/07, Carsten Haese <ca*****@uniqsys.comwrote:

On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:

In <ma***************************************@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-C...n-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.
--
http://mail.python.org/mailman/listinfo/python-list

[Gabriel Genellina]

En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
<bj****@gmx.netescribió:

In <ma***************************************@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')

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.

--
Gabriel Genellina

[Marc 'BlackJack' Rintsch]

In <op***************@furufufa-ec0e13.cpe.telecentro.com.ar>, Gabriel
Genellina wrote:

En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
<bj****@gmx.netescribió:

>In <ma***************************************@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')

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

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