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 15 · Answer 1 · edited Jun 10 '22 at 10:27

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()

score 13 · Answer 2 · 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
I just started using `e.sqlite_errorname` as I found it while debugging in 3.11. It is not present in 3.10 or prior. — altendky, Jul 10 '23 at 15:54

ChesuCR · Answer 3 · 2022-06-10T13:57:05.827

Well, the question is old and I think they already exposed some errors to catch them. If you want to manage just Integrity Errors as it was estated in the question you can do:

import sqlite3
import os, traceback

if os.path.exists("test.db"):
    os.remove("test.db")

con = sqlite3.connect('test.db')
cur = con.cursor()
cur.execute("create table lang (name TEXT UNIQUE, first_appeared)")

try:
    cur.execute("insert into lang values (?, ?)", ("C", 1972))
    cur.execute("insert into lang values (?, ?)", ("C", 1972))
except sqlite3.IntegrityError as e:
    print('INTEGRITY ERROR\n')
    print(traceback.print_exc())

con.commit()
con.close()

In addition, you can check also for these errors that they pusblished in the docs:

exception sqlite3.Warning
# A subclass of Exception.

exception sqlite3.Error
# The base class of the other exceptions in this module. It is a subclass of Exception.

exception sqlite3.DatabaseError
# Exception raised for errors that are related to the database.

exception sqlite3.IntegrityError
# Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It is a subclass of DatabaseError.

exception sqlite3.ProgrammingError
# Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It is a subclass of DatabaseError.

exception sqlite3.OperationalError
# Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, etc. It is a subclass of DatabaseError.

exception sqlite3.NotSupportedError
# Exception raised in case a method or database API was used which is not supported by the database, e.g. calling the rollback() method on a connection that does not support transaction or has transactions turned off. It is a subclass of DatabaseError.

How to get SQLite result/error codes in Python

3 Answers3

Linked