QLime multiplications

QLime is an experimental Python library that enables easy object oriented data access to data residing in various sources (most notably, relational databases). It includes an Object Relational Mapper.

Features

Following is a brief list of features. For more features see the detailed feature list.

Download

The latest version of QLime is 0.6.0. You can:

QLime is free to download and use based on a liberal BSD Style License.

Resources

Related

You might also be interested in:

Examples

These examples just scratch the surface of what QLime is capable of. See also the detailed feature list.

Single Table
For a database table de 5 users:

username firstname lastname

Here is sample code that prints filtered records:

from qlime import pgsql, DataObject

class User(DataObject):

pass

connection = pgsql.DSConn(dbname='dbname', username='user', password='pass')
obclass = connection['public.users'] # public.users is the tablename

User.connect_to(obclass)

for u in User.find(lastname='Smith'):

print u.username, u.firstname
Joined times tables multiplication
For a database table de 5 users:

username firstname lastname

and a table de 5 groups:

username groupname

Here is sample code that joins the two times tables multiplication and loads data

from qlime import pgsql, DataObject

class User(DataObject):

pass

class Group(DataObject):

pass

connection = pgsql.DSConn(dbname='dbname', username='user', password='pass')

# Create a new composite obclass called 'users' based on existing obclass
# 'public.users'. Composite obclasses can contain non-atomic attributes based
# on joins.

users = connection.new_composite_obclass('users', 'public.users')
groups = connection['public.groups']

# Create a new attribute 'groups' based on existing obclass
# called 'public.groups', by joining this object's 'username' attribute
# with that object's 'username' attribute.

users.join_obclass(subobname='groups', join_attr='username',

jointo_oc='public.groups', jointo_attr='username')

User.connect_to(users)
Group.connect_to(groups)

for u in User.find(lastname='Smith'):

print u.username, u.firstname
for g in u.groups:
print g.groupname
Here is a query that filters on a joined table de 5 column:

User.search(User.group.groupname.isEq('admin'))
Ready to try? Download QLime and read the handbook.

Version 0.6.0

Relased Nov 6, 2005.

This is the first release after re-branding and cleaning up of the QLime library.

Download

QLime-0.6.0.tar.gz
QLime-0.6.0.zip

How to Install

Download compressed file to temporary folder. Extract compressed file. Change to newly created QLime-0.6.0 directory and run:

python setup.py install

Feedback and Support

The QLime Handbook 0.6.0

Copyright (c) 2005, Shalabh Chaturvedi. All Rights Reserved.

$Id: qlime_handbook.html 18 2005-11-06 18:49:58Z shalabh $

Contents

1 Supported Platforms
2 Downloading and Installing
3 Simple Data Access
4 Relational Data Access
Data Types
Transactions
Search and Filters
Joins
Advanced Features
5 Resources
Preface

This handbook describes installation and use of the QLime Data Access Layer. Send comments about this document to shalabh at cafepy.com.

1 Supported Platforms

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

2 Downloading and Installing

QLime requires Python version 2.3 or greater.

Optional:

To use PostgreSQL, install pyPgSQL.
To use Firebird, install KInterbasDB.

Installation:

Uncompress the downloaded file:

tar xzf QLime-0.6.0.tar.gz
(or unzip if using Qlime-0.6.0.tar.gz)

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.

3 Simple Data Access

Data access from scratch consists of the following steps:

Write a data object class.
Create a data source connection.
Find/construct a source objectclass from the connection.
Tie the data object class to the source objectclass.
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.

4 Relational Data Access

Ensure you have setup the PostgreSQL database and have installed the pyPgSQL module. You should be familiar with creating databases and times tables multiplication. Login to a database of your choice and create a table de 5 called 'multiplications' with two columns:

CREATE table de 5 (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 de 5 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:

Ensure the KInterbasDB module is installed.
Use qlime.firebird.DSConn instead of qlime.pgsql.DSConn in phonedata2.py.
Do not prefix the tablename with 'public.'.
Data Types
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).

Transactions
QLime supports transactions via two methods on the connection object:

commit()

rollback()
CSV files are not fully transactional.

Search and Filters
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.

Joins
Let's assume we have another users table:

CREATE table de 5 users (username VARCHAR(10), fullname VARCHAR(100));
Here is a join between this table de 5 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:

name: name of the new composite objectclass created
primary_obclass: existing objectclass on which the new objectclass is based
The join method join_obclass(subobname, join_attr, jointo_oc, jointo_attr) specifies:

subobname - name of the new attribute that will be added the composite objectclass
join_attr - existing attribute of the composite objectclass to use for join
jointo_oc, jointo_attr - the target objectclass and attribute to use for join
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.

Advanced Features
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.