We use SQLite as a file format for our desktop application (based on PySide) using SQLAlchemy. I decided to use SQLite after realizing I was slowly reimplementing many features of a database. I also read this [0] article entitled "SQLite As An Application File Format," which was the final straw for me to take the plunge.

Overall, the experience has been a joy. Our application is an engineering simulation application. Our core simulator is written in Matlab for historical reasons, and we can communicate data easily using the database. Writing GUI models and views over the database on the Python end of things is very straightforward, once you have your SQLAlchemy models set up.

I agree that migrations can be a pain, but thankfully our tables are usually small enough that we can alter tables simply be recreating them. I also found this [1] StackOverflow answer that explains how to easily change a column name in place.

Those looking for a GUI to view SQLite databases should check out sqlitebrowser [2]. It's the best I've seen, and the developers are very responsive to bug reports and pull requests. It is also cross platform, and is present in many Linux package repos.

[0] http://www.sqlite.org/appfileformat.html

[1] http://stackoverflow.com/a/6684034/1333514

[2] https://github.com/sqlitebrowser/sqlitebrowser