As you may recall from our Python sqlite3 database tutorial, in Python we may define as many classes as we want, but SQLite only supports 5 native types. This tutorial will describe all methods that the sqlite3 module provides to map Python types into SQLite types, and vice versa.

Automatic Per-Value Type Transformations

Like we discussed in our sqlite3 python tutorial, the sqlite3 module implicitly adapts Python types into SQLite types and converts them back using the following table:

Python Type SQLite Type
None NULL
int Integer
float Real
str Text
bytes BLOB

Except for NULL values, which are converted and adapted automatically, we can customize all adapters listed in the table above by simply registering a new function for them, as we will see in detail later. Unlikely the converters that we will describe below, the default converters are applied on a per-value basis, i.e. the sqlite3 module matches a converter function to a value using the native type of the value, rather than the declared type or the column name. We will see how sqlite3 does that in the next section.

We can customize only the TEXT converter by assigning a new value to the text_factory attribute of a sqlite3.Connection object. The following example shows how to use it:

import sqlite3

def test_text_factory():
    """ Test on the `text_factory` converter. """
    
    def uppercase(b):
        print('converting %s' % b)
        return str(b.upper(), encoding='utf-8')
    
    with sqlite3.connect(':memory:') as conn:
        conn.text_factory = uppercase
        conn.execute('CREATE TABLE test(misc TEXT);')
        conn.executemany('INSERT INTO test VALUES(?);',
            ((None, ), (12, ), (34.56, ), 
            ('text-data', ), (b'blob-data', )))
            
        for value, ctype in conn.execute(
            'SELECT misc, typeof(misc) FROM test;').fetchall():
            print('%s is of type %s' % (value, ctype))

test_text_factory()

where:

  • The uppercase() function accepts a byte object and converts it into an uppercase string. We will explain converters in great detail below, so there is no need to worry about them right now. This function is set as the new text_factory.
  • Then, we create a test table, with just one misc column of type TEXT. Notice that declaring a column as TEXT means assigning a text affinity to it. It doesn’t mean all its values will be of TEXT type. The text_factory converter will be called just on TEXT values. The Text type affinity uses the following rules:
    • All NULL values will be stored as such.
    • All INTEGER values will be converted to their literals, e.g. 12 will become '12'.
    • All REAL values will be converted to their literals, e.g. 1.23 will become '1.23'.
    • All TEXT values will be stored as such.
    • All BLOB values will be stored as such.
  • We insert into the test table a value for each of the five SQLite native types: None (NULL), 12 (INTEGER), 34.56 (REAL), 'text-data' (TEXT) and b'blob-data' (BLOB). They will be adapted using the rules we just mentioned.
  • The SELECT statement selects all values and their types from the test table.

The output of the test_text_factory() function will be:

converting b'null'
converting b'12'
converting b'text'
converting b'34.56'
converting b'text'
converting b'text-data'
converting b'text'
converting b'blob'
None is of type NULL
12 is of type TEXT
34.56 is of type TEXT
TEXT-DATA is of type TEXT
b'blob-data' is of type BLOB

We can see from the output that the uppercase() function has been called for all values of the misc column, except for None and the BLOB sequence, and for all values of the typeof() column, since they are all strings. We will point out one last time that the difference between the text_factory converter and a registered converter is that the former is applied only on values of TEXT type, while the latter is applied to all values in the column, except for NULL. So, a registered converter would have converted the BLOB values, too.

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Get Our Python Dev Kit

Looking Under the Hood of Type Conversion

This section is rather technical, but it will allow us to understand how implicit and explicit type conversion really works.

While executing an SQL statement, we use the fetchone() method to get one row from the results set. The sqlite3 module performs all conversions before fetching the row. On the other hand, the SQLite core does not perform any conversion, it just fetches a row, and we must extract any value from it using an appropriate function. The SQLite C API provides the following functions to retrieve a value from a row:

  • The sqlite3_column_count(stmt) function returns the number of columns in the current row from the results set of the stmt statement. All other methods have an i parameter, which selects a column from the row by index. Indices start from 0.
  • The sqlite3_column_name(stmt, i) function returns the name of the i-th column on the current row. That’s how sqlite3 builds the Cursor.description tuple.
  • The sqlite3_column_type(stmt, i) function returns the SQLite value type, as stored in the database. That’s how sqlite3 knows how to perform per-value conversions.
  • Several sqlite3_column_xxx(stmt, i) functions, where xxx is the data type which we want to convert the value into; e.g. sqlite3_column_int() (for int conversion), sqlite3_column_text() (for UTF-8 encoded strings).

The following table lists all possible conversions from a native type into another, along with some examples.

