473,326 Members | 2,805 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQLite3__Python2.3-SQLite__Problem


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 ....

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

I did find one entry in the Debian Bug Tracking System
that had some discussions about SQLite3 & Python-SQLite versioning
but the original errors mentioned didn't seem to be of the same nature
as what i'm seeing here ....

================================================== =========

Operating System .... Debian GNU/Linux Sarge

python2.3 ........... Installed : 2.3.5-3sarge2

python2.3-sqlite .... Installed : 1.0.1-2

sqlite3 ............. Installed : 3.2.1-1

libsqlite3-0 ........ Installed : 3.2.1-1
Date SQLite3 Data Base Created Via
========== ================= ===========

2005-02-20 .... abook_00.sql3 ......... sqlite3 command line
2006-11-23 .... abook_01.sql3 ......... python module

SQLite3 data bases created via the command line
and those created using the python2.3-sqlite package
version 1.0.1-2 from within a Python program
are not compatible with each other ....

If I create an SQLite3 data base from the command line
and populate it with data, then I cannot use that db
from Python ....

If I create an SQLite3 data base from within Python
and populate it with data, then I cannot use that db
from the command line ....

The following console sessions illustrate the problem ....

================================================== =========

# Using an SQLite3 data base created at the command line

$ sqlite3 abook_00.sql3
SQLite version 3.2.1
Enter ".help" for instructions
sqlite>
sqlite.schema
CREATE TABLE addr( nid int ,
atype int ,
aid int ,
address varchar( 128 ) );
CREATE TABLE names( nid int,
name varchar( 32 ) );
sqlite>
sqlite.mode column
sqlite.width 16 32
sqlite>
sqliteselect names.name , addr.address
...from names , addr
...where names.nid = addr.nid ;
Bugs Bunny Rabbit Hole 0
Donald Duck Duck Pond 1
Goofy G Hut 2
Mickey Mouse Mouse Hole 3
Sylvester Cat House 4
sqlite>
================================================== =========

# Try to query that data base from within Python

$ python abook_select.py abook_00.sql3
Traceback (most recent call last):
File "abook_select.py", line 18, in ?
dbc = sqlite.connect( db = path_db )
File "/usr/lib/python2.3/site-packages/sqlite/__init__.py", line 61, in connect
return Connection(*args, **kwargs)
File "/usr/lib/python2.3/site-packages/sqlite/main.py", line 445, in __init__
self.db = _sqlite.connect(database, mode)
_sqlite.DatabaseError: file is encrypted or is not a database
================================================== =========

# Try the same query on a Python-created data base

$ python abook_select.py abook_01.sql3

abook_select.py

Name ............ Address

Donald Duck .... Duck Pond 1
Bugs Bunny .... Rabbit Hole 0
Mickey Mouse .... Mouse Hole 3
Goofy .... G Hut 2
Sylvester .... Cat House 4
================================================== =========

# Try to use the Python-created data base from the command line

$ sqlite3 abook_01.sql3
SQLite version 3.2.1
Enter ".help" for instructions
sqlite>
sqlite.schema
Error: file is encrypted or is not a database
sqlite>
--
Stanley C. Kitching
Human Being
Phoenix, Arizona
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 23 '06 #1
5 3985
....
SQLite3 data bases created via the command line
and those created using the python2.3-sqlite package
version 1.0.1-2 from within a Python program
are not compatible with each other ....

If I create an SQLite3 data base from the command line
and populate it with data, then I cannot use that db
from Python ....

If I create an SQLite3 data base from within Python
and populate it with data, then I cannot use that db
from the command line ....
....
This problem is occuring under Debian GNU/Linux Sarge
using Python 2.3 ....

This morning for a sanity check on myself
I installed SQLite3 and PySQLite2 on a Win2K box
running ActiveState Python 2.4 ....

The only things that I had to change in the Python code
for creating tables, inserting data, and querying the db
that I'm also using under Debian were the import lines ....

from .... import sqlite

to ...... from pysqlite2 import dbapi2 as DB

Using this setup under Win2K everything works as expected ....

That is SQLite3 data bases created either via the sqlite3
command line or from within Python are 100% transparent,
and no errors are produced ....

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 ....
--
Stanley C. Kitching
Human Being
Phoenix, Arizona
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 24 '06 #2
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

Nov 24 '06 #3
Le Fri, 24 Nov 2006 13:18:14 -0600,
Cousin Stanley <co***********@hotmail.coma écrit :

This problem is occuring under Debian GNU/Linux Sarge
using Python 2.3 ....
Hi,

if you look at
http://packages.debian.org/stable/py...thon2.3-sqlite,
you will see that the python2.3-sqlite package is built against
SQLite 2. This is why you have a "file is encrypted or is not a
database" message, since databases created with SQLite 2.x are not
file-compatible with SQLite 3.x file.
So, if you can find a Python binding for SQLite 3, go for it, but,
unless you are building it from scratch (I mean, not from a Debian
package), it might be difficult to find.

Otherwise, use the sqlite command line tool (not sqlite3), which is
built against SQLite 2.x.
If you go for this solution, it's easy to migrate from a 2.x to a 3.x
database, something like :
$ echo ".dump" sqlite database.db.2x | sqlite3 database.db.3x
(you will have to change the Python accordingly, of course ;)

Hope it helps,

- Jon
Nov 24 '06 #4
> ....
> 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 think that is probably a very good guess ....

> 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!?)
My Googl-er is probably OK, but the Google-ee ( e.g. me )
is always in need of a tune-up .... :-)
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
I did re-build the data bases from the command line
using sqlite3 instead of using the older ones
from a couple of years back which I wasn't sure
were sqlite2 or sqlite3 ....

It seems that the older Debian Sarge version
of the python2.3-sqlite package is the one
that isn't directly compatible with sqlite3 dbs
created at the command line ....

I downloaded the PySQLite2 source package
and compiled and installed it using distutils ....

Now everything is also working fine under Debian Sarge
and the dbs are OK either from the command line or from
within Python ....

Thanks a lot for taking the time to help ....
--
Stanley C. Kitching
Human Being
Phoenix, Arizona
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 25 '06 #5
if you look at
http://packages.debian.org/stable/py...thon2.3-sqlite,
you will see that the python2.3-sqlite package is built against
SQLite 2. This is why you have a "file is encrypted or is not a
database" message, since databases created with SQLite 2.x are not
file-compatible with SQLite 3.x file.
....
Jon ....

I downloaded the PySQLite2 source package
and compiled and installed it using distutils ....

Everything is now working as expected
under Debian Sarge ....

Thanks a lot for taking the time to help out ....
--
Stanley C. Kitching
Human Being
Phoenix, Arizona
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Nov 25 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.