Python自动生成sql语句

发布时间：2019-08-17 08:48:18编辑：auto阅读（2059）

#!usr/bin/env python
# coding=utf-8

def auto_insert_sql(objs, table=None, charset='UTF-8'):
    """
    自动生成insert SQL语句，如果obj为对象列表，则根据第一个对象生成SQL语句。
    @objs 对象或对象列表
    @table 数据表名
    @charset 数据库编码
    @return (SQL语句，转换后的对象或对象列表)
    """
    is_batch = isinstance(objs, list)
    if not objs:
        return (None, []) if is_batch else (None, None)
    obj = objs[0] if is_batch else objs
    obj_li = objs if is_batch else [objs]
    # 解析表名
    if not table:
        table = obj.__class__.__name__
    # 解析字段 和 转换数据
    cols = []
    row_li = []
    if hasattr(obj, '__iter__'):  # dict
        # 解析字段
        for k in obj:
            cols.append(k)
        # 转换数据
        for obj in obj_li:
            row = {}
            for k in cols:
                v = obj[k]
                row[k] = v.encode(charset) if isinstance(v, unicode) else v
            row_li.append(row)
    elif hasattr(obj, '__dict__'):  # object
        for i, (k, v) in enumerate(obj.__dict__.items()):
            cols.append(k)
        for obj in obj_li:
            row = {}
            for k in cols:
                v = getattr(obj, k)
                row[k] = v.encode(charset) if isinstance(v, unicode) else v
            row_li.append(row)
    elif hasattr(obj, 'ListFields'):  # Google Protocol Buffer
        for field, v in obj.ListFields():
            cols.append(field.name)
        for obj in obj_li:
            row = {}
            for k in cols:
                v = getattr(obj, k) if obj.HasField(k) else None
                row[k] = v.encode(charset) if isinstance(v, unicode) else v
            row_li.append(row)
    else:
        raise Exception("couldn't parse object: %s" % obj)
    # 生成SQL语句
    if not cols:
        raise Exception("no colume found in object: %s" % obj)
    sql_prefix = ['insert into ', table, ' (']
    sql_suffix = [') values(']
    for i, k in enumerate(cols):
        sql_prefix.append(', %s' % k if i > 0 else k)
        sql_suffix.append(', :%s' % k if i > 0 else ':%s' % k)
    sql_suffix.append(')')
    sql_prefix.extend(sql_suffix)
    sql = ''.join(sql_prefix)
    # --------------------------------------------------
    return (sql, row_li) if is_batch else (sql, row_li[0])


class A(object):
    def __init__(self):
        self.c1 = 0
        self.c2 = 0
        self.c3 = 3


def main():
    # a = A()
    a = {'a': 1, 'b': 2}
    print auto_insert_sql([a], 'test_table')
if __name__ == '__main__':
    main()

关键字：