Original Type Requested Type Converted Value Example
NULL Integer 0
NULL Float 0.0
NULL Text NULL pointer
NULL BLOB NULL pointer
Integer Float It behaves like float(n) 10 becomes 10.0
Integer Text ASCII literal for the number 10 becomes '10'
Integer BLOB ASCII literal for the number 10 becomes b'10'
Float Integer Rounded towards zero (floor) 10.7 becomes 10
Float Text ASCII literal for the number 10.7 becomes '10.7'
Float BLOB ASCII literal for the number 10.7 becomes b'10'
Text Integer It behaves like int(text) '10' becomes 10
Text Float It behaves like float(text) '10.75' becomes 10.75
Text BLOB No change 'field' becomes b'field'
BLOB Integer Converts to ASCII text, then int(text) b'10' becomes 10
BLOB Float Converts to ASCII text, then float(text) b'10.75' becomes 10.75
BLOB Text Adds NUL character (U+0000) b'field' becomes 'field'

If you need a refresher on SQLite literals, please take a look at this tutorial. The conversion from BLOB to TEXT makes sense for C-like strings, which are sequences of non-NUL characters terminated by a NUL-character, e.g. b'home\x00'. This way a string can be stored as a plain sequence, without needing to keep track of its length. The Python str type stores the string length along with its contents.

Though the sqlite3 API doesn’t expose the column_type() function, it uses it under the hood, and fetches all rows after performing the following conversions on each value:

  • If there is no registered converter available for a column, then the conversions specified above are performed on the value.
  • If a converter for a column is available, sqlite3 maps the value to bytes before passing it to the converter, regardless of the underlying SQLite representation. Then, the value returned by the converter will be placed in the result row. Notice the converter has no way of knowing the native data type of the value. This behavior seems reasonable, since registered converters are meant on a per-column basis, rather than a per-cell basis. Because of this, the sqlite3 module retrieves any value from the database with the sqlite3_column_blob(stmt, i) function.

In the following sections we will use the typeof(column) native scalar function on our SELECT statements every time we need to know what the native SQLite type of a value is before any conversion is applied to it. This function returns one of the following strings: null, integer, real, text, and blob.

Overview of Type Transformations

In this section we will describe all functions that the sqlite3 API provides to map Python types to SQLite types, and the the other way around. In the subsequent sections we will dig deeper into each one of them.

  • The detect_types parameter of the sqlite3.connect() function determines how the sqlite3 module matches a converter to an SQLite value. It can accept one of or both these values:
    • the sqlite3.PARSE_DECLTYPES value, which makes the sqlite3 module parse the declared type for each column. It will parse out the first word for the declared type, e.g. INTEGER from the INTEGER PRIMARY KEY type declaration. Then, for that column, it will look into the converters dictionary, and use the converter function registered for that type, if any.
    • the sqlite3.PARSE_COLNAMES value, which makes the sqlite3 module parse the column name, looking for a string enclosed by square brackets; e.g. birth_date [iso-date] will make the sqlite3 module look for an iso-date name among all registered converters.
  • the sqlite3.register_converter(typename, callable) registers a new converter to turn an SQLite type into an Python type. Types are matched to the typename parameter in a case-insensitive way. The return type of the callable function can be any Python type. A converter can be deleted by passing a None value to the callable parameter.
  • the sqlite3.register_adapter(python_class, callable) registers a new adapter to turn a Python type into an SQLite type. Types are matched to the python_class parameter. The callable parameter is a function-like object which returns a None, an int, a float, a str, or a bytes object.
  • the sqlite3.Connection.text_factory attribute is a special converter for the objects of the TEXT SQLite type. It defaults to str(). It can be set to any callable which accepts a bytes object as a parameter.

Adapters

There are two ways of mapping a Python type into an SQLite type:

  • by registering an adapter callable object.
  • by providing the Python type with a __conform__ method.

The following two sections will describe each method.

Letting Objects Adapt Themselves

In this case, a Python class is provided with a __conform__ method, which returns a native sqlite3 object: None, int, float, str, or bytes. The sqlite3 module will then convert such objects into SQLite objects, using the rules defined at the top of this tutorial. This approach is particularly suitable when you create the classes yourself. If you use classes from another module, you must subclass them to add the __conform__ method, so you might prefer to register a separate adapter function. Let’s see an sqlite3 Python example:

import sqlite3
import random

class Coordinate3D:
    """ A 3D Coordinate. """
    
    def __init__(self, x, y, z):
        self.x, self.y, self.z = x, y, z
        
    @staticmethod
    def random_coord():
        return Coordinate3D(
            random.uniform(0, 100),
            random.uniform(0, 100),
            random.uniform(0, 100))
    
    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return '(%.3f, %.3f, %.3f)' % (self.x, self.y, self.z)


def test_conform_adapter():
    """ Adapting a class with a `__conform__()` method. """
    with sqlite3.connect(':memory:') as conn:
        conn.execute('CREATE TABLE coords(c);')
        conn.executemany(
            'INSERT INTO coords VALUES (?);', 
            ((Coordinate3D.random_coord(), ) for i in range(10)))
                    
        for c in conn.execute('SELECT c FROM coords;').fetchall():
            print(c[0])

test_conform_adapter()

