<html> <head> <title>PyDbLite</title> </head> <body> <style type="text/css"> body, td { color: #000000; background-color: #ffffff; font-family: sans-serif; font-size: 13; } pre { font-family: arial } li { padding-bottom:10; } .python { color:330099; font-family: "Courier New"; } .console { color:white; background-color:black; font-family: "Courier New"; padding : 3; } td.navigation { background-color: #99ccff; font-weight: bold; font-family: avantgarde, sans-serif; font-size: 110%; width: 90%} td.lnk { background-color: #99ccff; font-size: 70%; } ol { margin-left : 20px; } </style> <table width="100%" cellspacing="0"><tr><td class="navigation" align="center">PyDbLite</td> <td class="lnk" align="right"></td> </tr></table> <p>PyDbLite is a pure-Python in-memory database engine, using Python list comprehensions as query language, instead of SQL <p>It consists of one small module, <code>PyDbLite.py</code>. The package also provides two modules, <code>SQLite.py</code> and <code>MySQL.py</code>. They use SQLite and MySQL backends with the same Pythonic syntax as the pure-Python PyDbLite engine <p>To install the package, just <a href="http://sourceforge.net/project/platformdownload.php?group_id=210258">download it</a> and install it by running <span class="console">>python setup.py install</span> <p><h2>Pure-Python engine</h2> <ul> <li> import class <CODE>Base</CODE> from module PyDbLite : <span class="python">from PyDbLite import Base</span> <li> create a database instance, passing it a path in the file system : <span class="python">db = Base('dummy')</span> <li>for a new database, define the field names : <span class="python">db.create('name','age','size')</span> <br>You don't have to define the field types. PyDbLite will accept any value that can be serialized by the <CODE>cPickle</CODE> module : strings, Unicode strings, integers, floats, dates and datetimes (instances of the <CODE>date</CODE> and <CODE>datetime</CODE> classes in the <CODE>datetime</CODE> module), user-defined classes, etc <li> if the base exists, open it : <span class="python">db.open()</span> <li> you can pass a parameter "mode" to the <CODE>create()</CODE> method, to specify what you want to do if the base already exists in the file system <ul> <li>mode = "open" : <span class="python">db.create('name','age','size',mode="open")</span> opens the database and ignores the field definition <li> mode = "override" : <span class="python">db.create('name','age','size',mode="override")</span> erases the existing base and creates a new one with the field definition <li> if mode is not specified and the base already exists, an <CODE>IOError</CODE> is raised </ul> <li> insert a new record <ul> <li> by keywords : <span class="python">db.insert(name='homer',age=23,size=1.84)</span> <br>If some fields are missing, they are initialized with the value <CODE>None</CODE> <li> by positional arguments : <span class="python">db.insert('homer',23,1.84)</span> <br>The arguments must be provided in the same order as in the <CODE>create()</CODE> method </ul> <li>save the changes on disk : <span class="python">db.commit()</span> <br>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 <span class="python"> open()</span> it again (this is equivalent to rollback in transactional databases) <li> besides the fields passed to the <CODE>create()</CODE> method, an internal field called <CODE>__id__</CODE> 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 <li> another internal field called <CODE>__version__</CODE> 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 <li>the selection of records uses Python list comprehension syntax : <br><span class="python">recs = [ r for r in db if 30 > r['age'] >= 18 and r['size'] < 2 ]</span> <br>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 <li> Python generator expression syntax can also be used : <br><span class="python">for r in (r for r in db if r['name'] in ('homer','marge') ):<br> do_something_with(r)</span> <br>iterates on the records where the name is one of 'homer' or 'marge' <li> to iterate on all the records : <br><span class="python">for r in db:<br> do_something_with(r)</span> <li> a record can be accessed by its identifier : <span class="python">record = db[rec_id]</span> returns the record such that record['__id__'] == rec_id <li> finally, a shortcut can be used for simple selections : <span class="python">db(key1=val1,key2=val2)</span> returns the list of records where the keys take the given value. It is equivalent to <span class="python">[ r for r in db if r["key1"]==val1 and r["key2"]==val2]</span>, but much more concise <li>to speed up selections, an index can be created on a field : <span class="python">db.create_index('age')</span> <br>When an index is created, the database instance has an attribute (here <CODE>_age</CODE> : 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 : <br><span class="python">records = db._age[23]</span> returns the list of records with age == 23 <br>If no record has this value, lookup by this value returns an empty list <br>The index supports iteration on the field values, and the <CODE>keys()</CODE> method returns all existing values for the field <li>number of records in the base : <span class="python">len(db)</span> <li>to delete a record : <span class="python">db.delete(record)</span> or, if you know the record identifier : <span class="python">del db[rec_id]</span> <li>to delete a list of records : <span class="python">db.delete(list_of_records)</span> <br><CODE>list_of_records</CODE> can be any iterable (list, tuple, set, etc) yielding records <li>to update a record : <span class="python">db.update(record,age=24)</span> <li>to add a new field to an existing base and specify a default value : <span class="python">db.add_field('new_field'[,default=v])</span>. If no default is provided, the field value is <CODE>None</CODE> <li>to drop an existing field : <span class="python">db.drop_field('name')</span> <li>to get the list of fields : <span class="python">db.fields</span> </ul> <a name="sqlite"><p><h2>SQLite adapter</h2> <p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation <ul> <li>import the class <code>Base</code> : <span class="python">from PyDbLite.SQLite import Base</span> <li>connect to the SQLite database : <span class="python">connection = sqlite.connect("test")</span> <li>to create a <code>Base</code> instance (a table in the SQLite database) you pass the connection as argument : <span class="python">db = Base('dummy',connection)</code> <li>to create the base you must specify an SQLite field type : NULL, INTEGER, REAL, TEXT or BLOB : <span class="python">db.create(('name','TEXT'),('age',"INTEGER'),('size','REAL'))</span> <p>For convenience, you can also use the types DATE and DATETIME (or TIMESTAMP), the package will transparently manage the conversions between the <code>datetime.date</code> and <code>datetime.datetime</code> and the TEXT type </ul> <p>For record insertion, selection, update and deletion, the syntax is the same as above. The only difference is that you can't use the <code>drop_field()</code> method, since dropping fields is not supported by SQLite <p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result by <span class="python">results = db.cursor.fetchall()</span> <p><a name="mysql"><h2>MySQL adapter</h2> <p>The only difference with the pure-Python module is the syntax to identify a <code>Base</code> and the need to specify field types on base creation <ul> <li>import the class <code>Base</code> : <span class="python">from PyDbLite.MySQL import Base</span> <li>connect to the SQLite database : <p><span class="python">import MySQLdb<br> connection = MySQLdb.connect("localhost","root","admin")<br> connection.cursor().execute("USE test") </span> <li>pass the connection as argument to Base creation : <span class="python">db = Base('dummy',connection)</code> <li>to create a new base (a table in the MySQL database),specify a valid MySQL field type : <span class="python">db.create(('name','INTEGER'),('age',"INTEGER'),('size','REAL'))</span> </ul> <p>For record insertion, selection, update and deletion, adding or dropping fields, the syntax is the same as above <p>The <code>Base</code> instance has an attribute <code>cursor</code>, so you can also execute SQL expressions by <span class="python">db.cursor.execute(some_sql)</span> and get the result by <span class="python">results = db.cursor.fetchall()</span> </body> </html>