python GnuCash interface to SQL backend

[John Ralls]

> On Nov 11, 2014, at 1:10 PM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> Hello,
> 
> After trying multiple times to work with GnuCash from python (via xml, via
> the python bindings, via sql), I finally had a try to use SQLAlchemy to
> handle the GnuCash Books saved through the SQL backend (sqlite3 and
> postgres).
> 
> I have a release on PyPI the package "pyscash" installable through 'pip
> install pyscash' (see some raw documentation on
> https://github.com/sdementen/pyscash). It is 'alpha' quality.
> 
> While it opens by default the Book in read only mode to be able to do
> reporting or extract data from a GnuCash Book, I also succeeded in doing
> more elaborate scripts that change a Book : creating new
> accounts/sub-accounts, creating new transactions, uploading quotes for
> stocks, etc.
> 
> I read that the SQL backend is just a backend to save the data and that
> GnuCash is not a DB application and that the preferred way to program
> GnuCash in python is through the python bindings. However, I found it much
> easier to work with this pyscash package, at least as long as it is done
> "offline" (i.e. not modifying a Book that is at the same time opened by
> GnuCash).
> 
> I would be genuinely interested to have more specific documentation on the
> 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.

The risk of writing to the database outside of GnuCash, whether in SQL or XML, is that unless you are very careful and have a deep understanding of how GnuCash works that you will irretrievably corrupt your accounting data. There is no business logic encoded in the SQL database, so your code must replicate the GnuCsah engine code to ensure that all required fields are computed and stored correctly. Much of GnuCash is neither straightforward nor obvious and some critical data are stored outside of the primary tables, usually to preserve backward compatibility with previous versions.

Regards,
John Ralls