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