store arbitrary Python objects in an SQLite database in sqlite3

[pynerds]

The SQLite database only allows basic types such as numbers, texts and blobs.

In order to make an SQLite support objects of a particular type, we have to define an adapter and a converter for that type. The two work together to manage data conversion between SQLite types and Python objects.

In the following example, we add support for lists in an SQLite database.

#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);'''

In the above example we defined an adapter and a converter for the list type. The adapter is called automatically when we insert a list value into the SQLite database, while the converter is called when a list value is retrieved from the database.

buran write Apr-01-2024, 05:55 AM:
Clickbait links removed