Tutorial : using existing objects¶

Opening an existing Book¶

To open an existing GnuCash document (and get the related Book), use the open_book() function:

import piecash

# for a sqlite3 document
book = piecash.open_book("existing_file.gnucash")

# or through an URI connection string for sqlite3
book = piecash.open_book(uri_conn="sqlite:///existing_file.gnucash")
# or for postgres
book = piecash.open_book(uri_conn="postgres://user:passwd@localhost/existing_gnucash_db")

The documents are open as readonly per default. To allow RW access, specify explicitly readonly=False as:

book = piecash.open_book("existing_file.gnucash", readonly=False)

When opening in full access (readonly=False), piecash will automatically create a backup file named filename.piecash_YYYYMMDD_HHMMSS with the original file. To avoid creating the backup file, specificy backup=False as:

book = piecash.open_book("existing_file.gnucash", readonly=False, backup=False)

To force opening the file even through there is a lock on it, use the open_if_lock=True argument:

book = piecash.open_book("existing_file.gnucash", open_if_lock=True)

Access to objects¶

Once a GnuCash book is opened through a piecash.core.book.Book, GnuCash objects can be accessed through two different patterns:

The object model

In this mode, we access elements through their natural relations, starting from the book and jumping from one object to the other:

In [1]: book = open_book(gnucash_books + "default_book.gnucash")

In [2]: book.root_account # accessing the root_account
Out[2]: Account<[EUR]>

In [3]: # looping through the children accounts of the root_account
   ...: for acc in book.root_account.children:
   ...:     print(acc)
   ...: 
Account<Assets[EUR]>
Account<Liabilities[EUR]>
Account<Income[EUR]>
Account<Expenses[EUR]>
Account<Equity[EUR]>

# accessing children accounts
In [5]: 
   ...: root = book.root_account              # select the root_account
   ...: assets = root.children(name="Assets")   # select child account by name
   ...: cur_assets = assets.children[0]         # select child account by index
   ...: cash = cur_assets.children(type="CASH") # select child account by type
   ...: print(cash)
   ...: 
Account<Assets:Current Assets:Cash in Wallet[EUR]>

In [10]: # get the commodity of an account
   ....: commo = cash.commodity
   ....: print(commo)
   ....: 
Commodity<CURRENCY:EUR>

In [13]: # get first ten accounts linked to the commodity commo
   ....: for acc in commo.accounts[:10]:
   ....:     print(acc)
   ....: 
Account<[EUR]>
Account<Assets[EUR]>
Account<Assets:Current Assets[EUR]>
Account<Assets:Current Assets:Checking Account[EUR]>
Account<Assets:Current Assets:Savings Account[EUR]>
Account<Assets:Current Assets:Cash in Wallet[EUR]>
Account<Liabilities[EUR]>
Account<Liabilities:Credit Card[EUR]>
Account<Income[EUR]>
Account<Income:Bonus[EUR]>

The “table” access

In this mode, we access elements through collections directly accessible from the book:

In [15]: book = open_book(gnucash_books + "default_book.gnucash")

