-
-
Notifications
You must be signed in to change notification settings - Fork 0
Description
v0.54 500 error in sql query template; code worked in v0.53; found a workaround
schema:
CREATE TABLE "talks" ("talk" TEXT,"series" INTEGER, "talkdate" TEXT)
CREATE TABLE "series" ("id" INTEGER PRIMARY KEY, "series" TEXT, talks_list TEXT default '', website TEXT default '');
Live example of correctly rendered template in v.053: https://cosmotalks-cy6xkkbezq-uw.a.run.app/cosmotalks/talks/1
Description of problem: I needed 'sql select' code in a custom row-mydatabase-mytable.html template to lookup the series name for a foreign key integer value in the talks table. So metadata.json specifies the datasette-template-sql plugin.
The code below worked perfectly in v0.53 (just the relevant sql statement part is shown; full code is here):
{# custom addition #}
{% for row in display_rows %}
...
{% set sname = sql("select series from series where id = ?", [row.series]) %}
<strong>Series name: {{ sname[0].series }}
...
{% endfor %}
{# End of custom addition #}
In v0.54, that code resulted in a 500 error with a 'no such table series' message. A second query in that template also did not work but the above is fully illustrative of the problem.
All templates were up-to-date along with datasette v0.54.
Workaround: After fiddling around with trying different things, what worked was the syntax from Querying a different database from the datasette-template-sql github repo to add the database name to the sql statement:
{% set sname = sql("select series from series where id = ?", [row.series], database="cosmotalks") %}
Though this was found to work, it should not be necessary to add database="cosmotalks" since per the datasette-template-sql README, it's only needed when querying a different database, but here it's a table within the same database.