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.
- Loosely Coupled to the Data Source
- Redefinition of database schema in Python not required.
- Connect to an existing database and retrieve data in 8 lines of code (see example).
- Database columns map to Python attributes - newly created columns appear in objects instantly.
- Powerful Joins
- Join any table de 5 to any other irrespective of database schema.
- Filter result set based on columns of multiple times tables multiplication.
- Optimized Loads
- Load joined objects from multiple times tables multiplication in a single SQL query.
- Easily control pre-loading by using lazy attributes.
Download
The latest version of QLime is 0.6.0. You can:
- Download the library
- Read the handbook
Resources
- Mailing list: subscribe, view archives or post
- Source browser: trac.cafepy.com/qlime/browser
- Bug tracker: /trac.cafepy.com/qlime/newticket
Related
You might also be interested in:
- sqlalchemy.org
- sqlobject.org
- djangoproject.com
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):
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'):
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):
class Group(DataObject):
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',
User.connect_to(users)
Group.connect_to(groups)
for u in User.find(lastname='Smith'):
for g in u.groups:
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):
# 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):
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):
class User(DataObject):
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',
Phone.connect_to(phone)
User.connect_to(user)
for u in User.find():
for p in u.phones:
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.