class MoneyController(object): """ The MoneyController() handles database connections/calls and writes data back to the GUI. """ def __init__(self, database_name='money.db', path='', debug=False): self.database = None self.debug = debug self.database_name = database_name self.path = path self.logger = logging.getLogger('money controller') self.logger.setLevel(logging.DEBUG) logname = os.path.join(os.path.abspath(os.path.dirname(self.path)), ('money.log')) file_handler = RotatingFileHandler(logname, maxBytes=1000000, backupCount=2) file_handler.setLevel(logging.DEBUG) # console logger with higher logging level console_handler = logging.StreamHandler() console_handler.setLevel(logging.DEBUG) formatter = logging.Formatter( '%(asctime)s,%(msecs)d %(name)s %(levelname)s %(message)s') file_handler.setFormatter(formatter) console_handler.setFormatter(formatter) # add the handlers to the logger self.logger.addHandler(file_handler) self.logger.addHandler(console_handler) self.filechecker = fc.fileChecker(self.logger) @printException @dbConnectAndClose def initialize_table(self, table=''): """ ===initialize_table(self)=== The database is initialised by means of selected table (GUI). Possible categories are read from the database. Get and show the last 100 entries. """ self.logger.debug(table) self.database.initialize(table) def initialize_db(self, database_name): # print"===initialize db===" if not len(database_name) == 0: self.database = Database(name=database_name, debug=True) @printException @dbConnectAndClose def query(self, sql): self.database.query(sql) return self.database.fetchall() # initialize with data from file @printException @dbConnectAndClose def get_all_categories(self, table=''): if not table: table = self.database.table sql = 'SELECT distinct(category) FROM ' + table + ' ORDER BY category COLLATE NOCASE ASC' self.database.query(sql) return self.database.fetchall() @printException @dbConnectAndClose def get_all_tables(self): return self.database.getAllTables() @printException @dbConnectAndClose def get_latest_from(self, table): # print 'colums from selected table: ', self.database.fieldnames # sql = 'SELECT ' + self.database.getColumnsSQL() + ' FROM ' + table + ' ORDER BY id DESC LIMIT 100' columns = self.database.getColumnsSQL() self.logger.error(columns) sql = 'SELECT {0} FROM {1} ORDER BY created DESC LIMIT 25'.format( columns, table) self.database.query(sql) return self.database.fetchall() @printException @dbConnectAndClose def get_all_from(self, table=''): """ sort by created """ # value, created, category, description columns = self.database.get_columns_without_id_sql(table) sql = 'SELECT {0} FROM {1} ORDER BY created ASC'.format(columns, table) self.database.query(sql) return self.database.fetchall() @printException @dbConnectAndClose def sum_values_for_categories_in(self, table, categories): columns = 'sum(value)' args_str = tuple('?') * len(categories) sql = 'SELECT {0} FROM {1} WHERE category in ({2})'.format( columns, table, ','.join(args_str)) self.database.query(sql, categories) return self.database.fetchone()[0] @printException @dbConnectAndClose def insert_into_db(self, args): self.database.insert(table='', cols='', args=args) @printException @dbConnectAndClose def fill_from_csv(self, path): if self.filechecker.exists(path): # print path, 'exists' from_csv.populate(path, self.database) def validate_input(self, ddate, value, category, description): from utils.input_client import check_date, check_value from utils.input_client import check_category, check_description try: ddate = check_date(value=ddate) value = check_value(value=value) category = check_category(value=category) description = check_description(value=description) args = [(ddate, value, category, description)] return args except AssertionError as e: self.logger.error(e) except ValueError as e: self.logger.error(e) except Exception as e: self.logger.error(e) return None