473,729 Members | 2,186 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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('sele ct * 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('sele ct * 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
6 3230

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('sele ct * 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.fetchal l())

--
HTH,
Rob

Apr 12 '07 #2
In <ma************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>>r = c.execute('sele ct * 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************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>r = c.execute('sele ct * 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("se lect * 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().conn ection.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.exec ute, so for ease of use and sparing a temp var, I put
the .fetchone() behind the connection.exec ute()

Thanks everyone for their help!
- Jorgen

On 4/12/07, Carsten Haese <ca*****@uniqsy s.comwrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In <ma************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>>r = c.execute('sele ct * 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("se lect * 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*****@uniqsy s.comwrote:
On Thu, 2007-04-12 at 13:43 +0200, Marc 'BlackJack' Rintsch wrote:
In <ma************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>>r = c.execute('sele ct * 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("se lect * 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.net escribió:
In <ma************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>>>r = c.execute('sele ct * 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.tele centro.com.ar>, Gabriel
Genellina wrote:
En Thu, 12 Apr 2007 08:43:49 -0300, Marc 'BlackJack' Rintsch
<bj****@gmx.net escribió:
>In <ma************ *************** ************@py thon.org>, Jorgen Bodde
wrote:
>>>>>r = c.execute('sele ct * 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2192
by: lolmcbride | last post by:
Hi, is it possible to pass args through the api which are the same as the args you can use on the sqlite3 command line? What I'm talking about is the .mode or .output commands which you can enter via the sqlite3 cli so I can dynamically change were the output goes. Ta
2
1971
by: John Machin | last post by:
Apologies in advance if this is a bit bloggy, but I'd like to get comments on whether I've lost the plot (or, more likely, failed to acquire it) before I start reporting bugs etc. From "What's new ...": """ # Create table c.execute('''create table stocks (date timestamp, trans varchar, symbol varchar, qty decimal, price decimal)''')
66
7072
by: mensanator | last post by:
Probably just me. I've only been using Access and SQL Server for 12 years, so I'm sure my opinions don't count for anything. I was, nevertheless, looking forward to Sqlite3. And now that gmpy has been upgraded, I can go ahead and install Python 2.5. So I open the manual to Section 13.13 where I find the first example of how to use Sqlite3:
3
1905
by: cjl | last post by:
P: I am using python 2.5.1 on windows. I have the following code: conn = sqlite3.connect('.\optiondata') c = conn.cursor() try: c.execute('''create table options (ssymbol text, strike real, osymbol text, bid real, mpp real, upp real)''') except sqlite3.OperationalError:
4
8922
by: Simon | last post by:
I installed the source code on unix for python 2.5.1. The install went mainly okay, except for some failures regarding: _ssl, _hashlib, _curses, _curses_panel. No errors regarding sqlite3. However, when I start python and do an import sqlite3 I get: /ptmp/bin/python Python 2.5.1 (r251:54863, May 29 2007, 05:19:30) on sunos5
33
2557
by: Stef Mientki | last post by:
hello, I discovered that boolean evaluation in Python is done "fast" (as soon as the condition is ok, the rest of the expression is ignored). Is this standard behavior or is there a compiler switch to turn it on/off ? thanks, Stef Mientki
3
2972
by: milan_sanremo | last post by:
I have sqlite installed, but when I try to import sqlite3 I receive: Python 2.5.1 (r251:54863, Nov 3 2007, 02:54:36) on sunos5 Type "help", "copyright", "credits" or "license" for more information. Traceback (most recent call last): File "<stdin>", line 1, in <module> ImportError: No module named sqlite3 Yet:
0
1760
by: Ben Lee | last post by:
hi folks -- a quick python and sqlite3 performance question. i find that inserting a million rows of in-memory data into an in-memory database via a single executemany() is about 30% slower than using the sqlite3 CLI and the .import command (reading the same data from a disk file, even.) i find this surprising, executemany() i assume is using a prepared statement and this is exactly what the .import command does (based on my quick...
15
14776
by: Kurda Yon | last post by:
Hi, I try to "build" and "install" pysqlite? After I type "python setup.py build" I get a lot of error messages? The first error is "src/ connection.h:33:21: error: sqlite3.h: No such file or directory". So, I assume that the absence of the "sqlite3.h" is the origin of the problem. I found on the web, that this file should be either in "/usr/local/ include" or in "/usr/local/lib". I check this directories and I really
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9289
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9156
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8159
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4798
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3242
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2694
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.