We can map SQL columns to class definitions so that we can create proper Python object instances from data in a database. SQLite includes a Binary Large Object (BLOB) data type. We can pickle our Python objects and store them in the BLOB columns. We can work out a string representation of our Python objects (for example, using the JSON or YAML notation) and use SQLite text columns too.

This technique must be used cautiously because it effectively defeats SQL processing. A BLOB column cannot be used for SQL DML operations. We can't index it or use it in the search criteria of DML statements.

SQLite BLOB mapping should be reserved for objects where it's acceptable to be opaque to the surrounding SQL processing. The most common examples are media objects such as videos, still images, or sound clips. SQL is biased towards text and numeric fields. It doesn't generally handle more complex objects.

If we're working with financial data, our application should use the decimal.Decimal values. . The reasons were discussed in Chapter 8, Creating Numbers; currency computations will be incorrect when performed with float values. We might want to query or calculate in SQL using this kind of data. As decimal.Decimal is not directly supported by SQLite, we need to extend SQLite to handle values of this type.

There are two directions to this: conversion and adaptation. We need to adapt Python data to SQLite, and we need to convert SQLite data back to Python. Here are two functions and the requests to register them:

import decimal 
def adapt_currency(value): 
    return str(value)
sqlite3.register_adapter(decimal.Decimal, adapt_currency)  
def convert_currency(bytes): 
    return decimal.Decimal(bytes.decode())
sqlite3.register_converter("DECIMAL", convert_currency) 

We've written an adapt_currency() function that will adapt decimal.Decimal objects into a suitable form for the database. In this case, we've done nothing more than a simple conversion to a string. We've registered the adapter function so that SQLite's interface can convert objects of class decimal.Decimal using the registered adapter function. We've also written a convert_currency() function that will convert SQLite bytes objects into the Python decimal.Decimal objects. We've registered the converter function so that columns of the DECIMAL type will be properly converted to Python objects.

Once we've defined the adapters and converters, we can use DECIMAL as a fully supported column type. For this to work properly, we must inform SQLite by setting detect_types=sqlite3.PARSE_DECLTYPES when making the database connection. Here's a table definition that uses our new column data type:

CREATE TABLE budget( 
    year INTEGER, 
    month INTEGER, 
    category TEXT, 
    amount DECIMAL 
) 

As with text fields, a maximum size isn't required by SQLite. Other database products require a size to optimize storage and performance. We can use our new column type definition like this:

database = sqlite3.connect('p2_c11_blog.db', detect_types=sqlite3.PARSE_DECLTYPES) 
database.execute(decimal_ddl) 
 
insert_budget= """ 
    INSERT INTO budget(year, month, category, amount) 
    VALUES(:year, :month, :category, :amount) 
""" 
database.execute(insert_budget, 
    dict(year=2013, month=1, category="fuel", amount=decimal.Decimal('256.78'))) 
database.execute(insert_budget, 
    dict(year=2013, month=2, category="fuel", amount=decimal.Decimal('287.65'))) 
 
query_budget= """ 
    SELECT * FROM budget 
""" 
for row in database.execute(query_budget): 
    print(row) 

We created a database connection that requires declared types to be mapped via a converter function. Once we have the connection, we can create our table using a new DECIMAL column type.

When we insert rows into the table, we use proper decimal.Decimal objects. When we fetch rows from the table, we'll see that we get proper decimal.Decimal objects back from the database. The following is the output:

(2013, 1, 'fuel', Decimal('256.78')) 
(2013, 2, 'fuel', Decimal('287.65')) 

This shows us that our decimal.Decimal objects were properly stored and recovered from the database. We can write adapters and converters for any Python class. To do this, we need to invent a proper byte representation of the object. As a string is so easily transformed into bytes, creating and parsing strings is often the simplest way to proceed. Bytes can be created from strings using the encode() method of a string. Similarly, strings can be recovered from bytes using the bytes decode() method.

Let's take a look at how to map Python objects to database rows manually.