sqlite3 question

[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 <carsten at uniqsys.com> wrote:
> 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.
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>


On 4/12/07, Carsten Haese <carsten at uniqsys.com> wrote:
> 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.
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>