where:

  • the Coordinate3D class represents a three dimensional coordinate. It provides a __conform__ method, which transforms it in an (x, y, z) string.
  • The Coordinate.random_coord() method generates a random coordinate, whose members are float objects between 0 and 100.
  • In the test_conform_adapter() function, we connect to an in-memory database and we create a coords table holding 10 randomly-generated coordinates. The executemany() function will turn the coordinates into strings before inserting them in the table.
  • Finally, we print the content of the table. A typical output of the test_conform_adapter() function is:
(67.086, 71.957, 18.415)
(32.630, 68.608, 70.063)
(73.841, 11.294, 51.590)
(68.018, 89.292, 46.438)
(38.982, 6.556, 68.023)
(6.006, 79.819, 90.643)
(89.691, 67.724, 76.922)
(86.932, 34.302, 46.751)
(45.766, 9.443, 15.538)
(31.812, 85.668, 35.401)

Registering an Adapter

Another way of adapting an object is to create a callable object, i.e. an object which provides a __call__() method (functions are callable objects), and then register the adapter. When sqlite3 encounters an object of that type, it will look up the adapters’ register. If it finds a suitable adapter, then it applies it before loading the object into the database. The following Python example references a csv file, which you can download here.

import sqlite3
import csv


class PostalAddress:
    
    def __init__(self, recipient, address, city, state):
        self.recipient = recipient
        self.address = address
        self.city, self.state = city, state
        

def adapt_postal_address(address):
    return '%s\n%s\n%s %s' % (address.recipient.upper(), 
        address.address.upper(), 
        address.city.upper(), address.state.upper())

def test_registered_adapter():
    """ Adapting via registered adapters. """
    sqlite3.register_adapter(PostalAddress, adapt_postal_address)
    
    with sqlite3.connect(':memory:') as conn, \
        open('messages.csv', newline='', encoding='utf-8') as src:
        conn.execute('CREATE TABLE mail(' \
            'message TEXT, address TEXT);')
            
        conn.executemany('INSERT INTO mail VALUES (?, ?);',
            ((message, PostalAddress(recipient, address, city, state)) \
            for message, recipient, address, city, state \
            in csv.reader(src)))
        
        for message, address in conn.execute(
            'SELECT * FROM mail;').fetchall():
            print('message: %s\nto: %s\n' % (message, address))

test_registered_adapter()

In this example,

  • The PostalAddress class represent a postal address.
  • The adapt_postal_address function maps a PostalAddress object into a multi-line string, following these rules:
    • all original strings will be converted in uppercase;
    • the mail recipient is in the first line;
    • the mail address is in the second line;
    • the city and state of the address are in the last line.
  • The test_registered_adapter() function opens an in-memory database and the messages.csv file, which contains the messages and the postal addresses. Then it creates a mail table, which has a message and an address column. The executemany() method loads all records from the CSV file into the mail table using the csv.reader() function. We described the Python CSV reader function in another tutorial. The first column in the CSV file is the message, the other columns are the parameters for the PostalAddress class. Finally, it prints the contents of the mail table.

The output of the test_registered_adapter() function will be:

message: Time traveling is just too dangerous. Better that I devote myself to study the other great mystery of the universe: women!
to: DR. EMMETT BROWN
1640 RIVERSIDE DRIVE
HILL VALLEY CA

message: The truth is out there
to: FOX MULDER
2630 HEGAL PLACE APT 42
ALEXANDRIA VA

message: I want to believe
to: DANA SCULLY
3170 W 53 RD 35
ANNAPOLIS MD

message: The Vampire Slayer
to: BUFFY SUMMERS
1630 REVELLO DRIVE
SUNNYDALE CA

message: The power of three
to: PHOEBE HALLIWELL
1329 PRESCOTT STREET
SAN FRANCISCO CA

message: Come to Freddy!
to: FREDDY KRUEGER
1428 ELM ST
SPRINGWOOD OH

message: I’m not crazy; my mother had me tested
to: DR SHELDON LEE COOPER
2311 N. LOS ROBLES AVE APT 4A
PASADENA CA

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Get Our Python Dev Kit

Beware that when you derive a class b for a class a, and you have already registered an adapter for class a, you must register another adapter for class b, too, because the sqlite3 module matches classes exactly, so it won’t make any attempt to look for adapters for the base class. If you have many classes derived from the same base class, you may provide the base class with a __conform__ method, so all derived classes will inherit it. Let’s see an example:

import sqlite3


class Point2d:
    
    def __init__(self, x, y):
        self.x, self.y = x, y
    
    def midpoint(self, p):
        return Point2d((self.x + p.x)/2, (self.y + p.y)/2)
        
        
class Point2dCheck(Point2d):
    
    def __init__(self, x, y, upper):
        if x > upper or y > upper:
            raise ValueError('Invalid point.')
            
        super().__init__(x, y)
   
        
def adapt_point(p):
    return '(%.3f, %.3f)' % (p.x, p.y)

