Using SQLite in Python - Adapting and Converting
(Page 5 of 5 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
