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

Author: Vaibhav Karve

Last updated: 2021-02-28

• Python documentation: https://docs.python.org/3/library/sqlite3.html
• Tutorial: https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3
• Tutorial I haven't looked at yet: https://www.w3schools.com/sql/

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.

---

import sqlite3 as sql

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:.

# conn: sql.Connection = sql.connect('platonic_solids.db')
conn: sql.Connection = sql.connect(':memory:')

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

cursor: sql.Cursor = conn.cursor()

Connection should be closed in order to save changes

conn.commit()  # save all the changes
conn.close()

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

conn: sql.Connection = sql.connect(':memory:')
cursor: sql.Cursor = conn.cursor()

cursor.execute('CREATE TABLE solids (name TEXT, vertices INTEGER, faces INTEGER)')
solids_list = [('tetrahedron', 4, 4),
               ('cube', 6, 6),
               ('octahedron', 8, 8),
               ('dodecahedron', 12, 20),
               ('icosahedron', 20, 12)]

for entry in solids_list:
    cursor.execute('INSERT INTO solids VALUES (?, ?, ?)', entry)

Reading from the DB

Use .fetchall() or .fetchone().

rows = cursor.execute('SELECT name, vertices, faces FROM solids').fetchall()
rows

[('tetrahedron', 4, 4),
 ('cube', 6, 6),
 ('octahedron', 8, 8),
 ('dodecahedron', 12, 20),
 ('icosahedron', 20, 12)]

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

rows = cursor.execute('SELECT name, vertices, faces FROM solids WHERE (vertices >= 5)').fetchall()
rows

[('cube', 6, 6),
 ('octahedron', 8, 8),
 ('dodecahedron', 12, 20),
 ('icosahedron', 20, 12)]

Modifying entries

This is done using the following statements --

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

cursor.execute('UPDATE solids SET name = ? WHERE vertices=4', ('pyramid', ))
rows = cursor.execute('SELECT name, vertices, faces FROM solids').fetchall()
rows

[('pyramid', 4, 4),
 ('cube', 6, 6),
 ('octahedron', 8, 8),
 ('dodecahedron', 12, 20),
 ('icosahedron', 20, 12)]

Python data types accepted by Sqlite3

Python type	SQLite type
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

For example, this does not work.

numbers = [1, 2, 3]
cursor.execute('SELECT ?', (numbers, )).fetchall() # This is supposed to throw an InterfaceError.

---------------------------------------------------------------------------
InterfaceError                            Traceback (most recent call last)
<ipython-input-9-9d26a0cd79b1> in <module>
      1 numbers = [1, 2, 3]
----> 2 cursor.execute('SELECT ?', (numbers, )).fetchall() # This is supposed to throw an InterfaceError.

InterfaceError: Error binding parameter 0 - probably unsupported type.

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

from ast import literal_eval

class NumList(list[int]):
    pass

def adapt_list_of_nums(list_of_nums):
    """Convert list to a type acceptable to SQL."""
    return str(list_of_nums)

def convert_to_list_of_nums(output_from_sql):
    """Get back the list of numbers."""
    return literal_eval(output_from_sql)

sql.register_adapter(NumList, adapt_list_of_nums)
sql.register_converter('NumList', convert_to_list_of_nums)

numbers = NumList([1, 2, 3])
cursor.execute('SELECT ?', (numbers, )).fetchall() # Now it works

[('[1, 2, 3]',)]

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.

conn = sql.connect(':memory:')
conn.execute('create table solids(name, vertices, faces)')

solids_list = [('tetrahedron', 4, 4),
               ('cube', 6, 6),
               ('octahedron', 8, 8),
               ('dodecahedron', 12, 20),
               ('icosahedron', 20, 12)]

conn.executemany('insert into solids(name, vertices, faces) values (?, ?, ?)', solids_list)

for row in conn.execute('select name, vertices, faces from solids'):
    print(row)
    
conn.close()

('tetrahedron', 4, 4)
('cube', 6, 6)
('octahedron', 8, 8)
('dodecahedron', 12, 20)
('icosahedron', 20, 12)