python GnuCash interface to SQL backend

Sébastien de Menten sdementen at
Fri Nov 14 07:28:47 EST 2014

First of all, thank you John for taking the time to answer to this thread !

> > If you see GnuCash from the (limited) perspective of an editor for a
> Book document (as LibreOffice Writer is an editor for a ODT document),
> object persistence is central. And luckily we have both a very clean and
> well designed object model in GnuCash as well as standard persistence
> formats (xml, SQL  and not an obscure binary format).
> I don’t share either opinion, especially about the object model in GnuCash.
> When talking about the object model, I am referring to the entities
(Account, Book, Split, ...), their relation and attributes. And for most of
the "core" objects (excluding company, invoice, ...) it is rather clean and
efficient. Personnally, I think it could be a great basis for an open
document format for this domain of application (better than QIF & co).

In terms of the implementation itself of the object model, the main things
I see not that clean are:
- KVP vs field representation
- XXX_denom / XXX_num split (instead of using a Decimal type)
But these two points should be hidden for the user/developper in python
bindings (official or piecash).

> > The GnuCash documentation states that the XML document can be used to
> get the GnuCash data in whatever other format (see "Converting XML GnuCash
> File"
> through XLST transformation and vice-versa (so generating a new/transformed
> gnucash XML file from a LibreOffice spreadsheet).
> No matter what the GnuCash documentation might say, only someone with a
> deep understanding of GnuCash could successfully create a correct GnuCash
> data file from a spreadsheet document using XSLT. I’m not familiar with
> LibreOffice’s ODS format, but I expect that it would take deep knowledge of
> that format to successfully extract useful data from it with XSLT as well.

I agree that creating GnuCash file through XSLT is probably clause to be a
nightmare ... that is why I quickly dropped the XML route (on ODS, I just
took what was written in the GnuCash doc, and indeed interacting with such
document require some library).

> >
> > PieCash (I like your name !) aims to fulfill exactly this purpose, no
> more, no less. As with the XLST transforms, it allows to do CRUD operations
> on the objects within a GnuCash Book.
> CRUD operations on a GnuCash database will corrupt it. The GnuCash schema
> is not normalized, and not all of the necessary data is stored in the table
> associated with the objects.
"CRUD operations" is not to be taken as "pure CRUD operations", there
should always be in piecash a check that the set of objects is
self-consistent (but this is really simple, obvious, natural constrains if
one know a bit the GnuCash object model)

Could you point me to 2 or 3 real case example we would nevertheless end up
corrupting the database ? It would really help me to materialise this
With my practical experience with piecash (creating a full account tree
structure and importing thousands of transactions from csv file), I haven't
found any case of corruption (except when developing the API and having
"obvious" bugs).

> >
> > Has this stance on the manipulation outside GnuCash of a GnuCash
> document evolved since it was written ? Would this still be supported after
> the C++ rewrite ?
> It isn’t supported now. It never has, and it is unlikely that it ever will
> be, even if we are able, after several development cycles, to actually
> migrate to a 3N database schema. There is too much logic that is encoded in
> the program and which cannot be portably encoded in a SQL database, to make
> that feasible.
For piecash, there is no need for a 3N db schema and there is no need to
write any logic in SQL !
It is through SQLAlchemy that we can handle/encapsulate the extra bit of
logic (which is, as far as I am in the development of piecash and given
piecash scope, rather limited). Nothing is done in SQL itself.


> > All the rest is handled automatically thanks to the SQLAlchemy layer
> (link between objects, cascade delete, locking and transactions, generation
> of GUID key, …).
> Won’t work. SQLAlchemy can’t automatically generate a correct class from
> the schema nor can it derive a correct table description from the C headers.
The table schema is exactly the only piece of code that needs to be written
(piecash is in fact just that, table schemas with some metadata).
I do not hope/expect/think to generate this code automatically from C
headers (as SWIG does).

> You’ll get closer working with the XML schema. There’s a reasonably
> up-to-date version in src/doc/xml/gnucash-v2.rnc.
As written here above, XML is not easy to work with (at least for me) and
does not save me of anything you said before (corruption, etc).

> >
> > So, I admit, the main effort in this project consisted in writing for
> each entity (book, account, etc) the equivalent here above code as well as
> the relationships between entities. The later is done with a syntax like
> >
> >    class Split(DeclarativeBaseGuid):
> >        [...]
> >        account = relation('Account', backref=backref('splits',
> cascade='all, delete-orphan'))
> >
> >
> >> It’s also very dangerous to attempt to select what parts of GnuCash’s
> internal functions to reimplement. To take your example of balances, it’s
> an integral part of GnuCash’s internal checks to compute and compare
> balances. Leaving that out greatly increases the risk of introducing
> corrupt data.
> > I may have not be totally clear in my previous email but I do not indeed
> see any common sense in implementing the Balance calculations in PieCash.
> These results are part of the GnuCash application logic and are not saved
> in the GnuCash XML/SQL files. They are neither part of the object model
> itself but transient calculations.
> You’re confusing the object model with the persistence model. They’re
> rather different.
I understand "object model" as the entities+relations+fields model and its
concrete implementation, the "persistence model" as the table schema. Is it
also what you are referring to ? If so, could you elaborate shortly on your
comment ? I can't succeed in making the link between my comment and yours.

and thank you again for your time !

More information about the gnucash-devel mailing list