def sync_ofx(): for connection in db.session.query(Connections).filter(Connections.source == 'ofx').all(): if connection.type in ['Checking', 'Savings']: try: start, = (db.session.query(Transactions.date) .join(AccountsFrom, Transactions.account_id == AccountsFrom.id) .filter(BankAccounts.acctid == connection.account_number) .order_by(Transactions.date.desc()).first()) start = start.date() end = date.today() except TypeError: start = None end = None account = BankAcct(connection.routing_number, connection.account_number, connection.type) elif connection.type == 'Credit Card': try: start, = (db.session.query(Transactions.date) .join(AccountsFrom, Transactions.account_id == AccountsFrom.id) .join(CreditCardAccounts, CreditCardAccounts.id == AccountsFrom.id) .filter(CreditCardAccounts.acctid == connection.account_number) .order_by(Transactions.date.desc()).first()) start = start.date() end = date.today() except TypeError: start = None end = None account = CcAcct(connection.account_number) else: raise Exception('Unrecognized account/connection type: {0}'.format(connection.type)) ofx_client = OFXClient(connection.url, connection.org, connection.fid) if start and end: statement_request = ofx_client.statement_request(connection.user, connection.password, connection.clientuid, [account], dtstart=start, dtend=end) else: statement_request = ofx_client.statement_request(connection.user, connection.password, connection.clientuid, [account]) response = ofx_client.download(statement_request) new_response = ConnectionResponses() new_response.connection_id = connection.id new_response.connected_at = datetime.now(tzlocal()) new_response.response = response.read() db.session.add(new_response) db.session.commit() response.seek(0) parser = OFXParser() engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False) DBSession.configure(bind=engine) parser.parse(response) parser.instantiate() DBSession.commit() connection.synced_at = datetime.now(tzlocal()) db.session.commit() for account in db.session.query(AccountsFrom).filter(AccountsFrom.name.is_(None)).all(): account.name = '' db.session.commit()
def import_ofx(): from pacioli.functions.ofx_functions import fix_ofx_file directory = os.path.abspath(os.path.join('configuration_files', 'data')) files = [ofx_file for ofx_file in os.listdir(directory) if ofx_file.endswith(('.ofx', '.OFX', '.qfx', '.QFX'))] for ofx_file_name in files: ofx_file_path = os.path.join(directory, ofx_file_name) parser = OFXParser() engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False) DBSession.configure(bind=engine) if 'fixed' not in ofx_file_path: ofx_file_path = fix_ofx_file(ofx_file_path) parser.parse(ofx_file_path) parser.instantiate() DBSession.commit()
def log(message, end='\n'): print(message, end=end) sys.stdout.flush() if __name__ == '__main__': # CLI arg handling parser = argparse.ArgumentParser() parser.add_argument('files', nargs='+') parser.add_argument('-v', '--verbose', action='store_true') parser.add_argument('--output', default='sqlite:///test.db', help='Destination database URI') args = parser.parse_args() # DB setup engine = create_engine(args.output, echo=args.verbose) DBSession.configure(bind=engine) Base.metadata.create_all(engine) parser = OFXParser() for filename in args.files: log('Parsing "{}"...'.format(filename), end='') parser.parse(filename) log('done. Commiting to database...', end='') parser.instantiate() DBSession.commit() log('done!')
from ofxtools.ofxalchemy import Base, DBSession, OFXParser def log(message, end='\n'): print(message, end=end) sys.stdout.flush() if __name__ == '__main__': # CLI arg handling parser = argparse.ArgumentParser() parser.add_argument('files', nargs='+') parser.add_argument('-v', '--verbose', action='store_true') parser.add_argument('--output', default='sqlite:///test.db', help='Destination database URI') args = parser.parse_args() # DB setup engine = create_engine(args.output, echo=args.verbose) DBSession.configure(bind=engine) Base.metadata.create_all(engine) parser = OFXParser() for filename in args.files: log('Parsing "{}"...'.format(filename), end='') parser.parse(filename) log('done. Commiting to database...', end='') parser.instantiate() DBSession.commit() log('done!')
def setUp(self): verbose = '-v' in sys.argv engine = create_engine('sqlite:///test.db', echo=verbose) DBSession.configure(bind=engine) Base.metadata.create_all(engine)
def sync_ofx_connection(connection): if connection.type in ['Checking', 'Savings']: filter_boolean = BankAccounts.acctid == connection.account_number account = BankAcct(connection.routing_number, connection.account_number, connection.type) elif connection.type == 'Credit Card': filter_boolean = CreditCardAccounts.acctid == connection.account_number account = CcAcct(connection.account_number) else: raise Exception('Unrecognized account/' 'connection type: {0}'.format(connection.type)) start, = (db.session.query(Transactions.date) .filter(filter_boolean) .order_by(Transactions.date.desc()) .first()) if start: start = start.date() end = date.today() else: start = None end = None # start = start.join(AccountsFrom, # Transactions.account_id == AccountsFrom.id) # elif connection.type == 'Credit Card': # start = db.session.query(Transactions.date) # start = start.join(AccountsFrom, Transactions.account_id == AccountsFrom.id) # start = start.filter(CreditCardAccounts.acctid == connection.account_number) # start = start.order_by(Transactions.date.desc()) # start, = start.first() # if start: # start = start.date() # end = date.today() # else: # start = None # end = None ofx_client = OFXClient(connection.url, connection.org, connection.fid) if start and end: statement_request = ofx_client.statement_request(connection.user, connection.password, connection.clientuid, [account], dtstart=start, dtend=end) else: statement_request = ofx_client.statement_request(connection.user, connection.password, connection.clientuid, [account]) response = ofx_client.download(statement_request) new_response = ConnectionResponses() new_response.connection_id = connection.id new_response.connected_at = datetime.now(tzlocal()) new_response.response = response.read() db.session.add(new_response) db.session.commit() if '<SEVERITY>ERROR' in new_response.response: status = new_response.response.lower().title() send_error_message(status) return False response.seek(0) parser = OFXParser() engine = create_engine(current_app.config['SQLALCHEMY_DATABASE_URI'], echo=False) DBSession.configure(bind=engine) parser.parse(response) parser.instantiate() DBSession.commit() connection.synced_at = datetime.now(tzlocal()) db.session.commit() return True