Getting more info from python sqlite exceptions

Question

I'm using python's builtin sqlite3 DB module.

While inserting objects to my DB tables, following sqlite exception raised:

"PRIMARY KEY must be unique"

As there are different insert methods for each object, I can't say for sure in which table does it failed:

import sqlite3
...
class SomeObject1:
....
def _insert_some_object1(self, db_object):
        self._cursor.execute('insert into %s values (?,?,?)' % TABLE_NAME,
        (db_oject.v1, db_object.v2, db_object_v3,))

Exception got caught in main() by except Exception as e:, so it's only info I've got.

I would want to know in which table insertion failed, value that failed, etc...

What's the right way to get the most info from sqlite exceptions?

Thanks

Answer 1

I think this really all depends on what you are using to connect to the database. Each module will display different errors.

I personally use sqlalchemy, and it gives you detailed errors. Here is an example to show what I mean (note: this is just an example, I personally do not support inline sql):

import sqlalchemy

connection = sqlalchemy.create_engine('sqlite:///mydb.db')

cursor = connection.connect()

query = "INSERT INTO my_table (id, name) values(1, 'test');"

cursor.execute(query)

And the error that is returned:

sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be unique "INSERT INTO my_table (id, name) values(1, 'test');" ()

As far as core sqlite3 module, I don't believe it will show the query that was executed. If you don't use a module such as sqlalchemy, then you will need to handle and show the error yourself. Take a look at this as example:

import sqlite3


def execute(query):
    try:
        conn = sqlite3.connect('mydb.db')
        c = conn.cursor()
        c.execute(query)
        conn.commit()
    except Exception as err:
        print('Query Failed: %s\nError: %s' % (query, str(err)))
    finally:
        conn.close()


execute("INSERT INTO my_table (id, name) values(1, 'test');")

And the output on error:

Query Failed: INSERT INTO weapon (id, name) values(1, 'test');
Error: PRIMARY KEY must be unique

Answer 2

I have seen some code like (sqlite3 in python)

try:
  conn = sqlite3.connect('mydb.db')
  c = conn.cursor()
  c.execute(query)
  conn.commit()
except sqlite3.Error as err:
  print('Sql error: %s' % (' '.join(err.args)))
  print("Exception class is: ", err.__class__)