CODEWITHSUNDEEP
pythonsqlitesqlalchemy
mblw
mblw

Reputation: 1798

SQLAlchemy IntegrityError violated constraint name

I have a lot of different constraints on columns of the database tables. Data that inserted into the database usually contain arrays of numbers (on these numbers constraints were set), for example (part of DDL):

CREATE TABLE "Object" (
    id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES "Generic" (id)  // Inheritance
);

CREATE TABLE "ObjectData" (
    id INTEGER NOT NULL, 
    x FLOAT NOT NULL, 
    y FLOAT NOT NULL, 
    d FLOAT NOT NULL, 
    a FLOAT NOT NULL, 
    e FLOAT NOT NULL, 
    object_id INTEGER NOT NULL, 
    PRIMARY KEY (id), 
    CONSTRAINT "duplicate x, y values" UNIQUE (x, y, object_id), 
    CONSTRAINT "x must be >= -1.0" CHECK (x >= 0.0), 
    CONSTRAINT "x must be <= 1.0" CHECK (x <= 1.0), 
    CONSTRAINT "y must be >= -1.0" CHECK (y >= -1.0), 
    CONSTRAINT "y must be <= 1.0" CHECK (y <= 1.0), 
    CONSTRAINT "d must be >= -1.0" CHECK (d >= 0.0), 
    CONSTRAINT "d must be <= 1.0" CHECK (d <= 1.0), 
    CONSTRAINT "a must be >= -270.0" CHECK (a >= -270.0), 
    CONSTRAINT "a must be <= 270.0" CHECK (a <= 270.0), 
    CONSTRAINT "e must be >= -1.0" CHECK (e >= -1.0), 
    CONSTRAINT "e must be <= 1.0" CHECK (e <= 1.0), 
    FOREIGN KEY(object_id) REFERENCES "Object" (id)
);

Inserting data looks like the follows (part of input text file that parsed to insert into DB):

DATA:
-1.0  -1.0  1.0  242.0  0.0
-1.0  -2.0  1.0  124.0  0.0
-1.0  -1.0  1.0  109.0  0.0
...........................
-1.0  -1.0  1.0  242.0  0.0
-1.0  -2.0  1.0  124.0  0.0
-1.0  -1.0  1.0  109.0  0.0

So if any of these values will violate one of constraint and no message will show then it extremely hard to determine which of values are bad. For this purposes descriptive names of constraints were given. Work with SQLite3 database carry out by means of SQLAlchemy ORM and when sqlalchemy.exc.IntegretyError occures this library completly cut off name of violated constraint.

Googling result in presence of constraint name in IntegrityError exception in SQLite3 have been started from [version 3.3.2][1]. That shows short test in Python REPL: 

>>> import sqlite3
>>> conn = sqlite3.connect('test/test.db')
>>> c = conn.cursor()
>>> c.execute("""INSERT INTO "ObjectData" (x, y, d, a, e, object_id) VALUES (?, ?, ?, ?, ?, ?)""", (-2.0, -1.0, 1.0, 242.0, 0.0, 1))
Traceback (most recent call last):
  File "<input>", line 1, in <module>
IntegrityError: CHECK constraint failed: x must be >= -1.0

But when I try to insert data using SQLAlchemy I get only message that can't describe nothing what happened wrong. Here the result of dir(error):

detail = []
params = (-2.0, -1.0, 1.0, 242.0, 0.0, 1)
statement = INSERT INTO "ObjectData" (x, y, d, a, e, object_id) VALUES (?, ?, ?, ?, ?, ?)
instance = <bound method type.instance of <class 'sqlalchemy.exc.IntegrityError'>>
args = ('(IntegrityError) constraint failed',)
orig = constraint failed
orig.message = constraint failed

So the question is there any way to force SQLAlchemy return violated constraint name because it greatly simplified code and allow to show user informative messages about errors? Or may be other way exists?

Upvotes: 2

Views: 2724

Answers (1)

mblw
mblw

Reputation: 1798

I've found what was wrong with SQLAlchemy library usage. When I ran software in debug mode and break when IntegrityError exception raised (break point was set in DBAPIError.instance method of SQLAlchemy library) then I've realised that original error type is class 'pysqlite2._sqlite.Error' rather than sqlite3. It appeared that in site-package library pysqlite2 has been installed in my Python environment but that is not the same sqlite3. I tried to use simple workaround: delete pysqlite2 directory from the site-package. After that original exception type become class 'sqlite3.Error' and orig.message in exception contain exactly what I expect. So it obvious that SQLAlchemy use pysqlite2 library by default rather than built-in sqlite3 library.

Upvotes: 1

Related Questions