def test_adapt_subclass():
    """ Adapting an object from a subclass. """
    sqlite3.register_adapter(Point2d, adapt_point)
    sqlite3.register_adapter(Point2dCheck, adapt_point)
    
    with sqlite3.connect(':memory:') as conn:
        conn.execute('CREATE TABLE tab(c TEXT);')
        conn.execute('INSERT INTO tab VALUES(?);',
            (Point2d(1.2, 3.4), ))
        conn.execute('INSERT INTO tab VALUES(?);',
            (Point2dCheck(5.6, 7.8, 10), ))
        
        for p in conn.execute('SELECT * FROM tab;').fetchall():
            print(p[0])

test_adapt_subclass()

In this case, we have a Point2d Python class, which provides some useful methods for dealing with plane coordinates (here we provide only the midpoint() method). We decide to define a Point2dCheck class, which is the same as Point2d, but checks that the x and y parameters are less than an upper value. To use them interchangeably, we must register the adapt_point function twice, once per class. The output of this test function will be:

(1.2, 3.4)
(5.6, 7.8)

Adapters' Precedence

Beware that the __conform__ method has lower precedence than registered adapters, as you can see from this example where we try to map an EMailAddress object into a str. The adapt_email_address() function will return the uppercase version of the string returned by EMailAddress.__conform__().

import sqlite3

class EMailAddress:
    """ An e-mail address. """
    
    def __init__(self, username, domain):
        """ Creates an e-mail from a user name and a domain. """
        self.username, self.domain = username, domain
        
    def __conform__(self, protocol):
        """ Transforms an `EMailAddress` object into a `str`. """
        if protocol is sqlite3.PrepareProtocol:
            return '%s@%s' % (self.username, self.domain)
        
def adapt_email_address(address):
    """ Transforms an `EMailAddress` object into a `str`. """
    return '%s@%s' % (
        address.username.upper(), 
        address.domain.upper())
        
def test_adapters_precedenece():
    """ Tests the precedence rules for adapters. """
    sqlite3.register_adapter(EMailAddress, adapt_email_address)
    
    with sqlite3.connect(':memory:') as connection:
        connection.execute('CREATE TABLE tab(address);')
        connection.execute(
            'INSERT INTO tab VALUES (?);', 
            (EMailAddress('john.smith', 'gmail.com'), ))
        curs = connection.execute(
            'SELECT address FROM tab;')
        print(curs.fetchone()[0])

test_adapters_precedenece()

Let’s explain what’s going on here. The test_adapters_precedence() functions goes as follows:

  • First we register the adapt_email_address() adapter.
  • We open a new in-memory database, where we create a tab table with an address column.
  • The table will hold a single address, that we get from adapting the EMailAddress('john.smith', 'gmail.com') object.
  • Finally, we read the record that has been loaded into the table. The result is JOHN.SMITH@GMAIL.COM, meaning that the registered adapter has been chosen instead of the __conform__() method.

Converters

As we have seen, converters can be registered using a name, but how does sqlite3 use that name to choose the appropriate function for a given value? There are two ways: by using declared types or by using column names. Either way, the name which the converter is registered to is case-insensitive, e.g. a converter registered as text will also match the TEXT declaration of a column, or a Text column name, depending on the method used. The next two sections will explain both methods in detail. Then, we will look at the rules of precedence for converters in case more than one converter can be applied to the same value. For each of our examples on converters, we will use these common classes and methods:

import sqlite3
import random

       
class Point2d:
    
    def __init__(self, x, y):
        self.x, self.y = x, y
        
    def __repr__(self):
        return 'Point2d(%.3f, %.3f)' % (self.x, self.y)
        return 'Point2d(%.3f, %.3f)' % (self.x, self.y)


def adapt_point(p):
    """ Maps a `Point2d` object into a string. """
    return '(%.3f, %.3f)' % (p.x, p.y)

def convert_point(p):
    """ Maps a string into a `Point2d` object. """
    x, y = p[1 : -1].split(b',')
    return Point2d(float(x), float(y))

def generate_lines(n=10, lower=-100, upper=100):
    """ Generates `n` lines within certain boundaries. """
    
    def generate_point(lower, upper):
        """ Generates a single point. """
        return Point2d(random.uniform(lower, upper),
            random.uniform(lower, upper))
    
    for p in range(n):
        yield (generate_point(lower, upper), 
            generate_point(lower, upper))

where:

  • The Point2d class represents a point on a plane. It has a __repr__() method that will be used in the examples to print a point in the Point(x, y) format.
  • The adapt_point() function will be used as an adapter from a Point2d object to an (x, y) string.
  • The convert_point() function will be used as a converter from an (x, y) string to a Point2d object. Notice that the parameter of a converter function is always a byte object, even when, like in this case, the parameter is stored in the SQLite database as TEXT data.
  • The generate_lines(n, lower, upper) function generates n pairs of points (n defaults to 10). The generate_point(lower, upper) function generates a single Point2d point, whose components are between lower and upper. random.uniform() returns a float between two bounds. The generate_lines() function will be used to feed data to the lines tables in our examples.

