sql语句生成

• getInsertAssist 生成 Insert 语句
• getUpdateAssist 生成 Update 语句

from pymysql.converters import escape_string
import json

def isJsonStr(jsonStr):
    try:
        if not isinstance(jsonStr, str):
            return False
        json.loads(jsonStr)
    except ValueError:
        return False
    return True


def escape_string(content):
    return escape_string(content)

def getInsertAssist(table, obj: dict):
    baseStr = "INSERT INTO `%s` (%s) VALUES (%s);"

    tableName = table
    keyStr = ""
    valStr = ""

    keyArray = list(obj.keys())
    valArray = list(obj.values())

    c = 0
    for item in keyArray:
        keyStr = keyStr + "`%s`" % item
        if c < len(keyArray) - 1:
            keyStr = keyStr + ", "
        c = c + 1

    c = 0

    for item in valArray:
        if item is None:
            valStr = valStr + "null"
        elif isinstance(item, int) or isinstance(item, float):
            valStr = valStr + "%s" % item
        elif isJsonStr(item):
            valStr = valStr + "'%s'" % item
        else:
            valStr = valStr + "'%s'" % item

        if c < len(valArray) - 1:
            valStr = valStr + ", "
        c = c + 1

    newStr = baseStr % (tableName, keyStr, valStr)
    return newStr


def getUpdateAssist(table, obj: dict, whereObj: dict):
    baseStr = "UPDATE `%s` SET %s WHERE %s;"

    tableName = table
    setValStr = ""
    whereStr = ""

    c = 0
    for k, v in obj.items():
        tmp_str = "`%s` = '%s'" % (k, v)
        if c < len(obj) - 1:
            tmp_str = tmp_str + ", "
        c = c + 1
        setValStr = setValStr + tmp_str

    c = 0
    for k, v in whereObj.items():
        if v is None:
            tmp_str = "`%s` = null"
        elif isinstance(v, int) or isinstance(v, float):
            tmp_str = "`%s` = %s" % (k, v)
        elif isJsonStr(v):
            valStr = valStr + "'%s'" % v
        else:
            tmp_str = "`%s` = '%s'" % (k, v)

        if c < len(whereObj) - 1:
            tmp_str = tmp_str + " And "
        c = c + 1
        whereStr = whereStr + tmp_str

    newStr = baseStr % (tableName, setValStr, whereStr)
    return newStr