python在使用sqlite时返回单个/多个记录时的是行信息,而我们希望能够返回字典信息。如下
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
def dic2InsertSql(dic, tblName):
sql_key = ', '.join(dic.keys())
sql_val = ''
for key in dic:
if type(dic[key]) is str:
sql_val = sql_val + '\'' + dic[key] + '\','
else:
sql_val = sql_val + str(dic[key]) + ' ,'
# 移除 sql_val最后一个 ','
return "insert into {} ({}) values ({}) ".format(tblName, sql_key, sql_val[:-1])
# 调用者需要自己补充 where之后的条件语句
def dic2UpdateSql(dic, tblName):
sql_key = ''
for key in dic:
if type(dic[key]) is str:
sql_key = sql_key + key + ' = \'' + dic[key] + '\','
else:
sql_key = sql_key + key + ' = ' + str(dic[key]) + ' ,'
# 移除 sql_key 最后一个 ','
return "update {} set {} where ".format(tblName, sql_key[:-1])
class SqliteBase:
def __init__(self, dbname):
self.conn = sqlite3.connect(dbname, check_same_thread=False)
# 设置返回的数据库信息中携带 表字段名
# dict_factory 一定要设置为静态函数,否则会导致变量引用计数多+1,
# 最后导致局部变量永远无法删除,这个bug没找到具体原因
self.conn.row_factory = dict_factory
def exec(self, sql):
"""
执行sql
返回错误
:param sql:
:return:
"""
ret = True
cursor = self.conn.cursor()
try:
cursor.execute(sql)
self.conn.commit()
except Exception as e:
log.logger.warning('sql {} , error: {}'.format(sql, e))
ret = False
finally:
pass
return ret