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]
