Host SQL in Python

by thinker

關鍵字:

昨天在 linkname:COSCup http://coscup.tossug.org/ 針對這個主題做了 lightning talk，特地將這篇 touch 一次。內容和 slide 有些差異，主要是 data model 的部分做了修改。晚一點有空再把修改的部分放出來。

這是一個小試驗，直接使用 $Python$ 組出 SQL command，而不透過字串。

{{{
import string

def get_tables(*args):
    return tuple(map(lambda x: table(x), args))

class table:
    def __init__(self, name, fields=[]):
	self.__name = name
	self.__fields = fields
	pass
    
    def set_fields(self, *fields):
	self.__fields = fields
	all = map(lambda x: (x, field(self, x)), fields)
	for n, f in all:
	    setattr(self, n, f)
	pass
    
    def __add__(self, other):
	return join_table(self, other)
    
    def __str__(self):
	return self.__name
    
class join_table:
    def __init__(self, *args):
	self.__members = set(args)
	self.__fields = []
	self.__cond = []
	pass
    
    def __add__(self, other):
	self.__members.add(other)
	return self
    
    def __str__(self):
	tbs = string.join(map(lambda x: str(x), self.__members), ', ')
	if self.__fields:
	    fds = string.join(map(lambda x: str(x), self.__fields), ', ')
	else:
	    fds = '*'
	if self.__cond:
	    conds = ' where ' + str(self.__cond)
	else:
	    conds = ''
	return 'select ' + fds + ' from ' + tbs + conds
    
    def where(self, cond):
	self.__cond = cond
	return self
	
    def fields(self, *args):
	self.__fields = args
	return self
    
class operand:
    def __init__(self):
	pass
    
    def __str__(self):
	return self.op
    
    def __eq__(self, other):
	return expr(self) == other
    
    def __gt__(self, other):
	return expr(self) > other
    
    def __lt__(self, other):
	return expr(self) < other
    
    def __and__(self, other):
	return expr(self) and other
    
class op:
    def __init__(self, op):
	self.op = op
	pass
    
    def __str__(self):
	return self.op
    
class field(operand):
    def __init__(self, tb, name):
	operand.__init__(self)
	self.__tb = tb
	self.__name = name
	
    def __str__(self):
	return str(self.__tb) + '.' + self.__name
    
class expr(operand):
    def __init__(self, ex=None):
	operand.__init__(self)
	self.__seq = []
	if ex != None:
	    self.__seq.append(ex)
	    
    def __apply_op(self, opstr, other):
	self.__seq.append(op(opstr))
	if not isinstance(other, operand):
	    other = const(other)
	self.__seq.append(other)
	return self
    
    def __eq__(self, other):
	return self.__apply_op('=', other)
    
    def __gt__(self, other):
	return self.__apply_op('>', other)
    
    def __lt__(self, other):
	return self.__apply_op('<', other)
    
    def __and__(self, other):
	return self.__apply_op('AND', other)
    
    def __str__(self):
	return string.join(map(lambda x: str(x), self.__seq), ' ')
    
class const(operand):
    def __init__(self, v):
	operand.__init__(self)
	self.value = v
	pass
    
    def __str__(self):
	return repr(self.value)
    
if __name__ == '__main__':
    t1, t2, t3 = get_tables('table1', 'table2', 'table3')
    
    t1.set_fields('f1', 'f2', 'f3')
    jtab = t1 + t2 + t3
    jtab.fields(t1.f1, t1.f2).where((t1.f1 == 'abc') & (t1.f2 < t1.f3) & (t1.f3 > 4))
    print str(jtab)
}}}

執行結果:
{{{
select table1.f1, table1.f2 from table2, table1, table3 where
 table1.f1 = 'abc' AND table1.f2 < table1.f3 AND table1.f3 > 4
}}}

這是簡單的試驗，只有很簡單/不完全的功能。若能更進一步發展，應該能很漂亮的將 SQL 整合入 $Python$。

== 後語 ==
做完試驗後，才發現的東西
http://www.sqlobject.org/

最後更新時間: 2006-10-29 19:48:11 CST | 引用

查詢:

COMMENTS: