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 get_field_from_value(self, value: Any) -> Field:
if type(value).__name__.endswith('UUID'):
return Field(stringValue=str(value))
elif type(value).__name__.endswith('PGobject'):
return Field(stringValue=str(value))
elif type(value).__name__.endswith('PgArray'):
return Field(stringValue=str(value))
else:
return super().get_field_from_value(value)
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 get_filed_from_jdbc_type(self, value: Any,
jdbc_type: Optional[int]) -> Field:
type_ = type(value)
if type_.__name__.endswith('UUID'):
return Field(stringValue=str(value))
elif type_.__name__.endswith('PGobject'):
return Field(stringValue=str(value))
elif type_.__name__.endswith('PgArray'):
return Field(stringValue=str(value))
else:
return super().get_filed_from_jdbc_type(value, jdbc_type)
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 get_filed_from_jdbc_type(self, value: Any,
jdbc_type: Optional[int]) -> Field:
type_ = type(value)
for pg_type in PG_TYPES:
if type_.__name__.endswith(pg_type):
return Field(stringValue=str(value))
return super().get_filed_from_jdbc_type(value, jdbc_type)
def test_from_value(mocker) -> None:
connection_mock = mocker.Mock()
dummy = PostgreSQLJDBC(connection_mock)
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 dummy.get_filed_from_jdbc_type(JavaUUID(uuid),
None) == Field(stringValue=uuid)
class PGobject:
def __init__(self, val: str):
self._val: str = val
def __str__(self) -> str:
return self._val
assert dummy.get_filed_from_jdbc_type(PGobject("{}"),
None) == Field(stringValue="{}")
class PgArray:
def __init__(self, val: str):
self._val: str = val
def __str__(self) -> str:
return self._val
assert dummy.get_filed_from_jdbc_type(
PgArray("{ITEM1,ITEM2}"), None) == Field(stringValue="{ITEM1,ITEM2}")
class PGpoint:
def __init__(self, val: str):
self._val: str = val
def __str__(self) -> str:
return self._val
assert dummy.get_filed_from_jdbc_type(
PGpoint("(50.074534,14.444137)"),
None) == Field(stringValue="(50.074534,14.444137)")
helper_default_test_field(dummy)
def get_filed_from_jdbc_type(self, value: Any, jdbc_type: Optional[int]) -> Field:
type_: Optional[JDBCType] = None
if jdbc_type:
try:
type_ = JDBCType(jdbc_type)
except ValueError:
pass
if type_:
if value is None:
return Field(isNull=True)
elif type_ in LONG:
return Field(longValue=value)
elif type_ in DOUBLE:
return Field(doubleValue=value)
elif type_ in STRING:
return Field(stringValue=value)
elif type_ in BOOLEAN:
return Field(booleanValue=value)
elif type_ in TIMESTAMP:
return Field(stringValue=self._format_datetime(value))
elif type_ in BLOB:
if isinstance(value, str): # pragma: no cover
value = value.encode()
return Field(blobValue=b64encode(value))
return self.get_field_from_value(value)
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 get_filed_from_jdbc_type(self, value: Any,
jdbc_type: Optional[int]) -> Field:
type_: Optional[JDBCType] = None
if jdbc_type:
try:
type_ = JDBCType(jdbc_type)
except ValueError:
pass
if type_:
if type_ in LONG:
return Field(longValue=value)
elif type_ in DOUBLE:
return Field(doubleValue=value)
elif type_ in STRING:
return Field(stringValue=value)
elif type_ in BOOLEAN:
return Field(booleanValue=value)
elif type_ in BLOB:
return Field(blobValue=b64encode(value))
return self.get_field_from_value(value)
def test_from_value(mocker) -> None:
connection_mock = mocker.Mock()
dummy = MySQLJDBC(connection_mock)
class BigInteger:
def __init__(self, val: int):
self._val: int = val
def __str__(self) -> int:
return self._val
assert dummy.get_filed_from_jdbc_type(BigInteger("55"), None) == Field(longValue=55)
helper_default_test_field(dummy)
def test_execute_insert_with_generated(clear, mocker):
connection_mock = mocker.Mock()
cursor_mock = mocker.Mock()
connection_mock.cursor.side_effect = [cursor_mock]
cursor_mock.description = ''
cursor_mock.rowcount = 1
cursor_mock.lastrowid = 1
dummy = DummyResource(connection_mock)
assert dummy.execute("insert into users ('name') values ('abc')"
) == ExecuteStatementResponse(
numberOfRecordsUpdated=1,
generatedFields=[Field(longValue=1)])
cursor_mock.execute.assert_called_once_with(
"insert into users ('name') values ('abc')")
cursor_mock.close.assert_called_once_with()
def test_execute_insert_with_generated_field(mocked_connection, mocked_cursor,
mocker):
mocked_cursor.description = ''
mocked_cursor.rowcount = 1
mocked_cursor.fetchone.side_effect = [[1]]
dummy = MySQLJDBC(mocked_connection)
assert dummy.execute(
"insert into users (name) values ('abc')") == ExecuteStatementResponse(
numberOfRecordsUpdated=1, generatedFields=[Field(longValue=1)])
mocked_cursor.execute.assert_has_calls([
mocker.call('SELECT LAST_INSERT_ID(NULL)'),
mocker.call("insert into users (name) values ('abc')"),
mocker.call('SELECT LAST_INSERT_ID()'),
])
mocked_cursor.close.assert_called_once_with()
def test_valid_field() -> None:
assert SqlParameter(name='abc',
value=Field(stringValue='abc')).valid_value == 'abc'
assert SqlParameter(name='abc',
value=Field(blobValue='abc')).valid_value == 'abc'
assert SqlParameter(name='abc',
value=Field(doubleValue=0.1)).valid_value == 0.1
assert SqlParameter(name='abc',
value=Field(isNull=True)).valid_value is None
assert SqlParameter(name='abc',
value=Field(longValue=123)).valid_value == 123
assert SqlParameter(name='abc',
value=Field(longValue=123)).valid_value == 123
assert SqlParameter(name='abc', value=Field()).valid_value is None
def helper_default_test_field(dummyResource: Resource) -> None:
assert dummyResource.get_field_from_value('str') == Field(stringValue='str')
assert dummyResource.get_field_from_value(123) == Field(longValue=123)
assert dummyResource.get_field_from_value(1.23) == Field(doubleValue=1.23)
assert dummyResource.get_field_from_value(True) == Field(booleanValue=True)
assert dummyResource.get_field_from_value(False) == Field(booleanValue=False)
assert dummyResource.get_field_from_value(b'bytes') == Field(
blobValue=b64encode(b'bytes')
)
assert dummyResource.get_field_from_value(None) == Field(isNull=True)
assert dummyResource.get_field_from_value(
datetime(2019, 5, 18, 15, 17, 8)
) == Field(stringValue='2019-05-18 15:17:08')
class Dummy:
pass
with pytest.raises(Exception):
dummyResource.get_field_from_value(Dummy())
def get_field_from_value(self, value: Any) -> Field:
if isinstance(value, bool):
return Field(booleanValue=value)
elif isinstance(value, str):
return Field(stringValue=value)
elif type(value).__name__ == 'datetime':
return Field(stringValue=str(value))
elif isinstance(value, int):
return Field(longValue=value)
elif isinstance(value, float):
return Field(doubleValue=value)
elif isinstance(value, bytes):
return Field(blobValue=b64encode(value))
elif value is None:
return Field(isNull=True)
else:
raise Exception(f'unsupported type {type(value)}: {value} ')
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 get_filed_from_jdbc_type(self, value: Any, jdbc_type: Optional[int]) -> Field:
if type(value).__name__.endswith('BigInteger'):
return Field(longValue=int(str(value)))
else:
return super().get_filed_from_jdbc_type(value, jdbc_type)
def test_valid_field() -> None:
assert SqlParameter(name='abc',
value=Field(stringValue='abc')).valid_value == 'abc'
assert SqlParameter(name='abc',
value=Field(blobValue='abc')).valid_value == 'abc'
assert SqlParameter(name='abc',
value=Field(doubleValue=0.1)).valid_value == 0.1
assert SqlParameter(name='abc',
value=Field(isNull=True)).valid_value is None
assert SqlParameter(name='abc',
value=Field(longValue=123)).valid_value == 123
assert SqlParameter(name='abc',
value=Field(longValue=123)).valid_value == 123
assert SqlParameter(name='abc', value=Field()).valid_value is None
assert (SqlParameter(name='abc',
value=Field(stringValue='123456789'),
typeHint='DECIMAL').valid_value == '123456789')
assert (SqlParameter(
name='abc',
value=Field(stringValue='2020-02-27 00:30:15.290'),
typeHint='TIMESTAMP',
).valid_value == '2020-02-27 00:30:15.290')
assert (SqlParameter(name='abc',
value=Field(stringValue='00:30:15.290'),
typeHint='TIME').valid_value == '00:30:15.290')
assert (SqlParameter(name='abc',
value=Field(stringValue='2020-02-27'),
typeHint='DATE').valid_value == '2020-02-27')
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 PGobject:
def __init__(self, val: str):
self._val: str = val
def __str__(self) -> str:
return self._val
assert Field.from_value(PGobject("{}")) == Field(stringValue="{}")
class BigInteger:
def __init__(self, val: int):
self._val: int = val
def __str__(self) -> int:
return self._val
assert Field.from_value(BigInteger("55")) == Field(longValue=55)
class Dummy:
pass
with pytest.raises(Exception):
Field.from_value(Dummy())
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()
def get_field_from_value(self, value: Any) -> Field:
if type(value).__name__.endswith('BigInteger'):
return Field(longValue=int(str(value)))
else:
return super().get_field_from_value(value)
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())
