SQLite & Python working together on OSX

[bkatiemills]

The python script at the top of the programming lesson fails for me, with the complaint

Traceback (most recent call last):
  File "test.py", line 4, in <module>
    cursor.execute("select site.lat, site.long from site;")
sqlite3.DatabaseError: file is encrypted or is not a database

A little googling suggests that python 2.7 uses some ancient sqlite version, that doesn't play nice with more recent ones; there are some suggestions on how to fix this on windows here and here, and something about coping with this in a virtualenv here, but this wasn't helpful to the casual OSX-using pythonista.

Does anyone know how to make Python 2.7 and SQL play nice together on OSX 10.9.5?

$ python --version
Python 2.7.8 :: Anaconda 2.1.0 (x86_64)

$ sqlite3 --version
3.7.13 2012-07-17 17:46:21 65035912264e3acbced5a3e16793327f0a2f17bb

[bkatiemills]

easiest fix is to just build the .db from a python script in situ - see #12 for my implementation.

[abbycabs]

I believe standard practice would be to include the .sql file to build the database (created using .dump > gen-survey-database.sql) then you can build the database from the file (sqlite3 survey.db < gen-survey-database.sqll).

The sql script exists here: https://github.com/swcarpentry/sql-novice-survey/blob/gh-pages/code/gen-survey-database.sql
-- Did you build the .db from this script? Or did you just copy an existing survey.db file from somewhere?

[bkatiemills]

So you're saying I created the most convoluted solution to this possible? :)

You're totally right, you can generate a working .db in the way you describe - I never saw that before. Actually, I think there's some confusion surrounding loading databases, since every time I've seen / taught this lesson, I've seen it done differently. Thanks @acabunoc, I'll close this and #12 and put some clarifying comments in the notes.

[abbycabs]

You may still run into the same problem building the db this way! Double check first :)

[bkatiemills]

looks ok to me.