使用builder模式生成SQL语句

最新推荐文章于 2024-04-24 13:35:29 发布

ChristopherChen

最新推荐文章于 2024-04-24 13:35:29 发布

阅读量2.2k

点赞数 1

分类专栏： Python 文章标签： python

Python 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

缘由

在利用Python做数据库操作的时候，经常需要拼接各种SQL语句，各种字符串拼，代码写起来，那叫一个恶心。

在字符串内，没有相应编译器（IDE或解释器）的语法检查功能，每次写都得全神贯注提心吊胆，写错了排查起来那也一样恶心。

见识过各种ORM黑魔法似的骚操作，不需要写一行SQL代码，而是完全利用宿主语言表达查询意图，并由ORM内部处理为SQL语句，用起来顿时开心了许多。

但是吧，本人向来喜欢轻量级的东西，也不喜欢啥玩意儿都总带一些私货或者是个黑箱。于是，决定动手自己撸一个最Naive的版本先用着，在使用中去体会如何再次进行扩展。

BB了半天，其实就是想做一个这样的东西：

作者：不是油条
链接：https://zhuanlan.zhihu.com/p/25962952
来源：知乎
著作权归作者所有，转载请联系作者获得授权。

BB了半天，其实就是想做一个这样的东西：

sql = select(['a', 'b']).from_table('demo_table').where(
    ((col('x') == 99) & (col('y') >= 7)) | (col('z') == 9)
).build()
print sql

就能够输出得到一条SQL语句：

SELECT a,b FROM demo_table WHERE (((x = 99) and (y >= 7)) or (z = 9))

这样，妈妈再也不用担心我在一堆套来套去的引号、加法、各式控制符中去寻找、更改万恶的既不能被静态检查、又没法高亮的SQL语句了。

开始构思

首先，按这个写法，很像某种套路，其实就是Builder模式。在想要构建一个具有大量参数的对象之时，可以通过builder模式去将各种参数分开处理，不用写一个超大的构造函数。同时，在builder中使用不同的成员函数设定参数，让代码更加富有语义、具备更强的可读性。

那就按照builder模式的套路来吧，先做一个类，用来包裹各种参数：

class SqlBuilder:
    def __init__(self, columns):
        pass

    def build(self):
        pass

    def from_table(self, table):
        pass
        
    def where(self, condition):
        pass

同时，加一个函数select，直接返回一个SqlBuilder对象：

def select(columns):
    return SqlBuilder(columns)

这样，第一步的

select(['a', 'b']).from_table ....

这种结构就出来了。接下来再细化各函数的实现，其实很简单，就是把参数先存起来，然后在build里统一去处理吧：

class SqlBuilder:
    def __init__(self, columns):
        self.table = None
        self.condition = None
        self.columns = columns 

    def build(self):
        if isinstance(self.columns, list):
            columns = ','.join(self.columns)
        else:
            columns = self.columns

        if self.condition is None:
            where = ''
        else:
            where = " WHERE %s" % self.condition
        
        sql = "SELECT %s FROM %s" % (columns, self.table)
        sql += where
        return sql

    def from_table(self, table):
        self.table = table
        return self

    def where(self, condition):
        self.condition = condition
        return self

到这里，我们的整个Builder就可以支持这样用了：

select(['a', 'b']).from_table('demo_table').where('a = 2 and b = 3').build()
# SELECT a,b FROM demo_table WHERE a = 2 and b = 3

好了，接下来是比较复杂的WHERE，各种限定条件，并且可以进行OR和AND这样的逻辑联结运算，还可以嵌套使用。

WHERE CLAUSE

在这里，庆幸自己用的是Python吧，要是换作某些辣鸡语言，就只能干巴巴地望着了。

想要舒舒服服地写WHERE后面的部分，需要语言本身支持“运算符重载”的功能，即我们可以重新定义Pyhton中特定类型的一些运算，来构造出与代码的运算等价的WHERE CLAUSE结构。

运算符重载

在Python中，用户可以自行定义对象的各种运算的行为，比如这样：

class Num:
    def __init__(self, n):
        self.n = n
        
    def __add__(self, other):
        print 'add operation'
        return Num(self.n + other.n)

这时候，如果进行运算：

s = Num(1) + Num(2)
print s.n

即可看到print语句输出的内容add operation同时得到s.n的值为3。加法可以，其它各种运算也如此。

数据结构设计

好在，多数情况下，我们在WHERE中就只是写个简单的查询，几乎都是二元运算，那这里就拿二元运算来开刀吧。

再考虑到这些基本的判断运算还可以通过逻辑联结进行组合，并且还可以嵌套使用，这不就成了一个递归结构了么。

喵的这不就是一个二叉树么。

比如：

((a = 2) and (b = 3)) or (c = 4)

不就是这样么：

         or
       /    \
      /      \
    and       =  
   /   \     / \
  =     =   c   4
 / \   / \
a   2 b   3

再进行简化：

       or
     /    \
    /      \
  and       =  
 /   \
=     =

这就好办了，把整个结构想象成一个二叉树。基本的判断运算即可认为是二叉树的叶子结点，二逻辑联结就是二叉树的非叶子结点。如此一分类，问题又简单了许多，怎么做呢？

首先，有两类结构：

基本的判断运算
逻辑联结

那么，我们可以把这两类结构分别看作不同类型的结点，基本条件判断的结点具备三个基本元素：

列名
运算符
值

也就是这样：

class PrimaryCondition:
    def __init__(self, column):
        self.column = column
        self.op = None
        self.value = None

而逻辑联结节点包含的内容有些不同：

左侧表达式
逻辑运算
右侧表达式

也就是这样：