# accessing all accounts
In [16]: book.accounts
Out[16]: 
[Account<Assets[EUR]>,
 Account<Assets:Current Assets[EUR]>,
 Account<Assets:Current Assets:Checking Account[EUR]>,
 Account<Assets:Current Assets:Savings Account[EUR]>,
 Account<Assets:Current Assets:Cash in Wallet[EUR]>,
 Account<Liabilities[EUR]>,
 Account<Liabilities:Credit Card[EUR]>,
 Account<Income[EUR]>,
 Account<Income:Bonus[EUR]>,
 Account<Income:Gifts Received[EUR]>,
 Account<Income:Interest Income[EUR]>,
 Account<Income:Interest Income:Checking Interest[EUR]>,
 Account<Income:Interest Income:Other Interest[EUR]>,
 Account<Income:Interest Income:Savings Interest[EUR]>,
 Account<Income:Other Income[EUR]>,
 Account<Income:Salary[EUR]>,
 Account<Expenses[EUR]>,
 Account<Expenses:Adjustment[EUR]>,
 Account<Expenses:Auto[EUR]>,
 Account<Expenses:Auto:Fees[EUR]>,
 Account<Expenses:Auto:Gas[EUR]>,
 Account<Expenses:Auto:Parking[EUR]>,
 Account<Expenses:Auto:Repair and Maintenance[EUR]>,
 Account<Expenses:Bank Service Charge[EUR]>,
 Account<Expenses:Books[EUR]>,
 Account<Expenses:Cable[EUR]>,
 Account<Expenses:Charity[EUR]>,
 Account<Expenses:Clothes[EUR]>,
 Account<Expenses:Computer[EUR]>,
 Account<Expenses:Dining[EUR]>,
 Account<Expenses:Education[EUR]>,
 Account<Expenses:Entertainment[EUR]>,
 Account<Expenses:Entertainment:Music/Movies[EUR]>,
 Account<Expenses:Entertainment:Recreation[EUR]>,
 Account<Expenses:Entertainment:Travel[EUR]>,
 Account<Expenses:Gifts[EUR]>,
 Account<Expenses:Groceries[EUR]>,
 Account<Expenses:Hobbies[EUR]>,
 Account<Expenses:Insurance[EUR]>,
 Account<Expenses:Insurance:Auto Insurance[EUR]>,
 Account<Expenses:Insurance:Health Insurance[EUR]>,
 Account<Expenses:Insurance:Life Insurance[EUR]>,
 Account<Expenses:Laundry/Dry Cleaning[EUR]>,
 Account<Expenses:Medical Expenses[EUR]>,
 Account<Expenses:Miscellaneous[EUR]>,
 Account<Expenses:Online Services[EUR]>,
 Account<Expenses:Phone[EUR]>,
 Account<Expenses:Public Transportation[EUR]>,
 Account<Expenses:Subscriptions[EUR]>,
 Account<Expenses:Supplies[EUR]>,
 Account<Expenses:Taxes[EUR]>,
 Account<Expenses:Taxes:Federal[EUR]>,
 Account<Expenses:Taxes:Medicare[EUR]>,
 Account<Expenses:Taxes:Other Tax[EUR]>,
 Account<Expenses:Taxes:Social Security[EUR]>,
 Account<Expenses:Taxes:State/Province[EUR]>,
 Account<Expenses:Utilities[EUR]>,
 Account<Expenses:Utilities:Electric[EUR]>,
 Account<Expenses:Utilities:Garbage collection[EUR]>,
 Account<Expenses:Utilities:Gas[EUR]>,
 Account<Expenses:Utilities:Water[EUR]>,
 Account<Equity[EUR]>,
 Account<Equity:Opening Balances[EUR]>]

# accessing all commodities
In [17]: book.commodities
Out[17]: [Commodity<CURRENCY:EUR>]

# accessing all transactions
In [18]: book.transactions
Out[18]: []

Each of these collections can be either iterated or accessed through some indexation or filter mechanism (return first element of collection satisfying some criteria(s)):

# iteration
In [19]: for acc in book.accounts:
   ....:     if acc.type == "ASSET": print(acc)
   ....: 
Account<Assets[EUR]>
Account<Assets:Current Assets[EUR]>

# indexation (not very meaningful)
In [20]: book.accounts[10]
Out[20]: Account<Income:Interest Income[EUR]>

# filter by name
In [21]: book.accounts(name="Garbage collection")
Out[21]: Account<Expenses:Utilities:Garbage collection[EUR]>

# filter by type
In [22]: book.accounts(type="EXPENSE")
Out[22]: Account<Expenses[EUR]>

# filter by fullname
In [23]: book.accounts(fullname="Expenses:Taxes:Social Security")
Out[23]: Account<Expenses:Taxes:Social Security[EUR]>

# filter by multiple criteria
In [24]: book.accounts(commodity=book.commodities[0], name="Gas")
Out[24]: Account<Expenses:Auto:Gas[EUR]>

The “SQLAlchemy” access (advanced users)

In this mode, we access elements through SQLAlchemy queries on the SQLAlchemy session:

# retrieve underlying SQLAlchemy session object
In [1]: session = book.session

# get all account with name >= "T"
In [2]: session.query(Account).filter(Account.name>="T").all()
Out[2]: 
[Account<Expenses:Entertainment:Travel[EUR]>,
 Account<Expenses:Taxes[EUR]>,
 Account<Expenses:Utilities[EUR]>,
 Account<Expenses:Utilities:Water[EUR]>,
 Account<>]

