dynamically parameter passing in sql where query best practices


搞定了 select columns 的动态列查询,接着就该来搞定 where and or 的动态参数化查询了。

猛一想,where 这部分的参数化查询,如果我用 pymysql 的 cursor,或者用 SQLAlchemy 的 engine 或者 session 直接调用 execute 执行文本语句,也是可以给语句的 where 参数动态传参的呀?

不,我说的更进一步的:除了参数可以动态传入,我想要 where 语句也要动态改变,即动态条件查询。

可能要是没辙的话 大多数都跑去冒着 sql 注入的危险,启用文本格式化工具来写一堆条件来动态拼接语句了。

不说最后拼出来的对不对,开发效率、调试速度,总要顾及一下吧?

之前的动态列查询使用了 SQLAlchemy Core 里面 select 工具,通过可以定义单个 column 的办法动态处理得到供 select 使用的 columns,实现无论你要多少列,要什么不要什么,都可以通过列名称参数进行动态地处理得到,具体再多去写一些实际需求,应该就会熟练上手了。

而 where 的动态化,你基本也想到了,还是会在 select 这个工具下面来进行。

看官方的写法:

s = select([users, addresses]).where(users.c.id == addresses.c.user_id)

不过目前写后台 API 程序的话,基本上可能会这么写:

s = select([users, addresses]).where(users.c['id'] == addresses.c['user_id'])

然后再把这里的字符根据情况使用变量名替换掉。

上面的 where 只有一个条件,如果有多个条件,要用到连接符了,同时也有提供专门的连接词来处理:

from sqlalchemy.sql import and_, or_, not_
and_(
    users.c['name'].like('j%'),
    users.c['id'] == addresses.c['user_id'],
    or_(
        addresses.c['email_address'] == '[email protected]',
        addresses.c['email_address'] == '[email protected]'
    ),
    not_(users.c['id'] > 5)
)

相对应的三个连接符就是:&、|、~

不过目前来看,我会更喜欢连接词方式,为啥,因为要动态化呀,嗯?没看出来咋搞?这样:

clauses = []

clauses.append(users.c['name'].like('j%'))
clauses.append(users.c['id'] == addresses.c['user_id'])

s = select([users, addresses]).where(and_(*clauses))

重点就在这个星号 *,标准的 *args 赋值模式,对吧。这样你就可以自由地去操作 clauses 这个列表了,根据不同的情况,动态地往里面加任何可以加的查询条件,甚至嵌套几层 and_ , or_ , not_ 进去都是可以的。

好了,基本就这样了,更多细节,其实要自己真的去尝试写了才会有概念,这里仅是做个笔记。