class SQLiteDatabase: def __init__(self, env): self.config_loader = ConfigLoader(env) self.db_filename = self.config_loader.get_db_filename() logging.basicConfig(filename=self.config_loader.get_log_filename(), level=logging.INFO, format="[%(levelname)s] %(asctime)s: %(message)s") try: # sqlite will create a new db if file doesn't exist, check before connecting open(self.db_filename, "r").close() except IOError: raise IOError("Couldn't find database file.") def connect(self): self.connection = sqlite3.connect(self.db_filename) self.connection.text_factory = str self.cursor = self.connection.cursor() logging.info("Database connection established.") def close(self): self.cursor.close() self.connection.close() logging.info("Database connection closed.") def run_sql(self, sql, parameters): self.cursor.execute(sql, parameters) return self.cursor.fetchall() def autocomplete_table(self, incomplete_name): table_name_matches = self.run_sql("SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE ?", [incomplete_name + "%"]) if len(table_name_matches) == 1: return table_name_matches[0][0] else: raise ValueError("Found no exact match for the given table name fragment: %s" % incomplete_name) def autocomplete_column_from_table(self, table_name, incomplete_name): columns_in_table = self.run_sql("PRAGMA table_info(%s)" % table_name, "") # result column header cid|name|type|notnull|dflt_value|pk column_name_matches = [column_line[1] for column_line in columns_in_table if column_line[1].startswith(incomplete_name)] if len(column_name_matches) == 1: return column_name_matches[0] else: raise ValueError("Found no exact match for the given column name fragment: %s" % incomplete_name) def get_primary_key_for(self, table_name): columns_in_table = self.run_sql("PRAGMA table_info(%s)" % table_name, "") # result column header cid|name|type|notnull|dflt_value|pk primary_key_line = next(column for column in columns_in_table if column[5] == 1) return primary_key_line[1] def get_table_list(self): return [table_line[0] for table_line in self.run_sql("SELECT name FROM sqlite_master WHERE type = 'table'", "")]