Home arrow Python arrow Page 5 - Using SQLite in Python

Adapting and Converting - Python

SQLite is a small C library that implements a self-contained SQL database engine. This article examines pysqlite, one of a number of libraries in Python, that is used as an interface with SQLite.

  1. Using SQLite in Python
  2. Connecting to a Database
  3. Writing Data
  4. Retrieving Data
  5. Adapting and Converting
By: Peyton McCullough
Rating: starstarstarstarstar / 132
April 12, 2006

print this article



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 =
>>> 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, ?)',
>>> 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


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.

>>> More Python Articles          >>> More By Peyton McCullough

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates


Dev Shed Tutorial Topics: