python拼接sql语句

python拼接生成sql语句

小工具,适用自动生成简单sql的场景

def where_condition(whereInfo:dict):
    '''
    拼接where条件
    @whereInfo where条件
    '''
    sql = ""
    whereConditionList = whereInfo.keys()
    if(len(whereConditionList)>0):
        sql = " where"
        for column in whereConditionList:
            sql += " "+column+"=\""+whereInfo[column]+"\""
            sql += " and"
        sql=sql[:-3]
    return sql


def gen_insert_sql(tableName, columnInfo:dict):
    '''
    生成insert的sql语句
    @tableName，插入记录的表名
    @columnInfo,插入的数据，字典
    '''
    #字段列表
    sequnceList = columnInfo.keys()
    sql = 'insert into %s (' % tableName
    for column in sequnceList:
        sql += "'"+column+"',"
    sql = sql[:-1]
    sql += ") values ("
    for column in sequnceList:
        sql += "\""+columnInfo[column]+"\","
    sql = sql[:-1]+");"
    return sql

def gen_update_sql(tableName,updateInfo:dict,whereInfo:dict):
    '''
    生成update的sql语句
    @updateInfo，更新的记录信息
    @whereInfo,条件信息
    '''
    sequnceList = updateInfo.keys()
    sql="update %s set " % tableName
    for column in sequnceList:
        sql+=column+"=\""+str(updateInfo[column])+"\","
    sql = sql[:-1]
    sql += where_condition(whereInfo)
    sql += ";"
    return sql

#生成查询sql
def gen_single_select_sql(tableName,columnList:list,whereInfo:dict):
    '''
    生成select的sql语句
    @tableName 查询的表名
    @columnList 查询的列名集合
    @whereInfo,条件信息
    '''
    sql="select "
    if(len(columnList)>0):
        sql += ','.join(str(column) for column in columnList)
    else:
        sql += "*"
    sql += " from %s " % tableName
    sql += where_condition(whereInfo)
    sql += ";"
    return sql

def gen_delete_sql(tableName,whereInfo:dict):
    '''
    生成select的sql语句
    @tableName 查询的表名
    @columnList 查询的列名集合
    @whereInfo,条件信息
    '''
    sql = "delete %s " % tableName
    sql += where_condition(whereInfo)
    sql += ";"
    return sql