How to use python's sqlite3 for simple DB-management

Author: Vaibhav Karve

Last updated: 2021-02-28

Key idea

so far I have been using a CSV (or sometimes writing to a plain TXT file) for storing graphs, triangulations, tables of intermediate results etc. This is considered bad research practice and is inefficient.

The more standard way is to use an SQL database i.e. a .db file. It is easy to use, easy to parse and the data file is less likely to break/get corrupted.

First we open a connection. Name a file if you want to create a new database. But for creating a database in RAM for testing, give it the special name :memory:.

We should then also initialize a "cursor" in the database to we can read/write.

Connection should be closed in order to save changes

Writing into the DB

The table I want to put into the DB.

name vertices faces
tetrehedron 4 4
cube 6 6
octahedron 8 8
dodecahedron 12 20
icosahedron 20 12

Reading from the DB

Use .fetchall() or .fetchone().

We can filter values if we want. WHERE is used for filtering the rows.

Modifying entries

This is done using the following statements --

  1. UPDATE <table> SET <variable> = <value> WHERE <filter>
  2. DELETE FROM <table> WHERE <filter>

Python data types accepted by Sqlite3

Python type SQLite type
float REAL
str TEXT
bytes BLOB

For example, this does not work.

The fix is to create a fake class and then define an adapter and a converter function for that class.

Shortcut methods can save us a lot of typing and make code more compact

Shortcut methods let us execute commands directly without needing the cursor object. For example, in the following code block we work directly with the connection and don't use the cursor at all.