Conversion via Declared Types

The name used to register a converter can refer to the declared type in the table schema; e.g. if in the CREATE TABLE statement of a table there is an event column defined as event DATE, then a convert_date converter, registered as sqlite3.register_converter('date', convert_date), will be applied to any value of the event column. Remember that unlike other database management systems, SQLite allows arbitrary type names in a column declaration, so you can use types like Point2D or Vector. This practice might reduce portability. Let’s see how we can use this conversion method:

def test_converters_by_decltype():
    """ Converters matched by declared type. """
    sqlite3.register_adapter(Point2d, adapt_point)
    sqlite3.register_converter('point', convert_point)
    
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_DECLTYPES) as conn:
        conn.execute('CREATE TABLE lines(' \
            'start POINT, end POINT);')
        
        conn.executemany('INSERT INTO lines VALUES(?, ?);',
            generate_lines())
            
        for start, stop in conn.execute(
            'SELECT * FROM lines;').fetchall():
            print('line from %s to %s' % (start, stop))

test_converters_by_decltype()

This function works as follows:

  • First, we register the proper adapters and converters. The convert_point converter will be associated with the ‘point’ string that we must use in the columns’ declaration.
  • The database connection is opened with the detect_types parameter set to sqlite3.PARSE_DECLTYPES, which enables converters matching by declared type.
  • The lines table is defined with two columns: start, which marks the starting point of the line, and end, which is the ending point. Both columns are of POINT type, which is the same name we used to register our converter.
  • The executemany() method uses the generate_lines() function to create 10 pairs of points, and inserts them in the lines tables. The adapt_point() is called on each point before executing the INSERT statement, in order to map all Point2d objects into strings.
  • After executing the SELECT statement, all points are converted back to Point2d objects by the convert_point() converter, which is called implicitly by the sqlite3 module.
  • Finally, all records in the lines table are printed to the screen. The %s format converter calls the Point2d.__repr__() method, so all points will be converted to Point(x, y) strings.

A typical output of this test function is:

line from Point2d(29.151, 92.279) to Point2d(47.191, -52.763)
line from Point2d(-69.111, -46.250) to Point2d(84.170, -26.698)
line from Point2d(16.679, 31.374) to Point2d(-26.544, 63.723)
line from Point2d(2.676, -8.389) to Point2d(-16.814, 44.766)
line from Point2d(-78.014, 20.717) to Point2d(-90.657, 99.813)
line from Point2d(-12.223, -50.294) to Point2d(-47.509, -64.680)
line from Point2d(-18.495, -8.212) to Point2d(95.576, -84.506)
line from Point2d(36.849, -35.806) to Point2d(84.230, -84.987)
line from Point2d(16.065, -38.936) to Point2d(9.025, 23.461)
line from Point2d(90.135, 37.083) to Point2d(27.995, 69.982)

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Get Our Python Dev Kit

Conversion via Column Names

Another way of matching a column to a converter is by using the column name of the column alias, included between square brackets; e.g. if in a SELECT statement a column is aliased as d AS "Date [date]", then a convert_date converter, registered as sqlite3.register_converter('date', convert_date), will be applied to any value of the d column. The [date] string will be stripped from the alias before assigning to Cursor.description, so the alias for the column will simply be Date.

def test_converters_by_colname():
    """ Converters matched by column names. """
    sqlite3.register_adapter(Point2d, adapt_point)
    sqlite3.register_converter('point', convert_point)
    
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_COLNAMES) as conn:
        conn.execute('CREATE TABLE lines(start, end);')
        
        conn.executemany('INSERT INTO lines VALUES(?, ?);',
            generate_lines())
            
        for start, stop in conn.execute(
            'SELECT start AS "s [point]", end AS "e [point]" '\
            'FROM lines;').fetchall():
            print('line from %s to %s' % (start, stop))

test_converters_by_colname()

This function is similar to the test_converters_by_decltype example above, but there are a few differences worth noting:

  • The detect_types parameter of the sqlite3.connect() function has been set to sqlite3.PARSE_COLNAMES since we want to match the converters by column names.
  • The lines table has been declared without any type, since we don’t need types to match our converters. For the type affinity rules, both columns will be assigned to the None Type Affinity, meaning that values will be stored without attempting any transformation.
  • The INSERT statement is exactly the same in both cases, since adapters (and not converters) take care of the transformation from Python types to SQLite types.
  • The SELECT statement has been modified to use name aliases with a '[point]' suffix. The sqlite3 module will apply to both columns of the convert_point() function before fetching the results.

A typical output of this test function will look just like the one of the test_converters_by_decltypes() function, since these two functions differ only in the way converters are matched to the values.

Beware of Column Names with Spaces

