how to use beanquery to execute queries programatically ?

[E]

Hello,

I'm trying to run queries via automation, but I cannot tell how to use the beanquery package

this is what I have

import beanquery

query = '''SELECT sum(position)
WHERE date <= 2024-04-01
  AND account ~ "Assets:MyAsset"'''

file_path = "path/to/file.beancount"

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

result = connection.execute(query)

print( result .fetchall())

the problem is that the result contains all the entries that match the WHERE clause, but they are not aggregated as I specified in the query

What am I missing?

[E]

I was able to get the aggregate by passing the resulting rows to numberify_results, though I really don't understand why that was necessary, as an API it makes it quite confusing, so maybe I'm still not fully grasping how beanquery works, but at least I got past that

to put the full example for posterity:

import beanquery

query = '''SELECT sum(position)
WHERE date <= 2024-04-01
  AND account ~ "Assets:MyAsset"'''

file_path = "path/to/file.beancount"

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

result = connection.execute(query)

rrows = connection.fetchall()

rtypes, rrows = beanquery.numberify.numberify_results(
                rtypes, rrows
            )

print(rrows[0][0])

I got this idea by perusing Fava code

[Daniele Nicolodi]

On 10/01/25 07:31, E wrote:
> I was able to get the aggregate by passing the resulting rows to
> numberify_results, though I really don't understand why that was
> necessary, as an API it makes it quite confusing, so maybe I'm still not
> fully grasping how beanquery works, but at least I got past that

That's not how things should work. You must be doing something wrong
somewhere. Here your example works just fine:

>>> import beanquery
>>> conn = beanquery.connect('beancount:tests/test52.beans')
>>> curs = conn.execute('''
... SELECT sum(position)
... WHERE date <= 2024-04-01 AND account ~ "Assets:MyAsset"
... ''')
>>> curs.fetchall()
[((3.0 TEST),)]

where tests/test52.beans is this:

plugin "beancount.plugins.auto_accounts"

2024-01-01 * "Test"
Assets:MyAsset 1.0 TEST
Expenses:Test

2024-01-02 * "Test"
Assets:MyAsset 1.0 TEST
Expenses:Test

2024-01-03 * "Test"
Assets:MyAsset 1.0 TEST
Expenses:Test

I don't think there has been any point in time where this did not work
in exactly this way in beanquery. Can you please show which unexpected
result you obtain?

Cheers,
Dan

[E]

Thank you for looking into this

I think there is the slight difference in that I am adding costs to the transactions

Here is my minimal repro file:

plugin "beancount.plugins.auto_accounts"

2024-01-01 * "Test"

  Expenses:Test        
  Assets:MyAsset         1.0 TEST {1.0 USD}

2024-01-02 * "Test"
  Expenses:Test        
  Assets:MyAsset         1.0 TEST {1.0 USD}

2024-01-03 * "Test"
  Expenses:Test        
  Assets:MyAsset         1.0 TEST {1.0 USD}

And my test script:

import beanquery

path = "path/to/file.beancount"
conn = beanquery.connect('beancount:' + path)
curs = conn.execute('''
 SELECT sum(position)

 WHERE date <= 2024-04-01 AND account ~ "Assets:MyAsset"

 ''')
print(curs.fetchall())

Result:

[((1.0 TEST {1.0 USD, 2024-01-01}, 1.0 TEST {1.0 USD, 2024-01-02}, 1.0 TEST {1.0 USD, 2024-01-03}),)]

[Daniele Nicolodi]

On 10/01/25 17:54, E wrote:
> Thank you for looking into this
>
> I think there is the slight difference in that I am adding costs to the
> transactions

[...]

> Result:
> [((1.0 TEST {1.0 USD, 2024-01-01}, 1.0 TEST {1.0 USD, 2024-01-02}, 1.0
> TEST {1.0 USD, 2024-01-03}),)]

This is correct. You would get the same result in the interactive
bean-query shell. As you can see, the costs get an implicit date added,
thus the lots cannot be summed together without loss of information.

To get the result you get applying numberify you need to drop the cost
information and sum the units in each posting using the units() BQL
function:

>>> import beanquery
>>> conn = beanquery.connect('beancount:tests/test53.beans')
>>> curs = conn.execute('''
... SELECT sum(units(position))

... WHERE date <= 2024-04-01 AND account ~ "Assets:MyAsset"
... ''')
>>> curs.fetchall()
[((3.0 TEST),)]

Conversely, to sum the costs at the time of the transaction, you can use
the cost() function.

Cheers,
Dan

[E]

Thank you for this information, so I was indeed not fully understanding some of it