SQLite

SQLite is an open source relational database included with the Python standard library as of Python 2.5. The pysqlite database driver is also included with the standard library so that no further external dependencies are required to access a SQLite database from within Python applications.

Useful SQLite tools and code

SQLite is used in such a wide variety of industries that there are open source tools and example code for all kinds of edge case uses. Here are several tools and bits of code I have found useful while coding my applications:

• sqlitebiter (source code) is a command-line tool for converting various data formats such as comma-separated values (CSV), HTML, Markdown and JSON (among others) into a SQLite database file.

• Scout (source code) is a Flask-powered search server for SQLite backends. The introductory post is really handy for getting started with Scout.

• Datasette makes it easy to expose JSON APIs from your SQLite database without coding up a custom web application. Make sure to check out the Datasette getting started guide as well.

• SQLite Browser is an open source graphical user interface for working with SQLite.

• The Membership SQLite SQL scripts provide example code for storing user accounts, roles and authentication tokens in web applications.

• ExtendsClass is an online SQLite browser.

SQLite tutorials

It's a good idea to brush up on the basics for using SQLite before you use the database in your project through SQL scripts or via an object-relational mapper. These tutorials will help you get started.

• A simple step-by-step SQLite tutorial walks through creating databases as well as inserting, updating, querying and deleting data.

• A Minimalist Guide to SQLite shows how to install SQLite, load data and work with the data stored in a new SQLite database.

• Python SQLite3 Basics covers how to connect to a SQLite database in Python, executing statements, committing and retrieving saved values.

• sqlite3 - embedded relational database is an extensive tutorial showing many of the common create, read, update and delete operations a developer would want to do with SQLite.

• The official sqlite3 module in the Python stdlib docs contains a bunch of scenarios with code for how to use the database from a Python application.

• Finding bugs in SQLite, the easy way explains how a bug was found - and quickly fixed - in the SQLite codebase. It's a great short read which shows that the code is well-tested and maintained.

• SQLite is not a toy database is a whirlwind overview of some of the best aspects of SQLite and why you should use it.

• Data Analysis of 8.2 Million Rows with Python and SQLite explains how you can load a large dataset in to SQLite and visualize it using the Plotly service.

• SQLite: The art of keep it simple uses C code examples from SQLite's codebase to show how its design has been kept consistent and tight throughout 15+ years of active development. There's also a great design document on the SQLite site that covers many of these principles.

• My list of SQLite resources is a nice roundup of useful tools to use with SQLite and tutorials for learning more about the database.

• Python SQLite3 tutorial provides another beginner's tutorial using the built-in sqlite3 Python standard library module.

• A SQLite tutorial with Python covers both SQL and Python code to interact with SQLite.

Specific SQLite scenarios

These are solid resources if you are looking to solve a particular problem you are having with SQLite rather than going through a general tutorial.

• Let's Build a Simple Database is an awesome read where the author re-creates a SQLite-type database for learning purposes.

• We are pretty happy with SQLite & not urgently interested in a fancier DBMS gives the rationale behind one development teams' decision to stick to SQLite instead of porting to another relational database such as MySQL or PostgreSQL.

• This overview of SQLite as part of the Databaseology Lectures is amazing because they are given by the creator and he shines a ton of light on how SQLite is built and why.

• How SQLite is tested digs into the nitty-gritty behind the quality assurance practices for testing potential SQLite releases.

• Using the SQLite JSON1 and FTS5 Extensions with Python shows how to compile SQLite 3.9.0+ with json1 and fts5 (full-text search) support to use these new features.

• SQLite with a fine-toothed comb digs into the internals of SQLite and shows some bugs found (and since fixed) while the author was researching the SQLite source code.

• How to Store Multimedia Files in a SQLite3 Database with Python goes through the Python code for storing and accessing BLOB-type objects.

• Going Fast with SQLite and Python shares essential knowledge for working effectively with SQLite in Python, particularly when it comes to transactions, concurrency and commits.

• Extending SQLite with Python uses the Peewee object-relational mapper (ORM) to implement virtual tables and aggregates on top of SQLite.

• SQLite Database Authorization and Access Control with Python covers how to control access to the SQLite database connection and file even though SQLite normally allows unauthorized access by design.

• Can I read and write to a SQLite database concurrently from multiple connections? answers one of the concerns that was an issue in earlier versions of SQLite that could have issues if more than one connection was writing to the database at one time.

• Appropriate uses for SQLite is an official documentation page that explains what types of applications are designed to work well with SQLite as the backend.

• How to corrupt a SQLite file explains how the database file could potentially get corrupted if you really work at screwing it up.

What do you want to learn next about data?