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 https://www.sqlite.org/rescode.html#extrc. I want to know, because I want to take a different action for different errors.

Answer 1

Python 3.11 and above

sqlite3.Error objects now contain sqlite_errorcode and sqlite_errorname attributes, respectively containing the extended error code (where applicable) or primary error code (where there is no applicable extended error code) and the name associated with that code.

New module-level constants for extended error code values have also been added. For example, sqlite3.SQLITE_CONSTRAINT_CHECK is 275. (You can look up the meanings of the codes in the SQLite docs at https://sqlite.org/rescode.html.)

Here's an example based on the module's unit tests, which prints the extended error code associated with a check constraint violation:

import sqlite3
con = sqlite3.connect("mydb.sqlite")
con.execute("create table t(t integer check(t > 0))")

try:
    con.execute("insert into t values(-1)")
except sqlite3.Error as er:
    print(er.sqlite_errorcode)  # Prints 275
    print(er.sqlite_errorname)  # Prints SQLITE_CONSTRAINT_CHECK

Python version prior to 3.11

In old versions of Python, you couldn'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 prior to 3.11 used to reveal that:

• sqlite3_extended_errcode never even got called (though it does now - this changed in 3.11)
• 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

Answer 2

It is possible since Python 3.11.

import os
import sqlite3

DB_FILE = "mydb.sqlite"

if os.path.exists(DB_FILE):
    os.remove(DB_FILE)

con = sqlite3.connect(DB_FILE)
cur = con.cursor()
cur.execute("CREATE TABLE user (first_name TEXT UNIQUE, las_name TEXT)")

try:
    cur.execute("INSERT INTO user VALUES(?, ?)", ("John", "MacDonald"))
    cur.execute("INSERT INTO user VALUES(?, ?)", ("John", "MacDonald"))
except sqlite3.IntegrityError as e:
    print(e)
    print("Error code:", e.sqlite_errorcode)
    assert e.sqlite_errorcode == sqlite3.SQLITE_CONSTRAINT_UNIQUE
    print("Error name:", e.sqlite_errorname)

con.rollback()
con.close()

would return

UNIQUE constraint failed: user.first_name
Error code: 2067
Error name: SQLITE_CONSTRAINT_UNIQUE

The support was added by this MR: https://github.com/python/cpython/pull/28076

Answer 3

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.

Answer 4

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