# display underlying query
In [3]: str(session.query(Account).filter(Account.name>="T"))
Out[3]: 'SELECT accounts.account_type AS accounts_account_type, accounts.commodity_scu AS accounts_commodity_scu, accounts.non_std_scu AS accounts_non_std_scu, accounts.placeholder AS accounts_placeholder, accounts.guid AS accounts_guid, accounts.name AS accounts_name, accounts.commodity_guid AS accounts_commodity_guid, accounts.parent_guid AS accounts_parent_guid, accounts.code AS accounts_code, accounts.description AS accounts_description, accounts.hidden AS accounts_hidden \nFROM accounts \nWHERE accounts.name >= :name_1'

Accounts¶

Accessing the accounts (piecash.core.account.Account):

from piecash import open_book

with open_book("gnucash_books/simple_sample.gnucash") as book:
    # accessing the root_account
    root = book.root_account
    print(root)

    # accessing the first children account of a book
    acc = root.children[0]
    print(acc)

    # accessing attributes of an account
    print("Account name={acc.name}\n"
          "        commodity={acc.commodity.namespace}/{acc.commodity.mnemonic}\n"
          "        fullname={acc.fullname}\n"
          "        type={acc.type}".format(acc=acc))

    # accessing all splits related to an account
    for sp in acc.splits:
        print("account <{}> is involved in transaction '{}'".format(acc.fullname, sp.transaction.description))

Commodities and Prices¶

The list of all commodities in the book can be retrieved via the commodities attribute:

# all commodities
print(book.commodities)

cdty = book.commodities[0]

# accessing attributes of a commodity
print("Commodity namespace={cdty.namespace}\n"
      "          mnemonic={cdty.mnemonic}\n"
      "          cusip={cdty.cusip}\n"
      "          fraction={cdty.fraction}".format(cdty=cdty))

The prices (piecash.core.commodity.Price) of a commodity can be iterated through the prices attribute:

# loop on the prices
for pr in cdty.prices:
    print("Price date={pr.date}"
          "      value={pr.value} {pr.currency.mnemonic}/{pr.commodity.mnemonic}".format(pr=pr))

Transactions and Splits¶

The list of all transactions in the book can be retrieved via the transactions attribute:

In [1]: book = open_book(gnucash_books + "book_schtx.gnucash", open_if_lock=True)

# all transactions (including transactions part of a scheduled transaction description)
In [2]: for tr in book.transactions:
   ...:     print(tr)
   ...: 
Transaction<[EUR] 'Monthly utility bill' on 2015-01-02>
Transaction<[EUR] 'Insurance' on 2015-01-02>
Transaction<[EUR] 'Monthly utility bill' on 2013-12-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-02-02 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-03-02 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-03-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-04-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-06-01 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-06-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-07-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-08-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-09-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-11-02 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-11-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2014-12-31 (from sch tx)>
Transaction<[EUR] 'Insurance' on 2013-05-31 (from sch tx)>
Transaction<[EUR] 'Insurance' on 2014-05-29 (from sch tx)>
Transaction<[EUR] 'Opening balance' on 2013-01-02>
Transaction<[EUR] 'Monthly utility bill' on 2015-02-01 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-03-01 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-03-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-04-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-05-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-06-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-08-02 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-08-31 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-09-30 (from sch tx)>
Transaction<[EUR] 'Monthly utility bill' on 2015-11-01 (from sch tx)>
Transaction<[EUR] 'Insurance' on 2015-05-31 (from sch tx)>
Transaction<[EUR] 'test' on 2015-11-17>
Transaction<[EUR] 'salary' on 2013-12-31>
Transaction<[EUR] 'salary' on 2014-12-31>

# selecting first transaction generated from a scheduled transaction
In [3]: tr = [ tr for tr in book.transactions if tr.scheduled_transaction ][0]

For a given transaction, the following attributes are accessible:

# accessing attributes of a transaction
In [1]: print("Transaction description='{tr.description}'\n"
   ...:       "            currency={tr.currency}\n"
   ...:       "            post_date={tr.post_date}\n"
   ...:       "            enter_date={tr.enter_date}".format(tr=tr))
   ...: 
Transaction description='Monthly utility bill'
            currency=Commodity<CURRENCY:EUR>
            post_date=2013-12-31 23:00:00+00:00
            enter_date=2015-01-03 08:18:13+00:00

