Re: sqlite3 question
In [EMAIL PROTECTED], Gabriel
Genellina wrote:
En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
[EMAIL PROTECTED] escribió:
In [EMAIL PROTECTED], 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
[EMAIL PROTECTED] escribió:
In [EMAIL PROTECTED], 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
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 [EMAIL PROTECTED] wrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], 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.
--
http://mail.python.org/mailman/listinfo/python-list
On 4/12/07, Carsten Haese [EMAIL PROTECTED] wrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], 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.
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
En Thu, 12 Apr 2007 04:38:06 -0300, Jorgen Bodde
[EMAIL PROTECTED] escribió:
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.
If you are only interested on how many songs there are, use count(*):
c.execute('select count(*) from song where id = 1')
print c.fetchone()[0]
If you are going to process the results, just iterate over the rows as in
your example.
Or, use the fetchone method; it returns the first row, or None if no data
is available (don't forget to process that row too).
If you don't expect many rows, you can use rows = list(c), or rows =
c.fetchall(), and check len(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?
As with many generic iterators, unfortunately, you have to consume the
elements in order to know its length.
--
Gabriel Genellina
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
In [EMAIL PROTECTED], 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 question
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In [EMAIL PROTECTED], 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.
--
http://mail.python.org/mailman/listinfo/python-list
