def fill_spn_pool(self): """ fill pool value """ dba = DatabaseAccess(self.config) self.gui.set_pool(dba.get_pool()) dba = None
def create_statements(self, input_fields): """ Creates the records needed for Table.CURRENCY_EXCHANGE. """ try: dba = DatabaseAccess(self.config) statement_currency_exchange = Statement(T_CURRENCY_EXCHANGE) date_created = current_date() date_modified = current_date() records = 0 for fields in input_fields: records = records + 1 #NOTE: we don't need to query, because we always add a new #currency_exchange line. The same value can be used multiple #times, so it's not possible to query if one already exists. statement_currency_exchange.add( records, { 'currency_exchange_id': None, 'currency_from_id': dba.currency_id_from_currency( fields[Input.CURRENCY_FROM]), 'currency_to_id': dba.currency_id_from_currency( fields[Input.CURRENCY_TO]), 'exchange_rate': Decimal(fields[Input.EXCHANGE_RATE]), 'date_created': date_created, 'date_modified': date_modified } ) return statement_currency_exchange except Exception as ex: print Error.CREATE_STATEMENTS_TABLE_CURRENCY_EXCHANGE, ex finally: dba = None
def filltxt_commodity_description(self): """ fill commodity description """ dba = DatabaseAccess(self.config) self.gui.set_commodity_description( dba.get_commodity_description(self.gui.get_commodity_name())) dba = None
def filltxt_market_description(self): """ fill market description """ dba = DatabaseAccess(self.config) self.gui.set_market_description( dba.get_market_description(self.gui.get_market_code())) dba = None
def fillcmb_commodity_name(self): """ fill cmb function """ dba = DatabaseAccess(self.config) self.gui.clear_cmb_commodity_name() for name in dba.get_commodity_names(self.gui.get_market_code()): self.gui.add_commodity_name(name) dba = None
def get_parameter_value(self, parameter_index): """ Get the parameter value from T_PARAMETER. Note: A final conversion needs to be done to the correct type, as this returns a string! """ dba = DatabaseAccess(self.config) result = dba.get_parameter_value(parameter_index) dba = None return result
def write_to_database(self, statements): """ Call to write the statements to the database. """ try: dba = DatabaseAccess(self.config) dba.write_to_database(statements) except Exception as ex: print Error.WRITE_TO_DATABASE_CORE, ex finally: dba = None
def csv_export(self): """ Export all data to csv-files. """ try: #TODO: write to csv like this: #records = session.Query(MyModel).all() #[ outcsv.writerow(curr.field_one, curr.field_two) for curr in records ] ## or maybe use outcsv.writerows(records) #outfile.close() dba = DatabaseAccess(self.config) try: #Prepare export dir exportdir = self.config.exportdir if not os.path.isdir(exportdir): os.makedirs(exportdir) #Create subdir to store the txt-files in subdir = os.path.join(exportdir, 'export_' + current_date('%Y-%m-%d_%H%M%S')) if not os.path.isdir(subdir): os.makedirs(subdir) print "Retrieving table records from database..." #Export all tables that are loaded by the ORM #But views where created per table to have more control: #to limit the export, only the views need to be updated. viewnames = [] for tablename in dba.tables: viewnames.append(tablename.upper().replace('T_', 'V_')) for viewname in viewnames: # use the viewname for this function, # the tablename to create the file tablename = viewname.replace('V_', 'T_') exportpath = os.path.join(subdir, tablename) exportfile = open(exportpath, 'w') outcsv = csv.writer(exportfile) records = dba.export_records(dba.loaded_objects[viewname]) # write column names on first row i = 0 if i == 0: outcsv.writerow([column.name for column in dba.loaded_objects[viewname].c]) i = i + 1 # write records for record in records: outcsv.writerow([ getattr(record, column.name) for column in dba.loaded_objects[viewname].c ]) print "Writing", len(records), "records for", tablename, "to file", exportpath, "..." exportfile.close() #Note: no need to close the csv, because it's just a parser #that uses exportfile as an underlying file. Closing exportfile #is all you need to do. print "Done." finally: dba = None except Exception as ex: print "Error in csv_export: ", ex
def get_account_totals_from_input_fields(self, input_fields): """ Returns a list with the account name / total pairs, only taking into consideration what's in the input_fields. """ # TODO: this was for V_rep_cheCK I think, this needs to be fixed! # So create a new V_REP_CHECK view! values = [] dba = DatabaseAccess(self.config) for account in dba.get_accounts(): values.append([account, self.get_account_total_from_input_fields( account, input_fields)]) dba = None return values
def set_info_details(self): """ Update infolabel details. """ dba = DatabaseAccess(self.config) account_from = self.gui.get_account_from() account_to = self.gui.get_account_to() commodity = self.gui.get_commodity_name() if deals_with_commodities(account_from, account_to) and not commodity: info = dba.get_commodity_info(commodity) self.gui.set_info_details('{} ({}): {}'.format( info[1], ''.join(info[2].split()), info[0])) else: self.gui.set_info_details('') dba = None
def update_accounts_for_commodities(self, market, new_commodity): """ Create a long and short account for a new commodity. """ dba = DatabaseAccess(self.config) dba.save_new_account( 'assets:current_assets:commodities:{} (long)'.format( new_commodity.lower() ) ) dba.save_new_account( 'assets:current_assets:commodities:{} (short)'.format( new_commodity.lower() ) ) dba = None
def create_statements(self, input_fields): """ Creates the records needed for Table.FINANCE and returns them as a Statement object. """ try: dba = DatabaseAccess(self.config) date_created = current_date() date_modified = current_date() statement_finance = Statement(T_FINANCE) records = 0 currency_exchange_id = dba.first_currency_exchange_id_from_latest() rate_id = dba.first_rate_id_from_latest() for fields in input_fields: account_from_id = dba.account_id_from_account_name( fields[Input.ACCOUNT_FROM], True) account_to_id = dba.account_id_from_account_name( fields[Input.ACCOUNT_TO], False) #NOTE: in the database, the first values in the tables of the #below id's, are empty/dummy values, used for when we are not #dealing with stocks. rate_id = 1 if deals_with_commodities( fields[Input.ACCOUNT_FROM], fields[Input.ACCOUNT_TO] ): rate_id = dba.get_latest_rate_id() amount_value = fields[Input.AMOUNT] if is_negative_amount(fields[Input.ACCOUNT_FROM]): amount_value = Decimal(-1.0) * amount_value records = records + 1 statement_finance.add( records, { 'finance_id': None, 'date': fields[Input.DATE], 'year': fields[Input.DATE].year, 'month': fields[Input.DATE].month, 'day': fields[Input.DATE].day, 'account_from_id': account_from_id, 'account_to_id': account_to_id, 'amount': amount_value, 'comment': fields[Input.COMMENT], 'currency_exchange_id': currency_exchange_id, 'rate_id': rate_id, 'active': 1, 'date_created': date_created, 'date_modified': date_modified } ) currency_exchange_id = currency_exchange_id + 1 print 'test: ', statement_finance.statements_insert return statement_finance except Exception as ex: print Error.CREATE_STATEMENTS_TABLE_FINANCE, ex finally: dba = None
def create_statements(self, input_fields, statements_finance): """ Creates the records needed for Table.TRADE and returns them as a Statement object. """ #NOTE: price_buy will be fields['i_price'] #When we buy more, it will be overwritten! #Trading without adding to positions is assumed by this code! try: dba = DatabaseAccess(self.config) calc = CalculatorFinance() self.date_created = current_date() self.date_modified = current_date() records = 0 self.finance_id = dba.first_finance_id_from_latest() if self.finance_id != -1: for fields in input_fields: if deals_with_commodities( fields[Input.ACCOUNT_FROM], fields[Input.ACCOUNT_TO] ): records = records + 1 # GENERAL INFO AT START self.general_info_at_start(dba, calc, fields) # UPDATE/INSERT print "test: invade_started = ", (self.open_trade_position > -1) if self.open_trade_position > -1: self.update_info(dba, calc, fields) else: self.insert_info(dba, calc, fields) # GENERAL VARIABLES THAT CAN BE CALCULATED # ON THE DATA WE HAVE self.general_info_at_end(dba, fields, self.trade_record) # TEST INFO #self.print_test_info() # ADDING THE STATEMENTS self.add_to_statement(records) self.finance_id = self.finance_id + 1 return self.statement_trade except Exception as ex: print Error.CREATE_STATEMENTS_TABLE_TRADE, ex finally: calc = None dba = None
def get_account_totals_from_input_fields(self, input_fields): """ Returns a list with the account name / total pairs, only taking into consideration what's in the input_fields. """ # TODO: this was for V_rep_cheCK I think, this needs to be fixed! # So create a new V_REP_CHECK view! values = [] dba = DatabaseAccess(self.config) for account in dba.get_accounts(): values.append([ account, self.get_account_total_from_input_fields( account, input_fields) ]) dba = None return values
def create_statements(self, input_fields, statements_finance): """ Creates the records needed for Table.TRADE and returns them as a Statement object. """ #NOTE: price_buy will be fields['i_price'] #When we buy more, it will be overwritten! #Trading without adding to positions is assumed by this code! try: dba = DatabaseAccess(self.config) calc = CalculatorFinance() self.date_created = current_date() self.date_modified = current_date() records = 0 self.finance_id = dba.first_finance_id_from_latest() if self.finance_id != -1: for fields in input_fields: if deals_with_commodities(fields[Input.ACCOUNT_FROM], fields[Input.ACCOUNT_TO]): records = records + 1 # GENERAL INFO AT START self.general_info_at_start(dba, calc, fields) # UPDATE/INSERT print "test: invade_started = ", (self.open_trade_position > -1) if self.open_trade_position > -1: self.update_info(dba, calc, fields) else: self.insert_info(dba, calc, fields) # GENERAL VARIABLES THAT CAN BE CALCULATED # ON THE DATA WE HAVE self.general_info_at_end(dba, fields, self.trade_record) # TEST INFO #self.print_test_info() # ADDING THE STATEMENTS self.add_to_statement(records) self.finance_id = self.finance_id + 1 return self.statement_trade except Exception as ex: print Error.CREATE_STATEMENTS_TABLE_TRADE, ex finally: calc = None dba = None
def set_info_details(self): """ Update infolabel details. """ dba = DatabaseAccess(self.config) account_from = self.gui.get_account_from() account_to = self.gui.get_account_to() commodity = self.gui.get_commodity_name() if deals_with_commodities(account_from, account_to) and not commodity: info = dba.get_commodity_info(commodity) self.gui.set_info_details( '{} ({}): {}'.format( info[1], ''.join(info[2].split() ), info[0] ) ) else: self.gui.set_info_details('') dba = None
def init_display_data(self): """ fill in the combo boxes with values. """ dba = DatabaseAccess(self.config) # Accounts for acc in dba.get_account_list(): self.gui.add_account_from(acc) self.gui.add_account_to(acc) # Market codes for mcd in dba.get_markets(): self.gui.add_market_code(mcd) # Currencies for currency in dba.get_currencies(): self.gui.add_currency_from(currency) self.gui.add_currency_to(currency) # Commodity names self.fillcmb_commodity_name() self.filltxt_market_description() self.filltxt_commodity_description() # Pool self.fill_spn_pool() dba = None
def create_statements(self, input_fields): """ Creates the records needed for Table.CURRENCY_EXCHANGE. """ try: dba = DatabaseAccess(self.config) statement_currency_exchange = Statement(T_CURRENCY_EXCHANGE) date_created = current_date() date_modified = current_date() records = 0 for fields in input_fields: records = records + 1 #NOTE: we don't need to query, because we always add a new #currency_exchange line. The same value can be used multiple #times, so it's not possible to query if one already exists. statement_currency_exchange.add( records, { 'currency_exchange_id': None, 'currency_from_id': dba.currency_id_from_currency( fields[Input.CURRENCY_FROM]), 'currency_to_id': dba.currency_id_from_currency( fields[Input.CURRENCY_TO]), 'exchange_rate': Decimal(fields[Input.EXCHANGE_RATE]), 'date_created': date_created, 'date_modified': date_modified }) return statement_currency_exchange except Exception as ex: print Error.CREATE_STATEMENTS_TABLE_CURRENCY_EXCHANGE, ex finally: dba = None
def update_accounts_for_commodities(self, market, new_commodity): """ Create a long and short account for a new commodity. """ dba = DatabaseAccess(self.config) dba.save_new_account( 'assets:current_assets:commodities:{} (long)'.format( new_commodity.lower())) dba.save_new_account( 'assets:current_assets:commodities:{} (short)'.format( new_commodity.lower())) dba = None
def file_import(self): """ Parse textfiles and insert data in db. """ try: dba = DatabaseAccess(self.config) importdir = self.config.importdir print importdir + ' -> ' + self.config.dbhost + '/' + self.config.dbname + ': ' for root, dirs, files in os.walk(importdir): try: for filename in files: print 'Importing table', filename + ':' #, end = ' ' # end code from python3 source = open( os.path.join(importdir, root[len(importdir):], filename), 'r') # assume first line is header csv_ = csv.DictReader(source, delimiter=',') i = 0 for row in csv_: #insert data in table #source.name should be the filename = e.g. T_ACCOUNT table = dba.loaded_objects[filename] table.insert().values(**row).execute() i = i + 1 sys.stdout.flush() sleep(0.001) print str( i ), 'rows imported...' #, end = ' ' # end code from python3 print '[OK]' except Exception as ex: print '[Error!]' print "Error in for loop: ", ex break finally: source.close() print '' except Exception as ex: print '' print "Error in file_import:", ex finally: dba = None
def csv_export(self): """ Export all data to csv-files. """ try: #TODO: write to csv like this: #records = session.Query(MyModel).all() #[ outcsv.writerow(curr.field_one, curr.field_two) for curr in records ] ## or maybe use outcsv.writerows(records) #outfile.close() dba = DatabaseAccess(self.config) try: #Prepare export dir exportdir = self.config.exportdir if not os.path.isdir(exportdir): os.makedirs(exportdir) #Create subdir to store the txt-files in subdir = os.path.join( exportdir, 'export_' + current_date('%Y-%m-%d_%H%M%S')) if not os.path.isdir(subdir): os.makedirs(subdir) print "Retrieving table records from database..." #Export all tables that are loaded by the ORM #But views where created per table to have more control: #to limit the export, only the views need to be updated. viewnames = [] for tablename in dba.tables: viewnames.append(tablename.upper().replace('T_', 'V_')) for viewname in viewnames: # use the viewname for this function, # the tablename to create the file tablename = viewname.replace('V_', 'T_') exportpath = os.path.join(subdir, tablename) exportfile = open(exportpath, 'w') outcsv = csv.writer(exportfile) records = dba.export_records(dba.loaded_objects[viewname]) # write column names on first row i = 0 if i == 0: outcsv.writerow([ column.name for column in dba.loaded_objects[viewname].c ]) i = i + 1 # write records for record in records: outcsv.writerow([ getattr(record, column.name) for column in dba.loaded_objects[viewname].c ]) print "Writing", len( records ), "records for", tablename, "to file", exportpath, "..." exportfile.close() #Note: no need to close the csv, because it's just a parser #that uses exportfile as an underlying file. Closing exportfile #is all you need to do. print "Done." finally: dba = None except Exception as ex: print "Error in csv_export: ", ex