SQLite identifiers for databases, tables, and columns can be be enclosed between double quotes or square brackets to allow more characters in an identifier, notably spaces and keywords; e.g. SELECT address AS [Postal Address] FROM mail; defines an alias for the address column of the mail table. If we want to use converters with column names on the Postal Address column, we must enclose the column alias between double quotes rather than square brackets, otherwise the SQLite core will complain about a syntax error. So:

/* This syntax will raise an exception */
SELECT address AS [Postal Address [paddress]] FROM mail; 
/* This is the right way to do it */
SELECT address AS "Postal Address [paddress]" FROM mail;

The sqlite3 module will strip the '[paddress]' string and its leading spaces before assigning the new alias to the Cursor.description of the mail table.

Precedence Rules for Converters

There are four types of converters that can be applied to a value exiting from an SQLite database:

  • The per-value default converters, which are applied implicitly by the sqlite3 module.
  • The text_factory converter, which is applied implicitly on a per-value basis to all TEXT values.
  • A registered converter matching a type declaration.
  • A registered converter matching a column name.

The rules in the following subsection will make it clear which converter will be applied to a value in case more than one converter is available.

Precedence of Column Names over Declared Types

If the detect_types parameter of the sqlite3.connect() function has been set to sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES, a converter can be matched either by column name or by type declaration. Then, column names will be matched first. Let’s see an example:

import sqlite3
import random
import string


def words_generator(n=20, minlen=5, maxlen=10):
    """ Generates `n` words. """
    for i in range(n):
        word = ''
        for i in range(random.randint(minlen, maxlen)):
            word += random.choice(string.ascii_letters)
            
        yield (word, )

def test_converters_precedence():
    """ Tests the precedence rules for converters. """
    def lower(s):
        return str(s.lower(), encoding='utf-8')
        
    def upper(s):
        return str(s.upper(), encoding='utf-8')
    
    sqlite3.register_converter('text', lower)
    sqlite3.register_converter('word', upper)
    
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) \
        as conn:        
        conn.execute('CREATE TABLE test(word TEXT);')
            
        conn.executemany('INSERT INTO test VALUES (?);', words_generator(10))
        
        print('Using PARSE_COLNAMES: upper'.center(50, '.'))
        for word in conn.execute(
            'SELECT word AS "w [word]" FROM test;').fetchall():
            print(word[0])
            
        print('Using PARSE_DECLTYPES: lower'.center(50, '.'))
        for word in conn.execute(
            'SELECT * FROM test;').fetchall():
            print(word[0])

test_converters_precedence()
  • The lower() converter is registered as 'text'. This converter will be used to match values by declared type. It maps all characters in a string to their respective lowercase character, e.g. A will be mapped to a.
  • The upper() converter is registered as 'word'. This converter will be used to match values by column names. It maps all characters in a string to their respective uppercase character, e.g. b will be mapped to B.
  • The detect_types parameter of the sqlite3.connect() function is set to sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES, meaning that converters can be both applied by column name or by declared types.
  • The CREATE TABLE statement creates a test table holding only a word column of type TEXT.
  • The table is filled with 10 random words generated by the words_generator(). This function creates strings of both uppercase and lowercase characters.
  • The SELECT word AS "w [word]" FROM test; statement selects all data from the word column. Notice that in this case converters could be matched both by column name (following the column alias), and by type declaration. Matching by column names prevails, and all words are converted to uppercase before being fetched.
  • The SELECT * FROM test; statement is straightforward, since only matching by declared type can be applied. All data will be converted to lowercase before being placed in the results set.

A typical output of the test_converters_precedence() function is:

...........Using PARSE_COLNAMES: upper............
JHVRBAAQX
YFHJFWF
ILJNQCYGB
ADBMSCTRB
NQSKFKLBP
OHETDSI
DNGTZ
ZBWYLIWW
TQGDFTIJOV
ZPPMLQGLA
...........Using PARSE_DECLTYPES: lower...........
jhvrbaaqx
yfhjfwf
iljnqcygb
adbmsctrb
nqskfklbp
ohetdsi
dngtz
zbwyliww
tqgdftijov
zppmlqgla

Precedence of Converters over Text Factory

As we learned above, the text_factory attribute of a sqlite3.Connection object can be assigned to a converter for all TEXT values in the database. This converter has lower precedence than any other registered converter, as we will see in the next example. Although I don’t include them (for brevity), this example uses the same import statements and words_generator() function as we used in this example.

