def results(self, max_results=100, raise_if_limit_exceeded=True): '''return many rows''' results = [] max_results = min(max_results, ABSOLUTE_MAX_RESULTS) try: for cur in self._cursors: try: cur.execute(self._stmt) if raise_if_limit_exceeded: rows = cur.fetchmany(max_results + 1) if len(rows) > max_results: raise DbError(( 'Too many results, result set was limited to {}. ' 'Try setting max_results to a higher value.' ).format(max_results), operation=self._stmt) else: rows = cur.fetchmany(max_results) for row in rows: results.append(row._asdict()) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self._stmt), None, sys.exc_info()[2]
def __init__(self, host, port, database, user='******', password='', enabled=True, timeout=60): self.__stmt = None self.__enabled = enabled if self.__enabled: self.__cx_MsSql = _import_db_driver() port = (int(port) if port and str(port).isdigit() else DEFAULT_PORT) try: self.__conn = self.__cx_MsSql.connect('{}:{}'.format( host, port), user, password, database, timeout, as_dict=True) self.__cursor = self.__conn.cursor() except Exception, e: raise DbError(str(e), operation='Connect to {}:{}'.format( host, port)), None, sys.exc_info()[2]
def __init__(self, shards, user='******', password='', timeout=60000, shard=None): ''' Parameters ---------- shards: dict A dict of shard definitions where key is the shard's name and value is the host/database string. user: str password: str timeout: int Statement timeout in milliseconds. shard: str Optional shard name. If provided, the check will be run on only one shard matching given name. ''' if not shards: raise CheckError('SqlWrapper: No shards defined') if shard and not shards.get(shard): raise CheckError( 'SqlWrapper: Shard {} not found in shards definition'.format( shard)) self._cursors = [] self._stmt = None mdb = _import_db_driver() for shard_def in ([shards[shard]] if shard else shards.values()): m = CONNECTION_RE.match(shard_def) if not m: raise CheckError( 'Invalid shard connection: {}'.format(shard_def)) try: conn = mdb.connect( host=m.group('host'), user=user, passwd=password, db=m.group('dbname'), port=(int(m.group('port')) if int(m.group('port')) > 0 else DEFAULT_PORT), connect_timeout=timeout) except Exception, e: raise DbError(str(e), operation='Connect to {}'.format( shard_def)), None, sys.exc_info()[2] # TODO: Find a way to enforce readonly=True as it is done in postgres Wrapper # TODO: Do we need to set charset="utf8" and use_unicode=True in connection? conn.autocommit(True) self._cursors.append(conn.cursor(mdb.cursors.DictCursor))
def results(self): '''return many rows''' results = [] try: for cur in self._cursors: try: cur.execute(self._stmt) rows = cur.fetchmany(MAX_RESULTS) for row in rows: results.append(dict(row)) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self._stmt), None, sys.exc_info()[2]
def result(self): # return single row result, will result primitive value if only one column is selected result = {} try: if self.__enabled and self.__cx_MsSql: cur = self.__cursor try: cur.execute(self.__stmt) row = cur.fetchone() if row: result = row finally: cur.close() except Exception, e: raise DbError(str(e), operation=self.__stmt), None, sys.exc_info()[2]
def results(self): # return many rows results = [] try: if self.__enabled and self.__cx_MsSql: cur = self.__cursor try: cur.execute(self.__stmt) rows = cur.fetchmany(MAX_RESULTS) for row in rows: results.append(row) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self.__stmt), None, sys.exc_info()[2]
def result(self, agg=sum): '''return single row result, will result primitive value if only one column is selected''' result = {} try: for cur in self._cursors: try: cur.execute(self._stmt) row = cur.fetchone() if row: for k, v in row._asdict().items(): result[k] = result.get(k, []) result[k].append(v) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self._stmt), None, sys.exc_info()[2]
def result(self, agg=sum): '''return single row result, will result primitive value if only one column is selected''' result = {} try: if self._enabled and self.__cx_Oracle: cur = self.__cursor try: cur.execute(self._stmt) desc = [d[0] for d in cur.description] # Careful: col names come out all uppercase row = cur.fetchone() if row: result = dict(zip(desc, row)) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self._stmt), None, sys.exc_info()[2]
def results(self): '''return many rows''' results = [] try: if self._enabled and self.__cx_Oracle: cur = self.__cursor try: cur.execute(self._stmt) desc = [d[0] for d in cur.description] # Careful: col names come out all uppercase rows = cur.fetchmany(MAX_RESULTS) for row in rows: row = dict(zip(desc, row)) results.append(row) finally: cur.close() except Exception, e: raise DbError(str(e), operation=self._stmt), None, sys.exc_info()[2]
def __init__(self, host, port, sid, user='******', password='', enabled=True): self._stmt = None self._dsn_tns = None self._enabled = enabled self.__cx_Oracle = None self.__conn = None self.__cursor = None if not host: raise ConfigurationError('Oracle wrapper improperly configured. Valid host is required!') if self._enabled: self.__cx_Oracle = _import_db_driver() port = (int(port) if port and str(port).isdigit() else DEFAULT_PORT) try: self._dsn_tns = self.__cx_Oracle.makedsn(host, port, sid) self.__conn = self.__cx_Oracle.connect(user, password, self._dsn_tns) self.__cursor = self.__conn.cursor() except Exception, e: raise DbError(str(e), operation='Connect to dsn={}'.format(self._dsn_tns)), None, sys.exc_info()[2]
def __init__( self, shards, user='******', password='', timeout=60000, shard=None, created_by=None, check_id=None, ): ''' Parameters ---------- shards: dict A dict of shard definitions where key is the shard's name and value is the host/database string. user: str password: str timeout: int Statement timeout in milliseconds. shard: str Optional shard name. If provided, the check will be run on only one shard matching given name. created_by: str Optional user name. If provided, the check will first make sure that the user has permissions to access the requested database. It's optional because it's currently supported only in trial run. check_id: int The check definition ID in order to set PostgreSQL application name (easier tracking on server side). ''' if not shards: raise CheckError('SqlWrapper: No shards defined') if shard and not shards.get(shard): raise CheckError( 'SqlWrapper: Shard {} not found in shards definition'.format( shard)) self._cursors = [] self._stmt = None permissions = {} for shard_def in ([shards[shard]] if shard else shards.values()): m = CONNECTION_RE.match(shard_def) if not m: raise CheckError( 'Invalid shard connection: {}'.format(shard_def)) connection_str = ( "host='{host}' port='{port}' dbname='{dbname}' user='******' password='******' " "connect_timeout=5 options='-c statement_timeout={timeout}' " "application_name='ZMON Check {check_id} (created by {created_by})' " ).format( host=m.group('host'), port=int(m.group('port') or DEFAULT_PORT), dbname=m.group('dbname'), user=user, password=password, timeout=timeout, check_id=check_id, created_by=make_safe(created_by), ) try: conn = psycopg2.connect(connection_str) conn.set_session(readonly=True, autocommit=True) cursor = conn.cursor(cursor_factory=NamedTupleCursor) self._cursors.append(cursor) except Exception, e: raise DbError(str(e), operation='Connect to {}'.format( shard_def)), None, sys.exc_info()[2] try: if created_by: cursor.execute(PERMISSIONS_STMT, [created_by]) row = cursor.fetchone() permissions[shard_def] = ( row.can_login and REQUIRED_GROUP in row.member_of if row else False) except Exception, e: raise DbError( str(e), operation='Permission query'), None, sys.exc_info()[2]