python insert动态sql

python insert动态sql

同事已经封装好的持久化工具，无法正却处理value为None时的插入，所以只能自己拼接出原生sql，记录如下
value正常拼接后，后续可根据需求，动态传入table_name,columns等字段

# -*- coding:utf-8 -*-

base_sql = """INSERT INTO table_name
( ip, user_name, pass_word, organize_name, organize_code, update_time, is_enable)
VALUES {values};
"""

def sing(base_sql, params):
    params = tuple(['null' if param is None else param for param in params])

    format = ",".join(["'%s'"] * len(params))
    format = "({})".format(format % params)

    base_sql = base_sql.format(values=format)
    return base_sql

def multi(base_sql,datas):
    multiParams =[]
    for params in datas:
        params = tuple(['null' if param is None else param for param in params])
        format = ",".join(["'%s'"] * len(params))

        format = "({})".format(format % params)
        multiParams.append(format)
    return base_sql.format(values = ",".join(multiParams))

if __name__ == '__main__':
    import sys

    reload(sys)
    sys.setdefaultencoding('utf-8')
    params = [
        ['127.0.0.1', 'admin', 'ct@123', 'ct', None, 1595670950, True],
        ['127.0.0.1', 'admin', 'ct@123', u'中文', None, 1595670950, True]
    ]
    base_sql = multi(base_sql, params)
    print base_sql

    print sing(base_sql, ['127.0.0.1', 'admin', 'ct@123', 'ct', None, 1595670950, True])

处理结果

'127.0.0.1','admin','ct@123','ct','null','1595670950','True'
'127.0.0.1','admin','ct@123','中文','null','1595670950','True'
INSERT INTO table_name
( ip, user_name, pass_word, organize_name, organize_code, update_time, is_enable)
VALUES ('127.0.0.1','admin','ct@123','ct','null','1595670950','True'),('127.0.0.1','admin','ct@123','中文','null','1595670950','True');

INSERT INTO table_name
( ip, user_name, pass_word, organize_name, organize_code, update_time, is_enable)
VALUES ('127.0.0.1','admin','ct@123','ct','null','1595670950','True'),('127.0.0.1','admin','ct@123','中文','null','1595670950','True');