def test_textfactory_precedence():
    """ `text_factory` has lower precendece than other converters. """
    def lower(s):
        return str(s.lower(), encoding='utf-8')
        
    def upper(s):
        return str(s.upper(), encoding='utf-8')
        
    def titlecase(s):
        return str(s.title(), encoding='utf-8')
        
    # Delete a previously registered converter
    sqlite3.register_converter('text', None)
    
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) \
        as conn:
        conn.text_factory = titlecase
            
        conn.execute('CREATE TABLE test(word TEXT);')
            
        conn.executemany('INSERT INTO test VALUES (?);', words_generator(5))
                
        print('Without any converter: row_factory'.center(50, '.'))
        for word in conn.execute(
            'SELECT * FROM test;').fetchall():
            print(word[0])
                    
        sqlite3.register_converter('text', lower)
        sqlite3.register_converter('word', upper)
        
        print('Using PARSE_COLNAMES: upper'.center(50, '.'))
        for word in conn.execute(
            'SELECT word AS "w [word]" FROM test;').fetchall():
            print(word[0])
            
        print('Using PARSE_DECLTYPES: lower'.center(50, '.'))
        for word in conn.execute(
            'SELECT * FROM test;').fetchall():
            print(word[0])


test_textfactory_precedence()

This function mirrors the test_converters_precedence() function, with a few differences:

  • A titlecase function is set as text_factory for the conn connection. This function maps the first character of the string to uppercase and all other characters to lowercase, e.g. 'pYthON' is turned into 'Python'.
  • The sqlite3.register_converter('text', None) call deletes any converter registered for the TEXT column type, to make sure that previously registered converters won’t interfere with text_factory.
  • The registration of the lower() and upper() converters has been delayed until after the first SELECT statement, so that it will not affect the results of the first SELECT statement.
  • The first SELECT statement will convert all values using text_factory, since this is the only available converter at the time. Because it’s the only one available, all data will be mapped to title-case.
  • The SELECT word AS "w [word]" FROM test; statement has all three converters available, but the converter registered as 'word' will have precedence over any other converter, so all words will be converted to uppercase.
  • The last SELECTstatement has both the text_factory and the converter registered as 'text' available, but it will apply the latter, since it has higher precedence over the other one. All data will be turned into lowercase words.

A typical output of this test function should be self-explanatory:

........Without any converter: row_factory........
Qivfal
Vxjmeyy
Panarjylo
Gofagwap
Pbuyje
...........Using PARSE_COLNAMES: upper............
QIVFAL
VXJMEYY
PANARJYLO
GOFAGWAP
PBUYJE
...........Using PARSE_DECLTYPES: lower...........
qivfal
vxjmeyy
panarjylo
gofagwap
pbuyje

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Get Our Python Dev Kit

Other Remarks on Adapters and Converters

Beware of NULL Values

Beware that when the sqlite3 encounters a NULL value, it converts it to None without calling any registered converter. The same holds for adapters: when the sqlite3 module encounters None, it maps it to a NULL value implicitly, even if you register an adapter for None. Let’s show you what I mean:

import sqlite3

def test_null_values():
    """ No converter or adapter is called for NULL values. """
    
    def convert_misc(b):
        print('converting a MISC value: %s' % b)
        return str(b, encoding='utf-8')
        
    def adapt_null(s):
        print('Adapting a None value.')
        return 'NULL'
        
    def adapt_str(s):
        print('Adapting a str value: %s' % s)
        return s
    
    sqlite3.register_adapter(None, adapt_null)
    sqlite3.register_adapter(str, adapt_str)
    sqlite3.register_converter('misc', convert_misc)
    
    with sqlite3.connect(':memory:',
        detect_types=sqlite3.PARSE_DECLTYPES) as conn:        
        conn.execute(
            'CREATE TABLE test(c misc);')
                
        conn.executemany(
            'INSERT INTO test VALUES (?);',
            (('a', ), (None, ), ('b', )))
                
        for value, ctype in conn.execute(
            'SELECT c, typeof(c) FROM test;').fetchall():
            print('"%s" is of type %s' % (value, ctype.upper()))


test_null_values()

The test_null_values() function works as follows:

  • We register a convert_misc() function as 'misc', which should took care of all values of the c column of the test table, since it is declared as MISC in the table schema. Before performing any conversion, it will print the value it is about to convert.
  • We define an adapt_null() function to adapt None values, and an adapt_str() function to adapt arbitrary strings. They both print the value they are working on before making the transformation.
  • Then, we register all adapters and converters using the appropriate functions, as we learned in the previous sections.
  • We open the database connection using declared types as the matching method for converter functions. Then, we create a table test with only one column of type `MISC`.
  • We insert 'a', None, and 'b' inside the test table, but as we will see from the function’s output below, the adapter for None doesn’t get called, while the adapter for str values do.
  • Finally, we print all values and types for the data in the test table, but the convert_misc() functions is not called for the NULL value, as we can see from the function’s output below.

This is the output of the test_null_values() function:

Adapting a str value: a
Adapting a str value: b
converting a MISC value: b'a'
converting a MISC value: b'b'
"a" is of type TEXT
"None" is of type NULL
"b" is of type TEXT

Default Adapters and Converters

The sqlite3 module provides two default converters and two default adapters for the datetime.datetime data type, registered as 'date' and 'timestamp'. The default adapters and converters imply the following functions’ calls:

