def count(self, table, params={}, join='AND'):
# 根据条件统计行数
try:
sql = 'SELECT COUNT(*) FROM `%s`' % table
if params:
where, whereValues = self.__contact_where(params)
sqlWhere = ' WHERE ' + where if where else ''
sql += sqlWhere
#sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
self.__display_Debug_IO(sql, tuple(whereValues)) #DEBUG
cursor.execute(sql, tuple(whereValues))
#cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone()
return result[0] if result else 0
#except:
# raise BaseError(707)
except Exception as err:
try:
raise BaseError(707, err._full_msg)
except:
raise BaseError(707)
def find(self, conditions, table=None, isRaise=True):
if not table: table = self.view
#1. 查询总数
#1.1 检查 conditions 是否包含 limit 分页条件
lstKeys = list(conditions.keys())
isLimit = False
for key in lstKeys:
if 'LIMIT' == key.upper():
isLimit = True
break
#1.2 存在分页，统计复合条件的总记录数
if isLimit:
try:
where = {'': '{{' + conditions['where'] + '}}'}
except:
where = {"{{1}}": 1}
intCount = self.db.count(table, where)
if intCount == 0 and isRaise:
raise BaseError(802) # 没有找到数据
#2 得到复合条件的记录
lstRowsData = self.db.getAllToList(table, conditions) # 查询结果以List的方式返回
#2. 错误处理
if (not lstRowsData) and isRaise:
raise BaseError(802) # 未找到数据
rows = {'struct': conditions['select'], 'rows': lstRowsData}
if isLimit: rows['count'] = intCount
return rows
def update(self,
table,
data,
params={},
join='AND',
commit=True,
lock=True):
# 更新数据
try:
fields, values = self.__contact_fields(data)
if params:
where, whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere = ' WHERE ' + where if where else ''
cursor = self.__getCursor()
if commit: self.begin()
if lock:
sqlSelect = "SELECT %s From %s %s for update" % (','.join(
tuple(list(params.keys()))), table, sqlWhere)
sqlSelect = sqlSelect % tuple(whereValues)
cursor.execute(sqlSelect) # 加行锁
#cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁
sqlUpdate = "UPDATE %s SET %s " % (table, fields) + sqlWhere
for index, val in enumerate(values):
if isinstance(val, str):
values[index] = "'" + val + "'"
if val == None:
values[index] = "null"
self.__display_Debug_IO(sqlUpdate, tuple(values)) #DEBUG
sqlUpdate = sqlUpdate % tuple(values)
cursor.execute(sqlUpdate)
#cursor.execute(sqlUpdate,tuple(values))
if commit: self.commit()
return cursor.rowcount
except Exception as err:
try:
raise BaseError(705, err._full_msg)
except:
raise BaseError(705, err.args)
def __query(self, table, criteria, all=False, isDict=True, join='AND'):
'''
table : 表名
criteria : 查询条件dict
all : 是否返回所有数据，默认为False只返回一条数据,当为真是返回所有数据
isDict : 返回格式是否为字典，默认为True ，即字典否则为数组
'''
try:
if all is not True:
criteria['limit'] = 1 # 只输出一条
sql, params = self.__contact_sql(table, criteria,
join) #拼sql及params
'''
# 当Where为多个查询条件时，拼查询条件 key 的 valuse 值
if 'where' in criteria and 'dict' in str(type(criteria['where'])) :
params = criteria['where']
#params = tuple(params.values())
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
params=tuple(whereValues)
else :
params = None
'''
#__contact_where(params,join='AND')
cursor = self.__getCursor()
self.__display_Debug_IO(sql, params) #DEBUG
#if self.DataName=="ORACLE":
#sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % ()
#pass
cursor.execute(sql, params if params else ())
rows = cursor.fetchall() if all else cursor.fetchone()
if isDict:
result = [dict(zip(cursor.column_names, row))
for row in rows] if all else dict(
zip(cursor.column_names, rows)) if rows else {}
else:
result = [row for row in rows] if all else rows if rows else []
return result
except Exception as err:
try:
raise BaseError(706, err._full_msg)
except:
raise BaseError(706)
def find(self, conditions, table=None, isRaise=True, encode=''):
# isRaise 出现错误是否立即抛出异常
# 0.2.x 版本： if not table : table=self.view
# 0.3.x 版本后，直接调用table而不是视图
if not table: table = self.table
#1. 查询总数
#1.1 检查 conditions 是否包含 limit 分页条件
lstKeys = list(conditions.keys())
isLimit = False
for key in lstKeys:
if 'LIMIT' == key.upper():
isLimit = True
break
#1.2 存在分页，统计复合条件的总记录数
if isLimit:
try:
where = {'': '{{' + conditions['where'] + '}}'}
except:
where = {"{{1}}": 1}
intCount = self.db.count(table, where)
if intCount == 0 and isRaise:
raise BaseError(802) # 没有找到数据
#2 得到复合条件的记录
lstRowsData = self.db.getAllToList(table, conditions) # 查询结果以List的方式返回
#2. 错误处理
if (not lstRowsData) and isRaise:
raise BaseError(802) # 未找到数据
#strcut = delXXX(conditions['select'])
rows = {
#'struct': strcut,
'struct': conditions['select'],
'rows': lstRowsData
}
if isLimit: rows['count'] = intCount
if encode.lower() == 'gbk':
rows = self.decodeToGBK(rows)
return rows
def __init__(self, config):
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(**config)
self.cnx = self.cur = None
except mysql.connector.Error as err:
# 这里需要记录操作日志
logging.debug(err.msg)
self.cnx = None
raise BaseError(701) # 与数据库连接异常
def open(self):
try:
self.__conn = self.pool.get_connection()
self.__cursor = self.__conn.cursor(buffered=True)
#self.__conn.autocommit=True
self.__conn.autocommit = False
self.cnx = self.__conn
self.cur = self.__cursor
except:
raise BaseError(702) # 无法获得连接池
def countBySql(self, sql, params={}, join='AND'):
# 自定义sql 统计影响行数
try:
cursor = self.__getCursor()
sql = self.__joinWhere(sql, params, join)
cursor.execute(sql, tuple(params.values()))
result = cursor.fetchone()
return result[0] if result else 0
except:
raise BaseError(707)
def update(self,
table,
data,
params={},
join='AND',
commit=True,
lock=True):
# 更新数据
try:
fields, values = self.__contact_fields(data)
if params:
where, whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere = ' WHERE ' + where if where else ''
cursor = self.__getCursor()
if commit: self.begin()
if lock:
sqlSelect = "SELECT %s From `%s` %s for update" % (','.join(
tuple(list(params.keys()))), table, sqlWhere)
cursor.execute(sqlSelect, tuple(whereValues)) # 加行锁
sqlUpdate = "UPDATE `%s` SET %s " % (table, fields) + sqlWhere
self.__display_Debug_IO(sqlUpdate, tuple(values)) #DEBUG
cursor.execute(sqlUpdate, tuple(values))
if commit: self.commit()
return cursor.rowcount
except Exception as err:
try:
raise BaseError(705, err._full_msg)
except:
raise BaseError(705, err.args)
def __init__(self, config):
self.debug = True if (DEBUG and Debug_Level >= 3) else False
if isinstance(config, DB):
self.DataName = config.DataName
self.pool = config.pool
self.cnx = self.cur = None
return
try:
self.DataName = config['datatype']
del config['datatype']
except:
self.DataName = 'POSTGRESQL'
if self.DataName == 'MYSQL':
try:
self.pool = mysql.connector.pooling.MySQLConnectionPool(
**config)
self.cnx = self.cur = None
except mysql.connector.Error as err:
# 这里需要记录操作日志
logging.debug(err.msg)
self.cnx = None
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'POSTGRESQL':
try:
self.pool = ThreadedConnectionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
elif self.DataName == 'ORACLE':
try:
if config['NLS_LANG']:
os.environ['NLS_LANG'] = config['NLS_LANG']
del config['NLS_LANG']
except:
pass
try:
self.pool = cx_Oracle.SessionPool(**config)
except:
raise BaseError(701) # 与数据库连接异常
#恢复删除的配置
config['datatype'] = self.DataName
def insert(self, table, data, commit=True):
# 新增一条记录
try:
'''
从data中分离含用SQL函数的字字段到funData字典中,
不含SQL函数的字段到newData
'''
funData, newData = self.__split_expression(data)
funFields = ''
funValues = ''
# 拼不含SQL函数的字段及值
fields = ','.join('`' + k + '`' for k in newData.keys())
values = ','.join(("%s", ) * len(newData))
# 拼含SQL函数的字段及值
if funData:
funFields = ','.join('`' + k + '`' for k in funData.keys())
funValues = ','.join(v for v in funData.values())
# 合并所有字段及值
fields += ',' + funFields if funFields else ''
values += ',' + funValues if funValues else ''
sql = 'INSERT INTO `%s` (%s) VALUES (%s)' % (table, fields, values)
cursor = self.__getCursor()
self.__display_Debug_IO(sql, tuple(newData.values())) #DEBUG
cursor.execute(sql, tuple(newData.values()))
if commit: self.commit()
insert_id = cursor.lastrowid
return insert_id
except Exception as err:
try:
raise BaseError(705, err._full_msg)
except:
raise BaseError(705, err.args)
def delete(self, ids, table=None, key='id', _commit=False, delete=True):
# 根据 ids 删除数据库表的数据, 多个id
if not table: table = self.table
try:
if isinstance(ids, str) and (',' in ids):
#ids="'"+"'".join(ids)+"'"
ids_list = ids.split(',')
for i, v in enumerate(ids_list):
ids_list[i] = "'%s'" % v
ids = ','.join(ids_list)
else:
ids = "'%s'" % ids
except:
pass
if delete:
rw = self.db.deleteByPk(table,
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
else:
if self.db.DataName == 'MYSQL':
rw = self.db.updateByPk(table, {
'isDelete': 'Y',
'updateTime': '{{now()}}'
},
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
elif self.db.DataName == 'ORALCE':
rw = self.db.updateByPk(table, {
'iS_DELETE': 'Y',
'update_date_Time': '{{sysdate}}'
},
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
elif self.db.DataName == 'POSTGRESQL':
rw = self.db.updateByPk(table, {
'is_Delete': 'Y',
'update_date_Time': '{{now()}}'
},
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
#rw=db.updateByPk(self.table,{'isDelete':'Y','updateTime':'{{now()}}'},id=rid)
if rw < 0: raise BaseError(705) # SQL执行错误
return rw
def deleteByAttr(self, table, params={}, join='AND'):
# 删除数据
try:
fields = ','.join('`' + k + '`=%s' for k in params.keys())
sql = "DELETE FROM `%s` " % table
sql = self.__joinWhere(sql, params, join)
cursor = self.__getCursor()
cursor.execute(sql, tuple(params.values()))
self.__conn.commit()
return cursor.rowcount
#except:
# raise BaseError(704)
except Exception as err:
raise BaseError(704, err._full_msg)
def deleteByCond(self, table, cond):
# 删除数据
try:
sql = "DELETE FROM %s where %s" % (table, cond)
cursor = self.__getCursor()
self.__display_Debug_IO(sql, ()) #DEBUG
cursor.execute(sql)
#self.__conn.commit() #
return cursor.rowcount
#except:
# raise BaseError(704)
except Exception as err:
raise BaseError(704, err._full_msg)
def update(self, data, ids, table=None, key='id', _commit=False):
if not table: table = self.table
# 根据 ids 数据更新到数据库,多个 id
#rw=self.db.updateByPk(table,data,rid,commit=_commit)
try:
if isinstance(ids, str) and (',' in ids):
ids = "'" + "'".join(ids) + "'"
except:
pass
rw = self.db.updateByPk(table,
data,
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
if rw < 0: raise BaseError(705) # SQL执行错误
return rw
def delete(self, ids, table=None, key='id', _commit=False):
# 根据 ids 删除数据库表的数据,多个 id
if not table: table = self.table
try:
if isinstance(ids, str) and (',' in ids):
ids = "'" + "'".join(ids) + "'"
except:
pass
rw = self.db.updateByPk(table, {
'isDelete': 'Y',
'updateTime': '{{now()}}'
},
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
#rw=db.updateByPk(self.table,{'isDelete':'Y','updateTime':'{{now()}}'},id=rid)
if rw < 0: raise BaseError(705) # SQL执行错误
return rw
def findBySql(self, sql, params={}, limit=0, join='AND', lock=False):
"""
自定义sql语句查找
limit = 是否需要返回多少行
params = dict(field=value)
join = 'AND | OR'
"""
try:
cursor = self.__getCursor()
sql = self.__joinWhere(sql, params, join)
cursor.execute(sql, tuple(params.values()))
rows = cursor.fetchmany(
size=limit) if limit > 0 else cursor.fetchall()
result = [dict(zip(cursor.column_names, row))
for row in rows] if rows else None
return result
except:
raise BaseError(706)
def update(self, data, ids, table=None, key='id', _commit=False):
if not table: table = self.table
# 根据 ids 数据更新到数据库,多个 id
#rw=self.db.updateByPk(table,data,rid,commit=_commit)
try:
if isinstance(ids, str) and (',' in ids):
#ids="'"+"'".join(ids)+"'"
ids_list = ids.split(',')
for i, v in enumerate(ids_list):
ids_list[i] = "'%s'" % v
ids = ','.join(ids_list)
else:
ids = "'%s'" % ids
except:
pass
rw = self.db.updateByPk(table,
data,
"{{ in (%s)}}" % (ids),
pk=key,
commit=_commit)
if rw < 0: raise BaseError(705) # SQL执行错误
return rw
def open(self, auto=False):
try:
DB.connections += 1
# print("===================================db.open, " + str(DB.connections))
if self.DataName == 'ORACLE':
self.__conn = self.pool.acquire()
self.__cursor = self.__conn.cursor()
elif self.DataName == 'POSTGRESQL':
self.__conn = self.pool.getconn()
self.__cursor = self.__conn.cursor()
else: # 默认为Mysql
self.__conn = self.pool.get_connection()
self.__cursor = self.__conn.cursor(buffered=True)
#self.__conn.autocommit=True
self.__conn.autocommit = auto
self.cnx = self.__conn
self.cur = self.__cursor
except:
raise BaseError(702) # 无法获得连接池
def add(self, data, table=None, _commit=False):
if not table: table = self.table
#加入到数据库
rid = self.db.insert(table, data, commit=_commit)
if rid < 1: raise BaseError(703) # SQL执行错误
return rid
def remove(self, ids, table=None, key='id', _commit=False, delete=False):
rw = self.delete(ids, table, key, _commit, delete)
if rw < 0: raise BaseError(705) # SQL执行错误
return rw
def insert(self, table, data, commit=True):
# 新增一条记录
try:
'''
从data中分离含用SQL函数的字字段到funData字典中,
不含SQL函数的字段到newData
'''
funData, newData = self.__split_expression(data)
funFields = ''
funValues = ''
# 拼不含SQL函数的字段及值
fields = ', '.join(k for k in newData.keys())
values = ', '.join(("%s", ) * len(newData))
# 拼含SQL函数的字段及值
if funData:
funFields = ','.join(k for k in funData.keys())
funValues = ','.join(v for v in funData.values())
# 合并所有字段及值
fields += ', ' + funFields if funFields else ''
values += ', ' + funValues if funValues else ''
sql = 'INSERT INTO %s (%s) VALUES (%s)' % (table, fields, values)
cursor = self.__getCursor()
for (k, v) in newData.items():
try:
if isinstance(v, str):
newData[k] = "'%s'" % (v, )
if v == None:
newData[k] = "null"
except:
pass
self.__display_Debug_IO(sql, tuple(newData.values())) #DEBUG
sql = sql % tuple(newData.values())
if self.DataName == 'POSTGRESQL':
sql += ' RETURNING id'
cursor.execute(sql)
#if self.DataName=='ORACLE':
#sql= sql % tuple(newData.values())
#cursor.execute(sql)
#else :
#cursor.execute(sql,tuple(newData.values()))
if self.DataName == 'ORACLE':
# 1. commit 一定要为假
# 2. Oracle Sequence 的命名规范为： [用户名.]SEQ_表名_ID
# 3. 每张主表都应该有ID
t_list = table.split('.')
if len(t_list) > 1:
SEQ_Name = t_list[0] + '.SEQ_' + t_list[1] + '_ID'
else:
SEQ_Name = 'SEQ_' + t_list[0] + '_ID'
cursor.execute('SELECT %s.CURRVAL FROM dual' %
SEQ_Name.upper())
result = cursor.fetchone()
insert_id = result[0] if result else 0
#insert_id=cursor.rowcount
elif self.DataName == 'MYSQL':
insert_id = cursor.lastrowid
elif self.DataName == 'POSTGRESQL':
item = cursor.fetchone()
insert_id = item[0]
if commit: self.commit()
return insert_id
except Exception as err:
try:
raise BaseError(705, err._full_msg)
except:
raise BaseError(705, err.args)
