How to get SQLite result/error codes in Python

Question

How do I get the (extended) result/error code from an SQLite query in Python? For example:

con = sqlite3.connect("mydb.sqlite")
cur = con.cursor() 
sql_query = "INSERT INTO user VALUES(?, ?)"     
sql_data = ("John", "MacDonald")

try:
    cur.execute(sql_query, sql)
    self.con.commit()

except sqlite3.Error as er:
    # get the extended result code here

Now suppose the first column should be unique and there is already a database entry with "John" in the first column. This will throw an IntegrityError, but I'd like to know the SQLite result/error code as stated on http://www.sqlite.org/rescode.html#extrc. I want to know, because I want to take a different action for different errors.

score 11 · Answer 1 · answered Aug 09 '16 at 09:53

Currently, you can't get error codes through Python's sqlite3 module. Per https://www.sqlite.org/c3ref/errcode.html, the C API exposes basic error codes, extended error codes, and error messages through sqlite3_errcode, sqlite3_extended_errcode and sqlite3_errmsg respectively. However, searching the CPython source reveals that:

sqlite3_extended_errcode never even gets called
sqlite3_errmsg gets called and the result exposed as an Exception message
sqlite3_errcode gets called, but the result is never exposed directly; it's just used to decide which Exception class to raise

While the feature you're asking for would be useful (indeed, I need it right now for debugging and am frustrated by its absence), it simply doesn't exist right now.

There's https://bugs.python.org/issue16379 tracking this deficiency and https://github.com/python/cpython/pull/1108/files is an effort to make things better. — Dima Tisnek, Jun 07 '19 at 00:20

score 10 · Answer 2 · edited Aug 24 '21 at 01:06

#More info on related error can be taken by:

    import sqlite3
    import traceback
    import sys
    
    con = sqlite3.connect("mydb.sqlite")
    cur = con.cursor() 
    sql_query = "INSERT INTO user VALUES(?, ?)"     
    sql_data = ("John", "MacDonald")
    
    try:
        cur.execute(sql_query, sql_data)
        con.commit()
    except sqlite3.Error as er:
        print('SQLite error: %s' % (' '.join(er.args)))
        print("Exception class is: ", er.__class__)
        print('SQLite traceback: ')
        exc_type, exc_value, exc_tb = sys.exc_info()
        print(traceback.format_exception(exc_type, exc_value, exc_tb))
    con.close()

How to get SQLite result/error codes in Python

2 Answers2

Linked