def query(self, _sql):
"""Execute the SQL statement, get a dataset"""
if self.connection is None:
raise Exception("Error: dal.query() is called without a database connection. Query:\n" + _sql)
print("Info: dal.query() at "+ str(self) + "/" + str(self.connection) + " running the following SQL:\n" + _sql)
# py-postgres doesn't use the DB-API, as it doesn't work well-
if self.db_type == DB_POSTGRESQL:
_ps = self.connection.prepare(_sql)
_res = _ps()
if _ps.column_names is not None:
self.field_names = _ps.column_names
self.field_types = []
for _curr_type in _ps.column_types:
self.field_types.append(python_type_to_sql_type(_curr_type))
else:
cur = self.connection.cursor()
cur.execute(_sql)
self.field_names, self.field_types = parse_description(cur.description, self.db_type)
_res = cur.fetchall()
# Untuple. TODO: This might need to be optimised, perhaps by working with the same array.
_results = []
for _row in _res:
_results.append(list(_row))
if self.db_type == DB_ORACLE:
import cx_Oracle
if cx_Oracle.BLOB in self.field_types:
# Loop results and read all blob data
for _curr_row_idx, _curr_row in enumerate(_results):
for _curr_col_idx, _curr_col in enumerate(_curr_row):
if isinstance(_curr_col, cx_Oracle.LOB):
_results[_curr_row_idx][_curr_col_idx] = _curr_col.read()
return _results