Python contains a SQLite client in it’s standard library. This is really useful for prototyping, and great for when a script grows into needing a little more data integrity. It’s really quite unexpected to be able to use something like this without any dependencies.
The command line interface for SQLite is easy to use. If you run the sqlite3 command it will start a prompt with an open in-memory database. Give it a filename as the first positional argument and it will open the file as a database, or create one if it does not exist.
Here’s an example creating a tiny database:
$ sqlite3 things.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> create table things (
...> id integer primary key,
...> name text);
sqlite> insert into things (name) values ('bicycle');
sqlite> insert into things (name) values ('trumpet');
sqlite> insert into things (name) values ('frog');
sqlite> insert into things (name) values ('ennui');
sqlite>
The documentation for the sqlite3 module is comprehensive and clear. The API actually has a few shortcuts compared to the standard one defined in pep-0249. For example the execute function on the connection object means you don’t have to create and manage a cursor. This makes simple programs even simpler.
This is a python session using execute function with the database file created above:
$ python3
imPython 3.5.3 (default, Jan 19 2017, 14:11:04)
[GCC 6.3.0 20170118] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> with sqlite3.connect('things.db') as c:
... for thing in c.execute('select * from things'):
... print(thing)
...
(1, 'bicycle')
(2, 'trumpet')
(3, 'frog')
(4, 'ennui')
>>>
When you use the connection as a context manager it automatically handles transactions, commiting or performing a rollback as necessary too.