Python自动生成sql语句
#!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()
相关文章