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'", "")]
