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.
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 |
Table created successfully
After the database has been created, we can then insert rows into the database.
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:
[('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.
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.
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.
('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.
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:
('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'])