class CompositeCondition:
    def __init__(self, left, op, right):
        self.left = left
        self.op = op
        self.right = right

其中的left和right又可以指向一个PrimaryCondition或者CompositeCondition。

OK。如此，我们该如何让

((col('x') == 99) & (col('y') >= 7)) | (col('z') == 9)

产生出对应的树形结构，并转化为正确的字符串呢？

生成数据结构

要对这里的运算动手脚了，这里需要重载的运算有这样几个：

先来解决这个：

col('x') == 99

这个表达式中的col('x')就应该得到一个对象，并且这个对象的==操作符被重载了，并且，很重要，这个重载的操作符返回的结果任然是对象，返回的对象仍然需要重载&和|运算才可以用于逻辑联结。

于是就是这样的：

class PrimaryCondition:
    def __init__(self, column):
        self.column = column
        self.op = None
        self.value = None

    def __eq__(self, other):
        self.op = '='
        self.value = other
        return self
        
def col(name):
    return PrimaryCondition(name)

col函数用于构造出一个对象，该对象重载了==操作符用于捕获后面的值99。

那再考虑复杂一点的：

(col('x') == '99') & (col('y') >= 7)

先从&运算切开，左侧和右侧分别都是一个PrimaryCondition。

看这样子，左侧的PrimaryCondition需要重载&运算符，并接受右侧的表达式产生的对象，并且生成并返回一个CompositeCondition对象，包含左侧的条件和右侧的条件，以及两个条件的逻辑联结运算。

实际上这个也比较容易做到，我们直接实现PrimaryCondition的__and__和__or__函数即可：

class PrimaryCondition:
    # some code ...
    
    def __and__(self, other):
        return CompositeCondition(self, 'and', other)

    def __or__(self, other):
        return CompositeCondition(self, 'or', other)

同时，CompositeCondition对象之间、CompositeCondition和PrimaryCondition之间也要支持直接的逻辑联结。以此类推，这也比较简单了，直接重载CompositeCondition的&和|运算符即可。

class CompositeCondition:
    # some code ...

    def __and__(self, other):
        return CompositeCondition(self, 'and', other)

    def __or__(self, other):
        return CompositeCondition(self, 'or', other)

好了，如此我们便可以让表如下达式：

((col('x') == 99) & (col('y') >= 7)) | (col('z') == 9)

返回一个二叉结构的数了，表达式本身的所有信息，都已经到了树结构中去。接下来就是把它转换成字符串了。

在这个递归的结构里面，转换成字符串的实现可以变得非常简单，首先是基本的判断运算的转换，无非就是把列名、运算符、值直接拼接到一起，像这样：

(a = 2)

为了方便后续地拼接不会打乱运算符优先级关系，还需要在外侧加上括号。

至于符合的逻辑联结运算，其实抽象地看，也非常简单，无非就是把左侧表达式的字符串，加上联结运算符，加上右侧表达式的字符串，比如：

(left and right)

这里的left和right可以用左右两个孩子对象转换后的字符串替换。那么这个实现就非常简单了：

class CompositeCondition:
    # some code ...

    def build(self):
        return '(%s %s %s)' % (self.left.build(), self.op, self.right.build())

class PrimaryCondition:
    # some code ...
    
    def build(self):
        return '(%s %s %s)' % (self.column, self.op, str(self.value))

OK，基本完工，来试试：

where = ((col('x') == 99) & (col('y') >= 7)) | (col('z') == 9)
print where.build()

得到：

((((x = 99) and (y >= 7)) or (z = 9)) or (aa = 2))

组合

接下来就是将where语句组合到SQL中去了，在前面的SqlBuilder中的where函数这里，现在传入的参数类型就可能是CompositeCondition或PrimaryCondition了，好在这两种类型都有一个方法叫做build，直接返回其对应的条件语句串。那这就简单了，直接把condition参数先存起来，需要的时候就调用build来生成SQL语句中的WHERE部分即可。

class SqlBuilder:

    # some code ...

    def where(self, condition):
        self.condition = condition
        return self
        
    def build(self):
        if isinstance(self.columns, list):
            columns = ','.join(self.columns)
        else:
            columns = self.columns

        if self.condition is None:
            where = ''
        else:
            where = " WHERE %s" % self.condition.build()
        sql = "SELECT %s FROM %s" % (columns, self.table)
        sql += where
        return sql

如此，我们的SqlBuilder就基本完成了，来试一下：

sql = select(['a', 'b']).from_table('demo_table').where(
    ((col('x') == '99') & (col('y') >= 7)) | (col('z') == 9) | (col('aa') == 2)
).build()
print sql

得到语句：

SELECT a,b FROM demo_table WHERE ((((x = 99) and (y >= 7)) or (z = 9)) or (aa = 2))

OK。剩下的还有一些细节问题，比如针对字符串类型，在语句中需要加入引号；对于时间日期的类型，需要怎么样做一下转换才行；还想加入新的运算类型，也可以自己稍加修改然后加入。这些细节问题，解决起来都很容易了，无非就是对代码进行修修补补。

到此，这个极简的SqlBuilder就完成了。

文中若有疏漏，还望指教。

ChristopherChen

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
使用builder模式生成SQL语句

缘由在利用Python做数据库操作的时候，经常需要拼接各种SQL语句，各种字符串拼，代码写起来，那叫一个恶心。在字符串内，没有相应编译器（IDE或解释器）的语法检查功能，每次写都得全神贯注提心吊胆，写错了排查起来那也一样恶心。见识过各种ORM黑魔法似的骚操作，不需要写一行SQL代码，而是完全利用宿主语言表达查询意图，并由ORM内部处理为SQL语句，用起来顿时开心了许多。但是吧，本人
复制链接

扫一扫