[Tutor] sqlite3 woes opening Moz, FFox sqlite db with only the inevitable 'DatabaseError: file is encrypted or is not a database' occurring...

[Peter Otten]

Paul Smith wrote:

> title = three days lost and counting...
> 
> Thanks in advance all:
> 
> Ok- I want to work with data in FFox, specifically a sqlite db found
> here..
> 
> 'C:
\Users\Hive2\AppData\Roaming\Mozilla\Firefox\Profiles\zabt0uq4.default\places.sqlite'
> 
> ...more specifically my FFox history found in a table 'moz_places'.
> 
> Running Py 2.7.5 on a windows 7-64 machine and after days of
> *consulting -*google; various-boards ;stackoverflow;
> py.org; sqlite.org; mingw32?(prereq. for sqlite v. 2.6.3?);
> sphynx?(another prereq. for I forget); and *addressing* - possible version
> differences between sqlite3 and FFoxdb; file path confirmation; db
> creation - query using sqlite3(successful); etc. etc.
> 
> We end up here...
> 
> import sqlite3 as lite
> import sys
> import os
> 
> print ("sqlite version info1:",lite.version)##clunky version confirm
> print ("sqlite version info2:",lite.sqlite_version,"\n")##clunk's cousin
> 
> mypath =
> os.path.realpath("C:
\\Users\\Hive2\\AppData\\Roaming\\Mozilla\\Firefox\\Profiles\\zabt0uq4.default\\places.sqlite")
> mydb = (mypath)## I'm trying to confirm the path to the friggin' thing
> print ("This is my db path""\n"+(mydb))
> con = None
> con = lite.connect(mydb)
> with con:
> cur = con.cursor()
> cur.execute("SELECT * FROM 'moz_places'")## it is a table in places.sqlite
> seems apropos
> print (con.fetchall()) ## print it all?
> con.close()
> 
> Which produces...
> 
> Python 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)]
> on win32
> Type "copyright", "credits" or "license()" for more information.
>>>> ================================ RESTART
> ================================
>>>>
> ('sqlite version info1:', '2.6.0')
> ('sqlite version info2:', '3.6.21', '\n')
> This is my db path
> C:
\Users\Hive2\AppData\Roaming\Mozilla\Firefox\Profiles\zabt0uq4.default\places.sqlite
> 
> Traceback (most recent call last):
>   File "E:\pyurlseek\zedpathconfirm.py", line 15, in <module>
>     cur.execute("SELECT * FROM 'moz_places'")
> DatabaseError: file is encrypted or is not a database
> 
> I just want to print out the table (or anything from the FFox db just to
> prove it is being opened/accessed?!)
> 
> I am sure it is a simple error but being a real newb that I am I cannot
> escape the dreaded 'DatabaseError: file is encrypted or is not a
> database', time and time again.
> 
> Help please
> 
> Thanks in advance,
> 
> PSmith
> 
> Additional items: no I did not have FFox open when running; yes I can
> access the FFox sqlite db from 3 other programs like SQLite Expert etc.
> yes I do need some sleep now so, ciao.

Trying my google-fu, looking for the error message I find

http://trac.edgewall.org/wiki/PySqlite#DatabaseError:fileisencryptedorisnotadatabase

"""
DatabaseError: file is encrypted or is not a database
 There are typically three situations in which this somewhat cryptic error 
message can be seen: 
-  when trying to modify the database file and the write permission is 
missing; fix the permissions 
-  when accessing a SQLite 2.x database file with a SQLite 3.x library; see 
above for the upgrade instructions. 
-  when accessing a database that has been created in or explicitly 
converted to WAL mode, using a version of SQLite older than 3.7.0 (i.e. with 
no WAL support); upgrade your bindings to use a recent SQLite
"""

Hm, what's WAL mode? There's a link on this very page, so I shun google and 
go directly to

http://www.sqlite.org/wal.html

"""
The default method by which SQLite implements atomic commit and rollback is 
a rollback journal. Beginning with version 3.7.0, a new "Write-Ahead Log" 
option (hereafter referred to as "WAL") is available.

[...]

Activating And Configuring WAL Mode
An SQLite database connection defaults to journal_mode=DELETE. To convert to 
WAL mode, use the following pragma:
PRAGMA journal_mode=WAL;
"""

But does Firefox use WAL mode? Luckily I don't get the error over here:

>>> import sqlite3
>>> db = sqlite3.connect("places.sqlite")
>>> cs = db.cursor()
>>> for row in cs.execute("pragma journal_mode;"): print row
... 
(u'wal',)

So yes. You need a newer version of sqlite to read the database.

I don't know what version of sqlite is shipped with Python 3.3 -- maybe a 
windows user can chime in.

If you don't want to switch to Python 3.3 -- or if it doesn't feature an 
sqlite version that supports WAL -- you could use sqlite's commandline tools 
to switch off WAL on a copy (!) of places.sqlite.