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
