Beanquery query

[Sagar Shankar]

Hi, I'm trying to generate a networth query and was trying this but am getting a syntax error. Any suggestions on what I'm getting wrong?

SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount
    where date <= {2025-09-27} AND account ~ 'Assets|Liabilities'

Thanks!

[Daniele Nicolodi]

``{currency}``, ``{date_iso}``, and ``{2025-09-27}`` are not valid
syntax in beanquery. The syntax error reported by beanquery would point
to the { in front of ``{currency}`` as the location of the syntax error.

However, it looks like that this is a Python f-string used to generate a
query programmatically. In this case the syntax error does not come from
beanquery but from Python: ``{2025-09-27}`` is interpreted as an
expression substitution in the f-string but it is not a valid Python
syntax as ``09`` is not a valid integer.

In any way, using string formatting for substituting parameters into
queries is not best practice as it requires to SQL-escape all string
substituted. A better way of doing it is to use a query with parameters:

import beanquery
import datetime
conn = beanquery.connect(...)
curs = conn.execute('''
SELECT
account,
convert(SUM(position), %s, %s) as amount
WHERE

date <= 2025-09-27 AND
account ~ 'Assets|Liabilities'

''', ('FOO', datetime.date(2025, 9, 27)))

or

curs = conn.execute('''
SELECT
account,
convert(SUM(position), %(currency)s, %(date)s) as amount
WHERE

date <= 2025-09-27 AND
account ~ 'Assets|Liabilities'

''', {currency='FOO', date=datetime.date(2025, 9, 27)})

beanquery implements the DB-ABI 2.0 and the same query parameters style
as psycopg, see https://www.psycopg.org/psycopg3/docs/basic/params.html

Cheers,
Dan