def test_open_RW_backup(self, book_uri):
# create book
with create_book(uri_conn=book_uri) as b:
engine_type = b.session.bind.name
# open book with readonly = False (ie RW)
if engine_type != "sqlite":
# raise an exception as try to do a backup on postgres which is not supported yet
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri, readonly=False)
elif engine_type == "sqlite":
# delete all potential existing backup files
url = book_uri[len("sqlite:///"):]
for fn in glob.glob("{}.[0-9]*.gnucash".format(url)):
os.remove(fn)
# open file in RW without a backup creation
with open_book(uri_conn=book_uri, readonly=False, do_backup=False) as b:
pass
# check no backup file creation
assert len(glob.glob("{}.[0-9]*.gnucash".format(url))) == 0
# open file in RW without a backup creation
with open_book(uri_conn=book_uri, readonly=False) as b:
pass
# check backup file creation
assert len(glob.glob("{}.[0-9]*.gnucash".format(url))) == 1
def open_book(self, for_writing=False) -> piecash.Book:
"""
Opens the database. Call this using 'with'.
If database file is not found, an in-memory database will be created.
"""
filename = None
# check if the file path is already a URL.
file_url = urllib.parse.urlparse(self.filename)
if file_url.scheme == "file" or file_url.scheme == "sqlite":
filename = file_url.path[1:]
else:
filename = self.filename
if not os.path.isfile(filename):
log(
WARN,
"Database %s requested but not found. Creating an in-memory book.",
filename)
return self.create_book()
access_type = "read/write" if for_writing else "readonly"
log(INFO, "Using %s in %s mode.", filename, access_type)
# file_path = path.relpath(self.filename)
file_path = path.abspath(filename)
if not for_writing:
book = piecash.open_book(file_path, open_if_lock=True)
else:
book = piecash.open_book(file_path,
open_if_lock=True,
readonly=False)
# book = create_book()
return book
def test_create_book(gnucash_creator):
"""Testing reproducibility of the create_example_book function."""
gnucash_creator.create_example_book()
example_file_dir = os.path.split(
os.path.dirname(os.path.realpath(__file__)))[0]
example_file_path = os.path.join(example_file_dir, "flask_app", "gnucash",
"gnucash_examples",
"example_gnucash.gnucash")
test_book = piecash.open_book(gnucash_creator.file_path)
compare_book = piecash.open_book(example_file_path)
assert len(test_book.transactions) == len(compare_book.transactions)
compare_list = []
tr_list = []
for book in (test_book, compare_book):
total_price = 0
tr_dict = Counter()
for tr in book.transactions:
for sp in tr.splits:
if sp.account.type == "EXPENSE":
total_price += round(float(sp.value), 2)
tr_dict[tr.description] += 1
compare_list.append(total_price)
tr_list.append(tr_dict)
assert compare_list[0] == compare_list[1]
for key in tr_list[0]:
assert key in tr_list[1]
assert tr_list[0][key] == tr_list[1][key]
def test_open_lock(self, book_uri):
# create book and set a lock
with create_book(uri_conn=book_uri) as b:
b.session.create_lock()
b.save()
# try to open locked book
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri)
# open book specifying open_if_lock as True
with open_book(uri_conn=book_uri, open_if_lock=True) as b:
pass
# open book specifying open_if_lock as True and RW to delete lock
with open_book(uri_conn=book_uri,
open_if_lock=True,
readonly=False,
do_backup=False) as b:
b.session.delete_lock()
b.save()
# open book specifying open_if_lock as False as lock has been removed
with open_book(uri_conn=book_uri, open_if_lock=False) as b:
pass
def cashDash_despesas():
if __name__=='__main__':
this_folder = '/home/guilherme/Dropbox/Docs/gnuCash/'
s = open_book(os.path.join(this_folder, "gtm-controle.gnucash"), open_if_lock=True)
else:
s = open_book(os.path.join("gtm-controle.gnucash"), open_if_lock=True)
today = datetime.datetime.today()
transactions = [tr for tr in s.transactions # query all transactions in the book/session and filter them on
if (tr.post_date.date() >= datetime.date(2016, 1, 1)) &
(tr.post_date.date() <= datetime.date(today.year, today.month, today.day))]
rows_list=[]
for tr in transactions:
for spl in tr.splits:
dict1={
'Date':pd.to_datetime(tr.post_date.date()),
'Value':spl.value,
'Type':spl.account.type,
'Parent':spl.account,
'Description':tr.description,
}
rows_list.append(dict1)
df = pd.DataFrame(rows_list)
# Ajustes no dataframe
df.Value = df.Value.astype(float)
df['Month']=pd.to_datetime(df.Date).dt.month
df['Year']=pd.to_datetime(df.Date).dt.year
df['Parent']=df.Parent.astype('string').str.replace('Account<|\[BRL\]>','')
df = df.drop(df[df.Parent.str.contains('Imbalance-BRL|Orphan-BRL|template')].index)
df['Sublevel'] = [el[1] for el in df.Parent.str.split(':')] #list comprehention
# Analise de Despesas
# Total de Despesas
totalByTypeMonth=df[['Date','Month','Value','Type']].groupby(['Type','Month']).sum()
# Despesas Fixas e Variaveis
# pd.options.mode.chained_assignment = None > Disable SettingWithCopyWarning
despesas = df[df['Type']=='EXPENSE'].copy()
# Despesas por subclassificacao
despesas['Sublevel2']=[el[2] for el in despesas.Parent.str.split(':')]
# Complementar de df despesas
outros = df[df['Type']!='EXPENSE'].copy()
# Todos os registros com sublevel2 presentes para despesas apenas
allrecords = pd.concat([outros,despesas],axis=0,ignore_index = True,join='outer')
#json_despesas = pd.json.dumps(despesas.to_dict('records'))
#http://pandas.pydata.org/pandas-docs/stable/io.html#json
json_despesas = despesas.to_json(orient='records',date_format='iso')
json_allrecords = allrecords.to_json(orient='records',date_format='iso')
#json_df = df.to_json(orient='records',date_format='iso')
s.close()
return json_allrecords
def check_file(cls, file_path):
result = False
try:
piecash.open_book(file_path)
result = True
except piecash.GnucashException:
pass
return result
def main():
"""Do what we do."""
parser = argparse.ArgumentParser()
parser.add_argument(
'-g',
'--gnucash',
type=str,
required=False,
help=
'Filename containing sqlite3 gnucash file or index into accounts map')
parser.add_argument('-o',
'--outfile',
type=str,
required=False,
help='Filename for account list')
args = parser.parse_args()
if args.gnucash is None:
args.gnucash = os.getenv('_gc__default_filename')
if args.outfile is None:
args.outfile = os.getenv('_gc__cache') + '/' + os.getenv(
'_gc__default_index')
if os.path.exists(args.gnucash):
book_filename = args.gnucash
else:
book_filename = os.getenv('_gc__' + args.gnucash)
book = piecash.open_book(book_filename, readonly=True, open_if_lock=True)
with open(args.outfile, 'w') as fp_out:
for account in book.accounts:
fp_out.write(('{n} ({d})\n'.format(n=account.name,
d=account.description)))
def export_csv(gnucash_file, csv_file, delimiter, date_format, warn_if_locked):
with click.open_file(csv_file, mode="w") as file:
writer = csv.DictWriter(file,
fieldnames=[
"guid",
"post_date",
"description",
"notes",
"from_account",
"to_account",
"value",
"currency",
],
delimiter=delimiter.encode())
writer.writeheader()
with piecash.open_book(gnucash_file,
open_if_lock=not warn_if_locked) as b:
for tr in b.transactions:
tr_dct = dict(guid=tr.guid,
post_date=tr.post_date.strftime(date_format),
description=tr.description,
notes=tr.notes)
splits = tr.splits
if len(splits) == 2:
sp_from = splits[0 if splits[0].value < 0 else 1]
sp_to = splits[1 if splits[0].value < 0 else 0]
tr_dct.update(
dict(from_account=sp_from.account.fullname,
to_account=sp_to.account.fullname,
value=sp_to.value,
currency=tr.currency.mnemonic))
writer.writerow(tr_dct)
def generate_report(
book_url, a_number: RangeOption(section="main",
sort_tag="a",
documentation_string="This is a number",
default_value=3),
a_str: StringOption(section="main",
sort_tag="c",
documentation_string="This is a string",
default_value="with a default value"),
a_date: DateOption(
section="main",
sort_tag="d",
documentation_string="This is a date",
default_value="(lambda () (cons 'absolute (cons (current-time) 0)))"),
another_number: RangeOption(section="main",
sort_tag="b",
documentation_string="This is a number",
default_value=3)):
import piecash
with piecash.open_book(uri_conn=book_url, readonly=True,
open_if_lock=True) as b:
tpl_name = os.path.basename(__file__).replace("py", "html")
env = jinja2.Environment(loader=jinja2.PackageLoader(__name__, '.'))
return env.get_template(tpl_name).render(
enumerate=enumerate,
list=list,
path_report=os.path.abspath(__file__),
**vars())
def get_lines(gnucash_filename):
"""Get all the income and expense account entries.
The gnucash_filename is the sqlite3 format gnucash file of the year's
accounts.
The dict returned has keys that are the account names. The value
is the account balance. Since this is a P&L report, we only look
at 6 and 7 accounts.
Return a data structure as follows:
{'charges': charges, 'produits': produits}
where each of charges and produits is itself a list of dicts as
returned by get_one_book().
"""
book = piecash.open_book(gnucash_filename,
readonly=True,
open_if_lock=True)
charges = []
produits = []
for account in book.accounts:
if len(account.children) == 0 and account.name[0] in ['6', '7']:
account_entries = get_one_account(account)
if len(account_entries['lines']) > 0:
if account.name[0] == '6':
charges.append(account_entries)
elif account.name[0] == '7':
produits.append(account_entries)
else:
print('Account error: {acct}'.format(acct=account.name))
return {'charges': charges, 'produits': produits}
def add_accounts(self):
book = piecash.open_book(uri_conn=self.dbname, readonly=False, do_backup=False)
USD = book.commodities.get(mnemonic='USD')
types = self.types
# Create level=1 Gnucash accounts
for type, values in types.iteritems():
acc = piecash.Account(name=values['name'],
type=values['gnucash_type'],
parent=book.root_account,
commodity=USD,
placeholder=True)
try:
book.save()
except ValueError:
#print '%s already exists!' % acc.name
book.cancel()
# Create level=2 Gnucash accounts for Mint accounts
for account in self.mint.get_accounts():
if account['accountType'].upper() == 'CREDIT':
parent = book.accounts(name=types['LIABILITY']['name'])
else:
parent = book.accounts(name=types['ASSET']['name'])
acc = piecash.Account(name=account['accountName'],
type=account['accountType'].upper(),
parent=parent,
commodity=USD)
try:
book.save()
except ValueError:
#print '%s already exists!' % acc.name
book.cancel()
book.close()
def __init__(self, gnucash_file, rules=None):
self.rules = rules
try:
self.book = open_book(gnucash_file)
except GnucashException as ex:
print(ex)
sys.exit()
def add_transactions(self):
book = piecash.open_book(uri_conn=self.dbname, readonly=False, do_backup=False)
USD = book.commodities.get(mnemonic='USD')
cnt = 0
for index, tran in self.mint.get_detailed_transactions().iterrows():
if cnt > 10:
break
if [tr for tr in book.transactions if tr.num==str(tran['id'])]:
#print 'already exists', tran['odate'], tran['merchant'], tran['amount']
cnt = cnt + 1
continue
if tran['isDuplicate']:
continue
a1 = book.accounts(code=str(tran['categoryId']))
a2 = book.accounts(name=tran['account'])
amount = Decimal("%.2f" % tran['amount'])
piecash.Transaction(currency=USD,
description=tran['merchant'],
splits=[
piecash.Split(account=a1, value=amount),
piecash.Split(account=a2, value=-1 * amount)
],
post_date=tran['odate'].to_pydatetime().date(),
num=str(tran['id']))
book.save()
book.close()
def main():
"""Generate beancount output from gnucash file."""
options = args()
with piecash.open_book(options.filename, open_if_lock=True) as book:
entries = convert.load_entries(book)
printer.print_entries(entries)
def get_budget_by_name(self, budget_name: str, budget_month: datetime) -> pd.DataFrame:
"""Fetches the budget items for the budget name for the given month"""
budget_month = budget_month.replace(day=1)
with piecash.open_book(self.fpath, open_if_lock=True, readonly=True) as mybook:
accounts = mybook.accounts
bdict = {}
for account in accounts:
bud_amts = account.budget_amounts
for ba in bud_amts:
if ba.budget.name != budget_name:
continue
budget_start = ba.budget.recurrence.recurrence_period_start
budget_month = budget_start.replace(month=budget_start.month + ba.period_num)
item_dict = {
'month': budget_month,
'account_fullname': account.fullname,
'account': account.fullname.split(':')[-1],
'type': account.fullname.split(':')[0],
'amount': float(ba.amount),
'cur': account.commodity.mnemonic
}
bdict[ba.id] = item_dict
df = pd.DataFrame(bdict).transpose()
df = df[df['month'] == budget_month].sort_values('account_fullname')
return df
def wrap(self, *args, **kwargs):
# print("self: {self}\n*args: {args}\nkwargs: {kwargs}".format(
# self=self, args=args, kwargs=kwargs))
# Get the normalized book filename.
filename = self._gnucash_file
filename = os.path.abspath(filename)
# Default case: Book was opened already
if filename in _open_books:
# print(" Not opening {} again for {}.".format(filename,
# func.__name__))
kwargs.update({"book": _open_books[filename]})
result = func(self, *args, **kwargs)
else:
# Now the book is opened, the function called, then the book is
# saved and closed automatically. Also the book is added to the
# list of open books and removed at the end.
with piecash.open_book(filename, readonly=False) as book:
# print("Opened {} for {}".format(filename, func.__name__))
_open_books[filename] = book
kwargs.update({"book": book})
result = func(self, *args, **kwargs)
# print("Saving and closing {}".format(filename))
book.save()
_open_books.pop(filename)
return result
def write_transactions_to_gnucash(gnucash_file, currency, all_items, dry_run=False, date_from=None):
logging.debug('Opening GnuCash file %s..', gnucash_file)
with open_book(gnucash_file, readonly=False) as book:
currency = get_or_create_currency(book, currency)
if date_from:
date_from = datetime.datetime.strptime(date_from, '%Y-%m-%d')
imported_items = set()
for item in all_items:
if date_from and item.date < date_from:
logging.info('Skipping entry %s (%s)', item.date.strftime('%Y-%m-%d'),
item.split_amount)
continue
if item.as_tuple() in imported_items:
logging.info('Skipping entry %s (%s) --- already imported!',
item.date.strftime('%Y-%m-%d'), item.split_amount)
continue
add_transaction(book, item, currency)
imported_items.add(item.as_tuple())
if book.is_saved:
logging.debug('Nothing changed')
else:
if dry_run:
logging.debug('** DRY-RUN **')
book.cancel()
else:
logging.debug('Saving GnuCash file..')
book.save()
def export_csv(gnucash_file, csv_file, delimiter, date_format, warn_if_locked):
with click.open_file(csv_file, mode="w") as file:
writer = csv.DictWriter(file,
fieldnames=["guid",
"post_date",
"description",
"notes",
"from_account",
"to_account",
"value",
"currency",
],
delimiter=delimiter.encode())
writer.writeheader()
with piecash.open_book(gnucash_file, open_if_lock=not warn_if_locked) as b:
for tr in b.transactions:
tr_dct = dict(guid=tr.guid,
post_date=tr.post_date.strftime(date_format),
description=tr.description,
notes=tr.notes)
splits = tr.splits
if len(splits) == 2:
sp_from = splits[0 if splits[0].value < 0 else 1]
sp_to = splits[1 if splits[0].value < 0 else 0]
tr_dct.update(dict(from_account=sp_from.account.fullname,
to_account=sp_to.account.fullname,
value=sp_to.value,
currency=tr.currency.mnemonic))
writer.writerow(tr_dct)
def transaction_to_list(gnucash_file):
rows = list()
fields = [
'Date',
'Description',
'TRANSACTION AMOUNT',
]
extra_fields = list()
book = piecash.open_book(gnucash_file, readonly=True)
debug.book = book ####
transactions = book.transactions
for t in transactions:
r = dict()
r['Date'] = t.post_date
r['Description'] = t.description
for s in t.splits:
account = s.account.commodity.mnemonic + ' ' + s.account.name
if account not in extra_fields:
extra_fields.append(account)
r[account] = s.value
if 'TRANSACTION AMOUNT' not in r:
r['TRANSACTION AMOUNT'] = 0
if s.value >= 0:
r['TRANSACTION AMOUNT'] = r['TRANSACTION AMOUNT'] + s.value
rows.append(r)
rows.sort(key=lambda x: x['Date'])
extra_fields = sort_extra_fields(rows, extra_fields)
fields.extend(extra_fields)
return rows, fields
def ledger(book, output):
"""Export to ledger-cli format.
This scripts export a GnuCash BOOK to the ledget-cli format.
"""
with piecash.open_book(book, open_if_lock=True) as data:
output.write(piecash.ledger(data))
def guess(account, description):
book = open_book(fp, open_if_lock=True, readonly=True)
data = list()
for t in book.transactions:
row = {'description': t.description, 'date': t.post_date}
for s in t.splits:
if s.account.type == 'EXPENSE':
row['expense_account'] = s.account.fullname
else:
row['charge_account'] = s.account.fullname
row['value'] = fabs(t.splits[0].value)
data.append(row)
df = pd.DataFrame(data).dropna()
# Only get data from the account we are ML-ing
df = df.loc[df.charge_account == account]
# Train the machine
text_clf = Pipeline([('vect', CountVectorizer()),
('tfidf', TfidfTransformer()),
('clf', MultinomialNB(alpha=0.4))])
text_clf = text_clf.fit(df.description, df.expense_account)
return text_clf.predict(description)
def filter_transactions(self, start: datetime = None, end: datetime = None,
filter_accounts: Union[str, List[str]] = None, filter_accounts_fullname: str = None,
filter_types: List[str] = None, filter_desc: List[str] = None,
groupby_list: List[str] = None) -> pd.DataFrame:
"""Filters all transactions falling in a certain month
Args:
start: start date to filter on
end: end date to filter on
filter_accounts: the name(s) of the account(s) to filter on
filter_accounts_fullname: the full path of the account to filter on
filter_types: the type of transaction splits to filter on (e.g., Expenses, Income, Asset, etc...)
filter_desc: filter on the descriptions of the transactions
groupby_list: list of columns to group by (date, account, desc, cur, etc...)
"""
if filter_accounts is not None:
if isinstance(filter_accounts, str):
filter_accounts = [filter_accounts]
desired_columns = {
'transaction.post_date': 'date',
'account.fullname': 'account_fullname',
'value': 'amount',
'memo': 'memo',
'transaction.description': 'desc',
'transaction.currency.mnemonic': 'cur'
}
with piecash.open_book(self.fpath, open_if_lock=True, readonly=True) as mybook:
df = mybook.splits_df()
df = df.sort_values(['transaction.post_date', 'transaction.guid'])
df = df[desired_columns.keys()].reset_index(drop=True).rename(columns=desired_columns)
# Extract the split type and actual account name from the full account name
df['type'] = df['account_fullname'].str.split(':').apply(lambda x: x[0])
df['account'] = df['account_fullname'].str.split(':').apply(lambda x: x[-1])
# Get the 1st account name after the account type
df['parent_account'] = df['account_fullname'].str.split(':').apply(lambda x: x[1])
# Handle (messy) filtering
if start is not None:
df = df[df['date'] >= start.date()]
if end is not None:
df = df[df['date'] <= end.date()]
if filter_accounts is not None:
df = df[df['account'].isin(filter_accounts)]
if filter_accounts_fullname is not None:
df = df[df['account_fullname'].isin(filter_accounts_fullname)]
if filter_types is not None:
df = df[df['type'].isin(filter_types)]
if filter_desc is not None:
df = df[df['desc'].isin(filter_desc)]
# Convert amounts to float
df['amount'] = df['amount'].astype(float)
# Set final column order
final_cols = ['date', 'account_fullname', 'type', 'account', 'desc', 'memo', 'amount', 'cur']
df = df[final_cols].reset_index(drop=True)
if groupby_list is not None:
# Perform grouping before returning
df = df.groupby(groupby_list, as_index=False).sum()
return df
def generate_report(book_url):
"""
Generates the report HTML.
"""
with piecash.open_book(book_url, readonly=True, open_if_lock=True) as book:
accounts = [acc.fullname for acc in book.accounts]
return f"""<html>
def realbook_session(request):
shutil.copyfile(file_template_full, file_for_test_full)
# default session is readonly
s = open_book(file_for_test_full)
request.addfinalizer(lambda: os.remove(file_for_test_full))
return s
def session_readonly(request):
shutil.copyfile(file_template, file_for_test)
# default session is readonly
s = open_book(file_for_test)
request.addfinalizer(lambda: os.remove(file_for_test))
return s
def book_complex(request):
"""
Returns a complex sample book for 2.6.N
"""
file_template_full = os.path.join(book_folder, "complex_sample.gnucash")
with open_book(file_template_full) as book:
yield book
def realbook_session(request):
shutil.copyfile(file_template_full, file_for_test_full)
# default book is readonly
s = open_book(file_for_test_full)
request.addfinalizer(lambda: os.remove(file_for_test_full))
return s
def book_sample(request):
"""
Returns a simple sample book for 2.6.N
"""
file_template_full = (book_folder / "simple_sample{}.gnucash".format(request.param))
with open_book(file_template_full) as book:
yield book
def session_readonly(request):
shutil.copyfile(file_template, file_for_test)
# default session is readonly
s = open_book(file_for_test)
request.addfinalizer(lambda: os.remove(file_for_test))
return s
def test_open_default(self, book_uri):
# open book that does not exists
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri)
# create book
with create_book(uri_conn=book_uri):
pass
# assert error if both sqlite_file and uri_conn are defined on open_book
with pytest.raises(ValueError):
b = open_book(db_sqlite, db_sqlite_uri)
# open book that exists
with open_book(uri_conn=book_uri) as b:
# try to save (raise as RO per default)
with pytest.raises(GnucashException):
b.save()
# read default currency (to check reading)
assert b.default_currency.mnemonic == "EUR"
# open book with checking existence
book_uri_fail = book_uri.replace("foo", "foofail")
with pytest.raises(GnucashException,
match="Database .* does not exist"):
open_book(uri_conn=book_uri_fail)
# open book without checking existence
with pytest.raises(OperationalError):
open_book(uri_conn=book_uri_fail, check_exists=False)
def book_investment(request):
"""
Returns the book that contains investment accounts and transactions.
"""
# name = request.param
# print(name)
file_template_full = os.path.join(book_folder, "investment.gnucash")
with open_book(file_template_full) as book:
yield book
def get_gnucash_transactions(self, book_file):
book = piecash.open_book(book_file, readonly=False)
account_labels = [a.fullname for a in self.book.accounts]
acc_map = {j: i for i, j in enumerate(self.account_labels)}
transactions = [
extract_transaction_features(tr, acc_map)
for tr in Prediction_Transactions(self.book)
]
return transactions
def find_interior(gnucash_filename):
"""Scan all accounts and report non-leaves that have transactions.
"""
book = piecash.open_book(gnucash_filename,
readonly=True,
open_if_lock=True)
for account in book.accounts:
if account.children and account.splits:
print(account.name)
def book_invoices(request):
"""
Returns the book that contains invoices.
"""
# name = request.param
# print(name)
file_template_full = book_folder / "invoices.gnucash"
with open_book(file_template_full) as book:
yield book
def ledger(book, output, locale, commodity_notes, short_account_names):
"""Export to ledger-cli format.
This scripts export a GnuCash BOOK to the ledget-cli format.
"""
with piecash.open_book(book, open_if_lock=True) as data:
output.write(
piecash.ledger(data,
locale=locale,
commodity_notes=commodity_notes,
short_account_names=short_account_names))
def book_readonly_lock(request):
shutil.copyfile(file_template, file_for_test)
# default session is readonly
book = open_book(file_for_test)
def close_s():
book.session.close()
os.remove(file_for_test)
request.addfinalizer(close_s)
return book
def test_open_default(self, book_uri):
# open book that does not exists
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri)
# create book
with create_book(uri_conn=book_uri):
pass
# assert error if both sqlite_file and uri_conn are defined on open_book
with pytest.raises(ValueError):
b = open_book(db_sqlite, db_sqlite_uri)
# open book that exists
with open_book(uri_conn=book_uri) as b:
# try to save (raise as RO per default)
with pytest.raises(GnucashException):
b.save()
# read default currency (to check reading)
assert b.default_currency.mnemonic == "EUR"
def book_readonly_lock(request):
shutil.copyfile(str(file_template), str(file_for_test))
# default session is readonly
book = open_book(file_for_test)
@request.addfinalizer
def close_s():
book.close()
file_for_test.unlink()
return book
def test_open_lock(self, book_uri):
# create book and set a lock
with create_book(uri_conn=book_uri) as b:
b.session.create_lock()
b.save()
# try to open locked book
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri)
# open book specifying open_if_lock as True
with open_book(uri_conn=book_uri, open_if_lock=True) as b:
pass
# open book specifying open_if_lock as True and RW to delete lock
with open_book(uri_conn=book_uri, open_if_lock=True, readonly=False, do_backup=False) as b:
b.session.delete_lock()
b.save()
# open book specifying open_if_lock as False as lock has been removed
with open_book(uri_conn=book_uri, open_if_lock=False) as b:
pass
def test_create_default(self, book_db_config):
with create_book(keep_foreign_keys=False, **book_db_config) as b:
a = Account(commodity=b.currencies(mnemonic="SEK"),
parent=b.root_account,
name="léviö",
type="ASSET")
assert str(b.uri) == build_uri(**book_db_config)
b.save()
# reopen the DB except if sqlite_file is None
if book_db_config.get("sqlite_file", True):
with open_book(**book_db_config) as b:
assert b.accounts(name="léviö").commodity == b.currencies(mnemonic="SEK")
def generate_report(
book_url,
):
with piecash.open_book(book_url, readonly=True, open_if_lock=True) as book:
return """<html>
<body>
Hello world from python !<br>
Book : {book_url}<br>
List of accounts : {accounts}
</body>
</html>""".format(
book_url=book_url,
accounts=[acc.fullname for acc in book.accounts],
)
def export_norme_A47(gnucash_file, xml_file, warn_if_locked):
with open_book(gnucash_file, open_if_lock=not warn_if_locked) as book:
# could add some filtering based on post_date if required
transactions = book.transactions
env = jinja2.Environment(trim_blocks=True, lstrip_blocks=True)
xml = env.from_string(u"""
<?xml version="1.0"?>
<comptabilite>
<exercice>
<DateCloture>2016-12-31T00:00:00</DateCloture>
<journal>
<JournalCode>Le code du Journal</JournalCode>
<JournalLib>Le libellé du Journal</JournalLib>
{% for i, ecriture in enumerate(transactions) %}
<ecriture>
<EcritureNum>{{ i }}</EcritureNum>
<EcritureDate>{{ ecriture.post_date.strftime("%Y-%m-%d") }}</EcritureDate>
<EcritureLib>{{ ecriture.description }}</EcritureLib>
<PieceRef>{{ ecriture.num }}</PieceRef>
<PieceDate>{{ ecriture.post_date.strftime("%Y-%m-%d") }}</PieceDate>
<ValidDate>{{ ecriture.post_date.strftime("%Y-%m-%d") }}</ValidDate>
{% for sp in ecriture.splits %}
<ligne>
<CompteNum>{{ sp.account.code }}</CompteNum>
<CompteLib>{{ sp.account.name }}</CompteLib>
<CompteAuxNum>Le numéro de compte auxiliaire (à blanc si non utilisé)</CompteAuxNum>
<CompteAuxLib>Le libellé de compte auxiliaire (à blanc si non utilisé)</CompteAuxLib>
<Montantdevise></Montantdevise>
<Montant>{{ abs(sp.value) }}</Montant>
<Sens>{% if sp.value >0 %}c{% else %}d{% endif %}</Sens>
</ligne>
{% endfor %}
</ecriture>
{% endfor %}
</journal>
</exercice>
</comptabilite>
""").render(transactions=transactions,
enumerate=enumerate,
abs=abs,
)
if xml_file and xml_file!="-":
with io.open(xml_file, "w", encoding="utf-8") as f:
f.write(xml)
else:
sys.stdout.write(xml)
def add_categories(self):
book = piecash.open_book(uri_conn=self.dbname, readonly=False, do_backup=False)
USD = book.commodities.get(mnemonic='USD')
types = self.types
# Create level=2 Gnucash accounts for Mint depth=1 categories
categories = {}
for category in self.mint.get_categories().itervalues():
categories[category['id']] = category
parent = category['parent']
categories[parent['id']] = parent
for category in categories.itervalues():
if category['depth'] == 1:
acc = piecash.Account(name=category['name'],
type=types[category['categoryType']]['gnucash_type'],
parent=book.accounts(name=types[category['categoryType']]['name']),
code=str(category['id']),
commodity=USD)
try:
book.save()
except ValueError:
#print '%s already exists!' % acc.name
book.cancel()
# Create level=3 Gnucash accounts for Mint depth=2 categories
for category in categories.itervalues():
if category['depth'] == 2:
acc = piecash.Account(name=category['name'],
type=types[category['categoryType']]['gnucash_type'],
parent=book.accounts(code=str(category['parent']['id'])),
code=str(category['id']),
commodity=USD)
try:
book.save()
except ValueError:
#print '%s already exists!' % acc.name
book.cancel()
book.close()
def __init__(self, bookname='rok', readonly=True, open_if_lock=True, do_backup=True):
self.bookname = bookname
self.book = open_book(uri_conn=Gncutils.BOOKS[bookname], readonly=readonly, open_if_lock=open_if_lock,
do_backup=do_backup)
self.session = self.book.session
self.connection = self.session.connection()
self.currency = self.book.currencies.get(mnemonic='IDR')
self.accounts = self.book.accounts
self.transactions = self.book.transactions
self.splits = self.book.splits
self.query = self.book.session.query
self.trxs = []
self.accounts_by_fullname = dict([(a.fullname, a) for a in self.book.accounts])
self.accounts_by_code = dict([(a.code.strip(), a) for a in self.book.accounts if a.code.strip() not in ''])
self.imbalance_acct = self.accounts_by_code['IMBALANCE-IDR']
self.bank_work = self.accounts_by_code['BNI373352345']
if self.bookname in 'rok':
self.bank_private = self.accounts_by_code['BNI289979573']
self.bank_saving = self.accounts_by_code['BNI387014784']
self.logger = logging.getLogger('sqlalchemy.engine')
def export(book, entities, output, inactive):
"""Exports GnuCash ENTITIES.
This scripts export ENTITIES from the BOOK in a CSV format.
When possible, it exports in a format that can be used to import the data into GnuCash.
\b
Remarks:
- for customers and vendors, the format does not include an header
- for prices, the format can be used with the `piecash import` command.
"""
from piecash import open_book
with open_book(book, open_if_lock=True) as book:
if entities == "prices":
output.write("date,type,value,value_num, value_denom, currency,commodity,source\n")
output.writelines(
"{p.date:%Y-%m-%d},{p.type},{p.value},{p._value_num},{p._value_denom},{p.currency.mnemonic},{p.commodity.mnemonic},{p.source}\n".format(
p=p) for p in book.prices)
elif entities in ["customers", "vendors"]:
columns = "id, name, addr_name, addr_addr1, addr_addr2, addr_addr3, addr_addr4, " \
"addr_phone, addr_fax, addr_email, notes, shipaddr_name, " \
"shipaddr_addr1, shipaddr_addr2, shipaddr_addr3, shipaddr_addr4, " \
"shipaddr_phone, shipaddr_fax, shipaddr_email".split(", ")
separator = ";"
filter_entity = (lambda e: True) if inactive else (lambda e: e.active)
# open the book
res = "\n".join([separator.join(getattr(v, fld, "")
for fld in columns)
for v in getattr(book, entities)
if filter_entity(v)
])
output.write(res)
def generate_report(
book_url,
a_number: RangeOption(
section="main",
sort_tag="a",
documentation_string="This is a number",
default_value=3),
a_str: StringOption(
section="main",
sort_tag="c",
documentation_string="This is a string",
default_value="with a default value"),
a_date: DateOption(
section="main",
sort_tag="d",
documentation_string="This is a date",
default_value="(lambda () (cons 'absolute (cons (current-time) 0)))"),
another_number: RangeOption(
section="main",
sort_tag="b",
documentation_string="This is a number",
default_value=3)
):
import piecash
with piecash.open_book(uri_conn=book_url, readonly=True, open_if_lock=True) as b:
tpl_name = os.path.basename(__file__).replace("py", "html")
env = jinja2.Environment(loader=jinja2.PackageLoader(__name__, '.'))
return env.get_template(tpl_name).render(
enumerate=enumerate,
list=list,
path_report=os.path.abspath(__file__),
**vars()
)
date=datetime(2015, 11, 4),
value=(127, 100),
)
Price(commodity=cdty,
currency=curr,
date=datetime(2015, 11, 2),
value=(234, 100),
)
b.save()
print(b.prices_df().to_string())
print(b.prices)
fdsdfsfds
with open_book(bookname, readonly=True, open_if_lock=True) as b:
for sp in b.splits:
print(sp.transaction.post_date, sp.transaction.enter_date, "=>", sp.slots)
for p in b.prices:
print(p._value_denom, p._value_num, p.slots)
print("\n".join(map(str, b.session.query(Slot).all())))
dfdsffd
if False:
sys.path.append("/home/sdementen/Apps/lib/python2.7/site-packages")
def lookup_account_by_path(parent, path):
acc = parent.lookup_by_name(path[0])
if acc.get_instance() == None:
def my_fixture():
file_template = book_folder / filename
with open_book(file_template) as book:
yield book
from piecash import open_book
# open a book
with open_book("../gnucash_books/simple_sample.gnucash", open_if_lock=True) as mybook:
# print all splits in account "Asset"
asset = mybook.accounts(fullname="Asset")
for split in asset.splits:
print(split)
# extract all split information to a pandas DataFrame
df = mybook.splits_df()
# print for account "Asset" some information on the splits
print(df.loc[df["account.fullname"] == "Asset", ["transaction.post_date", "value"]])
def add_transaction(t):
settings_file = os.environ['HOME'] + "/gnucash/settings.yaml"
with open(settings_file) as ymlfile:
settings = yaml.load(ymlfile)
book_path = settings['location'] + settings['gnucash']
log_file = settings['location'] + settings['log']
# check for existance of to_account and from_account
book = piecash.open_book(book_path)
to_account_found = False
from_account_found = False
for a in book.accounts:
if a.fullname == t.account:
from_account_found = True
elif a.fullname == t.expense:
to_account_found = True
success = True
expense_account_created = False
try:
# income - allow for not found accounts to instead go to Imbalance account
if t.income:
if not to_account_found:
t.account = 'Imbalance'
if not from_account_found:
t.expense = 'Imbalance'
# expense - allow creation of expense accounts ONLY
# - allow not found "from" accounts to instead go to Imbalance account
else:
# add missing expense account
if not to_account_found:
with open_book(book_path, open_if_lock=True, readonly=False) as book:
acc = book.root_account
for subacc in book.root_account.children:
if subacc.name == 'Expenses':
acc = subacc
break
# could change this and loop to support mutli-level expense account creation
#t.expense = 'Expense:' + t.expense.split(':')[-1]
a = Account(
parent=acc,
name=t.expense.split(':')[-1],
type="EXPENSE",
description='Automatically Added from SMS transaction.',
commodity = book.commodities.get(mnemonic="USD"))
book.save()
to_account_found = True
expense_account_created = True
if not from_account_found:
t.account = "Imbalance"
# reopen the book and add a transaction
# this must be a sqlite3 file
with open_book(book_path,
open_if_lock=True,
readonly=False) as mybook:
today = datetime.now()
today = today.replace(microsecond = 0)
# retrieve the currency from the book
USD = mybook.currencies(mnemonic='USD')
# define the amount as Decimal
amount = t.amount
# retrieve accounts
to_account = mybook.accounts(fullname=t.expense)
from_account = mybook.accounts(fullname=t.account)
# if income, flip the accounts so 'income' is used instead of 'charge'
if t.income:
to_account = mybook.accounts(fullname=t.account)
from_account = mybook.accounts(fullname=t.expense)
# create the transaction with its two splits
Transaction(
post_date=today,
enter_date=today,
currency=USD,
description=t.description,
splits=[
Split(account=to_account,
value=amount,
memo='Automated from script'),
Split(account=from_account,
value=-amount,
memo='Automated from script'),
]
)
# save the book
mybook.save()
except:
success = False
log(success, t, to_account_found, from_account_found, expense_account_created, log_file)
def test_out_write(self):
with piecash.open_book( file_template_full, open_if_lock=True ) as data:
out.write(piecash.ledger(data))
from __future__ import print_function
import datetime
import re
import os.path
from piecash import open_book
if __name__=='__main__':
this_folder = os.path.dirname(os.path.realpath(__file__))
s = open_book(os.path.join(this_folder, "..", "gnucash_books", "simple_sample.gnucash"), open_if_lock=True)
else:
s = open_book(os.path.join("gnucash_books", "simple_sample.gnucash"), open_if_lock=True)
# get default currency
print(s.default_currency)
regex_filter = re.compile("^/Rental/")
# retrieve relevant transactions
transactions = [tr for tr in s.transactions # query all transactions in the book/session and filter them on
if (regex_filter.search(tr.description) # description field matching regex
or any(regex_filter.search(spl.memo) for spl in tr.splits)) # or memo field of any split of transaction
and tr.post_date.date() >= datetime.date(2014, 11, 1)] # and with post_date no later than begin nov.
# output report with simple 'print'
print("Here are the transactions for the search criteria '{}':".format(regex_filter.pattern))
for tr in transactions:
print("- {:%Y/%m/%d} : {}".format(tr.post_date, tr.description))
for spl in tr.splits:
Account(name="Checking",
type="BANK",
commodity=mybook.currencies(mnemonic="USD"))
]),
]),
]
# save the book
mybook.save()
from piecash import open_book, Transaction, Split
from datetime import datetime
from decimal import Decimal
# reopen the book and add a transaction
with open_book("../gnucash_books/simple_book_transaction_creation.gnucash",
open_if_lock=True,
readonly=False) as mybook:
today = datetime.now()
# retrieve the currency from the book
USD = mybook.currencies(mnemonic="USD")
# define the amount as Decimal
amount = Decimal("25.35")
# retrieve accounts
to_account = mybook.accounts(fullname="Expenses:Some Expense Account")
from_account = mybook.accounts(fullname="Assets:Current Assets:Checking")
# create the transaction with its two splits
Transaction(
post_date=today,
enter_date=today,
currency=USD,
description="Transaction Description!",
def test_open_noarg(self):
with pytest.raises(ValueError):
open_book()
- type (string, optional)
- value (float)
- type (string, optional)
""", formatter_class=argparse.RawTextHelpFormatter)
parser.add_argument("gnucash_filename",
help="the name of the gnucash file")
parser.add_argument("--import", dest="operation",
help="to import the prices from a csv file (default export)")
args = parser.parse_args()
# args.operation is the name of the import file (.csv).
if args.operation is None:
# export the prices
sys.stdout.write("date,type,value,value_num, value_denom, currency,commodity,source\n")
with piecash.open_book(args.gnucash_filename, open_if_lock=True) as book:
sys.stdout.writelines(
"{p.date:%Y-%m-%d},{p.type},{p.value},{p._value_num},{p._value_denom},{p.currency.mnemonic},{p.commodity.mnemonic},{p.source}\n".format(
p=p) for p in book.prices)
else:
# import the prices
with piecash.open_book(args.gnucash_filename, open_if_lock=True, readonly=False) as book:
cdty = book.commodities
importFile = open(args.operation, 'r')
for l in csv.DictReader(importFile):
cur = cdty(mnemonic=l['currency'])
com = cdty(mnemonic=l['commodity'])
type = l.get('type', None)
date = datetime.strptime(l['date'], "%Y-%m-%d")
v = Decimal(l['value'])
"""
# pylint: disable=invalid-name
import sys
import piecash
from piecash import Commodity
# Variables
filename = sys.argv[1]
if filename is None:
print("You need to specify a valid .gnucash file to use.")
filename = "test.gnucash"
symbol = "AUD"
####################################
with piecash.open_book(filename, open_if_lock=True) as book:
# , readonly=False,
# SQLAlchemy session.
session = book.session
# query example:
#accountsFiltered = session.query(Account).filter(Account.name >= "T").all()
# SQLAlchemy methods: count, first, all, one...
# Get all the currencies in the book (i.e. for update).
print("All currencies used in the book:")
currencies = session.query(Commodity).filter(Commodity.namespace == "CURRENCY").all()
for c in currencies:
print(c)
#!/usr/local/bin/python """original script from https://github.com/MatzeB/pygnucash/blob/master/gnucash2ledger.py by Matthias Braun [email protected] adapted for: - python 3 support - new string formatting """ import argparse import sys import codecs import piecash if sys.version_info.major == 2: out = codecs.getwriter('UTF-8')(sys.stdout) else: out = sys.stdout parser = argparse.ArgumentParser(description="Generate a ledger-cli representation of a gnucash book") parser.add_argument("gnucash_filename", help="the name of the gnucash file to process") args = parser.parse_args() with piecash.open_book(args.gnucash_filename, open_if_lock=True) as data: out.write(piecash.ledger(data))
def test_open_RW_backup(self, book_uri):
# create book
with create_book(uri_conn=book_uri) as b:
engine_type = b.session.bind.name
# open book with readonly = False (ie RW)
if engine_type != "sqlite":
# raise an exception as try to do a backup on postgres which is not supported yet
with pytest.raises(GnucashException):
b = open_book(uri_conn=book_uri, readonly=False)
elif engine_type == "sqlite":
# delete all potential existing backup files
url = book_uri[len("sqlite:///") :]
for fn in glob.glob("{}.[0-9]*.gnucash".format(url)):
os.remove(fn)
# open file in RW without a backup creation
with open_book(uri_conn=book_uri, readonly=False, do_backup=False) as b:
pass
# check no backup file creation
assert len(glob.glob("{}.[0-9]*.gnucash".format(url))) == 0
# open file in RW without a backup creation
with open_book(uri_conn=book_uri, readonly=False) as b:
pass
# check backup file creation
assert len(glob.glob("{}.[0-9]*.gnucash".format(url))) == 1
