async def storeBeanDateList(beans, mysql):
"""一次性存储多个bean对象 讲道理结构是被破坏的，但是可以吧所有数据库请求压缩为一次"""
conn, cur = await mysql.getDatabaseConnected()
try:
for bean in beans:
if isinstance(bean, BeanBase):
tableName = AsyncSqlHelper.getInsertTableName(bean)
items = bean.getItemKeyList()
valueDict = bean.getValueDict()
format_table = SqlUtils.getInsertTableFormatString(tableName, items)
format_values = SqlUtils.getInsertTableValuesString(items.__len__())
sql = SqlUtils.STR_SQL_INSERT_TABLE_UTILS.format(format_table, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
for item in items:
values = values + (valueDict.get(item, None),) # 元组相加
try:
await cur.execute(sql, values)
except Exception as e:
print(e)
except Exception as e:
print(e)
finally:
if cur:
await cur.close()
await mysql.pool.release(conn)
async def insertValuesIntoTable(self, tableName, items, valueDict, primaryKeys=None):
"""插入语句"""
conn, cur = await self.getCursor()
format_table = SqlUtils.getInsertTableFormatString(tableName, items)
format_values = SqlUtils.getInsertTableValuesString(items.__len__())
if configPraser.getPrintMode():
print(format_table)
print(format_values)
sql = SqlUtils.STR_SQL_INSERT_TABLE_UTILS.format(format_table, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
for item in items:
values = values + (valueDict.get(item, None),) # 元组相加
try:
await cur.execute(sql, values)
except Exception as e:
print(e)
finally:
if cur:
await cur.close()
await self.pool.release(conn)
async def updateBeanDateList(beans, mysql):
"""一次性更新多个bean对象 讲道理结构是被破坏的，但是可以吧所有数据库请求压缩为一次"""
conn, cur = await mysql.getDatabaseConnected()
try:
for bean in beans:
if isinstance(bean, BeanBase):
tableName = AsyncSqlHelper.getInsertTableName(bean)
valueDict = bean.getValueDict()
format_target = SqlUtils.getUpdateTableSetString(bean.getItemKeyList())
format_condition = SqlUtils.getQueryTableConditionString(bean.getIdentifyKeys())
sql = SqlUtils.STR_SQL_UPDATE_TABLE_UTILS.format(tableName, format_target, format_condition)
if configPraser.getPrintMode():
print(sql)
values = ()
for item in bean.getItemKeyList():
values = values + (valueDict.get(item, None),)
for item in bean.getIdentifyKeys():
values = values + (valueDict.get(item, None),) # 元组相加
try:
await cur.execute(sql, values)
except Exception as e:
print(e)
except Exception as e:
print(e)
finally:
if cur:
await cur.close()
await mysql.pool.release(conn)
def updateValuesFromTable(tableName, targets, targetsDict, conditions, conditionsDict):
"""修改某张表"""
conn = DataBaseOpenHelper.connect()
cursor = conn.cursor()
format_target = SqlUtils.getUpdateTableSetString(targets)
format_condition = SqlUtils.getQueryTableConditionString(conditions)
sql = SqlUtils.STR_SQL_UPDATE_TABLE_UTILS.format(tableName, format_target, format_condition)
if configPraser.getPrintMode():
print(sql)
values = ()
if targets is not None:
for item in targets:
values = values + (targetsDict.get(item, None),)
if conditions is not None:
for item in conditions:
values = values + (conditionsDict.get(item, None),) # 元组相加
try:
cursor.execute(sql, values)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
conn.close()
def insertValuesIntoTable(tableName, items, valueDict, primaryKeys=None):
"""插入语句"""
res = SqlExecuteHelper.queryValuesFromTable(tableName, primaryKeys, valueDict)
if res is not None and res.__len__() > 0:
if configPraser.getPrintMode():
print('数据重复插入失败')
return
conn = DataBaseOpenHelper.connect()
cursor = conn.cursor()
format_table = SqlUtils.getInsertTableFormatString(tableName, items)
format_values = SqlUtils.getInsertTableValuesString(items.__len__())
if configPraser.getPrintMode():
print(format_table)
print(format_values)
sql = SqlUtils.STR_SQL_INSERT_TABLE_UTILS.format(format_table, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
for item in items:
values = values + (valueDict.get(item, None),) # 元组相加
try:
cursor.execute(sql, values)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
conn.close()
def queryValuesFromTable(tableName, items, valueDict):
"""查询数据库"""
ret = []
conn = DataBaseOpenHelper.connect()
cursor = conn.cursor()
format_values = SqlUtils.getQueryTableConditionString(items)
sql = SqlUtils.STR_SQL_QUERY_TABLE_UTILS.format(tableName, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
if items is not None:
for item in items:
values = values + (valueDict.get(item, None),) # 元组相加
try:
cursor.execute(sql, values)
ret = cursor.fetchall()
if configPraser.getPrintMode():
print(ret)
except Exception as e:
print(e)
conn.close()
return ret
async def queryBeanData(beans, mysql, defineItems=None):
"""一次性查询多个bean对象 define 为[[key1,key2], [key3,key4] ...]
返回多个元组 [((),),((),())...]"""
conn, cur = await mysql.getDatabaseConnected()
resultBeans = []
try:
pos = 0
for bean in beans:
if isinstance(bean, BeanBase):
tableName = AsyncSqlHelper.getInsertTableName(bean)
items = defineItems[pos]
if items is None:
items = bean.getIdentifyKeys()
pos += 1
valueDict = bean.getValueDict()
format_values = SqlUtils.getQueryTableConditionString(
items)
sql = SqlUtils.STR_SQL_QUERY_TABLE_UTILS.format(
tableName, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
for item in items:
values = values + (valueDict.get(item, None), ) # 元组相加
try:
await cur.execute(sql, values)
r = await cur.fetchall()
resultBeans.append(r)
except Exception as e:
print(e)
resultBeans.append(None)
except Exception as e:
print(e)
finally:
if cur:
await cur.close()
await mysql.pool.release(conn)
return resultBeans
def deleteValuesFromTable(tableName, items, valueDict):
"""删除某张表"""
conn = DataBaseOpenHelper.connect()
cursor = conn.cursor()
format_values = SqlUtils.getQueryTableConditionString(items)
sql = SqlUtils.STR_SQL_DELETE_TABLE_UTILS.format(tableName, format_values)
if configPraser.getPrintMode():
print(sql)
values = ()
if items is not None:
for item in items:
values = values + (valueDict.get(item, None),) # 元组相加
try:
cursor.execute(sql, values)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
conn.close()