SQLite3__Python2.3-SQLite__Problem

[John Machin]

Cousin Stanley wrote:
> It's been almost 2 years since I've done anything
>   with Python and SQLite and I'm having some problems
>   that I don't recall from my last usage ....
>
>   It seems that SQLite3 data bases created at the command line
>   and those created using the sqlite module from within Python
>   are no longer compatible with each other using the setup that
>   I now have ....
>
>   I thought I remembered that the data bases created either way
>   were always 100% transparent with each other and that I could
>   use an SQLite3 data base either from the command line or from
>   within Python without any problems at all ....

My guess is that 2 years ago you were using sqlite 2, not 3.

>
>   I did a fair amount of Google-izing looking for related problems
>   but didn't have much success ....
>

In that case your googler is absolutely rooted and should be replaced
immediately.

With mine, google("file is encrypted or is not a database") produces as
first hit (would you believe!?)
http://wiki.rubyonrails.org/rails/pages/HowtoUseSQLite
which contains the text:

"""
Q: SQLite::Exceptions::\DatabaseException file is encrypted or is not a
database.

A: It seems that sqlite databases created with version 2 do not work
with sqlite version 3 and vice versa.
"""

IOW sqlite is a candidate for nomination to the "We Could Have Given A
Much More Helpful Error Message" Hall of Illfame :-)

Alternatively, you could get it straight from the the horse's mouth, at
http://www.sqlite.org/version3.html

"""The format used by SQLite database files has been completely
revised. The old version 2.1 format and the new 3.0 format are
incompatible with one another. Version 2.8 of SQLite will not read a
version 3.0 database files and version 3.0 of SQLite will not read a
version 2.8 database file."""

According to http://www.sqlite.org/oldnews.html, the first non-beta
version of 3.0 was released in September 2004. You appear to have a 3.2
version of the sqlite3 command-line utility. So far, so good. However,
I would be very suspicious of an sqlite that came with Python 2.3 --
it's probably sqlite version 2.something

>From your 2nd message:

> I'm now off to look for a version of PySQLite2
> that is built for Debian Sarge and Python 2.3
> to see if that might help to rectify the problem ....

Sounds like a good idea. Change it to read "that has been built
*recently*" and it sounds like an even better idea :-)

On Windows, with Python 2.4 (just like your trial):

| >>> from pysqlite2 import dbapi2 as DB
| >>> DB.version # version of pysqlite2
| '2.3.2'
| >>> DB.sqlite_version # This is the one that matters!
| '3.3.6'

Try extracting sqlite_version from your Linux setup.

Aside: with Python 2.5:
| >>> import sys; sys.version
| '2.5 (r25:51908, Sep 19 2006, 09:52:17) [MSC v.1310 32 bit (Intel)]'
| >>> import sqlite3; sqlite3.sqlite_version
| '3.3.4' # time warp?

As you are discovering with Debian, one of the downsides of having
3rd-party goodies bundled in instead of downloading and installing them
yourself is that thay can become frozen in time -- not quite as bad as
the ruins of Pompeii; although Python 2.3 is getting close :-)

HTH,
John