# accessing the splits of the transaction
In [2]: tr.splits
Out[2]: 
[Split<Account<Assets:Current Assets:Checking Account[EUR]> -70 EUR>,
 Split<Account<Expenses:Utilities:Electric[EUR]> 30 EUR>,
 Split<Account<Expenses:Utilities:Gas[EUR]> 40 EUR>]

# accessing the scheduled transaction
In [3]: [ sp for sp in tr.scheduled_transaction.template_account.splits]
Out[3]: 
[SplitTemplate<Account<Assets:Current Assets:Checking Account[EUR]> credit=70 >,
 SplitTemplate<Account<Expenses:Utilities:Electric[EUR]>  debit=30>,
 SplitTemplate<Account<Expenses:Utilities:Gas[EUR]>  debit=40>]

Other objects¶

In fact, any object can be retrieved from the session through a generic get(**kwargs) method:

from piecash import Account, Commodity, Budget, Vendor

# accessing specific objects through the get method
acc = book.get(Account, name="Asset", parent=book.root_account)
cdty = book.get(Commodity, namespace="CURRENCY", mnemonic="EUR")
bdgt = book.get(Budget, name="my first budget")
invoice = book.get(Vendor, name="Looney")

If you know SQLAlchemy, you can get access to the underlying Session as book.session and execute queries using the piecash classes:

from piecash import Account, Commodity, Budget, Vendor

# get the SQLAlchemy session
session = book.session

# loop through all invoices
for invoice in session.query(Invoice).all():
    print(invoice.notes)

Note

Easy access to objects from piecash.business and piecash.budget could be given directly from the session in future versions if deemed useful.

Working with slots¶

With regard to slots, GnuCash objects and Frames behave as dictionaries and all values are automatically converted back and forth to python objects:

In [1]: import datetime, decimal

In [2]: book = create_book()

# retrieve list of slots
In [3]: print(book.slots)
[<SlotGUID default-currency=Commodity<CURRENCY:EUR>>]

# set slots
In [4]: book["myintkey"] = 3

In [5]: book["mystrkey"] = "hello"

In [6]: book["myboolkey"] = True

In [7]: book["mydatekey"] = datetime.datetime.today().date()

In [8]: book["mydatetimekey"] = datetime.datetime.today()

In [9]: book["mynumerickey"] = decimal.Decimal("12.34567")

In [10]: book["account"] = book.root_account

# iterate over all slots
In [11]: for k, v in book.iteritems():
   ....:     print("slot={v} has key={k} and value={v.value} of type {t}".format(k=k,v=v,t=type(v.value)))
   ....: 
slot=<SlotGUID default-currency=Commodity<CURRENCY:EUR>> has key=default-currency and value=Commodity<CURRENCY:EUR> of type <class 'piecash.core.commodity.Commodity'>
slot=<SlotInt myintkey=3> has key=myintkey and value=3 of type <type 'int'>
slot=<SlotString mystrkey='hello'> has key=mystrkey and value=hello of type <type 'str'>
slot=<SlotInt myboolkey=True> has key=myboolkey and value=True of type <type 'bool'>
slot=<SlotDate mydatekey=datetime.date(2017, 2, 15)> has key=mydatekey and value=2017-02-15 of type <type 'datetime.date'>
slot=<SlotTime mydatetimekey=datetime.datetime(2017, 2, 15, 6, 40, 23, 997157)> has key=mydatetimekey and value=2017-02-15 06:40:23.997157 of type <type 'datetime.datetime'>
slot=<SlotNumeric mynumerickey=Decimal('12.34567')> has key=mynumerickey and value=12.34567 of type <class 'decimal.Decimal'>
slot=<SlotGUID account=Account<>> has key=account and value=Account<> of type <class 'piecash.core.account.Account'>

# delete a slot
In [12]: del book["myintkey"]

# delete all slots
In [13]: del book[:]

# create a key/value in a slot frames (and create them if they do not exist)
In [14]: book["options/Accounts/Use trading accounts"]="t"

# access a slot in frame in whatever notations
In [15]: s1=book["options/Accounts/Use trading accounts"]

In [16]: s2=book["options"]["Accounts/Use trading accounts"]

In [17]: s3=book["options/Accounts"]["Use trading accounts"]

In [18]: s4=book["options"]["Accounts"]["Use trading accounts"]

In [19]: assert s1==s2==s3==s4

Slots of type GUID use the name of the slot to do the conversion back and forth between an object and its guid. For these slots, there is an explicit mapping between slot names and object types.