Skip to main content

Datasette plugin for executing SQL queries from templates

Project description

datasette-template-sql

PyPI CircleCI License

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.

Files for datasette-template-sql, version 1.0.1
Filename, size File type Python version Upload date Hashes
Filename, size datasette_template_sql-1.0.1-py3-none-any.whl (7.3 kB) File type Wheel Python version py3 Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page