def insert(cls, data, truncate=None, ignore=False, replace=False):
"""
插入数据
:param truncate: int 分段插入，每次插入数量
:param data: dict/list 要插入的数据
:param ignore: bool 启用 'INSERT IGNORE INTO'
:param replace: bool 启用 'REPLACE INTO'
:return: int 插入成功条数
"""
table = cls.get_table()
if isinstance(data, list):
lst = []
for d in data:
d = cls._process_method(d, "set_insert_")
lst.append(d)
data = lst
elif isinstance(data, dict):
data = cls._process_method(data, "set_insert_")
result = table.insert(data, truncate, ignore, replace).execute()
logger.debug("{} insert result: {}".format(cls.__name__, result))
return result
def delete(cls, where):
"""
删除数据
:param where: str 删除条件
:return: int 删除条数
"""
table = cls.get_table()
result = table.delete().where(where).execute()
logger.debug("{} delete result: {}".format(cls.__name__, result))
return result
def update(cls, data, where):
"""
更新数据
:param data: dict 数据字典
:param where: str 更新条件
:return: int 更新成功的条数
"""
table = cls.get_table()
data = cls._process_method(data, "set_update_")
result = table.update(data).where(where).execute()
logger.debug("{} update result: {}".format(cls.__name__, result))
return result
def query(self):
"""
select 需要调用此方法才会有查询结果
:return: generator(Row)/ generator(dict)
"""
sql = self.sql_builder()
start = time.time()
cursor = self._database.execute_sql(sql)
self._clear()
result = cursor.fetchall()
end = time.time()
logger.debug("query time: {:.3f}s".format(end - start))
return Records(cursor.keys(), result, cursor.rowcount)
def sql_builder(self):
"""
sql条件
1、不可以select *
2、不可以不写where条件, 如果需要删除、更新所有, 则 1=1
3、不可以不写limit
:return: str
"""
sql = " ".join(self._sql)
logger.debug("SQL: {}".format(sql))
if not sql:
raise ThinkException("SQL can not None")
# 安全校验
if "UPDATE" in sql and "WHERE" not in sql:
raise ThinkException("ThinkDatabase if update must have where")
if "DELETE" in sql and "WHERE" not in sql:
raise ThinkException("ThinkDatabase if delete must have where")
return sql