def execute(
self,
sql: str,
params: Optional[Dict[str, Any]] = None,
database_name: Optional[str] = None,
include_result_metadata: bool = False,
) -> ExecuteStatementResponse:
try:
if database_name:
self.use_database(database_name)
cursor: Optional[jaydebeapi.Cursor] = None
try:
cursor = self.connection.cursor()
if self.autocommit:
self.autocommit_off(cursor)
self.reset_generated_id(cursor)
if params:
cursor.execute(self.create_query(sql, params))
else:
cursor.execute(str(text(sql)))
if cursor.description:
response = ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(column) for column in row]
for row in cursor.fetchall()],
)
if include_result_metadata:
meta = getattr(cursor, '_meta')
response.columnMetadata = create_column_metadata_set(
meta)
return response
else:
rowcount: int = cursor.rowcount
last_generated_id: int = self.last_generated_id(cursor)
generated_fields: List[Field] = []
if last_generated_id > 0:
generated_fields.append(
Field.from_value(last_generated_id))
return ExecuteStatementResponse(
numberOfRecordsUpdated=rowcount,
generatedFields=generated_fields,
)
finally:
if cursor: # pragma: no cover
cursor.close()
except jaydebeapi.DatabaseError as e:
message: str = 'Unknown'
if len(getattr(e, 'args', [])):
message = e.args[0]
if len(getattr(e.args[0], 'args', [])):
message = e.args[0].args[0]
if getattr(e.args[0].args[0], 'cause', None):
message = e.args[0].args[0].cause.message
raise BadRequestException(str(message))
def execute(
self,
sql: str,
params: Optional[Dict[str, Any]] = None,
database_name: Optional[str] = None,
include_result_metadata: bool = False,
) -> ExecuteStatementResponse:
try:
if database_name:
self.use_database(database_name)
cursor: Optional[Cursor] = None
try:
cursor = self.connection.cursor()
if params:
cursor.execute(self.create_query(sql, params))
else:
cursor.execute(str(text(sql)))
if cursor.description:
response: ExecuteStatementResponse = ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(column) for column in row]
for row in cursor.fetchall()],
)
if include_result_metadata:
response.columnMetadata = [
create_column_metadata(*d)
for d in cursor.description
]
return response
else:
rowcount: int = cursor.rowcount
last_generated_id: int = cursor.lastrowid
generated_fields: List[Field] = []
if last_generated_id > 0:
generated_fields.append(
Field.from_value(last_generated_id))
return ExecuteStatementResponse(
numberOfRecordsUpdated=rowcount,
generatedFields=generated_fields,
)
finally:
if cursor: # pragma: no cover
cursor.close()
except Exception as e:
message: str = 'Unknown'
if hasattr(e, 'orig') and hasattr(e.orig, 'args'): # type: ignore
message = str(e.orig.args[1]) # type: ignore
elif len(getattr(e, 'args', [])) and e.args[0]:
message = str(e.args[0])
raise BadRequestException(message)
def test_execute_select(clear, mocker):
connection_mock = mocker.Mock()
cursor_mock = mocker.Mock()
connection_mock.cursor.side_effect = [cursor_mock]
cursor_mock.description = 1, 1, 1, 1, 1, 1, 1
cursor_mock.fetchall.side_effect = [((1, 'abc'),)]
dummy = DummyResource(connection_mock, transaction_id='123')
assert dummy.execute("select * from users",) == ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1), Field.from_value('abc')]],
)
cursor_mock.execute.assert_called_once_with('select * from users')
cursor_mock.close.assert_called_once_with()
def test_execute_select(mocked_connection, mocked_cursor, mocker):
mocked_cursor.description = 1, 1, 1, 1, 1, 1, 1
mocked_cursor.fetchall.side_effect = [((1, 'abc'), )]
dummy = PostgreSQLJDBC(mocked_connection, transaction_id='123')
assert dummy.execute("select * from users", ) == ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1),
Field.from_value('abc')]],
)
mocked_cursor.execute.assert_has_calls(
[mocker.call('select * from users')])
mocked_cursor.close.assert_called_once_with()
def test_execute_select_with_include_metadata(clear, mocker):
connection_mock = mocker.Mock()
cursor_mock = mocker.Mock()
connection_mock.cursor.side_effect = [cursor_mock]
cursor_mock.description = (1, 2, 3, 4, 5, 6, 7), (8, 9, 10, 11, 12, 13, 14)
cursor_mock.fetchall.side_effect = [((1, 'abc'), )]
dummy = DummyResource(connection_mock, transaction_id='123')
dummy.use_database = mocker.Mock()
assert dummy.execute(
"select * from users",
database_name='test',
include_result_metadata=True).dict() == ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1),
Field.from_value('abc')]],
columnMetadata=[
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label='1',
name='1',
nullable=1,
precision=5,
scale=6,
tableName=None,
type=None,
typeName=None,
),
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label='8',
name='8',
nullable=1,
precision=12,
scale=13,
tableName=None,
type=None,
typeName=None,
),
],
)
cursor_mock.execute.assert_called_once_with('select * from users')
cursor_mock.close.assert_called_once_with()
def test_from_value() -> None:
assert Field.from_value('str') == Field(stringValue='str')
assert Field.from_value(123) == Field(longValue=123)
assert Field.from_value(1.23) == Field(doubleValue=1.23)
assert Field.from_value(True) == Field(booleanValue=True)
assert Field.from_value(False) == Field(booleanValue=False)
assert Field.from_value(b'bytes') == Field(blobValue=b64encode(b'bytes'))
assert Field.from_value(None) == Field(isNull=True)
class Dummy:
pass
with pytest.raises(Exception):
Field.from_value(Dummy())
def test_execute_select(mocked_connection, mocked_cursor, mocker):
mocked_cursor.description = 1, 1, 1, 1, 1, 1, 1
mocked_cursor.fetchall.side_effect = [((1, 'abc'), )]
dummy = MySQLJDBC(mocked_connection, transaction_id='123')
dummy.use_database = mocker.Mock()
assert dummy.execute(
"select * from users",
database_name='test') == ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1),
Field.from_value('abc')]],
)
mocked_cursor.execute.assert_has_calls([
mocker.call('SELECT LAST_INSERT_ID(NULL)'),
mocker.call('select * from users')
])
mocked_cursor.close.assert_called_once_with()
def test_from_value() -> None:
assert Field.from_value('str') == Field(stringValue='str')
assert Field.from_value(123) == Field(longValue=123)
assert Field.from_value(1.23) == Field(doubleValue=1.23)
assert Field.from_value(True) == Field(booleanValue=True)
assert Field.from_value(False) == Field(booleanValue=False)
assert Field.from_value(b'bytes') == Field(blobValue=b64encode(b'bytes'))
assert Field.from_value(None) == Field(isNull=True)
class JavaUUID:
def __init__(self, val: str):
self._val: str = val
def __str__(self) -> str:
return self._val
uuid = 'e9e1df6b-c6d3-4a34-9227-c27056d596c6'
assert Field.from_value(JavaUUID(uuid)) == Field(stringValue=uuid)
class Dummy:
pass
with pytest.raises(Exception):
Field.from_value(Dummy())
def test_execute_select_with_include_metadata(mocked_connection, mocked_cursor,
mocker):
meta_mock = mocker.Mock()
mocked_cursor._meta = meta_mock
mocked_cursor.description = (1, 2, 3, 4, 5, 6, 7), (8, 9, 10, 11, 12, 13,
14)
mocked_cursor.fetchall.side_effect = [((1, 'abc'), )]
dummy = MySQLJDBC(mocked_connection, transaction_id='123')
dummy.use_database = mocker.Mock()
create_column_metadata_set_mock = mocker.patch(
'local_data_api.resources.jdbc.create_column_metadata_set')
create_column_metadata_set_mock.side_effect = [[
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label=1,
name=1,
precision=5,
scale=6,
tableName=None,
type=None,
typeName=None,
),
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label=8,
name=8,
precision=12,
scale=13,
tableName=None,
type=None,
typeName=None,
),
]]
assert dummy.execute(
"select * from users",
database_name='test',
include_result_metadata=True) == ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1),
Field.from_value('abc')]],
columnMetadata=[
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label=1,
name=1,
precision=5,
scale=6,
tableName=None,
type=None,
typeName=None,
),
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label=8,
name=8,
precision=12,
scale=13,
tableName=None,
type=None,
typeName=None,
),
],
)
create_column_metadata_set_mock.assert_called_once_with(meta_mock)
mocked_cursor.execute.assert_has_calls([
mocker.call('SELECT LAST_INSERT_ID(NULL)'),
mocker.call('select * from users')
])
mocked_cursor.close.assert_called_once_with()
def test_execute_select_with_include_metadata(clear, mocker):
connection_mock = mocker.Mock()
cursor_mock = mocker.Mock()
connection_mock.cursor.side_effect = [cursor_mock]
cursor_mock.description = (1, 2, 3, 4, 5, 6, 7), (8, 9, 10, 11, 12, 13, 14)
cursor_mock.fetchall.side_effect = [((1, 'abc'),)]
field_1 = mocker.Mock()
field_1.name = '1'
field_1.org_name = '1'
field_1.flags = 2
field_1.get_column_length.return_value = 5
field_1.scale = 6
field_1.table_name = None
field_2 = mocker.Mock()
field_2.name = '8'
field_2.org_name = '8'
field_2.flags = 2
field_2.get_column_length.return_value = 12
field_2.scale = 13
field_2.table_name = None
cursor_mock._result.fields = [field_1, field_2]
dummy = MySQL(connection_mock, transaction_id='123')
assert (
dummy.execute("select * from users", include_result_metadata=True).dict()
== ExecuteStatementResponse(
numberOfRecordsUpdated=0,
records=[[Field.from_value(1), Field.from_value('abc')]],
columnMetadata=[
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label='1',
name='1',
nullable=1,
precision=5,
scale=6,
tableName=None,
type=None,
typeName=None,
),
ColumnMetadata(
arrayBaseColumnType=0,
isAutoIncrement=False,
isCaseSensitive=False,
isCurrency=False,
isSigned=False,
label='8',
name='8',
nullable=1,
precision=12,
scale=13,
tableName=None,
type=None,
typeName=None,
),
],
).dict()
)
cursor_mock.execute.assert_called_once_with('select * from users')
cursor_mock.close.assert_called_once_with()