multiplications - QLime

multiplications - QLime 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 QLime is free to download and use based on a liberal BSD Style License.

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): 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.