python - 当SQL语句不返回任何行时，停止Python引发错误

Question

我一直在使用python自动执行一些SQL查询，并且我一直在尝试try和除了捕获错误。这在大多数情况下都能正常工作，但是如果我的SQL语句不返回行(例如，插入数据库的表中)，则会发送错误并停止脚本。

错误看起来像:

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

有没有一种使用case语句或类似语句的方法，以便如果错误与以上相同，则该错误继续运行，否则停止？

样例代码:

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)

Answer 1

检查此答案的第一部分: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