basic example

#allow list column types in SQLite database

import sqlite3
import pickle

def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)


sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)

with sqlite3.connect('my_database.db') as conn:
  conn.exeute( '''
   CREATE TABLE mytabe (
    col1 integer,
    col2 text,
    col3 list);'''

 

The sqlite3 module in the standard library provides an interface for working with the SQLite database in Python.

Basically, the SQLite database supports only some few basic column types such as numbers, text, blobs/bytes etc. In this article we will look at how we can define new column types for an SQLite database to support complex Python objects.

Create a new SQLite database

Let us start by creating a new database.

The sqlite3.connect() function starts a connection with an existing database, it creates a new one if a database with the given name doesn't exist.

import sqlite3

conn = sqlite3.connect("students.db")

conn.close()

After running the above snippet, a new database file, 'students.db', will be created in the working directory.

In our students database, we will create a table called student with . We will start with the following basic columns then we will add more as we go on.

basic student table:

id  integer Student's unique identifier
name text Student's name
course text Student's course 
import sqlite3

create_table_command = """
    CREATE TABLE student (
    id integer PRIMARY KEY AUTOINCREMENT,
    name text NOT NULL,
    course text
   );
  """

with sqlite3.connect('students.db') as conn:
  try:
    conn.execute(create_table_command)
    print('Table created successfully')
  except ZeroDivisionError:
    print("Something went wrong.")

Table created successfully

After the database has been created, we can then insert rows into the database.

import sqlite3

insert_commands = """
    INSERT INTO  student (name, course) VALUES ('John', 'Computer Science');
    INSERT INTO  student (name, course) VALUES ('Mary', 'Medicine');
    """

with sqlite3.connect('students.db') as conn:
  try:
    conn.executescript(insert_commands)
    print('Students added successfully')
  except ZeroDivisionError:
    print("Something went wrong.")

Students added successfully 

In the above example, we inserted two rows by executing the two INSERT commands. The conn.executescript() is used to execute multiple sql statements at once like in the above example.

You can retrieve data from the database by executing SELECT commands through a cursor object:

import sqlite3

select_command = "SELECT name, course from student;"

with sqlite3.connect('students.db') as conn:
  cursor = conn.cursor()
  cursor.execute(select_command)
  print(cursor.fetchall())

[('John', 'Computer Science'), ('Mary', 'Medicine')]

Add a builtin type column

Consider if we wanted to add a list field into the database, in this case we will need to create custom field type because the SQLite database doesn't have a builtin support for lists.

Basically creating a new column types involves converting python objects into basic SQLite types and vice verse. This is achieved by creating an adapter and a converter for the new type.  The adapter and converter work together to manage data type conversion process for the new column. The adapter is responsible for converting the python object  to an SQLite representation, while the converter converts the SQLite column back to a Python object.

Let us start with a working example. We will add another column to the database for units being taken by the student which will be in form of a list.

import sqlite3
import pickle

def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)

sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)

new_column_command = "ALTER TABLE student ADD units list;"
with sqlite3.connect('students.db', detect_types=sqlite3.PARSE_DECLTYPES ) as conn:

  try:
    conn.execute(new_column_commad)
    print('Column added successfully')
  except:
    print('Something went wrong.')

Column added successfully

In the above example, we defined two functions, adapter() and converter().

The adapter() function takes an arbitrary Python objects and uses the pickle.dumps() function to convert the object into a series of bytes that can be stored in the SQLite database. To allow the sqlite3 module to be able handle list objects, we called the sqlite3.register_adapter() method with the builtin list class as the first argument and the adapter() function as the second. This ensures that whenever we try to add a list objects into a database, the adapter function will be called first with the list as its argument in order to convert the list into a supported SQLite type.

The converter() function, on the other hand, takes a bytes string and converts it into the equivalent Python object by calling the pickle.loads() function. The sqlite3.register_converter()  takes the name of the database column type as the argument and the function to be used for conversion as the second argument. In the above example calling sqlite3.register_converter('list', converter), adds support for list type columns in the database  and ensures that whenever a value with this column is being retrieved from the database,  it will first be converted into the equivalent Python object by automatically calling the converter() function with the value.

import sqlite3
import pickle

def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)

sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)

command = f"INSERT INTO student (name, course, units) VALUES ('Mike', 'Software Engineering', :units);"

with sqlite3.connect('students.db', detect_types=sqlite3.PARSE_DECLTYPES ) as conn:

  try:
    conn.execute(command, {'units':['Mathematics', 'Programming', 'Electronics']})
    print('Student added successfully')

  except ZeroDivisionError:
    print('Something went wrong.')

Student added successfully 

In the above example we passed the units as a keyword arguments to the cursor.execute() method. The :units parameter in the command gets replaced with the actual value from the dictionary. You can look more on how sqlite3 arguments work here. 

We can now retrieve rows from the database to see the added row with a list column.

import sqlite3
import pickle

def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)

sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)

select_command = "SELECT name, course, units from student;"

with sqlite3.connect('students.db', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
  cursor = conn.cursor()
  cursor.execute(select_command)
  print(*cursor.fetchall(), sep = '\n')

('John', 'Computer Science', None)
('Mary', 'Medicine', None)
('Mike', 'Software Engineering', ['Mathematics', 'Programming', 'Electronics']) 

Note that the conversion will only work automatically if you have the detect_types parameter set. Otherwise, the field might be returned in the format they are stored in the database i.e raw bytes.

Add a custom class type column

To add a custom class as a type in the database, we similarly follow the above steps,  for example in our student table we can add a school column which will hold objects of a School class.

import sqlite3
import pickle

class School:
  def __init__(self, name):
    self.name = name
  def __str__(self):
    return f"School({self.name})"
  def __repr__(self):
    return f"School({self.name})"
sch = School('Reeds College')


def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)

sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)

sqlite3.register_adapter(School, adapter)
sqlite3.register_converter('school', converter)

command1 = "ALTER TABLE student ADD college school;"
command2 = "INSERT INTO student (name, college, course, units) VALUES ('Rose', :school, 'Software Engineering', :units);"

with sqlite3.connect('students.db', detect_types=sqlite3.PARSE_DECLTYPES ) as conn:

  try:
    conn.execute(command1)
    print("Column added successfully")
    conn.execute(command2, {'school': sch, 'units':['Mathematics', 'Programming', 'Electronics']})
    print('Student inserted successfully')

  except ZeroDivisionError:
    print('Something went wrong.')

Column added successfully
Student inserted successfully 

In the above example, the "ALTER TABLE student ADD college school;" command  adds a column called college of type school.

Let us now retrieve rows from the database:

import sqlite3
import pickle
import pprint

class School:
  def __init__(self, name):
    self.name = name
  def __str__(self):
    return f"School({self.name})"
  def __repr__(self):
    return f"School({self.name})"

def adapter(obj):
  return pickle.dumps(obj)

def converter(obj):
  return pickle.loads(obj)

sqlite3.register_adapter(list, adapter)
sqlite3.register_converter('list', converter)
sqlite3.register_adapter(School, adapter)
sqlite3.register_converter('school', converter)

select_command = "SELECT name, college, course, units from student;"

with sqlite3.connect('students.db', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
  cursor = conn.cursor()
  cursor.execute(select_command)
  for i in cursor.fetchall():
    pprint.pprint(i)

('John', None, 'Computer Science', None)
('Mary', None, 'Medicine', None)
('Mike',
 None,
 'Software Engineering',
 ['Mathematics', 'Programming', 'Electronics'])
('Rose',
 School(Reeds College),
 'Software Engineering',
 ['Mathematics', 'Programming', 'Electronics'])