Adapting and Converting - Python

An interesting feature of pysqlite is the ability to easily store representations of other types of information inside of an SQLite database. For example, let's say we have a class whose instances we want to be able to store inside a database. SQLite will only hold a few types of information, but we can force instances of our class to adapt to one of SQLite's types. Then, when we want to retrieve the instance, we can convert it back.

Before we do anything, we have to close our connection and reopen it with instructions to recognize new types:

>>> cursor.close()
>>> connection.close()
>>> connection = sqlite.connect('test.db', detect_types =
sqlite.PARSE_DECLTYPES)
>>> cursor = connection.cursor()

Now, let's create a class for our new type. Continuing with names and emails, and for simplicity's sake, we'll create a Person class that stores a person's name and email. Note that pysqlite forces us to subclass object and create a new-style class:

>>> class Person (object):
                def __init__ (self, name, email):
                               self.name = name
                               self.email = email

Now, we'll create a table that stores a unique number and then an information field, which will use our new type:

>>> cursor.execute('CREATE TABLE people (id INTEGER PRIMARY KEY,
information Person)')
>>> cursor.commit()

Next, we have to create a way for our object to be converted into text, and we have to create a way to reverse the process. We can do this using two functions:

>>> def personAdapt (person):
                return person.name + "n" + person.email
>>> def personConvert (text):
                text = text.split("n")
                return Person(text[0], text[1])

The first function puts a linebreak character between the name and email, combining them into a single string. The second function splits the string between the character and then creates an object with the two resulting strings. It's very simple. We now have to register our two functions, one as an adapter and the other as a converter:

>>> sqlite.register_adapter(Person, personAdapt)
>>> sqlite.register_converter("Person", personConvert)

Now everything is set up properly. We are able to insert an instance of Person, and we are able to retrieve it. Let's create an instance and insert it first:

>>> socrates = Person('Socrates', 'socrates@ancient.gr')
>>> cursor.execute('INSERT INTO people VALUES (null, ?)',
(socrates,))
>>> connection.commit()

We'll now retrieve it and print out the object's information:

>>> cursor.execute('SELECT * FROM people')
>>> row = cursor.fetchone()
>>> print row[1].name, row[1].email
Socrates socrates@ancient.gr

Conclusion

SQLite is a pretty amazing database engine. Unlike other database engines which require a database server, SQLite functions as a library that uses normal files as databases. It also requires no dependencies and no configuration. This makes it extremely portable, which, combined with its small size, makes it ideal for projects of various sizes. The pysqlite library takes these benefits and allows Python developers to make use of them, allowing the benefits of both Python and SQLite to be used together.