PyDbLite

PyDbLite is a pure-Python in-memory database engine, using Python list comprehensions as query language, instead of SQL

It consists of one small module, PyDbLite.py. The package also provides two modules, SQLite.py and MySQL.py. They use SQLite and MySQL backends with the same Pythonic syntax as the pure-Python PyDbLite engine

To install the package, just download it and install it by running >python setup.py install

Pure-Python engine

import class Base from module PyDbLite : from PyDbLite import Base
create a database instance, passing it a path in the file system : db = Base('dummy')
for a new database, define the field names : db.create('name','age','size')
You don't have to define the field types. PyDbLite will accept any value that can be serialized by the cPickle module : strings, Unicode strings, integers, floats, dates and datetimes (instances of the date and datetime classes in the datetime module), user-defined classes, etc
if the base exists, open it : db.open()
you can pass a parameter "mode" to the create() method, to specify what you want to do if the base already exists in the file system
- mode = "open" : db.create('name','age','size',mode="open") opens the database and ignores the field definition
- mode = "override" : db.create('name','age','size',mode="override") erases the existing base and creates a new one with the field definition
- if mode is not specified and the base already exists, an IOError is raised
insert a new record
- by keywords : db.insert(name='homer',age=23,size=1.84)
  If some fields are missing, they are initialized with the value None
- by positional arguments : db.insert('homer',23,1.84)
  The arguments must be provided in the same order as in the create() method
save the changes on disk : db.commit()
If you don't commit the changes, the insertion, deletion and update operations will not be saved on disk. To return to the previous version, just open() it again (this is equivalent to rollback in transactional databases)
besides the fields passed to the create() method, an internal field called __id__ is added. It is a integer which is guaranteed to be unique and unchanged for each record in the base, so that it can be used as the record identifier
another internal field called __version__ is also managed by the database engine. It is a integer which is set to 0 when the record is created, then incremented by 1 each time the record is updated. This is used to detect concurrency control, for instance in a web application where 2 users select the same record and want to update it at the same time
the selection of records uses Python list comprehension syntax :
recs = [ r for r in db if 30 > r['age'] >= 18 and r['size'] < 2 ]
returns the records in the base where the age is between 18 and 30, and size is below 2 meters. The record is a dictionary, where the key is the field name and value is the field value
Python generator expression syntax can also be used :
for r in (r for r in db if r['name'] in ('homer','marge') ):
do_something_with(r)
iterates on the records where the name is one of 'homer' or 'marge'
to iterate on all the records :
for r in db:
do_something_with(r)
a record can be accessed by its identifier : record = db[rec_id] returns the record such that record['__id__'] == rec_id
finally, a shortcut can be used for simple selections : db(key1=val1,key2=val2) returns the list of records where the keys take the given value. It is equivalent to [ r for r in db if r["key1"]==val1 and r["key2"]==val2], but much more concise
to speed up selections, an index can be created on a field : db.create_index('age')
When an index is created, the database instance has an attribute (here _age : note the heading underscore, to avoid name conflicts with internal names). This attribute is a dictionary-like object, where keys are the values taken by the field, and values are the records whose field values are egal to the key :
records = db._age[23] returns the list of records with age == 23
If no record has this value, lookup by this value returns an empty list
The index supports iteration on the field values, and the keys() method returns all existing values for the field
number of records in the base : len(db)
to delete a record : db.delete(record) or, if you know the record identifier : del db[rec_id]
to delete a list of records : db.delete(list_of_records)
list_of_records can be any iterable (list, tuple, set, etc) yielding records
to update a record : db.update(record,age=24)
to add a new field to an existing base and specify a default value : db.add_field('new_field'[,default=v]). If no default is provided, the field value is None
to drop an existing field : db.drop_field('name')
to get the list of fields : db.fields

SQLite adapter

The only difference with the pure-Python module is the syntax to identify a Base and the need to specify field types on base creation

import the class Base : from PyDbLite.SQLite import Base
connect to the SQLite database : connection = sqlite.connect("test")
to create a Base instance (a table in the SQLite database) you pass the connection as argument : db = Base('dummy',connection)
to create the base you must specify an SQLite field type : NULL, INTEGER, REAL, TEXT or BLOB : db.create(('name','TEXT'),('age',"INTEGER'),('size','REAL'))
For convenience, you can also use the types DATE and DATETIME (or TIMESTAMP), the package will transparently manage the conversions between the datetime.date and datetime.datetime and the TEXT type

For record insertion, selection, update and deletion, the syntax is the same as above. The only difference is that you can't use the drop_field() method, since dropping fields is not supported by SQLite

The Base instance has an attribute cursor, so you can also execute SQL expressions by db.cursor.execute(some_sql) and get the result by results = db.cursor.fetchall()

MySQL adapter

The only difference with the pure-Python module is the syntax to identify a Base and the need to specify field types on base creation

import the class Base : from PyDbLite.MySQL import Base
connect to the SQLite database :
import MySQLdb
connection = MySQLdb.connect("localhost","root","admin")
connection.cursor().execute("USE test")
pass the connection as argument to Base creation : db = Base('dummy',connection)
to create a new base (a table in the MySQL database),specify a valid MySQL field type : db.create(('name','INTEGER'),('age',"INTEGER'),('size','REAL'))

For record insertion, selection, update and deletion, adding or dropping fields, the syntax is the same as above

The Base instance has an attribute cursor, so you can also execute SQL expressions by db.cursor.execute(some_sql) and get the result by results = db.cursor.fetchall()