def db_exec_commit(self, stmt, args: Union[dict, list] = None) -> int:
"""
Execute sql statement and commit.
:param stmt: SQL statement
:param args: List or dictionary of parameterized arguments.
:return: Last row id or 1.
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not stmt:
raise InvalidStatementError('sql statement is missing')
# Convert dict to list
if args and isinstance(args, collections.abc.Mapping):
args = list(args.values())
try:
cursor = self._handle.cursor()
cursor.execute(stmt, args)
lastrowid = cursor.lastrowid
self._handle.commit()
cursor.close()
return lastrowid if lastrowid else 1
except Exception as e:
raise ExecStatementFailedError(e)
def db_exec_stmt(self, stmt: str, args: dict = None) -> dict:
"""
Execute a select statement
:param stmt: sql statement
:param args: argument list
:return: sqlite cursor or none
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not stmt:
raise InvalidStatementError('sql statement is missing')
try:
cursor = self._handle.cursor()
cursor.execute(stmt, tuple(args.values()))
data = cursor.fetchall()
cursor.close()
return data
except Exception as e:
raise ExecStatementFailedError(e)
def db_exec_stmt(self, stmt: str, args: Union[dict, list] = None) -> dict:
"""
Execute a statement that returns data.
:param stmt: SQL statement
:param args: List or dictionary of parameterized arguments.
:return: cursor or none
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not stmt:
raise InvalidStatementError('sql statement is missing')
# Convert dict to list
if args and isinstance(args, collections.abc.Mapping):
args = args.values()
try:
cursor = self._handle.cursor()
cursor.execute(stmt, args)
data = cursor.fetchall()
fields = list()
if cursor.description is not None:
for x in range(len(cursor.description)):
fields.append(cursor.description[x][0])
new_data = list()
for row in data:
d = dict()
for idx, col in enumerate(fields):
d[col] = row[idx]
new_data.append(d)
data = new_data
cursor.close()
# TODO: If cursor.description is not None, convert row tuples to dict
# https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-description.html
return data
except Exception as e:
raise ExecStatementFailedError(e)
def db_commit(self) -> bool:
"""
Call database commit
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
try:
self._handle.commit()
except Exception as e:
raise ExecStatementFailedError(e)
return True
def db_exec(self, stmt: str, args: dict = None) -> bool:
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
try:
cursor = self._handle.cursor()
cursor.execute(stmt, tuple(args.values()))
cursor.close()
self._handle.commit()
except Exception as e:
raise ExecStatementFailedError(e)
return True
def db_callproc(self, proc: str, args: Union[dict, list] = None) -> dict:
"""
Call a database stored procedure.
:param proc: procedure name
:param args: arguments to procedure.
:return: dict
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not proc:
raise ValueError('Procedure name must not be empty.')
# Convert dict to list
if args and isinstance(args, collections.abc.Mapping):
args = args.values()
try:
cursor = self._handle.cursor()
cursor.callproc(proc, args)
data = cursor.fetchall()
fields = list()
if cursor.description is not None:
for x in range(len(cursor.description)):
fields.append(cursor.description[x][0])
new_data = list()
for row in data:
d = dict()
for idx, col in enumerate(fields):
d[col] = row[idx]
new_data.append(d)
data = new_data
cursor.close()
return data
except Exception as e:
raise ExecStatementFailedError(e)
def db_exec_commit(self, stmt: str, args: dict = None) -> int:
"""
Execute sql statement and commit
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not stmt:
raise InvalidStatementError('sql statement is missing')
try:
cursor = self._handle.cursor()
cursor.execute(stmt, tuple(args.values()))
lastrowid = cursor.lastrowid
self._handle.commit()
cursor.close()
return lastrowid if lastrowid else 1
except Exception as e:
raise ExecStatementFailedError(e)
def db_exec(self, stmt: str, args: Union[dict, list] = None) -> bool:
"""
Execute a SQL Statement that returns no data.
:param stmt: SQL Statement to execute.
:param args: List or dictionary of parameterized arguments.
:return: True if successful, otherwise False.
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
# Convert dict to list
if args and isinstance(args, collections.abc.Mapping):
args = args.values()
try:
cursor = self._handle.cursor()
cursor.execute(stmt, args)
cursor.close()
self._handle.commit()
except Exception as e:
raise ExecStatementFailedError(e)
return True