def execute_query(self, query=None, data=None):
'''Executes the insert/update sql query.'''
if DatabaseConnection.__connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.__connection.cursor()
cursor.execute(query, data)
DatabaseConnection.__connection.commit()
cursor.close()
def query_all_result(self, query=None, data=None):
'''Executes the sql query and returns all the results of the table.'''
if DatabaseConnection.connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.connection.cursor(dictionary=True)
cursor.execute(query, data)
resultSet = cursor.fetchall()
cursor.close()
return resultSet
def query_many_result(self, query=None, count=1, data=None):
'''Executes the sql query and returns a max number of result (for pagination).'''
if DatabaseConnection.connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.connection.cursor(dictionary=True)
cursor.execute(query, data)
resultSet = cursor.fetchmany(count)
cursor.close()
return resultSet
def query_single_result(self, query=None, data=None):
'''Executes the sql query and returns a single row of data.'''
if DatabaseConnection.connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.connection.cursor(dictionary=True)
cursor.execute(query, data)
row = cursor.fetchone()
cursor.close()
return row
def update_one(self, query=None, data=None):
'''Executes the insert/update sql query.'''
if DatabaseConnection.connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.connection.cursor()
cursor.execute(query, data)
row_id = cursor.lastrowid
DatabaseConnection.connection.commit()
cursor.close()
return row_id
def count_entries(self, table_name):
query = "SELECT COUNT(*) FROM {}".format(table_name)
if DatabaseConnection.connection is None:
printt_error("Database connection is null.")
cursor = DatabaseConnection.connection.cursor()
cursor.execute(query)
cursor.fetchall()
rowCount = cursor.rowcount
cursor.close()
return rowCount
def __init__(self):
'''Creates and opens a single db connection.'''
if DatabaseConnection.connection is None:
db_config = get_db_properties()
try:
DatabaseConnection.connection = connection.MySQLConnection(
user=db_config["dbUsername"],
password=db_config["dbPassword"],
host=db_config["dbHost"],
database=db_config["dbName"])
DatabaseConnection.connection.autocommit = True
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
printt_error("Invalid username or password.")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
printt_error("Database does not exist.")
else:
printt_error(str(err))
return
def validate_schema(db_schema, tbl_name):
"""Validates that the table schema is sensible."""
# Validates that table exists.
if tbl_name not in db_schema:
printt_error(
"'{}' object could not be found in table definition json.".format(
tbl_name))
return False
# Validates that columns are sensible.
tbl_json = db_schema[tbl_name]
for col in tbl_json["fields"]:
# Validates that the name exists.
if not col["name"]:
printt_error(
"Name must be provided in column definition for table '{}'.".
format(tbl_name))
return False
# Validates that the data type exists.
if not col["type"] or col["type"] not in SQL_DATA_TYPES:
printt_error(
"Valid type must be provided for '{}' column in table '{}'.".
format(col["name"], tbl_name))
return False
# Validates there is a default value for column.
if "default" in col and "value" not in col["default"]:
printt_error(
"A default value must be provided for column '{}' in '{}'.".
format(col["name"], tbl_name))
return False
# Validates that FK is a valid entity in the db schema.
if "references" in col:
column_name = col["name"]
ref_table = col["references"]["ref_table"]
ref_column = col["references"]["ref_column"]
if ref_table in db_schema:
if not any(x for x in db_schema[ref_table]["fields"]
if x["name"] == ref_column):
printt_error("'{}' table does have '{}' column.".format(
ref_table, ref_column))
return False
else:
printt_error("'{}' table does not exists.".format(ref_table))
return False
return True