def execute_sql_select(cursor=None, sql_query=None): """ Executes SQL SELECT queries. :param cursor: Database cursor :param sql_query: SQl query to execute :return: Database cursor and result of database query """ if cursor is None: raise AttributeError("Provide cursor as parameter") if sql_query is None: raise AttributeError("Provide sql_query as parameter") try: cursor.execute(sql_query) except Exception as exp: exp = append_description_to_exception(exp=exp, description='Error in SQL SELECT query execution') logger.error('Error in SQL query execution: ' + repr(exp)) raise else: logger.debug('SQL query executed') try: data = cursor.fetchall() except Exception as exp: exp = append_description_to_exception(exp=exp, description='cursor.fetchall() failed') logger.error('cursor.fetchall() failed: ' + repr(exp)) raise else: logger.debug('Data fetched') return cursor, data
def clear_apikey_sqlite_db(): """ Initializes SQLite database. :param connection: Database connection object :return: Database connection object """ try: connection = get_sqlite_connection() except Exception as exp: exp = append_description_to_exception(exp=exp, description='Could not get database connection') logger.error('get_sqlite_connection: ' + repr(exp)) raise sql_query = '''DELETE FROM api_keys WHERE account_id > 3;''' try: logger.info('Clearing database') logger.debug('Executing: ' + str(sql_query)) connection.execute(sql_query) except Exception as exp: exp = append_description_to_exception(exp=exp, description='Could not clear database') logger.error('connection.execute(sql): ' + repr(exp)) connection.rollback() raise else: connection.commit() connection.close() logger.info('Database cleared') return True
def get_sqlite_connection(): """ Get connection for SQLite Database :return: Database connection object """ if(os.path.exists(DATABASE) and os.path.isfile(DATABASE)): logger.debug("init_db = False") init_db = False else: logger.debug("init_db = True") init_db = True try: connection = sqlite3.connect(DATABASE) except Exception as exp: exp = append_description_to_exception(exp=exp, description="Could not get database connection. Could not open db file.") logger.error('sqlite3.connect(' + DATABASE + '): ' + repr(exp)) raise else: if init_db: try: connection = init_sqlite_db(connection=connection) except Exception: raise logger.debug('DB connection at ' + repr(connection)) return connection
def init_sqlite_db(connection=None): """ Initializes SQLite database. :param connection: Database connection object :return: Database connection object """ if connection is None: raise AttributeError("Provide connection as parameter") sql_query = '''CREATE TABLE api_keys ( id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER UNIQUE NOT NULL, api_key BLOB NOT NULL );''' try: logger.debug('Initializing database') connection.execute(sql_query) except Exception as exp: exp = append_description_to_exception(exp=exp, description='Could not initialize database') logger.error('connection.execute(sql): ' + repr(exp)) connection.rollback() raise else: connection.commit() logger.debug('Database initialized') return connection
def store_api_key_to_db(account_id=None, account_api_key=None, cursor=None): """ Store API Key to DB :param account_id: User account ID :param account_api_key: API Key :param cursor: Database cursor :return: Database cursor and last inserted row id """ if account_id is None: raise AttributeError("Provide account_id as parameter") if account_api_key is None: raise AttributeError("Provide account_api_key as parameter") if cursor is None: raise AttributeError("Provide cursor as parameter") sql_query = "INSERT INTO api_keys (account_id, api_key) VALUES ('%s', '%s')" % \ (account_id, account_api_key) try: cursor, last_id = execute_sql_insert(cursor=cursor, sql_query=sql_query) except Exception as exp: exp = append_description_to_exception(exp=exp, description='Could not store API Key to Database') logger.error('Could not store API Key to Database: ' + repr(exp)) logger.debug('sql_query: ' + repr(sql_query)) raise else: return cursor, last_id
def get_api_key(account_id=None, cursor=None): """ Get API key from DB :param account_id: ID of user account :param cursor: Database cursor :return: Database cursot, JWK Object and Key ID """ if account_id is None: raise AttributeError("Provide account_id as parameter") if cursor is None: raise AttributeError("Provide cursor as parameter") api_key_dict = {} sql_query = "SELECT id, account_id, api_key FROM api_keys WHERE account_id='%s' ORDER BY id DESC LIMIT 1" % ( account_id) try: cursor, data = execute_sql_select(sql_query=sql_query, cursor=cursor) except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not fetch APi Key from database') logger.error('Could not fetch APi Key from database: ' + repr(exp)) logger.debug('sql_query: ' + repr(sql_query)) raise else: logger.debug("APi Key fetched from database") try: api_key_dict['id'] = data[0][0] api_key_dict['account_id'] = data[0][1] api_key_dict['api_key'] = data[0][2] except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not move database response to new dict') logger.error('APi Key for account not found from database: ' + repr(exp)) raise ApiKeyNotFoundError( "Api Key for account not found from database") else: logger.info("API Key Fetched") log_dict_as_json(data=api_key_dict) return cursor, api_key_dict['api_key']
def get_account_id(api_key=None, cursor=None): """ Get User account ID from DB :param api_key: Api Key :param cursor: Database cursor :return: Database User account ID """ if api_key is None: raise AttributeError("Provide api_key as parameter") if cursor is None: raise AttributeError("Provide cursor as parameter") api_key_dict = {} sql_query = "SELECT id, account_id, api_key FROM api_keys WHERE api_key='%s' ORDER BY id DESC LIMIT 1" % ( api_key) try: cursor, data = execute_sql_select(sql_query=sql_query, cursor=cursor) except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not fetch Account ID from database') logger.error('Could not fetch Account ID from database: ' + repr(exp)) logger.debug('sql_query: ' + repr(sql_query)) raise else: logger.debug("Account ID fetched from database") try: api_key_dict['id'] = data[0][0] api_key_dict['account_id'] = data[0][1] api_key_dict['api_key'] = data[0][2] except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not move database response to new dict') logger.error('Account ID for Api Key not found from database: ' + repr(exp)) raise AccountIdNotFoundError("Account ID for Api Key not found") else: logger.info("Account ID Fetched") log_dict_as_json(data=api_key_dict) return cursor, api_key_dict['account_id']
def delete_entry_from_apikey_sqlite_db(account_id=None): """ Delete entry from ApiKey database. :return: Database connection object """ if account_id is None: raise AttributeError("Provide account_id as parameter") try: account_id = str(account_id) except Exception: raise TypeError("account_id MUST be str, not " + str(type(account_id))) try: connection = get_sqlite_connection() except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not get database connection') logger.error('get_sqlite_connection: ' + repr(exp)) raise sql_query = "DELETE FROM api_keys WHERE account_id='%s';" % (account_id) try: logger.info('Deleting entry') logger.debug('Executing: ' + str(sql_query)) connection.execute(sql_query) except Exception as exp: exp = append_description_to_exception( exp=exp, description='Could not delete entry from database') logger.error('connection.execute(sql): ' + repr(exp)) connection.rollback() raise else: connection.commit() connection.close() logger.info('Entry deleted') return True
def execute_sql_insert(cursor, sql_query): """ Executes SQL INSERT queries. :param cursor: Database cursor :param sql_query: SQl query to execute :return: Database cursor and last inserted row id """ if cursor is None: raise AttributeError("Provide cursor as parameter") if sql_query is None: raise AttributeError("Provide sql_query as parameter") last_id = "" try: cursor.execute(sql_query) except Exception as exp: exp = append_description_to_exception( exp=exp, description='Error in SQL INSERT query execution') logger.error('Error in SQL query execution: ' + repr(exp)) raise else: logger.debug('SQL query executed') try: last_id = str(cursor.lastrowid) except Exception as exp: exp = append_description_to_exception( exp=exp, description='lastrowid not found') logger.error('cursor.lastrowid not found: ' + repr(exp)) logger.info('cursor.lastrowid not found. Using None instead') last_id = None else: logger.debug('cursor.lastrowid: ' + last_id) return cursor, last_id
def get_sqlite_connection(): """ Get connection for SQLite Database :return: Database connection object """ if (os.path.exists(DATABASE) and os.path.isfile(DATABASE)): logger.debug("init_db = False") init_db = False else: logger.debug("init_db = True") init_db = True # If there is no db directory it will be created if DATABASE_DIRECTORY != "./": if not os.path.isdir(DATABASE_DIRECTORY): try: os.mkdir(DATABASE_DIRECTORY) print( "Creating LOG_PATH: '{}'.".format(DATABASE_DIRECTORY)) except IOError: print("LOG_PATH: '{}' already exists.".format( DATABASE_DIRECTORY)) except Exception as e: print( "LOG_PATH: '{}' could not be created. Exception: {}.". format(DATABASE_DIRECTORY, repr(e))) try: connection = sqlite3.connect(DATABASE) except Exception as exp: exp = append_description_to_exception( exp=exp, description= "Could not get database connection. Could not open db file.") logger.error('sqlite3.connect(' + DATABASE + '): ' + repr(exp)) raise else: if init_db: try: connection = init_sqlite_db(connection=connection) except Exception: raise logger.debug('DB connection at ' + repr(connection)) return connection
def get_sqlite_cursor(connection=None): """ Get cursor for SQLite database connection. :param connection: Database connection object :return: Database cursor object and Database connection object """ if connection is None: raise AttributeError("Provide connection as parameter") try: cursor = connection.cursor() except Exception as exp: exp = append_description_to_exception(exp=exp, description='Could not get database cursor') logger.error('connection.cursor(): ' + repr(exp)) raise else: logger.debug('DB cursor at ' + repr(cursor)) return cursor, connection