def get_http_parameters_dict(self, params: dict) -> dict:
""" Build the http parameters dictionary to pass to remote server. """
parms_dict: dict = {}
param_index = 0
# Blob lists
if params is None:
return parms_dict
for x in params:
param_index += 1
# For Py2 always convert "unicode" aka str with special chars to str
x = CursorUtil.get_utf8_value(x)
param_type = CursorUtil.get_sql_type(x)
parms_dict["param_type_" + str(param_index)] = param_type
# print(str(param_index) + " param_type: " + str(param_type))
# NULL values are defined in a (None, SqlNullType.TYPE) typle
# BLOB values are defined in a (fd,) or (fd, length) tuple where
# fd = open(filename)
if CursorUtil.get_class_name(x) == "tuple":
if param_type == "BLOB":
blob_id = FileUtil.get_unique_id() + ".blob"
parms_dict["param_value_" + str(param_index)] = blob_id
self.blob_ids.append(blob_id)
self.blob_streams.append(x[0])
if len(x) == 2:
self.blob_lengths.append(x[1])
else:
self.blob_lengths.append(0)
else:
parms_dict["param_value_" + str(param_index)] = "NULL"
elif CursorUtil.get_class_name(x) == "datetime.datetime":
parms_dict[
"param_value_" +
str(param_index)] = DateTimeUtil.get_timestamp_from_date(x)
elif CursorUtil.get_class_name(x) == "datetime.date":
parms_dict[
"param_value_" +
str(param_index)] = DateTimeUtil.get_timestamp_from_date(x)
elif CursorUtil.get_class_name(x) == "datetime.time":
the_datetime = datetime.now()
the_datetime.hour = x.hour
the_datetime.minute = x.minute
the_datetime.second = x.second
parms_dict[
"param_value_" +
str(param_index)] = DateTimeUtil.get_timestamp_from_date(
the_datetime)
else:
parms_dict["param_value_" + str(param_index)] = str(x)
print("parms_dict: " + str(parms_dict))
return parms_dict
def executemany(self, sql: str, seq_params: list) -> List[int]:
"""Execute the given SQL operation multiple times
The executemany() method will execute the operation iterating
over the list of parameters in seq_params.
Note that the SQL operation are transferred with one unique HTTP call to the server side which will execute
them using a JDBC PreparedStatement in batch mode: this will allow fast execution.
"""
if not ConnectionUtil.is_batch_supported(self.__connection):
raise Exception("AceQL Server version must be >= " + ConnectionUtil.BATCH_MIN_SERVER_VERSION
+ " in order to call executemany.")
batch_file_parameters = FileUtil.build_batch_file()
try:
self.__raise_error_if_closed()
if sql is None:
raise TypeError("sql is null!")
sql = sql.strip()
if not CursorUtil.is_update_call(sql):
raise aceql.Error("Only DELETE, INSERT or UPDATE calls are supported this AceQL Client version.", 0,
None, None, 200)
if not seq_params:
return
# The addBatch() part
for params in seq_params:
the_cursor_util: CursorUtil = CursorUtil()
parms_dict: dict = the_cursor_util.get_http_parameters_dict(params)
blob_ids: list = the_cursor_util.blob_ids
if blob_ids is not None and len(blob_ids) > 0:
raise aceql.Error(
"Cannot call executemany for a table with BLOB parameter in this AceQL Client version.", 0,
None, None, 200)
prep_statement_parameters_holder_schema = marshmallow_dataclass.class_schema(
PrepStatementParametersHolder)
prep_statement_parameters_holder: PrepStatementParametersHolder = PrepStatementParametersHolder(
parms_dict)
json_string: str = prep_statement_parameters_holder_schema().dumps(prep_statement_parameters_holder)
with open(batch_file_parameters, "a") as fd:
fd.write(json_string + "\n")
# The executeBatch() part
rows: List[int] = self.__aceql_http_api.execute_batch(sql, batch_file_parameters)
return rows
finally:
CursorUtil.remove_file_safe(batch_file_parameters)
def treat_result(self, filename: str):
file_out = None
if self.__aceQLHttpApi.is_gzip_result():
file_out = filename[0:len(filename) - 4] + ".ungzipped.txt"
FileUtil.decompress(filename, file_out)
if AceQLDebugParms.DELETE_FILES:
CursorUtil.remove_file_safe(filename)
else:
file_out = filename
AceQLDebug.debug("Before StreamResultAnalyzer")
result_analyzer = StreamResultAnalyzer(
file_out, self.__aceQLHttpApi.get_http_status_code())
if not result_analyzer.is_status_ok():
if AceQLDebugParms.DELETE_FILES:
CursorUtil.remove_file_safe(filename)
raise Error(result_analyzer.get_error_message(),
result_analyzer.get_error_type(), None, None,
self.__aceQLHttpApi.get_http_status_code())
row_counter = RowCounter(file_out)
row_count = row_counter.count()
result_set_info = ResultSetInfo(file_out, row_count)
AceQLDebug.debug("Before resultSetInfo")
return result_set_info
def load():
"""Loads the file that contains the classes to debug and update the CLASSES_TO_DEBUG set."""
if len(FrameworkDebug.CLASSES_TO_DEBUG) > 0:
return
aceql_debug_file = FileUtil.get_user_home_dot_kawansoft_dir(
) + sep + "aceql-debug-python.ini"
if not os.path.isfile(aceql_debug_file):
return
# Loads from file classes to debug
with open(aceql_debug_file) as f:
lines = f.readlines()
for line in lines:
FrameworkDebug.CLASSES_TO_DEBUG.add(line.strip())
def get_utf8_value(x: str) -> str:
""" For python 2: string values with special chars must be UTF-8 encoded """
if FileUtil.is_python_2() and CursorUtil.get_class_name(
x) == "unicode":
x = x.encode('utf-8')
return x
def execute_server_query(self, server_query_executor_class_name: str,
parameters: list):
"""Calls /execute_server_query API"""
try:
action = "execute_server_query"
dict_params = {
"server_query_executor_class_name":
server_query_executor_class_name
}
AceQLDebug.debug("dictParams 1: " + str(dict_params))
server_query_executor_dto_schema = marshmallow_dataclass.class_schema(
ServerQueryExecutorDto)
server_query_executor_dto: ServerQueryExecutorDto = ServerQueryExecutorDtoBuilder.build(
server_query_executor_class_name, parameters)
json_string: str = server_query_executor_dto_schema().dumps(
server_query_executor_dto)
dict_params["server_query_executor_dto"] = json_string
url_withaction = self.__url + action
AceQLDebug.debug("url_withaction: " + url_withaction)
AceQLDebug.debug("parameters : " + str(parameters))
self.update_dict_params(dict_params)
AceQLDebug.debug("dictParams 2: " + str(dict_params))
# r = requests.post('http://httpbin.org/post', data = {'key':'value'})
if self.__aceQLHttpApi.get_timeout() is None:
AceQLDebug.debug("QUERY HERE 1")
response: Request = requests.post(
url_withaction,
headers=self.__aceQLHttpApi.get_headers(),
data=dict_params,
proxies=self.__aceQLHttpApi.get_proxies(),
auth=self.__aceQLHttpApi.get_auth())
else:
AceQLDebug.debug("QUERY HERE 2")
response: Request = requests.post(
url_withaction,
headers=self.__aceQLHttpApi.get_headers(),
data=dict_params,
proxies=self.__aceQLHttpApi.get_proxies(),
auth=self.__aceQLHttpApi.get_auth(),
timeout=self.__aceQLHttpApi.get_timeout())
AceQLDebug.debug("DONE!")
self.__aceQLHttpApi.set_http_status_code(response.status_code)
filename = FileUtil.build_result_set_file()
AceQLDebug.debug("filename1: " + filename)
# We dump the JSon stream into user.home/.kawansoft/tmp
with open(filename, 'wb') as fd:
for chunk in response.iter_content(chunk_size=2048):
fd.write(chunk)
AceQLDebug.debug("after open filename")
result_set_info = self.treat_result(filename)
return result_set_info
except Exception as e:
if isinstance(e, Error):
raise
else:
raise Error(str(e), 0, e, None,
self.__aceQLHttpApi.get_http_status_code())
def execute_query(self, sql: str, is_prepared_statement: bool,
statement_parameters: dict):
"""Calls /execute_query API"""
try:
action = "execute_query"
AceQLExecutionUtil.check_values(is_prepared_statement, sql)
dict_params = {"sql": sql}
AceQLExecutionUtil.set_is_prepared_statement(
dict_params, is_prepared_statement)
url_withaction = self.__url + action
AceQLDebug.debug("url_withaction: " + url_withaction)
AceQLDebug.debug("dictParams 1: " + str(dict_params))
if statement_parameters is not None:
if not isinstance(statement_parameters, dict):
raise TypeError("statementParameters is not a dictionary!")
dict_params.update(statement_parameters)
self.update_dict_params(dict_params)
AceQLDebug.debug("dictParams 2: " + str(dict_params))
# r = requests.post('http://httpbin.org/post', data = {'key':'value'})
if self.__aceQLHttpApi.get_timeout() is None:
AceQLDebug.debug("QUERY HERE 1")
response: Request = requests.post(
url_withaction,
headers=self.__aceQLHttpApi.get_headers(),
data=dict_params,
proxies=self.__aceQLHttpApi.get_proxies(),
auth=self.__aceQLHttpApi.get_auth())
else:
AceQLDebug.debug("QUERY HERE 2")
response: Request = requests.post(
url_withaction,
headers=self.__aceQLHttpApi.get_headers(),
data=dict_params,
proxies=self.__aceQLHttpApi.get_proxies(),
auth=self.__aceQLHttpApi.get_auth(),
timeout=self.__aceQLHttpApi.get_timeout())
AceQLDebug.debug("DONE!")
self.__aceQLHttpApi.set_http_status_code(response.status_code)
filename = FileUtil.build_result_set_file()
AceQLDebug.debug("filename1: " + filename)
# We dump the JSon stream into user.home/.kawansoft/tmp
with open(filename, 'wb') as fd:
for chunk in response.iter_content(chunk_size=2048):
fd.write(chunk)
AceQLDebug.debug("after open filename")
result_set_info = self.treat_result(filename)
return result_set_info
except Exception as e:
if isinstance(e, Error):
raise
else:
raise Error(str(e), 0, e, None,
self.__aceQLHttpApi.get_http_status_code())
def do_it():
aceql_http_api = AceQLHttpApi("http://localhost:9090/aceql", "user1", "password1", "sampledb")
print("connect done!")
print("client version: " + aceql_http_api.get_client_version())
print("server version: " + aceql_http_api.get_server_version())
auto_commit = aceql_http_api.get_auto_commit()
print("auto_commit: " + str(auto_commit))
aceql_http_api.set_auto_commit(False)
auto_commit = aceql_http_api.get_auto_commit()
print("auto_commit: " + str(auto_commit))
aceql_http_api.commit()
aceql_http_api.rollback()
aceql_http_api.set_auto_commit(True)
auto_commit = aceql_http_api.get_auto_commit()
print("auto_commit: " + str(auto_commit))
holdability = aceql_http_api.get_holdability()
print("holdability: " + holdability)
transaction_isolation = aceql_http_api.get_transaction_isolation()
print("transaction_isolation: " + transaction_isolation)
read_only = aceql_http_api.is_read_only()
print("readOnly: " + str(read_only))
print()
sql = "update customer set fname = ? where customer_id = ?"
is_prepared_statement = True
statement_parameters = {}
statement_parameters["param_type_1"] = "VARCHAR"
statement_parameters["param_value_1"] = "Nicolas"
statement_parameters["param_type_2"] = "INTEGER"
statement_parameters["param_value_2"] = "1"
result = aceql_http_api.execute_update(sql, is_prepared_statement, statement_parameters)
print("result: " + str(result))
status_code = aceql_http_api.get_http_status_code()
print("statusCode: " + str(status_code))
status_message = aceql_http_api.get_http_status_message()
print("status_message: " + str(status_message))
print()
print("UUID: " + FileUtil.get_unique_id())
print("Home: " + FileUtil.get_user_home_dot_kawansoft_dir())
print("Tmp : " + FileUtil.get_kawansoft_temp_dir())
print()
statement_parameters2 = {}
statement_parameters2["param_type_1"] = "INTEGER"
statement_parameters2["param_value_1"] = "0"
sql = "select * from customer where customer_id >= ? order by customer_id"
aceql_http_api.set_pretty_printing(True)
result = aceql_http_api.execute_query(sql, is_prepared_statement, statement_parameters2)
print("result: " + str(result))
print()