On 27/09/25 12:01, Sagar Shankar wrote:
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'
``{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
--
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 [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/beancount/a3c4ea35-ca52-45a1-a2bf-9c0d837d7453%40grinta.net.
