Copyright (c) 2005, Shalabh Chaturvedi. All Rights Reserved.
$Id$
Preface
This handbook describes installation and use of the QLime Data Access Layer. Send comments about this document to shalabh at cafepy.com.
QLime is written entirely in Python 2.3 and should run wherever Python can run.
Platforms:
- FreeBSD
- Linux
- Mac OS X
- Windows
Data Sources:
- PostgreSQL (requires PyPgSQL)
- Firebird (requires KInterbasDB)
- CSV files
QLime requires Python version 2.3 or greater.
Optional:
Always check http://www.qlime-project.org/ for the latest version. This handbook is for version 0.6.0.
Installation:
Uncompress the downloaded file:
tar xzf QLime-0.6.0.tar.gz
(or unzip if using Qlime-0.6.0.zip)
Cd to the created directory:
cd QLime-0.6.0
Run setup.py:
python setup.py install
QLime should now be installed in the Python site-packages directory (for example, /usr/local/lib/python2.3/site-packages/qlime/). To see if it is installed correctly, run python and do:
>>> import qlime >>>
It should not return any errors.
Data access from scratch consists of the following steps:
Sounds fancy, but it's really quite simple. Here are all 4 steps:
# This is tutorial/phonedata.py
from qlime import DataObject
from qlime import csvdb
# Data class
class Phone(DataObject):
pass
# Connection
conn = csvdb.DSConn(filepath="phone.csv", attrs=['name', 'number'])
# Source objectclass - a csv based datasource has only one objectclass
obclass = conn['default']
# Bind the data class to the objectclass
Phone.connect_to(obclass)
That's it! Now our class is ready to use:
# Creating and saving p = Phone() p.name = 'spam' p.number = '333-4444' Phone.insert(p) p = Phone() p.name = 'eggs' p.number = '777-0000' Phone.insert(p) conn.commit() # Reading it back p = Phone.find_one(name='eggs') print p.number # Updating p.number = '999-1111' conn.commit()
Remember that the data class is a Python class you define and the source objectclass is an instance returned by the connection object.
Ensure you have setup the PostgreSQL database and have installed the pyPgSQL module. You should be familiar with creating databases and tables. Login to a database of your choice and create a table called 'phone' with two columns:
CREATE TABLE phone (name VARCHAR(10), number VARCHAR(10));
Insert some data into the table:
INSERT INTO phone (name, number) VALUES ('spam', '222-2222');
INSERT INTO phone (name, number) VALUES ('eggs', '555-5555');
INSERT INTO phone (name, number) VALUES ('bacon', '800-bacon');
Here's a new sample file that gets data from the database:
# This is tutorial/phonedata2.py
from qlime import DataObject, pgsql
conn = pgsql.DSConn(dbname='dbname', username='user', password='pass')
obclass = conn['public.phone']
class Phone(DataObject):
pass
Phone.connect_to(obclass)
p = Phone.find_one(name='spam')
print p.number
Replace 'dbname', 'user' and 'pass' above with appropriate values.
The PostgreSQL connection returns each table as an objectclass with the fully qualified name (schema.tablename) as the name of the objectclass. The default schema in PostgreSQL is called 'public' hence we use the 'public.' prefix.
If using Firebird, follow the exact same steps except:
QLime does not introduce its own data types. It simply passes through the data types of the underlying database library (for e.g. PyPgSQL or KInterbasDB).
QLime supports transactions via two methods on the connection object:
commit() rollback()
CSV files are not fully transactional.
There are three ways to search a DataObject:
find(**kw) # returns iterator find_one(**kw) # returns object or None search(filter) # returns iterator
To construct a filter, use class level attributes - some examples:
Phone.search(Phone.name.isEq('spam'))
from qlime import Or
Phone.search(Or(Phone.name.isEq('eggs'), Phone.number.isEq('555-3333')))
The search() method also supports limit and sort_on keyword arguments:
Phone.search(limit=5, sort_on=(Phone.name, Phone.number))
This will sort first by name and then by number.
Let's assume we have another users table:
CREATE TABLE users (username VARCHAR(10), fullname VARCHAR(100));
Here is a join between this table and the earlier phone table:
# This is tutorial/userphone.py
from qlime import pgsql, DataObject
class Phone(DataObject):
pass
class User(DataObject):
pass
conn = pgsql.DSConn(dbname='dbname', username='user', password='pass')
phone = conn['public.phone']
user = conn.new_composite_obclass('User', 'public.users')
user.join_obclass('phones', join_attr='username', jointo_oc='public.phone',
jointo_attr='name')
Phone.connect_to(phone)
User.connect_to(user)
for u in User.find():
print u.fullname
for p in u.phones:
print p.number
You need a composite objectclass to do joins. The method new_composite_obclass(name, primary_obclass) specifies:
The join method join_obclass(subobname, join_attr, jointo_oc, jointo_attr) specifies:
Once joined, the subobname attribute of the main class will pull out instances of the jointo_oc class.
You can build joins on top of other composite objectclasses.
Multi-valued and single-valued joins: Use the keyword parameter is_multi in the join_obclass() method.
Lazy attributes: Use the set_lazy_attributes() method on the source objectclass.
Cyclic joins and self joins: Use the usual join_obclass() method.
Filtering on sub-objects: Use attribute notation to filter even on sub-object attributes! For example:
User.search(User.phones.number.isEq('222-2222'))Optimizations For non-lazy joins, QLime is optimized to fire the minimal number of SQL queries. For example, when loading a user row, all the joined objets are loaded in the same query.
Official Homepage: http://www.qlime-project.org/
Mailing list: qlime@googlegroups.com, http://groups.google.com/group/qlime
Python: http://www.python.org/