I haven't been able to test this via Python because my system sqlite3 version isn't new enough.  But I was able to test this against sqlite3 directly.  I suspect there may be no issue.

John, have you tried naming your constraint?

http://www.sqlite.org/syntaxdiagrams.html#column-constraint

Specifically, if I try your example in sqlite3 3.7.13 (from the command-line, independent of Python), I get the generic error message:

"Error: constraint failed"

But if I add "CONSTRAINT name" to your test case SQL, e.g.

create table test1 (
        db_insert_date       timestamp,
        CONSTRAINT test_constraint1
        check(cast(substr(db_insert_date,1,4) as integer) <= 2025),
        CONSTRAINT test_constraint2
        check(cast(substr(db_insert_date,6,2) as integer) <= 12)
);

I get the error message "Error: constraint test_constraint2 failed".

I suspect things will work fine in Python because the Python code seems simply to use whatever error message sqlite3 provides it:

case SQLITE_CONSTRAINT:
case SQLITE_MISMATCH:
    PyErr_SetString(pysqlite_IntegrityError, sqlite3_errmsg(db));
    break;

(from http://hg.python.org/cpython/file/ca54c27a9045/Modules/_sqlite/util.c#l92 )