467,118 Members | 1,012 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,118 developers. It's quick & easy.

sqlite3 question

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
Apr 12 '07 #1
  • viewed: 2989
Share:
6 Replies

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

Apr 12 '07 #2
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
Apr 12 '07 #3
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.
Apr 12 '07 #4
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
Apr 13 '07 #5
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
Apr 15 '07 #6
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
Apr 16 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lolmcbride@googlemail.com | last post: by
2 posts views Thread by John Machin | last post: by
66 posts views Thread by mensanator@aol.com | last post: by
3 posts views Thread by cjl | last post: by
4 posts views Thread by Simon | last post: by
33 posts views Thread by Stef Mientki | last post: by
3 posts views Thread by milan_sanremo | last post: by
reply views Thread by Ben Lee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.