def delete(self, sql, params=None, cursor_mode=CursorMode.CURSOR_MODE): if sql.upper().startswith('DROP') | sql.upper().startswith( 'CREATE') | sql.upper().startswith('INSERT') | sql.upper( ).startswith('SELECT') | sql.upper().startswith('UPDATE'): raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR, message="Not support this operate") conn = None cursor = None try: delete_result = self.__execute(sql=sql, params=params, cursor_mode=cursor_mode) conn = delete_result["conn"] cursor = delete_result["cursor"] self.__commit_connection() return cursor.rowcount except DBError as e: raise e except Exception as e: reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2]) finally: if cursor: cursor.close() if conn: self.__close_connection()
def get_executor(db_config, pool_name="mysql-pool", pool_size=32, pool_reset_session=True): key = MysqlFactory.__get_pool_key(db_config) try: return MysqlFactory.__db_connection_pools[key] except KeyError: try: _db_config = db_config.copy() if 'password' in _db_config: _db_config['password'] = '******' logger.info("Init mysql pool info - name: " + pool_name + ", size: " + str(pool_size) + ", reset session: " + str(pool_reset_session) + ", dbconfig: " + str(_db_config)) __mysql_connection_pool = mysql.connector.pooling.MySQLConnectionPool( pool_name=pool_name, pool_size=pool_size, pool_reset_session=pool_reset_session, **db_config) MysqlFactory.__db_connection_pools[key] = MysqlExecutor( __mysql_connection_pool, True if Configer.get("db.show_sql") == "True" else False) except Exception as e: raise DBError(e) return MysqlFactory.__db_connection_pools[key]
def build_update_sql(self, models=[], columns=[], where="", params=None): if len(models) > 0: # 批量更新 sql = "" columns = models[0].get_attrs().keys() if len(columns) <= 0: raise DBError(key=DBErrorType.SQL_BUILD_ERROR, message="Not found any columns to update.") else: for model in models: params = [] set_columns = [] for column in columns: if column != self.primary_key: set_columns.append("`" + column + "`= %s") params.append(model.get_attr(column)) separator = ", " set_sql = separator.join(set_columns) primary_value = model.get_attr(self.primary_key) where = "`%s` = %s" % (self.primary_key, primary_value) params.append(primary_value) sql += "UPDATE " + self._assembly_from_sql( ) + " SET " + set_sql + (" WHERE " + where if where != "" else "") + ";" else: if len(columns) <= 0: raise DBError(key=DBErrorType.SQL_BUILD_ERROR, message="Not found any columns to update.") else: set_columns = [] for column in columns: if column != self.primary_key: set_columns.append("`" + column + "`= %s") separator = ", " set_sql = separator.join(set_columns) sql = "UPDATE " + self._assembly_from_sql() + " SET " + set_sql + ( " WHERE " + where if where != "" else "") return sql, params
def insert(self, sql, params=None, execute_mode=ExecuteMode.ONE_MODE, cursor_mode=CursorMode.CURSOR_MODE): if sql.upper().startswith('DROP') | sql.upper().startswith( 'CREATE') | sql.upper().startswith('SELECT') | sql.upper( ).startswith('UPDATE') | sql.upper().startswith('DELETE'): raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR, message="Not support this operate") conn = None cursor = None try: insert_result = self.__execute(sql=sql, params=params, execute_mode=execute_mode, cursor_mode=cursor_mode) conn = insert_result["conn"] cursor = insert_result["cursor"] rowcount = cursor.rowcount if execute_mode == ExecuteMode.ONE_MODE: result = cursor.lastrowid else: result = rowcount self.__commit_connection() return result except DBError as e: raise e except Exception as e: reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2]) finally: if cursor: cursor.close() if conn: self.__close_connection()
def select(self, sql, params=None, cursor_mode=CursorMode.CURSOR_MODE): if sql.upper().startswith('DROP') | sql.upper().startswith( 'CREATE') | sql.upper().startswith('INSERT') | sql.upper( ).startswith('UPDATE') | sql.upper().startswith('DELETE'): raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR, message="Not support this operate") conn = None cursor = None try: select_result = self.__execute(sql=sql, params=params, cursor_mode=cursor_mode) conn = select_result["conn"] cursor = select_result["cursor"] result = [] column_values = cursor.fetchall() if column_values is not None and len(column_values) > 0: column_names = [i[0] for i in cursor.description] for i, row in enumerate(column_values): tmp = {} for j, value in enumerate(row): tmp[column_names[j]] = value result.append(tmp) return result except DBError as e: raise e except Exception as e: reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2]) finally: if cursor: cursor.close() if conn: self.__close_connection()
def __get_connection(self): """ 获取一个连接 """ if self.__has_connection(): connection = self.__local_connection.mysql_connection else: connection = self.__mysql_connection_pool.get_connection() self.__local_connection.mysql_connection = connection if not connection.is_connected(): raise DBError(key=DBErrorType.NOT_CONNECT_ERROR, message="Connection is not connect.") if self.__has_transaction(): connection.autocommit = False else: connection.autocommit = True logger.debug('Connection autocommit: {}'.format(connection.autocommit)) return connection
def __execute(self, sql, params=None, execute_mode=ExecuteMode.ONE_MODE, cursor_mode=CursorMode.CURSOR_MODE): """ 通用sql执行工具 """ if self.__show_sql: if isinstance(params, list): log_params = params[:5] logger.info("Execute Sql: {}, params(top5): \n{}".format( sql, ', \n'.join( [str(param_tuple) for param_tuple in log_params]))) else: logger.info("Execute Sql: {}, params: {}".format(sql, params)) conn = self.__get_connection() if cursor_mode == CursorMode.CURSOR_MODE: cursor_class = mysql.connector.cursor.MySQLCursor elif cursor_mode == CursorMode.DICT_CURSOR_MODE: cursor_class = mysql.connector.cursor.MySQLCursorDict else: raise DBError(key=DBErrorType.CURSOR_MODE_ERROR, message="Cursor mode value is wrong.") cursor = conn.cursor(buffered=True, raw=None, prepared=True, cursor_class=cursor_class) if execute_mode == ExecuteMode.ONE_MODE: cursor.execute(sql, params) else: cursor.executemany(sql, params) return {"conn": conn, "cursor": cursor}