sqlite3.register_adapter(datetime.datetime, adapt_datetime)
sqlite3.register_adapter(datetime.date, adapt_date)
sqlite3.register_converter('date', convert_date)
sqlite3.register_converter('timestamp', convert_timestamp)

where adapt_datetime(), adapt_date(), convert_date(), and convert_timestamp() are fictional functions, which will perform the kind of transformations explained in the examples below. If you register other functions for the same name and classes, you will overwrite these default converters and adapters. Let’s try them out:

import sqlite3
import datetime


def test_date_by_decltypes():
    """ Tests default date adapters and converters. """
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_DECLTYPES) as conn:
        conn.execute(
            'CREATE TABLE test(' \
            'd DATE,' \
            'ts TIMESTAMP);')
            
        today = datetime.date.today()
        now = datetime.datetime.now()
        
        conn.execute(
            'INSERT INTO test(d, ts) ' \
            'VALUES (?, ?);', (today, now))
            
        date, type_1, timestamp, type_2,  = conn.execute(
            'SELECT d, typeof(d), ' \
            'ts, typeof(ts) FROM test;').fetchone()
        print('today is %s (from %s to %s)\n' \
            'now is %s (from %s to %s)\n' \
            % (date, type_1.upper(), type(date),
            timestamp, type_2.upper(), type(timestamp)))


test_date_by_decltypes()

This function creates a test table with a d column of type DATE, and a ts column of type TIMESTAMP. time uses the datetime module to retrieve the current date, while now uses it to find the current date and time. Before executing the INSERT statement, the sqlite3 module will call the appropriate adapters. This way, the today and now objects will be mapped, respectively, to YYYY-MM-DD and YYYY-MM-DD hh:mm:ss strings. Finally, before executing the SELECT statement, all dates and timestamps will be turned into datetime objects once again, and printed out as follows:

today is 2019-04-09 (from TEXT to <class 'datetime.date'>)
now is 2019-04-09 14:07:33.544763 (from TEXT to <class 'datetime.datetime'>)

As for any other converter, these default converters can also be matched by column name:

import sqlite3
  
def test_date_by_colnames():
    """ Match default date converters by column names. """
    with sqlite3.connect(
        ':memory:',
        detect_types=sqlite3.PARSE_COLNAMES) as conn:
        date, type_1, timestamp, type_2 = conn.execute(
            'SELECT CURRENT_DATE AS "d [date]", typeof(CURRENT_DATE), ' \
            'CURRENT_TIMESTAMP AS "ts [timestamp]", ' \
            'typeof(CURRENT_TIMESTAMP);').fetchone()
        print('current date is %s (from %s to %s)\n' \
            'current timestamp is %s (from %s to %s)\n' \
            % (date, type_1.upper(), type(date),
            timestamp, type_2.upper(), type(timestamp)))


test_date_by_colnames()

This function uses the SQLite CURRENT_DATE keyword to retrieve the current date, in YYYY-MM-DD format, and the SQLite CURRENT_TIMESTAMP keyword to retrieve the current date and UTC time, in YYYY-MM-DD hh:mm:ss format (see the official documentation for more information on these keywords). Both the current date and the current timestamp are of type TEXT, as you will see from the function’s output below. Then, it maps them to the default date converters by using the "d [date]" and "ts [timestamp]" column aliases, respectively. The output of the test_date_by_colnames() function will be:

current date is 2019-04-09 (from TEXT to <class 'datetime.date'>)
current timestamp is 2019-04-09 12:07:33 (from TEXT to <class 'datetime.datetime'>)

Closing Thoughts

In this tutorial we learned all kinds of type transformations for Python and SQLite. We started by describing the default per-value transformations. While all default adapters can be easily overwritten, only the TEXT converter can be customized by defining a new text_factory function for the current connection. By giving a peek at the SQLite C API for retrieving data from a cursor, we understood how per-value conversion is possible, since the sqlite3 module exposes no function to retrieve the native type of a value. Then, we described how to define and register adapters, which maps Python types into SQLite types. We can either let the objects adapt themselves, by providing them with a __conform__ method, or register an adapter for a certain class. When we derive a class from another one, we have to register an adapter for both the base class and its subclass. Then, we took a look at converters, which maps an SQLite type to a Python type. All converters are associated with an identifier by the sqlite3.register_converter() function. This identifier can be matched to a column of data in two ways: by column name or by type declaration. Either way, the converter will be called on each value of the column, except for NULL values. Finally, we listed the default converters and adapters for dates and timestamps.

Did you find this free tutorial helpful? Share this article with your friends, classmates, and coworkers on Facebook and Twitter! When you spread the word on social media, you’re helping us grow so we can continue to provide free tutorials like this one for years to come.

Code More, Distract Less: Support Our Ad-Free Site

You might have noticed we removed ads from our site - we hope this enhances your learning experience. To help sustain this, please take a look at our Python Developer Kit and our comprehensive cheat sheets. Each purchase directly supports this site, ensuring we can continue to offer you quality, distraction-free tutorials.

Get Our Python Dev Kit