def delete(self, oid=None, name=None):
"""Delete property.
:param oid: property id
:param name: property name
:param value: property value
:return: delete response
:raises: :class:`gibbonutil.db.TransactionError`
"""
session = self.get_session()
if oid is not None:
prop = session.query(ConfigProp).filter_by(id=oid).first()
elif name is not None:
prop = session.query(ConfigProp).filter_by(name=name).first()
else:
self.logger.error("Specify at least oid or name")
raise SQLAlchemyError("Specify at least oid or name")
if prop is None:
self.logger.error("No property found")
raise SQLAlchemyError("No property found")
res = session.delete(prop)
self.logger.debug('Delete property: %s' % prop)
return res
def get_instance(self, filter_data):
instance_trial = self.filter_queryset().filter_by(**filter_data).all()
if len(instance_trial) > 1:
raise SQLAlchemyError("查询集只能返回一个结果 请核对lookup参数")
elif len(instance_trial) < 1:
raise SQLAlchemyError("没有查询到数据")
instance = instance_trial[0]
print(id(instance))
return instance
def test_health_db_fail(self, mock_db, client):
mock_db.side_effect = SQLAlchemyError("db connect error")
r = client.get("/api/v1.0/health")
assert r.status_code == 503
data = r.get_json()
assert data["ok"] is False
assert "DB Error:" in data["message"]
def test_flask_seed_raises_expection_on_duplicate_seed_data(
self, mock_bulk_insert):
mock_bulk_insert.side_effect = SQLAlchemyError()
with self.assertRaises(Exception) as e:
bulk_insert(**model_mapper.get('location'))
def get(self, app=None, group=None, oid=None, name=None):
"""Get configuration properties.
:param app: property app [optional]
:param group: property group [optional]
:param oid: property id [optional]
:param name: property name [optional]
:return: list of :class:`ConfigProp`
:raises: :class:`gibbonutil.db.QueryError`
"""
session = self.get_session()
if oid is not None:
prop = session.query(ConfigProp).filter_by(id=oid).all()
elif name is not None:
prop = session.query(ConfigProp).filter_by(name=name).all()
elif app is not None or group is not None:
query = session.query(ConfigProp)
if app is not None:
query = query.filter_by(app=app)
if group is not None:
query = query.filter_by(group=group)
prop = query.all()
else:
prop = session.query(ConfigProp).all()
if len(prop) == 0:
self.logger.warn(u'No properties (app=%s, group=%s, '\
u'oid=%s, name=%s) found' %
(app, group, oid, name))
raise SQLAlchemyError(u'No properties (app=%s, group=%s, '\
u'oid=%s, name=%s) found' %
(app, group, oid, name))
self.logger.debug(u'Get properties: %s' % prop)
return prop
def get_user_access_token(email_address):
"""
Retrieves a user's access token from the database and refreshes it if needed.
:param str email_address: The email address being used to retrieve an access token.
:return str: This is the Token.access_token we need to authenticate our getTalent API calls.
"""
user = session.query(User).filter(User.email == email_address).first()
if not user:
error_msg = 'Unable to retrieve a user with the email {}'.format(
email_address)
logger.info(error_msg)
raise UserWarning(error_msg)
token = session.query(Token).filter(Token.user_id == user.Id).first()
if not token:
error_msg = 'Unable to retrieve a token with the user_id {}'.format(
user.Id)
logger.info(error_msg)
raise SQLAlchemyError(error_msg)
access_token = token.access_token
if token.expires.replace(tzinfo=pytz.UTC) < utcnow():
access_token = refresh_token(token)
return access_token
def execute(self, query: str, params: dict=None, is_transaction: bool=False):
# logger.debug('PostgreSQLConnection.execute()')
# logger.debug(f'PostgreSQLConnection.execute() - is_transaction: {is_transaction}')
# logger.debug(f'PostgreSQLConnection.execute() - query: {query}')
# logger.debug(f'PostgreSQLConnection.execute() - params: {params}')
try:
# INSERT, UPDATE and DELETE
if is_transaction:
with self.engine.begin() as connection: # runs a transaction
connection.execute(query, params)
return
# SELECT (return ResultProxy)
# with self.engine.connect() as connection:
# # convert rows from ResultProxy to list and return the object
# return list(connection.execute(query))
# SELECT (return dataframe)
return read_sql(query, con=self.engine)
except SQLAlchemyError as error:
logger.error(f'PostgreSQLConnection.execute() - An error occurred during query execution.')
logger.error(f'PostgreSQLConnection.execute() - error.code: {error.code} - error.args: {error.args}')
logger.error(f'PostgreSQLConnection.execute() - error: {error}\n')
raise SQLAlchemyError(error)
def test_update_data_2(a):
"""Test update_data."""
with pytest.raises(Exception):
DatabaseIngestion.update_data(None)
a.side_effect = SQLAlchemyError()
with pytest.raises(Exception):
DatabaseIngestion.update_data(None)
def test_add_with_db_error(self, DaoSession):
session = DaoSession.return_value = Mock()
session.commit.side_effect = SQLAlchemyError()
callfilter = Mock(Callfilter)
self.assertRaises(SQLAlchemyError, callfilter_dao.add, callfilter)
session.rollback.assert_called_once_with()
def test_health_db_fail(self, mock_db, client):
mock_db.side_effect = SQLAlchemyError('db connect error')
r = client.get('/api/v1.0/health')
assert r.status_code == 503
data = r.get_json()
assert data['ok'] is False
assert 'DB Error:' in data['message']
def update(cls, data, commit=True, **kwargs):
"""
:kwargs must be `id` or `email`
:data must be a dict
"""
data['updated'] = datetime.today()
email = kwargs.get('email')
id = kwargs.get('id')
if email:
instance = session.query(cls).filter(
cls.email == email).update(data)
if id:
instance = session.query(cls).filter(cls.id == id).update(data)
if commit:
try:
if instance == 0:
raise SQLAlchemyError('Record not found!')
session.commit()
return {"status": True}
except Exception as exc:
resp = {"msg": str(exc), "status": False}
print(f'Error in update, exception: {exc} ')
session.rollback()
return resp
finally:
session.close()
return cls
def delete(id):
try:
query = session.query(ClientModel).filter(ClientModel.id == id)
row = query.first()
if row is None:
raise SQLAlchemyError(Exception('Record not found'))
data = {
'id': row.id,
'first_name': row.first_name,
'last_name': row.last_name,
'company_name': row.company_name,
'email': row.email,
'phone_number': row.phone_number,
'street': row.street,
'address_line_2': row.address_line_2,
'city': row.city,
'postal_code': row.postal_code,
'province_id': row.province_id
}
query.delete()
session.commit()
return {'success': True, 'message': None, 'data': data}
except SQLAlchemyError as e:
return {'success': False, 'message': str(e)}
finally:
session.close()
async def test_invalid_url_on_update(
hass: HomeAssistant,
caplog: pytest.LogCaptureFixture,
):
"""Test invalid db url with redacted credentials on retry."""
config = {
"db_url": "sqlite://",
"query": "SELECT 5 as value",
"column": "value",
"name": "count_tables",
}
entry = MockConfigEntry(
domain=DOMAIN,
source=SOURCE_USER,
data={},
options=config,
entry_id="1",
)
entry.add_to_hass(hass)
await hass.config_entries.async_setup(entry.entry_id)
await hass.async_block_till_done()
with patch(
"homeassistant.components.sql.sensor.sqlalchemy.engine.cursor.CursorResult",
side_effect=SQLAlchemyError(
"sqlite://*****:*****@homeassistant.local"),
):
await async_update_entity(hass, "sensor.count_tables")
assert "sqlite://*****:*****@homeassistant.local" not in caplog.text
assert "sqlite://****:****@homeassistant.local" in caplog.text
async def test_invalid_url_setup(
hass: HomeAssistant,
caplog: pytest.LogCaptureFixture,
url: str,
expected_patterns: str,
not_expected_patterns: str,
):
"""Test invalid db url with redacted credentials."""
config = {
"db_url": url,
"query": "SELECT 5 as value",
"column": "value",
"name": "count_tables",
}
entry = MockConfigEntry(
domain=DOMAIN,
source=SOURCE_USER,
data={},
options=config,
entry_id="1",
)
entry.add_to_hass(hass)
with patch(
"homeassistant.components.sql.sensor.sqlalchemy.create_engine",
side_effect=SQLAlchemyError(url),
):
await hass.config_entries.async_setup(entry.entry_id)
await hass.async_block_till_done()
for pattern in not_expected_patterns:
assert pattern not in caplog.text
for pattern in expected_patterns:
assert pattern in caplog.text
def get_categories():
try:
categories = CategoryModel.query.all()
except SQLAlchemyError as error:
raise SQLAlchemyError(error)
return jsonify(CategorySchema(many=True).dump(categories)), 200
def delete(self):
"""Delete object from db."""
try:
db.session.delete(self)
db.session.commit()
except SQLAlchemyError:
raise SQLAlchemyError("DB Commit failed.")
def __init__(self, dsn=None, schema_check=True, echo=False):
"""
@param dsn: database connection string.
@param schema_check: disable or enable the db schema version check.
@param echo: echo sql queries.
"""
self._lock = SuperLock()
if not dsn:
dsn = "mysql://*****:*****@localhost/scrapy_paper?charset=utf8"
self._connect_database(dsn)
# Disable SQL logging. Turn it on for debugging.
self.engine.echo = echo
# Connection timeout.
self.engine.pool_timeout = 60
# Create schema.
try:
Base.metadata.create_all(self.engine)
except SQLAlchemyError as e:
raise SQLAlchemyError(
"Unable to create or connect to database: {0}".format(e))
# Get db session.
self.Session = sessionmaker(bind=self.engine)
def test_index_metrics_database_failure(self):
"""
verify handles failure from database
send one bibcode, verify there are two commits
"""
self.app.update_storage('abc', 'metrics', {
'author_num': 1,
'bibcode': 'abc',
})
trans = mock.Mock()
trans.commit.side_effect = SQLAlchemyError('test')
m = mock.Mock()
m.begin_nested.return_value = trans
m.__exit__ = mock.Mock()
m.__enter__ = mock.Mock()
m.__enter__.return_value = mock.Mock()
m.__enter__.return_value.begin_nested.return_value = trans
# init database so timestamps and checksum can be updated
with mock.patch(
'adsmp.app.ADSMasterPipelineCelery.metrics_session_scope',
return_value=m) as p:
metrics_payload = {'bibcode': 'abc', 'author_num': 1}
checksum = 'checksum'
self.app.index_metrics([metrics_payload], [checksum])
self.assertEqual(trans.commit.call_count, 2)
def test_delete(logger, app):
"""Test pid delete."""
with app.app_context():
i = 1
for s in [
PIDStatus.RESERVED, PIDStatus.RESERVED, PIDStatus.REDIRECTED,
PIDStatus.DELETED
]:
pid = PersistentIdentifier.create('rec', str(i), status=s)
i += 1
assert pid.delete()
assert logger.info.call_args[0][0] == "Deleted PID."
# New persistent identifiers are removed completely
count = PersistentIdentifier.query.count()
pid = PersistentIdentifier.create('rec', str(i), status=PIDStatus.NEW)
db.session.commit()
assert PersistentIdentifier.query.count() == count + 1
pid.delete()
assert PersistentIdentifier.query.count() == count
assert logger.info.call_args[0][0] == "Deleted PID (removed)."
pid = PersistentIdentifier.create('rec', str(i + 1))
with patch('invenio_pidstore.models.db.session.begin_nested') as mock:
mock.side_effect = SQLAlchemyError()
pytest.raises(SQLAlchemyError, pid.delete)
assert logger.exception.call_args[0][0].startswith(
"Failed to delete")
assert 'pid' in logger.exception.call_args[1]['extra']
def _create_engine(self):
from os import getenv
# MYSQL connection
MYSQL_DB_USER = getenv('MYSQL_DB_USER', 'test')
MYSQL_DB_PASSWORD = getenv('MYSQL_DB_PASSWORD', 'test')
MYSQL_DB_HOST = getenv('MYSQL_DB_HOST', 'localhost')
MYSQL_DB_PORT = getenv('MYSQL_DB_PORT', '3306')
MYSQL_DB_DATABASE = getenv('MYSQL_DB_DATABASE', 'database')
engine_connection = (
f'mysql+pymysql://{MYSQL_DB_USER}:{MYSQL_DB_PASSWORD}@'
f'{MYSQL_DB_HOST}:{MYSQL_DB_PORT}/{MYSQL_DB_DATABASE}')
try:
# `NullPool prevents the Engine from using any connection more than once`
self.engine = create_engine(engine_connection, poolclass=NullPool)
except SQLAlchemyError as error:
logging.error(
'MySQLConnection._create_engine() - An error occurred during engine creation.'
)
logging.error(
f'MySQLConnection._create_engine() - error.code: {error.code} - error.args: {error.args}'
)
logging.error(
f'MySQLConnection._create_engine() - error: {error}\n')
raise SQLAlchemyError(error)
def test_register(logger, app):
"""Test pid register."""
with app.app_context():
i = 1
for s in [PIDStatus.NEW, PIDStatus.RESERVED]:
pid = PersistentIdentifier.create('rec', str(i), status=s)
i += 1
assert pid.register()
assert logger.info.call_args[0][0].startswith("Registered PID")
for s in [
PIDStatus.REGISTERED, PIDStatus.DELETED, PIDStatus.REDIRECTED
]:
pid = PersistentIdentifier.create('rec', str(i), status=s)
i += 1
pytest.raises(PIDInvalidAction, pid.register)
# Test logging of bad errors.
pid = PersistentIdentifier.create('rec',
str(i),
status=PIDStatus.RESERVED)
with patch('invenio_pidstore.models.db.session.begin_nested') as mock:
mock.side_effect = SQLAlchemyError()
pytest.raises(SQLAlchemyError, pid.register)
assert logger.exception.call_args[0][0].startswith(
"Failed to register")
assert 'pid' in logger.exception.call_args[1]['extra']
def execute(self, query, params=None, is_transaction=False):
# logging.debug('PostgreSQLConnection.execute()')
# logging.debug(f'PostgreSQLConnection.execute() - is_transaction: {is_transaction}')
# logging.debug(f'PostgreSQLConnection.execute() - query: {query}')
# logging.debug(f'PostgreSQLConnection.execute() - params: {params}')
try:
if is_transaction:
with self.engine.begin() as connection: # runs a transaction
connection.execute(query, params)
return
# SELECT
# with self.engine.connect() as connection:
# # safe code against SQL injection - https://realpython.com/prevent-python-sql-injection/
# result = connection.execute("SELECT admin FROM users WHERE username = %(username)s", {'username': username});
# for row in result:
# print("username:", row['username'])
except SQLAlchemyError as error:
logging.error(f'PostgreSQLConnection.execute() - An error occurred during query execution.')
logging.error(f'PostgreSQLConnection.execute() - error.code: {error.code} - error.args: {error.args}')
logging.error(f'PostgreSQLConnection.execute() - error: {error}\n')
raise SQLAlchemyError(error)
def drop(self):
"""Drop all tables."""
try:
Base.metadata.drop_all(self.engine)
except SQLAlchemyError as e:
raise SQLAlchemyError(
"Unable to create or connect to database: {0}".format(e))
def test_status_when_db_fails(client: TestClient):
operational_error_code = "e3q8"
side_effect = SQLAlchemyError(code=operational_error_code) # type: ignore
with mock.patch("app.db.ping_db", side_effect=side_effect) as ping_db_mock:
response = client.get("/api/ping/db")
assert response.json() == {"status": "ERROR"}
assert ping_db_mock.called
def put_db(self, sample):
try:
self.db.add(sample)
self.db.commit()
except SQLAlchemyError as sqlae:
self.db.rollback()
print(" --", sqlae.args, sample)
raise SQLAlchemyError("general sql db error?!", sqlae.args, sample)
def test_save_message_raises_message_save_exception_on_db_error(self):
"""Tests exception is logged if message save fails"""
with self.app.app_context():
mock_session = mock.Mock(db.session)
mock_session.commit.side_effect = SQLAlchemyError("Not Saved")
with current_app.test_request_context():
with self.assertRaises(MessageSaveException):
Saver().save_message(self.test_message, mock_session)
def test_delete_all_db_error(self, session_init):
session = Mock()
session.commit.side_effect = SQLAlchemyError()
session_init.return_value = session
self.assertRaises(ElementDeletionError, call_log_dao.delete_all)
session.begin.assert_called_once_with()
session.rollback.assert_called_once_with()
def create_item(user_id, category, data):
item_name = data["name"]
try:
existing_item = ItemModel.query.filter_by(name=item_name).one_or_none()
except SQLAlchemyError as error:
raise SQLAlchemyError(error)
if existing_item:
raise BadRequestError(f"Item {item_name} already existed.")
new_item = ItemModel(user_id=user_id, **data)
try:
db.session.add(new_item)
db.session.commit()
except SQLAlchemyError as error:
raise SQLAlchemyError(error)
return jsonify(ItemSchema().dump(new_item)), 201
def test_session_database_returns_false_if_sql_alchemy_error(self):
app_mock = MagicMock()
app_mock.session_interface.sql_session_model.query.first.side_effect = (
SQLAlchemyError()
)
actual = health.session_database(app_mock)
self.assertEqual(False, actual)
def export_user_data(user_id):
try:
user = User.query.filter_by(uuid=user_id).first()
return user.export_serialization
except SQLAlchemyError as e:
raise SQLAlchemyError(e)
except Exception as e:
raise Exception(e)