James Tauber

journeyman of some

blog > 2006 > 11 > 25 >

Incompatible SQLite in OS X and Python

I started my porting of Quisition to django last night.

For development, I'm using SQLite as my database, but it seems I've hit a problem with conflicting versions.

Mac OS X comes with SQLite 3.1.3. That's what's used running sqlite3 at the command-line or running django's dbshell.

However, I'm guessing that when my application is running normally, django is using the SQLite that comes with Python 2.5 (which looks like it could be 3.3.5 or something).

As a result, databases created by django cannot be viewed using sqlite3 at the command-line or running dbshell. There must be a file format incompatibility between SQLite 3.1.x and 3.3.x as I get the error message:

Error: unsupported file format

UPDATE: I downloaded SQLite 3.3.8 and built it (although only after working out I needed to do a gcc_select 4.0 first). The new sqlite3 works fine on the django-generated database.

Categories:
prev « python » next
prev « django » next
prev « os_x » next
sqlite

Comments (6)

Brian on Nov. 25, 2006:

Hi I hope won't stop working on Pyjamas. It is a really great project.

larry on Nov. 28, 2006:

Good find. I am in the planning phase of some stuff that will use SQLite. Thank you.

You mention you had to fiddle some. Did you replace the framework or put SQLite upstream in /usr/local? Perhaps you could post your solution? Thanks.

James Tauber on Nov. 29, 2006:

Brian, don't worry - worked hasn't stopped on pyjamas. I'm using it for the client side parts.

James Tauber on Nov. 29, 2006:

Larry, after gcc_select 4.0, I did the normal ./configure; make; sudo make install

Then I just renamed /usr/bin/sqlite to sqlite3.1.3 and made /usr/bin/sqlite a symlink to /usr/local/bin/sqlite

Tim Finer on May 6, 2007:

Thanks for your solution! I just ran into the same problem and used your symlink method to get around it. I also just pyjamas - very cool.

Matthew Bogosian on May 14, 2007:

There is another work around, albeit a tedious one. Before you do the first "syncdb", zero out the database file by hand (warning, this will truncate the file and erase any data previously contained therein):

$ cat /dev/null >/path/to/django/project/db.sqlite3

Now start the command line version of sqlite3 with that new (empty) file:

$ sqlite3 /path/to/django/project/db.sqlite3
SQLite version 3.1.3
Enter ".help" for instructions
sqlite>

Create some kind of dummy data to populate the file, but remove it immediately:

sqlite> CREATE TABLE dummy ( id INTEGER ) ;
sqlite> DROP TABLE dummy ;
sqlite> VACUUM ;
sqlite> SELECT * FROM sqlite_master ;
sqlite> .exit

Now you can safely do a .../manage.py syncdb and you will be able to continue using the sqlite3 command line interface.

What I suspect happens (that allows this to work) is that the entity responsible for creating the initial data structures in the file "freezes" that format at the version of the creating entity. If Python 2.5 gets there first, then 3.3.x is the format and the 3.1.x sqlite3 command line utility can't read it. However, if the sqlite3 command line utility gets there first, then Python 2.5 is happy because 3.3.x seems to behave in some backward-compatible fashion when it notices a file format from a previous version.

Add a Comment

Created: Nov. 25, 2006
Last Modified: Nov. 25, 2006
Author: jtauber