def db_connect(self,
user=None,
password=None,
database=None,
host=None,
**kwargs) -> bool:
"""
Connect to a local MySQL/Mariadb database.
:param user: User name to connect to database with.
:param password: Password to connect to database with.
:param database: Database name.
:param host: Database host name or ip address.
:param kwargs: Additonal named arguments to pass to MySQLdb connection.
:return: True if connected otherwise False.
"""
try:
self._handle = mysql.connect(user=user,
passwd=password,
db=database,
host=host,
**kwargs)
self._connected = True
return True
except Exception as e:
raise NotConnectedError(
"Error: Connection attempt to database failed. \n{0}".format(
e))
def db_connect(self, alt_db_path=None) -> bool:
"""
Connect to a local sqlite3 database
:param alt_db_path: Alternate database path to use besides hardcoded path
:return: True if connected otherwise False
"""
db_path = self._db_path
if alt_db_path:
db_path = alt_db_path
if os.path.exists(db_path):
self._db_path = db_path
else:
raise FileNotFoundError(
'database path not found ({0})'.format(db_path))
try:
self._handle = sqlite3.connect(db_path)
self._handle.row_factory = dict_factory
self._connected = True
return True
except Exception as e:
raise NotConnectedError("connection attempt to database failed")
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_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_cursor(self):
"""
Return a mysql connection cursor object
:return: Cursor object """
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
cursor = self._handle.cursor()
return cursor
def db_exec_select_by_id_all(self, table: str, pk: int) -> dict:
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
tmp_l = list()
tmp_l.append(pk)
return self.db_exec_select(
"SELECT * FROM {0} WHERE id = ?".format(table), tuple(tmp_l))
def db_attach_database(self, alias: str, db_path: str = None) -> bool:
"""
Attach a database to the current connection with the specified alias
:param alias: alias of connected database
:param db_path: path to database
:return: True if attached, otherwise false
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
if not db_path or not os.path.exists(db_path):
raise NotConnectedError("database path given to attach is invalid")
stmt = "ATTACH DATABASE ? AS ?"
args = {
"db_path": db_path,
"alias": db_path,
}
return self.db_exec(stmt, args)
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_get_table_record_count(self, table: str):
""" return the record count of a table """
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
data = self.db_exec_select(
"SELECT COUNT(1) AS count FROM {0}".format(table))
if data:
return int(data['count'])
return 0
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_detach_database(self, alias: str) -> bool:
"""
Detach an attached database on the current connection
:param alias: alias of connected database
:return: True if attached, otherwise false
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
sql = 'DETACH DATABASE ?'
args = {
"alias": alias,
}
return self.db_exec(sql, args)
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_test_connection(self) -> bool:
"""
Test the connection for connection errors with a simple Select statement
:return: True if connection is good otherwise False, exception message
"""
if self.db_connected() is False:
raise NotConnectedError("not connected to a database")
try:
sql = 'SELECT * FROM sqlite_master LIMIT 1;'
cursor = self._handle.cursor()
cursor.execute(sql)
cursor.close()
except Exception as e:
raise ConnectionFailedError(e)
return True
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