Python sqlite3.register_converter Function

Last modified April 15, 2025

This comprehensive guide explores Python's sqlite3.register_converter function, which enables custom type conversion when retrieving data from SQLite.

Basic Definitions

The sqlite3.register_converter function registers a callable to convert SQLite values of a specific type to Python objects. It works with the detect_types parameter in sqlite3.connect.

Key characteristics: it maps SQLite types to Python objects, enables complex type handling, and works with both built-in and custom types. The conversion happens when fetching data from the database.

Basic Type Conversion

This example shows how to register a converter for SQLite DATE type to Python date objects.

basic_converter.py

import sqlite3
from datetime import date

def convert_date(val):
    return date.fromisoformat(val.decode())

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("DATE", convert_date)
    
    conn.execute("CREATE TABLE events(id INTEGER, event_date DATE)")
    conn.execute("INSERT INTO events VALUES (1, '2025-04-15')")
    
    row = conn.execute("SELECT * FROM events").fetchone()
    print(row[1], type(row[1]))  # 2025-04-15 <class 'datetime.date'>

The converter function takes a bytes object from SQLite and returns a Python date. The PARSE_DECLTYPES flag enables type detection from column declarations.

This pattern is useful for converting database-native formats to Python objects automatically during fetch operations.

Converting JSON Data

This example demonstrates converting JSON strings stored in SQLite to Python dictionaries automatically.

json_converter.py

import sqlite3
import json

def convert_json(val):
    return json.loads(val.decode())

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("JSON", convert_json)
    
    conn.execute("CREATE TABLE configs(id INTEGER, settings JSON)")
    conn.execute("INSERT INTO configs VALUES (1, '{\"theme\": \"dark\", \"notifications\": true}')")
    
    row = conn.execute("SELECT * FROM configs").fetchone()
    print(row[1]['theme'])  # dark

The converter automatically deserializes JSON strings when retrieving data. This is particularly useful for storing semi-structured data in SQLite.

Note that SQLite doesn't have a native JSON type - we're using the type name as a convention that our converter recognizes.

Custom Object Conversion

This example shows how to convert SQLite values to custom Python objects.

custom_object.py

import sqlite3

class Point:
    def __init__(self, x, y):
        self.x = x
        self.y = y
    
    def __repr__(self):
        return f"Point({self.x}, {self.y})"

def convert_point(val):
    x, y = map(float, val.decode().split(','))
    return Point(x, y)

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("POINT", convert_point)
    
    conn.execute("CREATE TABLE shapes(id INTEGER, location POINT)")
    conn.execute("INSERT INTO shapes VALUES (1, '3.5,4.2')")
    
    row = conn.execute("SELECT * FROM shapes").fetchone()
    print(row[1])  # Point(3.5, 4.2)

Here we store points as comma-separated strings in SQLite and convert them to Point objects when retrieving. The converter handles the parsing logic.

This pattern is useful for domain-specific objects that need persistence in a relational database.

Binary Data Conversion

This example demonstrates converting binary data to a custom Python object.

binary_converter.py

import sqlite3
import pickle

class CustomData:
    def __init__(self, data):
        self.data = data
    
    def __repr__(self):
        return f"CustomData({self.data!r})"

def convert_custom(val):
    return pickle.loads(val)

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("CUSTOM", convert_custom)
    
    original = CustomData([1, 2, 3])
    conn.execute("CREATE TABLE data_store(id INTEGER, data CUSTOM)")
    conn.execute("INSERT INTO data_store VALUES (1, ?)", 
                (pickle.dumps(original),))
    
    row = conn.execute("SELECT * FROM data_store").fetchone()
    print(row[1])  # CustomData([1, 2, 3])

This example uses pickle to serialize Python objects to binary format for storage in SQLite. The converter unpickles the data when retrieving.

While powerful, be cautious with pickle due to security implications when loading untrusted data.

Multiple Converters

This example shows registering multiple converters for different SQLite types.

multiple_converters.py

import sqlite3
from datetime import datetime, date

def convert_datetime(val):
    return datetime.strptime(val.decode(), "%Y-%m-%d %H:%M:%S")

def convert_date(val):
    return date.fromisoformat(val.decode())

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("DATETIME", convert_datetime)
    sqlite3.register_converter("DATE", convert_date)
    
    conn.execute("""CREATE TABLE events(
        id INTEGER, 
        event_date DATE,
        created_at DATETIME)""")
    
    conn.execute("""INSERT INTO events VALUES 
        (1, '2025-04-15', '2025-04-15 14:30:00')""")
    
    row = conn.execute("SELECT * FROM events").fetchone()
    print(f"Date: {row[1]}, Datetime: {row[2]}")

Here we register separate converters for DATE and DATETIME types. Each converter handles its specific format and returns appropriate Python objects.

This approach allows fine-grained control over how different SQLite types are converted to Python objects.

Error Handling in Converters

This example demonstrates proper error handling in converter functions.

error_handling.py

import sqlite3
from datetime import date

def safe_date_converter(val):
    try:
        return date.fromisoformat(val.decode())
    except (ValueError, AttributeError) as e:
        print(f"Conversion error: {e}")
        return None

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    sqlite3.register_converter("DATE", safe_date_converter)
    
    conn.execute("CREATE TABLE events(id INTEGER, event_date DATE)")
    conn.execute("INSERT INTO events VALUES (1, 'invalid-date')")
    
    row = conn.execute("SELECT * FROM events").fetchone()
    print(row[1])  # None (due to conversion error)

The converter includes error handling to manage malformed data. It returns None when conversion fails, preventing crashes in the application.

Robust converters should always handle potential errors in the input data to maintain application stability.

Combining with Adapters

This example shows using converters together with adapters for two-way conversion.

adapter_converter.py

import sqlite3
from decimal import Decimal

def adapt_decimal(d):
    return str(d)

def convert_decimal(val):
    return Decimal(val.decode())

# Register the adapter and converter
sqlite3.register_adapter(Decimal, adapt_decimal)
sqlite3.register_converter("DECIMAL", convert_decimal)

with sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
    conn.execute("CREATE TABLE prices(id INTEGER, amount DECIMAL)")
    
    price = Decimal('19.99')
    conn.execute("INSERT INTO prices VALUES (1, ?)", (price,))
    
    row = conn.execute("SELECT * FROM prices").fetchone()
    print(row[1], type(row[1]))  # 19.99 <class 'decimal.Decimal'>

Adapters convert Python objects to SQLite-compatible formats when storing data, while converters transform them back when retrieving. This provides seamless two-way conversion.

The combination is powerful for working with custom types while maintaining database compatibility.

Best Practices

Handle errors: Converters should manage malformed data gracefully
Use efficient formats: Choose compact storage representations
Document types: Clearly document custom type conventions
Consider performance: Complex conversions may impact speed
Test thoroughly: Verify edge cases in type conversion

Source References

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Python tutorials.

Ebooks