How to query Beancount from a Python app

[Alen Šiljak]

Hi all! As I managed to port all my data, I am now adapting the tooling I built around it, before I can fully switch to Beancount. I would highly appreciate the assistance from the helpful people in this group.

I will post the questions separately so that they are easier to track.

The first question is - how to query Beancount from a Python application?

To provide some context - I am using Cashier for quick overview and transaction entry on my phone. I use Cashier Server to synchronize the balances from the book. Cashier Server is a Python application. This used to forward queries to ledger and return the data back. Now, with Beancount, I would assume that it can execute those directly via Python and not run any external processes.

So, if I manage to identify the BQL queries I need to get accounts, balances, payees, and whatever else, how would I run them from an external app?

I have Python experience but I have not worked with any Beancount tools yet. I've looked briefly into what's available and tried bean-query.

Thank you for any pointers!

[Chary Ev2geny]

Alen,

you need to use run_query

from beanquery.query import run_query

You can see how it is done here for instance

https://colab.research.google.com/drive/17Mbk2JC4KHMg3b1iXoZg-50kEI2bCoQ-?usp=drive_link

[Alen Šiljak]

Thank you for this information!

In the meantime I was poking through the developer documentation. I assume the Scripting section at

https://beancount.github.io/docs/beancount_scripting_plugins.html

would come in handy, as well? It seems more geared towards plugin authors so I'll try some more with beanquery.

[Martin Blais]

https://g.co/gemini/share/ea750735a803

https://g.co/gemini/share/0905b3166339

Use the force, Luke!

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/7d9315f7-96e1-483e-a791-2e8dc00dda0en%40googlegroups.com.

[Alen Šiljak]

Thanks!! I was trying with Perplexity and it didn't go well. :)) Maybe it gets confused by the progressing versions but it was giving some strange function names in the queries and some non-existent namespaces.

[Martin Blais]

Gemini 2.5 pro is killing it these days

- much faster than OpenAI

- better than Claude (anecdotally)

- cheaper than both of these, I think I got a year free with my new phone 

- quota seems higher too (I rarely run against the limits compared to the other models)

Check it out, the web page is ugly, but that's standard Google cluelessness, the tech behind it is second to none

To view this discussion visit https://groups.google.com/d/msgid/beancount/28bec0d6-2626-40ad-8108-36492fd85745n%40googlegroups.com.

[Daniele Nicolodi]

On 12/05/25 19:43, Martin Blais wrote:
> https://g.co/gemini/share/ea750735a803
> https://g.co/gemini/share/0905b3166339
> Use the force, Luke!

I would be so much more impressive if it would regurgitate code that
actually works. Some of the code in there uses beancount.query which is
gone from beancount v3.

If you want to use beanquery from Python code, look at some old posts on
this mailing lists. I've shared some example code a few times now (and I
don't have time for doing it once more just now).

Cheers,
Dan

[Chary Ev2geny]

I found a very powerful feature available in the OpenAI O3 model, which I just checked is not available by the Gemini 2.5 Pro:

I can upload to O3 a zip archive with the project source code, the O3 will then unzip it, and will be able to explore it on my request. So, in this way it can analyze entire project, not just code snippets.

This is what helped me to create this PR for instance.

[Alen Šiljak]

Yeah, trying the code examples now in a Jupyter Notebook, that I want to keep for reference. But, as already reported, the code doesn't work. That's what I've already been doing for hours beforehand. :) So, the struggle continues. It doesn't help that big restructurings have happened over the years so the models may be getting confused.

As far as the AI goes, I've been paying much more attention to it since being assigned to a chatbot project at work. I've tried various models, including running some locally with Ollama.

Cool thing is to use it from a coding assistant. Then a model can analyze the whole codebase. Some vscode extensions were pretty good but they are so chatty that they use up my free allowance for all models very quickly. :))

To summarize, this sucks and I have to keep on looking. :) 

Strange, but expected to find some working examples quickly. Is it really the case that people don't actually do this that often?

In that case, maybe Cashier + Sync will be a nice addition to the ecosystem. I'm adding Beancount support (parsing inputs and outputs) with a switch.

[Alen Šiljak]

OK, here's a working code from one of the previous messages. I'm going to use this in Cashier server as it feels right to have the data queries in BQL, sent from the PWA, and not manipulating the data records directly in Python.

```python

import beanquery

query = '''

select description, sum(position)

where date between 2023-01-01 and 2023-01-31

    and account ~ "Assets:"

'''

connection = beanquery.connect('beancount:' +  BOOK )

cursor = connection.execute(query)

result = cursor.fetchall()

for row in result:

    print(row)

```

[Chary Ev2geny]

On Tuesday, May 13, 2025 at 7:51:03 PM UTC+2 Alen Šiljak wrote:

Yeah, trying the code examples now in a Jupyter Notebook, that I want to keep for reference. But, as already reported, the code doesn't work. 

not sure what "code does not work", but the Jupyter notebook I sent you a link to just works

Open the link https://colab.research.google.com/drive/17Mbk2JC4KHMg3b1iXoZg-50kEI2bCoQ-?usp=sharing

Then go Runtime => Run all.

[Alen Šiljak]

Oh, I didn't realize it was a link to a Notebook. That's useful, indeed. And seems simpler. Thank you!