If you use sqlite3 as a shell to manage SQLite databases, then you
might want to consider using APSW, "Another Python SQLite Wrapper".
When run through the Python interpreter, the APSW library has an interactive shell, much like the one included with sqlite3, but with some added functionality like .autoimport. A list of what APSW does better can be found in the docs.
Update sqlite. Force upgrade of sqlite3 since an
older version ships with the Mac. This will be placed in
/usr/local/bin, preserving the location of the OS version at
/usr/bin. I needed at least v3.8.
brew install sqlite3 brew link --force sqlite3 rehash which sqlite3
Download APSW. Download the source ZIP and unpack it in a temporary folder. Find the folder and run this command:
python2.7 setup.py build_ext -R /usr/local/opt/sqlite/lib -I /usr/local/opt/sqlite/include --enable-all-extensions install
After install, you simply run it by typing
python -c "import apsw;apsw.main()"
I use an alias in zsh to make launching it easier.
alias s='python -c "import apsw;apsw.main()"'
A poorman's Excel using SQLite, APSW and csv2sql. Some good
handwritten CSV import code here in Python usinter itertools,
sqlite3 and optparse.
A note from the author Roger Binns on Stack Overflow.
And another note on Hacker News from the Roger:
The reason why the sqlite3 module (aka pysqlite) behaves the way it does is because of DBAPI (Python standard database API). That tries to make all databases look and behave the same. pysqlite could do more - eg the SQLite error code could be an attribute on the exception, but the code base is very rarely updated.
Wanting a Python wrapper for SQLite that celebrated SQLite's features, functionality and semantics rather than paper them over is why I started APSW a decade ago. It also has considerably better testing and diagnostics, and is thread safe.