By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,095 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,095 IT Pros & Developers. It's quick & easy.

another SQL implement

P: n/a
Hash: SHA1


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

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()

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

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

return ds_define()

cx = sqlite.connect('test

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



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 * - 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?

The implementation depend on pythk, so you need two modules, pysql & pythk
- --
Thinker Li - th***** th********
Version: GnuPG v1.4.6 (FreeBSD)
Comment: Using GnuPG with Mozilla -


Jan 8 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.