implement named parameterized queries

[WolfgangFahl]

see e.g. https://github.com/WolfgangFahl/pyLoDStorage/blob/master/lodstorage/params.py

which does jinja compatible parameter handling and is used in https://github.com/WolfgangFahl/snapquery

with demos at https://snapquery.bitplan.com/ and https://genwiki2024.bitplan.com/

[dnicolodi]

From the provided links I'm not able to understand what the feature you are asking would be doing. The examples provided are web GUIs for running some sort of queries. beanquery does not have a GUI, only an interactive shell. How do you foresee this feature to work in the interactive shell? beanquery does not even have a very well defined concept of named queries. It can load named queries defined in Beancount ledgers with the query directive. Are you proposing to extend this facility with the possibility of passing parameters to the queries stored in Beancount ledgers? How would the user interface for this functionality look like?

In particular, I don't understand the reference to "ninja compatible parameter handling". This seems to imply that query parameters are interpolated textually into the query string. This is a very bad idea and results in having to deal with escaping of the substituted parameters when composing the query, which is very fragile. beanquery has support for parametrized queries like most SQL client implementations that does not rely on textual manipulation of the query string.

[WolfgangFahl]

the GUI screen shots are only for illustration Take e.g. https://snapquery.bitplan.com/query/scholia.toolforge.org/named_queries/author_list-of-publications which is derived from https://github.com/WDscholia/scholia/blob/master/scholia/app/templates/author_list-of-publications.sparql

this query has a single parameter with the name "q" which is used in the line

PREFIX target: <http://www.wikidata.org/entity/{{ q }}>

now the named parameterized query has the flexibility to create queries on any wikidataitem with the identifier q e.g Q80 - Tim Berners Lee. So a named parameterized query is a query template.

In beancount e.g. a paramater {{year}} might be useful.

In my apps i use yaml for the declaration of the queries e.g. https://github.com/WolfgangFahl/genwiki2024/blob/main/genwiki_examples/queries.yaml

if beancount would have the same interface my UIs would work out of the box and we could even use a beancount RESTful server as an endpoint.

[dnicolodi]

beanquery supports queries with parameters. parameters placeholders have the same style as defined in the Python DB-API with paramstyles format or pyformat as implemented for example by the psicopg https://www.psycopg.org/psycopg3/docs/basic/params.html

IIUC, the main difference from what you propose is that the parameters are textually replaced in the query string. What beanquery does (and what other SQL and SQL-like client libraries do) is to use placeholders that are bound to values at query execution time.

beanquery is not going to provide a text substitution implementation of query parameters.

[dnicolodi]

I still don't understand why what you want to do needs support in beanquery. If you want your query front-end to do textual interpolation of query parameters, just pass the query string through string.Template() on ninja or something that does the interpolation.

[WolfgangFahl]

Thanks i will happily try this out. Currently my main obstacle is the incompatibility of beancount 3 and beancount 2 - how can i use beanquery as a component without automatically getting beancount2 ? Could you make beanquery available as a separate pypi release?

[dnicolodi]

I don't understand what you mean. beanquery is a separate package.

[WolfgangFahl]

I found https://pypi.org/project/beanquery/ with This is a fork of beancount.query that is intended to eventually replace it. This is currently work in progress. The dev0 label the statement and the missing badges in the README (see #209) confused me a bit. I am now going to try to use it.

dev0 is something have note encountered before. I learned: 0.1.dev0 is a valid semantic version for PyPI. According to PEP 440, which defines the versioning scheme for Python packages, 0.1.dev0 is compliant as a development release. The .devN suffix is used to denote a developmental release preceding the base version 0.1.

>=0.1 will not match 0.1.dev0 in pyproject.toml. According to PEP 440, development releases (e.g., 0.1.dev0) are considered to be earlier than their corresponding base release (e.g., 0.1). Therefore, the constraint >=0.1 will only match versions that are 0.1 or higher, excluding any development releases before 0.1.

so i am going with any beanquery version for the time being.

[dnicolodi]

The attentive observer would have spotted the fact that the "Homepage" link of the package on PyPI points to exactly this GitHub repository.