def mock_helper(command, *args, **kwargs): if '_get_schema_columns' in command: # Creating exception exactly how SQLAlchemy does raise DBAPIError.instance( ''' SELECT /* sqlalchemy:_get_schema_columns */ ic.table_name, ic.column_name, ic.data_type, ic.character_maximum_length, ic.numeric_precision, ic.numeric_scale, ic.is_nullable, ic.column_default, ic.is_identity, ic.comment FROM information_schema.columns ic WHERE ic.table_schema='schema_name' ORDER BY ic.ordinal_position''', {'table_schema': 'TESTSCHEMA'}, ProgrammingError("Information schema query returned too much data. Please repeat query with more " "selective predicates.", 90030), Error, hide_parameters=False, connection_invalidated=False, dialect=SnowflakeDialect(), ismulti=None ) else: return original_execute(command, *args, **kwargs)
def run(self) -> None: self.validate() try: uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) if database is not None: database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) with closing(engine.raw_connection()) as conn: if not engine.dialect.do_ping(conn): raise DBAPIError(None, None, None) except DBSecurityException as ex: logger.warning(ex) raise DatabaseSecurityUnsafeError()
def recuperer_oeuvres(reponses_correctes_completes=True): """ Récupère les films et séries de la base de données et optionnellement ceux non renseignés, c'est-à-dire, ne disposant pas d'une réponse à chaque question False si l'on souhaite uniquement récupérer les oeuvres à réponses incomplètes :return: itérateur d'objet Oeuvre """ try: # Récupère toutes les oeuvres if reponses_correctes_completes: oeuvres = Oeuvre.query.all() # Récupère les oeuvres avec des réponses incomplètes else: # Remarque : La comparaison de reponse correcte avec None n'est pas une erreur mais # la façon correcte de comparer pour les requêtes SQL fabriquées avec SQLAlchemy oeuvres = Oeuvre.query.join(Reponse, Oeuvre.id == Reponse.id_oeuvre). \ filter(Reponse.reponse_correcte == None).all() except (DBAPIError, SQLAlchemyError) as e: gerer_exception(e) raise DBAPIError("Aucune oeuvre récupérée", e.params, e.orig) else: return oeuvres finally: pass
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) if database is not None: database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) with closing(engine.raw_connection()) as conn: if not engine.dialect.do_ping(conn): raise DBAPIError(None, None, None) except (NoSuchModuleError, ModuleNotFoundError): driver_name = make_url(uri).drivername raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format(driver_name), ) except DBAPIError: raise DatabaseTestConnectionFailedError() except SupersetSecurityException as ex: raise DatabaseSecurityUnsafeError(message=str(ex)) except Exception: raise DatabaseTestConnectionUnexpectedError()
def detail_view(request): try: entry_id = request.matchdict['id'] except IndexError: return HTTPNotFound() try: query = request.dbsession.query(Entry) entry_detail = query.filter(Entry.id == entry_id).first() except DBAPIError: return DBAPIError(DB_ERR_MSG, content_type='text/plain', status=500) res = requests.get('https://pixabay.com/api?key={}&q={}'.format( API_KEY, entry_detail.title.split(' ')[0])) try: url = res.json()['hits'][0]['webformatURL'] except (KeyError, IndexError): url = 'https://via.placeholder.com/300x300' return { "entry": entry_detail, "img": url, }
def test_database_ping__raises_exception_on_failure(db: Database): mocked_scalar = mock.Mock(side_effect=DBAPIError(None, None, None)) mocked_engine = create_engine_mock(scalar=mocked_scalar) with mock_db(db, engine=mocked_engine): with pytest.raises(DBAPIError): db.ping()
def test_db_check_dbapi_error(mocker, db): exception = DBAPIError.instance("", [], Exception(), Exception) engine_connect = mocker.patch.object(db.engine, "connect") engine_connect.side_effect = exception errors = checks.check_database_connected(db) assert len(errors) == 1 assert errors[0].id == health.ERROR_DB_API_EXCEPTION
def test_database_ping__retries_on_invalidated_connection(filedb: Database): mocked_scalar = mock.Mock(side_effect=[ DBAPIError(None, None, None, connection_invalidated=True), 1 ]) mocked_engine = create_engine_mock(scalar=mocked_scalar) with mock_db(filedb, engine=mocked_engine): assert filedb.ping() is True
def test_shall_return_503_on_dead_db(self): with patch('sqlalchemy.orm.session.Session.execute') as mock_exec: mock_exec.side_effect = DBAPIError('Boom', 'mock', 'mock') response = self.app.get( '/v1/forward', expect_errors=True ) self.assertEqual(response.status, HTTP_SERVICE_UNAVAILABLE)
def entries_view(request): try: query = request.dbsession.query(Entry) all_entries = query.all() except DBAPIError: return DBAPIError(DB_ERR_MSG, content_type='text/plain', status=500) return {'entries': all_entries}
def test_dbError_returnsUnsuccess(self, mocked_DBSession): mocked_DBSession.add.side_effect = DBAPIError(1, 2, 3, 4) request = testing.DummyRequest(post=self.body) response = recieve_message(request) expected = {'payload': {'success': False, 'error': 'Database error'}} self.assertEquals(response, expected)
async def test_async_database_ping__raises_exception_on_failure( async_db: AsyncDatabase): mocked_scalar = mock.AsyncMock(side_effect=DBAPIError(None, None, None)) mocked_engine = create_async_engine_mock(scalar=mocked_scalar) with mock_async_db(async_db, engine=mocked_engine): with pytest.raises(DBAPIError): await async_db.ping()
def test_read_from_database_api_error(self): connection = MockConnection() connection.execute = MagicMock() connection.execute.side_effect = DBAPIError("", "", "") with self.assertRaises(GOBException): read_from_database( connection, ["SELECT something FROM something WHERE something=true"])
async def test_async_database_ping__raises_when_invalidated_connection_retry_fails( async_db: AsyncDatabase, ): mocked_scalar = mock.AsyncMock( side_effect=DBAPIError(None, None, None, connection_invalidated=True)) mocked_engine = create_async_engine_mock(scalar=mocked_scalar) with mock_async_db(async_db, engine=mocked_engine): with pytest.raises(DBAPIError): await async_db.ping()
async def test_async_database_ping__retries_on_invalidated_connection( async_filedb: AsyncDatabase): mocked_scalar = mock.AsyncMock(side_effect=[ DBAPIError(None, None, None, connection_invalidated=True), 1 ]) mocked_engine = create_async_engine_mock(scalar=mocked_scalar) with mock_async_db(async_filedb, engine=mocked_engine): assert await async_filedb.ping() is True
def test_database_ping__raises_when_invalidated_connection_retry_fails( db: Database): mocked_scalar = mock.Mock( side_effect=DBAPIError(None, None, None, connection_invalidated=True)) mocked_engine = create_engine_mock(scalar=mocked_scalar) with mock_db(db, engine=mocked_engine): with pytest.raises(DBAPIError): db.ping()
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) with closing(engine.raw_connection()) as conn: if not engine.dialect.do_ping(conn): raise DBAPIError(None, None, None) # Log succesful connection test with engine event_logger.log_with_context( action="test_connection_success", engine=database.db_engine_spec.__name__, ) except (NoSuchModuleError, ModuleNotFoundError) as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format( database.db_engine_spec.__name__), ) except DBAPIError as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # check if we have connectivity to the host, and if the port is open self._diagnose(uri) raise DatabaseTestConnectionFailedError() except SupersetSecurityException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseSecurityUnsafeError(message=str(ex)) except Exception as ex: # pylint: disable=broad-except event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionUnexpectedError(str(ex))
def get_portfolio_view(request): """Load portfolio from database and display it.""" try: query = request.dbsession.query(Stock) user_stocks = query.filter(Junction.account_id == request.authenticated_userid, Junction.stock_id == Stock.symbol) except DBAPIError: raise DBAPIError(DB_ERR_MSG, content_type='text/plain', status=500) return{'stocks': user_stocks}
def test_sqlalchemy_reraise_exception(self, mock_create_engine): mock_create_engine.side_effect = DBAPIError("", "", "") config = { 'username': '******', 'database': 'somedatabase', 'url': 'connection_url', 'name': 'someconfig', } with self.assertRaises(GOBException): connect_to_database(config)
def test_dump_data_throws_error_on_exec_failure( self, read_sql: MagicMock, boston_sqldataset: Type[SQLDataset], test_engine: Engine, ): read_sql.side_effect = DBAPIError("test", "test", "test") with pytest.raises(DBAPIError): boston_sqldataset(test_engine, "")._dump_data() read_sql.assert_called_once()
def get_user_id(email): """Get user ID by email. Argument: email (str) : the email of the user. """ try: user = session.query(User).filter_by(email=email).one() return user.id except: DBAPIError('Error!', email, get_user_id, code=None) return None
def portfolio_view(request): """ Returns portfolio view with data from postgres """ try: query = request.dbsession.query(Stock) user_entries = query.filter(Stock.account_id == request.authenticated_userid) except DBAPIError: return DBAPIError(DB_ERR_MSG, content_type='text/plain', status=500) return {'stocks': all_entries}
def get_jobs(request): if request.method == 'POST': try: query = request.dbsession.query(Keyword) keyword_query = query.filter(Association.user_id == request.authenticated_userid, Association.keyword_id == Keyword.keyword).all() keywords = [keyword.keyword for keyword in keyword_query] except DBAPIError: raise DBAPIError(DB_ERR_MSG, content_type='text/plain', status=500) try: city = request.POST['city'] except KeyError: return HTTPBadRequest() url_template = 'https://www.indeed.com/jobs?q={}&l={}' max_results = 30 df = pd.DataFrame(columns=['location', 'company', 'job_title', 'salary', 'job_link']) requests.packages.urllib3.disable_warnings() for keyword in keywords: for start in range(0, max_results): url = url_template.format(keyword, city) http = urllib3.PoolManager() response = http.request('GET', url) soups = BeautifulSoup(response.data.decode('utf-8'), 'html.parser') for b in soups.find_all('div', attrs={'class': ' row result'}): location = b.find('span', attrs={'class': 'location'}).text job_title = b.find('a', attrs={'data-tn-element': 'jobTitle'}).text base_url = 'http://www.indeed.com' href = b.find('a').get('href') job_link = f'{base_url}{href}' try: company = b.find('span', attrs={'class': 'company'}).text except AttributeError: company = 'Not Listed' try: salary = b.find('span', attrs={'class': 'no-wrap'}).text except AttributeError: salary = 'Not Listed' df = df.append({'location': location, 'company': company, 'job_title': job_title, 'salary': salary, 'job_link': job_link}, ignore_index=True) df.company.replace(regex=True,inplace=True,to_replace='\n',value='') df.salary.replace(regex=True,inplace=True,to_replace='\n',value='') output = df.head(30) output.to_csv('results.csv', index=False) results = [] with open('./results.csv') as infile: data = csv.DictReader(infile) for row in data: results.append(row) return {'data': results}
def recuperer_questions(): """ Récupère les questions présentes dans la base de données :return: itérateur d'objet Question """ try: # Récupère toutes les questions questions = Question.query.all() except (DBAPIError, SQLAlchemyError) as e: gerer_exception(e) raise DBAPIError("Aucune question récupérée", e.params, e.orig) else: return questions
def _read_only_connect(dialect, connection_info): """ Creator function invoked for creating read only connections Note that the parameters are first created using a partial. SqlAlchemy invokes this without parameters. Every time a new connection is created, we query plusmoin for the current list of servers, and serve these in a round robin fashion. @param dialect: The SqlAlchemy Dialect class that represents our database @param connection_info: dict defining host, port, user, password and database. @returns: A dbapi object """ global _last_host try: plusmoin = _plusmoin_status() if plusmoin: if len(plusmoin['clusters']) == 0: raise Exception('Plusmoin advertises no available servers') available = list(plusmoin['clusters'][0]['slaves']) if plusmoin['clusters'][0]['has_master']: available.append(plusmoin['clusters'][0]['master']) if len(available) == 0: raise Exception('Plusmoin advertises no available servers') try: p = available.index(_last_host) host = available[(p+1) % len(available)] except ValueError: host = available[0] _last_host = host else: host = connection_info logger = logging.getLogger('db_load_balancing') logger.debug("Read only connection using %s", str(host)) return dialect.connect( user=connection_info['user'], password=connection_info['password'], database=connection_info['database'], host=host['host'], port=host['port'] ) except Exception as e: import sys raise DBAPIError.instance( None, None, e, dialect.dbapi.Error, connection_invalidated= dialect.is_disconnect(e, None, None)), \ None, sys.exc_info()[2]
def test_get_invalid_user(self): # Setup our dummy request before using it request = testing.DummyRequest() request.matchdict['user_id'] = '12' request.dbsession = mock(sqlalchemy.orm.session.Session) mock_query = query.Query([]) when(request.dbsession).query(User).thenReturn(mock_query) when(mock_query).filter_by(id=ANY).thenReturn(mock_query) when(mock_query).first().thenRaise( DBAPIError(statement='', params=[], orig='')) view_being_tested = ReferralView(request) response = view_being_tested.get_user() self.assertEqual(response.status_code, 400)
def recuperer_question_proposee(texte): """ Récupère une question proposée à partir de son texte :param texte: question (str) :return: question recherchée (PropositionQuestion) """ try: # Récupère une question proposée question_proposee = PropositionQuestion.query.filter_by( texte=texte).first() except (DBAPIError, SQLAlchemyError) as e: gerer_exception(e) raise DBAPIError("Aucune reponse récupérée", e.params, e.orig) else: return question_proposee
def recuperer_reponse(id_oeuvre, id_question): """ Récupère une unique réponse se trouvant dans la base de données :param id_question: l'id de la question de la réponse (int) :param id_oeuvre: l'id de l'oeuvre de la réponse (int) :return: la réponse (Reponse) """ try: # Récupère une réponse reponse = Reponse.query.filter_by(id_oeuvre=id_oeuvre, id_question=id_question).first() except (DBAPIError, SQLAlchemyError) as e: gerer_exception(e) raise DBAPIError("Aucune reponse récupérée", e.params, e.orig) else: return reponse
def test_test_connection_failed_invalid_hostname( self, mock_event_logger, mock_build_db ): """ Database API: Test test connection failed due to invalid hostname """ msg = 'psql: error: could not translate host name "locahost" to address: nodename nor servname provided, or not known' mock_build_db.return_value.set_sqlalchemy_uri.side_effect = DBAPIError( msg, None, None ) mock_build_db.return_value.db_engine_spec.__name__ = "Some name" superset_error = SupersetError( message='Unable to resolve hostname "locahost".', error_type="TEST_CONNECTION_INVALID_HOSTNAME_ERROR", level="error", extra={ "hostname": "locahost", "issue_codes": [ { "code": 1007, "message": ( "Issue 1007 - The hostname provided can't be resolved." ), } ], }, ) mock_build_db.return_value.db_engine_spec.extract_errors.return_value = [ superset_error ] self.login("admin") data = { "sqlalchemy_uri": "postgres://*****:*****@locahost:12345/db", "database_name": "examples", "impersonate_user": False, "server_cert": None, } url = "api/v1/database/test_connection" rv = self.post_assert_metric(url, data, "test_connection") assert rv.status_code == 422 assert rv.headers["Content-Type"] == "application/json; charset=utf-8" response = json.loads(rv.data.decode("utf-8")) expected_response = {"errors": [dataclasses.asdict(superset_error)]} assert response == expected_response
def test_load_data_throws_error_on_exec_failure( self, boston_sqldataset: Type[SQLDataset], test_engine: Engine ): dataset = boston_sqldataset(test_engine, None) trans_mock = MagicMock() conn_mock = MagicMock() conn_mock.begin.return_value = trans_mock create_conn_mock = MagicMock() create_conn_mock.return_value.__enter__.return_value = conn_mock dataset.create_connection = create_conn_mock with pytest.raises(DBAPIError): data_mock = MagicMock() data_mock.to_sql.side_effect = DBAPIError("test", "test", "test") dataset._load_data(data_mock) trans_mock.rollback.assert_called()
def recuperer_oeuvre_proposee(titre, annee): """ Récupère une oeuvre proposée à partir de son titre et de son année de parution :param titre: titre de l'oeuvre (str) :param annee: année de parution de l'oeuvre (int) :return: oeuvre recherché (PropositionOeuvre) """ try: # Récupère une oeuvre proposée oeuvre_proposee = PropositionOeuvre.query.filter_by( titre=titre, annee=annee).first() except (DBAPIError, SQLAlchemyError) as e: gerer_exception(e) raise DBAPIError("Aucune reponse récupérée", e.params, e.orig) else: return oeuvre_proposee
def _execute(self, query, *multiparams, **params): try: result = yield from super()._execute(query, *multiparams, **params) return result except self._dialect.dbapi.Error as e: if isinstance(query, str): statement = query params = params else: compiled = query.compile(dialect=self._dialect) statement = str(compiled) params = compiled.params raise DBAPIError.instance( statement, params, e, self._dialect.dbapi.Error, dialect=self._dialect, )
def _read_write_connect(dialect, connection_info): """ Creator function invoked for creating read/write connections Note that the parameters are first created using a partial. SqlAlchemy invokes this without parameters. Every time a new connection is created, we query plusmoin for the current master and return a connection to that host. @param dialect: The SqlAlchemy Dialect class that represents our database @param connection_info: dict defining host, port, user, password and database. @returns: A dbapi object """ try: plusmoin = _plusmoin_status() if plusmoin: if len(plusmoin['clusters']) == 0 or not plusmoin['clusters'][0]['has_master']: raise Exception('Plusmoin advertises no available masters') master = plusmoin['clusters'][0]['master'] else: master = connection_info logger = logging.getLogger('db_load_balancing') logger.debug("Read/write connection using %s", str(master)) return dialect.connect( user=connection_info['user'], password=connection_info['password'], database=connection_info['database'], host=master['host'], port=master['port'] ) except Exception as e: import sys raise DBAPIError.instance( None, None, e, dialect.dbapi.Error, connection_invalidated= dialect.is_disconnect(e, None, None)), \ None, sys.exc_info()[2]