Metadata-Version: 2.1
Name: datasette-template-sql
Version: 1.0
Summary: Datasette plugin for executing SQL queries from templates
Home-page: https://github.com/simonw/datasette-template-sql
Author: Simon Willison
License: Apache License, Version 2.0
Platform: UNKNOWN
Description-Content-Type: text/markdown
Requires-Dist: datasette (~=0.32)
Provides-Extra: test
Requires-Dist: pytest ; extra == 'test'
Requires-Dist: pytest-asyncio ; extra == 'test'
Requires-Dist: httpx ; extra == 'test'
Requires-Dist: sqlite-utils ; extra == 'test'

# datasette-template-sql

[![PyPI](https://img.shields.io/pypi/v/datasette-template-sql.svg)](https://pypi.org/project/datasette-template-sql/)
[![CircleCI](https://circleci.com/gh/simonw/datasette-template-sql.svg?style=svg)](https://circleci.com/gh/simonw/datasette-template-sql)
[![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/datasette-template-sql/blob/master/LICENSE)

Datasette plugin for executing SQL queries from templates.

## 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:

```html+jinja
{% 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:

```html+jinja
{% 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:

```html+jinja
{% 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:

```html+jinja
{% 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 %}
```


