python GnuCash interface to SQL backend
Sébastien de Menten
sdementen at gmail.com
Thu Nov 13 01:12:53 EST 2014
On Thursday, November 13, 2014, John Ralls <jralls at ceridwen.us> wrote:
> On Nov 12, 2014, at 12:08 PM, Sébastien de Menten <sdementen at gmail.com
> On Wednesday, November 12, 2014, John Ralls <jralls at ceridwen.us
>> > On Nov 11, 2014, at 1:10 PM, Sébastien de Menten <sdementen at gmail.com>
>> > ....
>> > I would be genuinely interested to have more specific documentation on
>> > risks of going the SQL way.
>> There's nothing wrong with reading the database to generate reports. That
>> is indeed easier for many people via SQL query than writing custom report
>> plugins in Scheme.
>> It may also be easier to go with the SQL than with the std python binding
> for reporting but also to change the GnuCash book.
> It might be, but I doubt it. You won’t be able to implement the business
> logic in SQL alone.
The python interface uses SQLAlchemy (an ORM) only to handle the backend
(retrieve and save objects), all business logic is in the python code. For
instance, when creating a transaction and the related splits, it is the
python code that ensures the business logic (for instance that the sum of
the splits = 0). This is close to what GnuCash does.
Moreover, there are some basic SQL integrity constrains (we cannot remove a
split without removing the related transaction) that are added in the ORM
layer as they do not exist in the SQL backend.
>> I have mainly used the basic objects from GnuCash required for
> basic personal finance (so no invoice, no budget, ...) and did not found
> any issues while handling lot of accounts/transactions/splits and stock
> prices. I had the impression that GnuCash does indeed calculations when the
> book is opened but that it does not save them in the SQL backend. Hence, if
> we access the book when it's not opened by GnuCash at the same time, risks
> are quite reduced, would this be a "wrong" impression ?
> I’m not sure I follow you about calculations when the book is opened that
> aren’t saved.
I was thinking about temporary results/cached calculations/etc that are not
saved to the back ends (if there are any).
> With the SQL backend, everything is written back to the database when a
> change is committed, so the *results* of the calculations are immediately
> saved. What GnuCash doesn’t do is *read* the database after the initial
> load, nor does it use any database concurrency control, so there are two
> potential ways to screw things up with two programs (even two instances of
> GnuCash) using the same database: A change made by one instance could be
> overwritten by a change made in the other or, much worse, the two instances
> could try writing the same records at the same time, corrupting those
> Indeed, if GnuCash has opened the file and is using it (ie there is a lock
in the table gnc_lock), we are almost 100% sure to have the issues you
mentions if we change the file in parallel through SQL. There is a check
in pyscash that raises an exception in this case (it can be overruled but
at user's own risk.
More information about the gnucash-devel