another SQL implement

[Thinker]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

hello,

I have implement a SQL command generator for Python. It uses features
and syntax of Python to describe SQL schema & query. Following is a
example:

from pysqlite2 import dbapi2 as sqlite
from pysql import *

#
# define data source
# comprises tables and queries
#
class my_data_src(data_src):
def ds_definition():
class t1(pysql.table):
#
# define table 't1'
#
item = pysql.str_f()
count = pysql.int_f()
pass

class t2(pysql.table):
#
# define table 't2'
#
item = pysql.str_f()
money = pysql.float_f()
pass

#
# define a query 'get_count_and_money_of_item'
#
get_count_and_money_of_item = \
lambda: (t1 * t2) \
.fields(t1.item, t1.count * t2.money - 30) \
.where((t1.item == t2.item) & (t1.item != pysql._q))

return ds_define()
pass

cx = sqlite.connect('test
<http://heaven.branda.to/%7Ethinker/GinGin_CGI.py/show_kw_docs/test>.db')

db = my_data_src(cx) # create a instance of data source

db.init_db() # create tables

db.insert(db.t1, item='foo', count=100)
db.insert(db.t2, item='foo', money=3.2)
db.insert(db.t1, item='boo', count=50)
db.insert(db.t2, item='boo', money=3.0)

db.update(db.t1, count=140, where=~(db.t1.item == 'foo'))

cu = db.get_count_and_money_of_item('foo')
rows = cu.fetchall()
print rows

cu = db.get_count_and_money_of_item('cool')
rows = cu.fetchall()
print rows

db.commit()

pass

This is example code to define database schema and query.
get_count_and_money_of_item is initialized by a lambda expression. It
can also be a function. By inherit class data_src and defining method
ds_definition, my_data_src is acting as a wrapper of a database. It
includes two tables, t1 & t2, and a query,
get_count_and_money_of_item. Instantiate a instance of my_data_src
with a DB connection, you can create tables (init_db), insert records,
update records, and perform queries.

get_count_and_money_of_item = \
lambda: (t1 * t2) \
.fields(t1.item, t1.count * t2.money - 30) \
.where((t1.item == t2.item) & (t1.item != pysql._q))

(t1 * t2) means join tables t1 & t2, you can specify fields to be
return by query with *.fields(...). *.where() is just like WHERE
expression in SQL. get_count_and_money_of_item comprises a
free-variable; pysql._q stands as a free-variable waiting for caller
specified as parameter when calling get_count_and_money_of_item.

cu = db.get_count_and_money_of_item('foo')
rows = cu.fetchall()

It calls get_count_and_money_of_item() with 'foo' for free-variable.
A query returns a cursor defined in DBAPI.

Why another SQL ? Why not SQLObject?
RDBMS provides powerful query language, it provides performance by
reducing traffic between database & application and reducing memory
copy. A OR-mapping made RDBMS weak. DBMS is reduced as a indexed
storage. Relational algebra is so powerful & effective. Why don't
integrate it into Python language?

Downloads:
The implementation depend on pythk, so you need two modules, pysql & pythk
http://master.branda.to/downloads/pysql/pysql-20070108.tar.gz
http://master.branda.to/downloads/pythk/pythk-20070108.tar.gz


- --
Thinker Li - thinker at branda.to thinker.li at gmail.com
http://heaven.branda.to/~thinker/GinGin_CGI.py
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFoflZ1LDUVnWfY8gRAqw9AJ9UQ5OAOIEWo+ovELQvcKohAkWJtwCfcloq
ecOxlrstOZ77Mr9qPxlkBj0=
=U12Y
-----END PGP SIGNATURE-----