Datasette plugin for executing SQL queries from templates
Project description
datasette-template-sql
Datasette plugin for executing SQL queries from templates.
Examples
www.niche-museums.com uses this plugin to run a custom themed website on top of Datasette. The full source code for the site is here - see also niche-museums.com, powered by Datasette.
simonw/til is another simple example, described in Using a self-rewriting README powered by GitHub Actions to track TILs.
Installation
Run this command to install the plugin in the same environment as Datasette:
$ pip install datasette-template-sql
Usage
This plugin makes a new function, sql(sql_query)
, available to your Datasette templates.
You can use it like this:
{% for row in sql("select 1 + 1 as two, 2 * 4 as eight") %}
{% for key in row.keys() %}
{{ key }}: {{ row[key] }}<br>
{% endfor %}
{% endfor %}
The plugin will execute SQL against the current database for the page in database.html
, table.html
and row.html
templates. If a template does not have a current database (index.html
for example) the query will execute against the first attached database.
Queries with arguments
You can construct a SQL query using ?
or :name
parameter syntax by passing a list or dictionary as a second argument:
{% for row in sql("select distinct topic from til order by topic") %}
<h2>{{ row.topic }}</h2>
<ul>
{% for til in sql("select * from til where topic = ?", [row.topic]) %}
<li><a href="{{ til.url }}">{{ til.title }}</a> - {{ til.created[:10] }}</li>
{% endfor %}
</ul>
{% endfor %}
Here's the same example using the :topic
style of parameters:
{% for row in sql("select distinct topic from til order by topic") %}
<h2>{{ row.topic }}</h2>
<ul>
{% for til in sql("select * from til where topic = :topic", {"topic": row.topic}) %}
<li><a href="{{ til.url }}">{{ til.title }}</a> - {{ til.created[:10] }}</li>
{% endfor %}
</ul>
{% endfor %}
Querying a different database
You can pass an optional database=
argument to specify a named database to use for the query. For example, if you have attached a news.db
database you could use this:
{% for article in sql(
"select headline, date, summary from articles order by date desc limit 5",
database="news"
) %}
<h3>{{ article.headline }}</h2>
<p class="date">{{ article.date }}</p>
<p>{{ article.summary }}</p>
{% endfor %}
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for datasette-template-sql-1.0.2.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 664c795c319e820be635803198b505dc01621324a8b4627b269e4c174563e8c3 |
|
MD5 | 6a69736e674b5e98205e543a4e13cff5 |
|
BLAKE2b-256 | c9026022d05e4ba267c60b6e775ac80d445f3ea4b0509ce14208ca84166f9c58 |
Hashes for datasette_template_sql-1.0.2-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 194514201e8ce3489040e89a7887d8144bf27fd32580649a904be31de2cd0752 |
|
MD5 | a15725eac86cf1bef43401a52bf9f7e5 |
|
BLAKE2b-256 | 703a80703eb171f8294dbf77c5777b0fa644370e9c5b53cd8f9e5722cf4cacfb |