Stop Python throwing errors when SQL statement returns no rows

Question

I've been automating some SQL queries using python and I've been experimenting with try and except to catch errors. This works fine most of the time but if my SQL statement doesn't return rows (e.g. inserting into a table on the database) then it sends an error and stops the script.

The error looks like:

This result object does not return rows. It has been closed automatically.

Is there a way to use a case statement or similar so that if the error is the same as the above, it continues on running, otherwise it stops?

Sample code:

import time
import logging   
import datetime
import sys
from datetime import timedelta

def error_logs(e):
    #calculate running time
    runtime = (time.time() - start_time)

    #capture error messages (only using Line number)
    exc_type, exc_obj, exc_tb = sys.exc_info()
    #fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
    line =  'Line ' + str(exc_tb.tb_lineno)   

    #print the error 
    logging.exception("Error")
    message = "***ERROR***: Script Failed. "
    write_logs((str(datetime.datetime.now()) + ", " + message + str(e) + ". " + line + ". Run time: " + str(round(runtime)) + " seconds." + "\n"))

def write_logs(message):
    log_path = r"C:\Logs\Logs.txt"
    with open(log_path, 'a+') as log:        
        log.write(str(datetime.datetime.now()) + ", " + message + "\n")

try:
    db.query(''' 
    insert into my_table (column1, column2, column3)
    select * from my_other_table
    where date = '2019-09-12'
    ''')

except Exception as e:    
    error_logs(e)

Is it a *raised exception*? A *logged message*? What library are you using? That's not valid Python… — deceze, Sep 12 '19 at 13:40
Sorry. Wasn't thinking straight! Of course you need more info. I've updated the original post. Please consider removing your downvote if you did so :) — Lucas, Sep 12 '19 at 13:47
So `db.query` raises an exception on empty results? I'd start there, that seems like unusual behaviour. What is `db`? Your own class? Some existing database adapter? – If the behaviour can't be fixed there, then what type of error is it raising? Some distinct type, or something generic? — deceze, Sep 12 '19 at 13:50
Whatever db is, it may distinguish between queries (which can return rows) and the used "insert" statement which can't. So "db.query" may be just the wrong method. — Michael Butscher, Sep 12 '19 at 13:51
db.query is a custom function we have which connects python to Redshift. From googling the error message it seems like it is using SQLAlchemy in the background. @deceze how do I know what type of error it is? The Exception is only "This result object does not return rows. It has been closed automatically" — Lucas, Sep 12 '19 at 13:55
Here's more details: "This result object does not return rows. " sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. — Lucas, Sep 12 '19 at 13:58
did you test query directly in database ? Maybe it work incorrectly. Maybe `select` gets no data so `insert` doesn't insert data and this is why `object does not return rows` — furas, Sep 12 '19 at 13:58
@furas that was my first thought. The query is in fact updating the SQL table with the new data as intended. So the script works, it just thinks it's having an error since it expects some data back I suppose. — Lucas, Sep 12 '19 at 14:01
if `db.query()` is your own function then check in this function if it expects rows and maybe you should add some `if/else` to resolve this problem — furas, Sep 12 '19 at 14:01

score 1 · Answer 1 · answered Sep 12 '19 at 14:00

Check the first part of this answer: https://stackoverflow.com/a/14388356/77156

First answer - on "preventing automatic closing".

SQLAlchemy runs DBAPI execute() or executemany() with insert and do not do any select queries. So the exception you've got is expected behavior. ResultProxy object returned after insert query executed wraps DB-API cursor that doesn't allow to do .fetchall() on it. Once .fetchall() fails, ResultProxy returns user the exception your saw.

The only information you can get after insert/update/delete operation would be number of affected rows or the value of primary key after auto increment (depending on database and database driver).

If your goal is to receive this kind information, consider checking ResultProxy methods and attributes like:
.inserted_primary_key
.last_inserted_params()
.lastrowid
etc

Yea I found this part of code in SQLAlchemy's result.py file: def _non_result(self, default): if self._metadata is None: raise exc.ResourceClosedError( "This result object does not return rows. " "It has been closed automatically.", ) elif self.closed: raise exc.ResourceClosedError("This result object is closed.") else: return default — Lucas, Sep 12 '19 at 14:06

score 0 · Answer 2 · answered Sep 12 '19 at 14:31

I found a workaround to my own question. You can force the SQL to return some rows of data by adding a dummy select statement at the end. This way it won't think the results are empty and thus it won't throw an error.

db.query(''' 
    insert into my_table (column1, column2, column3)
    select * from my_other_table
    where date = '2019-09-12';
    select 'test';
    ''')

The problem stemmed from SQL Alchemy's code searching for some metadata within the results of the sql statement (and there aren't any for an insert statement).

From result.py in SQLAlchemy package

def _non_result(self, default):
        if self._metadata is None:
            raise exc.ResourceClosedError(
                "This result object does not return rows. "
                "It has been closed automatically.",
            )
        elif self.closed:
            raise exc.ResourceClosedError("This result object is closed.")
        else:
            return default

You’d think there was some way to do an INSERT query without raising an error in SQLAlchemy… ‍♂️ — deceze, Sep 12 '19 at 17:52

Stop Python throwing errors when SQL statement returns no rows

2 Answers2