def __exit__(self, type, value, traceback):
if type is None:
# If the transaction has ended successfully with a clean exit
get_conn().commit()
else:
# If the transaction has been interrupted due to an unexpected exception
get_conn().rollback()
get_g().autocommit = True
def query(self, q, params=None):
# Fetch the connection and get a cursor
conn = get_conn()
cursor = conn.cursor(DictCursor)
try:
# Execute the query, with or without parameters and return the result
if params:
cursor.execute(q, params)
else:
cursor.execute(q)
# Return a list of dictionary
obj = {}
res = []
all_data = cursor.fetchall()
columns = [i[0] for i in cursor.description]
for data in all_data:
obj = {}
for i, col in enumerate(columns):
obj[col] = data[i]
res.append(obj)
return res
except Exception as exc:
# If anything happens, wrap the exceptions in a DALException
raise DALException(exc) from exc
finally:
# Close the cursor
cursor.close()
def execute(self, q, params=None):
conn = get_conn()
cursor = conn.cursor(DictCursor)
# Check whether we are under autocommit mode or not
# (it will be false inside a transaction)
try:
autocommit = get_g().get("autocommit", True)
except RuntimeError:
# Allow this code to run outside the application context
# (useful for populate_database.py and tests)
autocommit = True
# Fetch the connection and get a cursor
try:
#Return id when insert, update and delete a new element
#Get the new id
new_oid = cursor.var(oracle_engine.STRING)
table_name = self.table_name(q)
oid_query = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cols.table_name = '" + table_name + "' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name, cols.position"
oid_name = self.query(oid_query)[0]['COLUMN_NAME']
q = q + " returning " + oid_name + " into :" + str(len(params) + 1)
#This transformation is for add a new element
aux = list(params)
aux.append(new_oid)
params = tuple(aux)
# Execute the query, with or without parameters and return the result
if params:
cursor.execute(q, params)
else:
cursor.execute(q)
oid = new_oid.getvalue()[0]
# If we're in autocommit mode (true by default), commit the operation
# This will be false if we're inside a transaction
if autocommit:
conn.commit()
# Return the ID of the row that was modified or inserted
#return cursor.lastrowid
#cursor.execute("select sec_despacho.currval from DUAL")
#res = cursor.fetchall()[0][0]
return oid
except Exception as exc:
# Rollback the operation if we're under autocommit mode and
# wrap the exception in a DALException
if autocommit:
conn.rollback()
raise DALException(exc) from exc
finally:
# Close the cursor
cursor.close()
def create_database(verbose=False):
conn = get_conn()
cursor = conn.cursor()
with open("create_database.sql", "r", encoding="utf-8") as f:
for stmt in f.read().split(";"):
if not stmt or stmt.strip() == "": continue # Skip blank lines
if verbose: print(stmt + ";")
cursor.execute(stmt)
conn.commit()
cursor.close()
def create_database(verbose=False):
conn = get_conn()
cursor = conn.cursor()
for script in DB_CONFIG["sql_scripts"]:
print(f"Executing {script}:")
with open(f"sql/{script}", "r", encoding="utf-8") as f:
for stmt in f.read().split(";"):
if not stmt or stmt.strip() == "": continue # Skip blank lines
if verbose: print(stmt + ";")
cursor.execute(stmt)
conn.commit()
cursor.close()
def query(self, q, params=None):
# Fetch the connection and get a cursor
conn = get_conn()
cursor = conn.cursor(DictCursor)
try:
# Execute the query, with or without parameters and return the result
if params:
cursor.execute(q, params)
else:
cursor.execute(q)
res = cursor.fetchall()
return res
except Exception as exc:
# If anything happens, wrap the exceptions in a DALException
raise DALException(exc) from exc
finally:
# Close the cursor
cursor.close()
def execute(self, q, params=None):
conn = get_conn()
cursor = conn.cursor(DictCursor)
# Check whether we are under autocommit mode or not
# (it will be false inside a transaction)
try:
autocommit = get_g().get("autocommit", True)
except RuntimeError:
# Allow this code to run outside the application context
# (useful for populate_database.py and tests)
autocommit = True
# Fetch the connection and get a cursor
try:
# Execute the query, with or without parameters and return the result
if params:
cursor.execute(q, params)
else:
cursor.execute(q)
# If we're in autocommit mode (true by default), commit the operation
# This will be false if we're inside a transaction
if autocommit:
conn.commit()
# Return the ID of the row that was modified or inserted
return cursor.lastrowid
except Exception as exc:
# Rollback the operation if we're under autocommit mode and
# wrap the exception in a DALException
if autocommit:
conn.rollback()
raise DALException(exc) from exc
finally:
# Close the cursor
cursor.close()
def _test_teardown():
# Roll back the data modified by every test
get_conn().rollback()