BQL PRINT command in a script

[Daniele Nicolodi]

Hello,

I am writing a tool that should operate on a selection of entries from a
Benacount ledger. I could come up with my own little DSL to do the
selection, but we already have BQL, thus I think reusing it would be nice.

To this extent, I was expecting to do simply something like:

from beancount.query import query
query.run_query(entries, options, f"PRINT FROM {where:}")

and get back a list of entries, but it crashes. Apparently the PRINT
command is not supposed to be executed in this context.

Is there a way to use BQL to filter the entries in a ledger?

Thank you.

Cheers,
Dan

[Daniele Nicolodi]

On 15/04/2021 15:31, Daniele Nicolodi wrote:
> Hello,
>
> I am writing a tool that should operate on a selection of entries from a
> Benacount ledger. I could come up with my own little DSL to do the
> selection, but we already have BQL, thus I think reusing it would be nice.

Replying to myself. There is not a ready made function, but it is easy
to put together one. For example:

from beancount.query import query_compile
from beancount.query import query_env
from beancount.query import query_execute
from beancount.query import query_parser


def filter_entries(entries, options, what):
env_targets = query_env.TargetsEnvironment()
env_entries = query_env.FilterEntriesEnvironment()
env_postings = query_env.FilterPostingsEnvironment()

# Parse the BQL statement.
parser = query_parser.Parser()
statement = parser.parse(f'PRINT FROM {what:}')

# Compile the BQL statement.
compiled = query_compile.compile(statement,
env_targets, env_postings, env_entries)

# Execute only the filtering part of the BQL statement.
context = query_execute.create_row_context(entries, options)
entries = query_execute.filter_entries(compiled.c_from,
entries, options, context)

return entries

Cheers,
Dan

[Martin Blais]

I would just write a loop against the API.

shorter and more powerful.;

--
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 on the web visit https://groups.google.com/d/msgid/beancount/74e9e559-14e0-3065-63e5-171af236cf09%40grinta.net.

[Daniele Nicolodi]

On 15/04/2021 20:27, Martin Blais wrote:
> I would just write a loop against the API.
> shorter and more powerful.;

This does not look too bad already, but I have an hard time unpacking
you suggestion. To which API are you referring to?

Cheers,
Dan

[Daniele Nicolodi]

The what argument in my filter_entries() function is a string I get from
the command line, thus I need anyway the query parsing part, I think.

Cheers,
Dan

[Martin Blais]

entries, _, __ = loader.load_file(filename)

for entry in data.filter_txns(entries):

  if <condition>:

    continue

  printer.print_entry(entry)

--
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 on the web visit https://groups.google.com/d/msgid/beancount/b5dab48d-d8da-da62-4cb8-0a5d47102fe5%40grinta.net.

[Daniele Nicolodi]

On 15/04/2021 23:27, Martin Blais wrote:
>
> entries, _, __ = loader.load_file(filename)
> for entry in data.filter_txns(entries):
>   if <condition>:
>     continue
>   printer.print_entry(entry)
>

My goal is to be able to do something like this:

$ foobar.py ledger.beans --where="META('foo') = 'bar'"

to have my tool operate on a selection of the entries in ledger.beans. I
don't want to have to re-invent a DSL for specifying the --where option,
and I think a BQL expression is a fairly nice solution.

Cheers,
Dan

[Ben Blount]

Why not write small python scripts whenever you want to do a query? You could have pre-written scripts with bindable parameters. You could even go as far as to accept python snippets from the command line, similarly to how petl does.

--
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 on the web visit https://groups.google.com/d/msgid/beancount/890a57f0-bb7c-4dfc-34f6-01eda6fff46d%40grinta.net.

[Martin Blais]

Oh wow, that's cool, I didn't realize it could do that on the cmdline.

We'll build some Beancount helpers for petl. I love most things about petl.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CACGEkZs4Jocphm9DVsr5dOtYwNLN4O-%3D%3DGTVOk%2Bg1XqYneGFcg%40mail.gmail.com.

[Daniele Nicolodi]

On 16/04/2021 00:51, Ben Blount wrote:
> Why not write small python scripts whenever you want to do a query?

Because writing single-use Python scripts does not fit my definition of
fun. There are much better solutions.

> You
> could have pre-written scripts with bindable parameters. You could even
> go as far as to accept python snippets from the command line, similarly
> to how petl does.

This is not as easy as it may seem, and a DSL is always going to to
offer a much nicer user experience than having to write a Python
expression evaluated in an (forcefully) ill-defined context.

We already have a very nice DSL to operate on Beancount ledger entries,
I don't see why not using it, it works extremely well!

Cheers,
Dan

[Martin Blais]

I agree and I disagree at the same time. Let me explain.

- Side of Agree: Yes, it has served us well, and I think it's time for that tool to be expanded beyond Beancount to work on any data table and to become more mature (e.g. add types). As long as we can register new datatypes (for Amount, Position, Inventory), it should be possible.

Having the ability to run SQL on the command-line and e.g. join CSV files or directly upload to/from Google Sheets is immensely useful.

- Side of Disagree: If I could do 95% of what it does with 10-20% more code (e.g. Python) without having to write the processing tool itself (e.g. using petl), 

I wouldn't have written the tool in the first place. Less code to maintain.

Those things being said, I'm a huge fan of people contributing to what they love :-)

We should have both

(Source code for the query tool has started to migrate to its own repo:

https://github.com/beancount/beanquery)