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)