Differentiate between an empty result set and an error using Python3

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Differentiate between an empty result set and an error using Python3

Rob Sciuk

Forgive me if this is an FAQ, but in looking over the python3 interface to
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
execute() command.

My use case is to differentiate between an empty row set (OK) vs an error
of some kind in the query.

Anyone figured this out?

Cheers,
Rob.

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk                                         [hidden email]
Principal Consultant                                        905.706.1354
Control-Q Research                  97 Village Rd. Wellesley, ON N0B 2T0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Differentiate between an empty result set and an error using Python3

David Raymond
If you run a query that returns no results, then cursor.fetchone() will return None, or cursor.fetchall() will return an empty list. If there is an error during the processing then some sort of exception should be raised.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Rob Sciuk
Sent: Wednesday, September 04, 2019 2:18 PM
To: [hidden email]
Subject: [sqlite] Differentiate between an empty result set and an error using Python3


Forgive me if this is an FAQ, but in looking over the python3 interface to
SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
execute() command.

My use case is to differentiate between an empty row set (OK) vs an error
of some kind in the query.

Anyone figured this out?

Cheers,
Rob.

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Robert S. Sciuk                                         [hidden email]
Principal Consultant                                        905.706.1354
Control-Q Research                  97 Village Rd. Wellesley, ON N0B 2T0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Differentiate between an empty result set and an error using Python3

Keith Medcalf
In reply to this post by Rob Sciuk

On Wednesday, 4 September, 2019 12:18, Rob Sciuk <[hidden email]> wrote:

>Forgive me if this is an FAQ, but in looking over the python3 interface to
>SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
>execute() command.

>My use case is to differentiate between an empty row set (OK) vs an error
>of some kind in the query.

>Anyone figured this out?

If there is an error then an exception will be thrown.  No exception means no error.  Otherwise, iterating over the cursor will return the rows.  If there are no rows then it is like iterating over an empty list -- there is nothing to return (the cursor object is a generator that yields row tuples and it will internally raise StopIteration when it is out of data to return, just like any other generator).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Differentiate between an empty result set and an error using Python3

E.Pasma

> Op 5 sep. 2019, om 00:10 heeft Keith Medcalf <[hidden email]> het volgende geschreven:
>
>
> On Wednesday, 4 September, 2019 12:18, Rob Sciuk <[hidden email]> wrote:
>
>> Forgive me if this is an FAQ, but in looking over the python3 interface to
>> SQLITE3, I cannot see a way to get the result code (SQLITE_OK) after an
>> execute() command.
>
>> My use case is to differentiate between an empty row set (OK) vs an error
>> of some kind in the query.
>
>> Anyone figured this out?
>
> If there is an error then an exception will be thrown.  No exception means no error.  Otherwise, iterating over the cursor will return the rows.  If there are no rows then it is like iterating over an empty list -- there is nothing to return (the cursor object is a generator that yields row tuples and it will internally raise StopIteration when it is out of data to return, just like any other generator).
>
> --
Possibly the Python documentation is overwhelming here as data can also be retrieved with explicit fetch steps. The example where the cursor is treated as a generator shows how simple it is:
>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print(row)

('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
('2006-04-06', 'SELL', 'IBM', 500, 53.0)
('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)

(from https://docs.python.org/3/library/sqlite3.html <https://docs.python.org/3/library/sqlite3.html>)

Personal note: even more simple is to use the execute method directly from the connection instance.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Differentiate between an empty result set and an error using Python3

Keith Medcalf

>Possibly the Python documentation is overwhelming here as data can also be
>retrieved with explicit fetch steps.

The APSW documentation is better.  The sqlite3 documentation is somewhat limited.

>>> import sqlite3
>>> db = sqlite3.connect('', isolation_level=None) # isolation_level=None turns off automagic which does not work anyway
>>> db.execute('create table x(x)')
<sqlite3.Cursor object at 0x000001BE183BEEA0>

.fetchone() returns None if there is no rows, else it returns the row tuple:

>>> print(db.execute('select x from x').fetchone())
None

.fetchall() returns an list of rows (which will be empty if there are no rows):

>>> print(db.execute('select x from x').fetchall())
[]

Calling the iterator step function manually will throw StopIteration if there are no more rows (that is how "for row in iter(...)" knows to stop iterating -- the for catches the StopIteration exception and stops executing the loop):

>>> print(next(db.execute('select x from x')))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
StopIteration

>>> print(db.execute('select x from x').__next__())
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
StopIteration


APSW works the same way.  https://github.com/rogerbinns/apsw

>>> import apsw
>>> db = apsw.Connection('')
>>> db.cursor().execute('create table x(x)')
<apsw.Cursor object at 0x000001BE17D36C30>

>>> print(db.cursor().execute('select x from x').fetchone())
None

>>> print(db.cursor().execute('select x from x').fetchall())
[]

>>> print(next(db.cursor().execute('select x from x')))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
StopIteration

>>> print(db.cursor().execute('select x from x').__next__())
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
StopIteration

--  
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users