运行 SQL 查询¶

Datasette 将 SQLite 数据库文件视为只读且不可变。这意味着无法使用 Datasette 执行 INSERT 或 UPDATE 语句，这使得我们可以将 SELECT 语句公开给外部，而无需担心 SQL 注入攻击。

对 Datasette 执行自定义 SQL 的最简单方法是通过 Web UI。数据库索引页面包含一个 SQL 编辑器，您可以在其中运行任何喜欢的 SELECT 查询。您也可以使用表页面上的过滤界面构建查询，然后点击“查看并编辑 SQL”在自定义 SQL 编辑器中打开该查询。

请注意，此界面仅在允许 execute-sql 权限时可用。

任何 Datasette SQL 查询都会反映在页面的 URL 中，您可以将其加入书签、与他人共享，并使用浏览器后退按钮导航到之前的查询。

您还可以通过在基本 URL 末尾添加 .json 来以 JSON 格式检索任何查询的结果。

命名参数¶

Datasette 对 SQLite 命名参数有特殊支持。考虑一个像这样的 SQL 查询

select * from Street_Tree_List
where "PermitNotes" like :notes
and "qSpecies" = :species

如果您使用自定义查询编辑器执行此查询，Datasette 将提取这两个命名参数，并使用它们为您构建表单字段以供提供值。

您也可以通过构建 URL 为这些字段提供值

/mydatabase?sql=select...&species=44

SQLite 字符串转义规则将应用于使用命名参数传递的值 - 它们将被包裹在引号中，其内容将被正确转义。

命名参数的值被视为 SQLite 字符串。如果您需要对其进行数字比较，应首先使用 cast(:name as integer) 或 cast(:name as real) 将它们转换为整数或浮点数，例如

select * from Street_Tree_List
where latitude > cast(:min_latitude as real)
and latitude < cast(:max_latitude as real)

Datasette 不允许包含字符串 PRAGMA 的自定义 SQL 查询（只有极少数例外），因为 SQLite pragma 语句可以在运行时更改数据库设置。如果您需要在查询中包含字符串“pragma”，可以使用命名参数安全地完成。

视图¶

如果您想将一些预先编写好的 SQL 查询与您的 Datasette 托管数据库打包在一起，有两种方法。第一种是在数据库中包含 SQL 视图 - Datasette 将在数据库索引页面上列出这些视图。

创建视图的最快方法是使用 SQLite 命令行界面

$ sqlite3 sf-trees.db
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> CREATE VIEW demo_view AS select qSpecies from Street_Tree_List;
<CTRL+D>

预设查询¶

作为向数据库添加视图的替代方法，您可以在 metadata.json 文件中定义预设查询。示例如下

{
    "databases": {
       "sf-trees": {
           "queries": {
               "just_species": {
                   "sql": "select qSpecies from Street_Tree_List"
               }
           }
       }
    }
}

然后像这样运行 Datasette

datasette sf-trees.db -m metadata.json

每个预设查询将列在数据库索引页面上，并有自己的 URL，位于

/database-name/canned-query-name

对于上面的示例，URL 将是

/sf-trees/just_species

您可以选择包含 "title" 和 "description" 键，以在预设查询页面上显示标题和描述。与常规表元数据一样，您也可以指定 "description_html"，以便将描述渲染为 HTML（而不是对 HTML 特殊字符进行转义）。

预设查询参数¶

预设查询支持命名参数，因此如果您在 SQL 中包含这些参数，就可以使用预设查询页面上的表单字段或通过将其添加到 URL 来输入它们。这意味着预设查询可以用于基于精心设计的 SQL 语句创建自定义 JSON API。

这是一个带有命名参数的预设查询示例

select neighborhood, facet_cities.name, state
from facetable
  join facet_cities on facetable.city_id = facet_cities.id
where neighborhood like '%' || :text || '%'
order by neighborhood;

在预设查询元数据中（此处使用使用 YAML 配置元数据作为 metadata.yaml），它看起来像这样

databases:
  fixtures:
    queries:
      neighborhood_search:
        sql: |-
          select neighborhood, facet_cities.name, state
          from facetable
            join facet_cities on facetable.city_id = facet_cities.id
          where neighborhood like '%' || :text || '%'
          order by neighborhood
        title: Search neighborhoods

这是使用 JSON 的等效配置（作为 metadata.json）

{
    "databases": {
        "fixtures": {
            "queries": {
                "neighborhood_search": {
                    "sql": "select neighborhood, facet_cities.name, state\nfrom facetable\n  join facet_cities on facetable.city_id = facet_cities.id\nwhere neighborhood like '%' || :text || '%'\norder by neighborhood",
                    "title": "Search neighborhoods"
                }
            }
        }
    }
}

请注意，我们在这里使用 SQLite 字符串连接 - || 运算符 - 以向用户提供的字符串添加通配符 % 字符。

您可以在这里尝试这个预设查询：https://latest.datasette.io/fixtures/neighborhood_search?text=town

在此示例中，使用正则表达式自动从查询中提取 :text 命名参数。

您也可以使用 "params" 键提供明确的命名参数列表，如下所示

databases:
  fixtures:
    queries:
      neighborhood_search:
        params:
        - text
        sql: |-
          select neighborhood, facet_cities.name, state
          from facetable
            join facet_cities on facetable.city_id = facet_cities.id
          where neighborhood like '%' || :text || '%'
          order by neighborhood
        title: Search neighborhoods

额外的预设查询选项¶

可以在 YAML 或 JSON 配置中为预设查询指定其他选项。

hide_sql¶

预设查询默认在页面顶部显示其 SQL 查询。如果查询非常长，您可能希望默认隐藏它，并提供一个“显示”链接使其可见。

添加 "hide_sql": true 选项可以默认隐藏 SQL 查询。

fragment¶

一些插件，例如 datasette-vega，可以通过在 URL 的片段哈希（即 # 符号后面的部分）中包含额外数据来配置。

您可以使用 "fragment" 键设置默认的片段哈希，该哈希将包含在从数据库索引页面链接到预设查询的 URL 中。

此示例演示了 fragment 和 hide_sql

{
    "databases": {
        "fixtures": {
            "queries": {
                "neighborhood_search": {
                    "sql": "select neighborhood, facet_cities.name, state\nfrom facetable join facet_cities on facetable.city_id = facet_cities.id\nwhere neighborhood like '%' || :text || '%' order by neighborhood;",
                    "fragment": "fragment-goes-here",
                    "hide_sql": true
                }
            }
        }
    }
}

在此查看此功能的演示。

可写预设查询¶

预设查询默认是只读的。您可以使用 "write": true 键指示预设查询可以写入数据库。

有关如何使用 "allow" 键向预设查询添加权限检查的详细信息，请参阅控制对特定预设查询的访问。

{
    "databases": {
        "mydatabase": {
            "queries": {
                "add_name": {
                    "sql": "INSERT INTO names (name) VALUES (:name)",
                    "write": true
                }
            }
        }
    }
}

此配置将在 /mydatabase/add_name 创建一个页面，显示一个包含 name 字段的表单。提交该表单将执行配置的 INSERT 查询。

您可以使用以下可选属性自定义 Datasette 如何表示成功和错误

on_success_message - 查询成功时显示的消息
on_success_redirect - 查询成功时用户重定向到的路径或 URL
on_error_message - 查询抛出错误时显示的消息
on_error_redirect - 查询出错时用户重定向到的路径或 URL

例如

{
    "databases": {
        "mydatabase": {
            "queries": {
                "add_name": {
                    "sql": "INSERT INTO names (name) VALUES (:name)",
                    "write": true,
                    "on_success_message": "Name inserted",
                    "on_success_redirect": "/mydatabase/names",
                    "on_error_message": "Name insert failed",
                    "on_error_redirect": "/mydatabase"
                }
            }
        }
    }
}

您可以使用 "params" 明确列出应显示为表单字段的命名参数 - 否则将自动检测它们。

您可以在页面初次加载时使用查询字符串预填充表单字段，例如 /mydatabase/add_name?name=Prepopulated。用户需要提交表单来执行查询。

魔术参数¶

以下划线开头的命名参数是特殊的：它们可以用来自动添加由 Datasette 创建的、不包含在传入的表单字段或查询字符串中的值。

这些魔术参数仅支持预设查询：为避免安全问题（例如提取用户私有 cookie 的查询），它们不适用于用户作为自定义 SQL 查询执行的 SQL。

可用的魔术参数有

_actor_* - 例如 _actor_id, _actor_name: 当前已认证的Actors的字段。
_header_* - 例如 _header_user_agent: 传入 HTTP 请求的头部。键应为小写，并将连字符转换为下划线，例如 _header_user_agent 或 _header_accept_language。
_cookie_* - 例如 _cookie_lang: 该名称的传入 cookie 的值。
_now_epoch: 自 Unix 纪元以来的秒数。
_now_date_utc: UTC 日期，例如 2020-06-01
_now_datetime_utc: UTC 的 ISO 8601 日期时间，例如 2020-06-24T18:01:07Z
_random_chars_* - 例如 _random_chars_128: 指定长度的随机字符串。

这是一个示例配置（这次使用 metadata.yaml，因为它更好地支持多行 SQL 查询），它添加了已认证用户的消息，并使用魔术参数存储了各种附加元数据

databases:
  mydatabase:
    queries:
      add_message:
        allow:
          id: "*"
        sql: |-
          INSERT INTO messages (
            user_id, message, datetime
          ) VALUES (
            :_actor_id, :message, :_now_datetime_utc
          )
        write: true

在 /mydatabase/add_message 显示的表单将只有一个用于 message 的字段 - 其他参数将由魔术参数机制填充。

插件可以使用 register_magic_parameters(datasette) 钩子添加额外的自定义魔术参数。

可写预设查询的 JSON API¶

可写预设查询也可以通过 JSON API 访问。您可以使用 JSON 向其 POST 数据，并且可以请求将其响应作为 JSON 返回。

要向可写预设查询提交 JSON，请将键/值参数编码为 JSON 文档

POST /mydatabase/add_message

{"message": "Message goes here"}

您也可以继续使用常规的表单编码提交数据，如下所示

POST /mydatabase/add_message

message=Message+goes+here

有三种选项可以指定您希望请求响应返回 JSON 数据，而不是 HTTP 重定向到另一个页面。

在您的请求中设置 Accept: application/json 头部
在您 POST 的 URL 中包含 ?_json=1
在您的 JSON 主体中包含 "_json": 1，或在您的表单编码主体中包含 &_json=1

JSON 响应将如下所示

{
    "ok": true,
    "message": "Query executed, 1 row affected",
    "redirect": "/data/add_name"
}

这里的 "message" 和 "redirect" 值将考虑 on_success_message, on_success_redirect, on_error_message 和 on_error_redirect 的设置（如果已设置）。

分页¶

Datasette 的默认表分页设计得非常高效。一旦处理数千行数据，SQL OFFSET/LIMIT 分页可能会带来显著的性能损失，因为每一页仍然需要数据库扫描之前的所有行才能找到正确的偏移量。

当对表进行分页时，Datasette 转而按主键对表中的行进行排序，并对前一页的最后一个主键执行 WHERE 子句。例如

select rowid, * from Tree_List where rowid > 200 order by rowid limit 101

这代表此特定表的第三页，页面大小为 100。

请注意，我们在 limit 子句中请求 101 项而不是 100 项。这使我们能够检测是否位于结果的最后一页：如果查询返回的行少于 101 行，我们就知道已经到达了分页集的末尾。Datasette 只会返回前 100 行 - 第 101 行仅用于检测是否应该有下一页。

由于 where 子句作用于主键上的索引，因此即使对于在整个分页集中位置较远的记录，查询速度也极快。

跨数据库查询¶

SQLite 具有执行跨多个数据库连接查询的能力。最多可以将十个数据库附加到单个 SQLite 连接并一起查询。

如果使用 --crossdb 选项启动 Datasette，则可以执行跨多个数据库的连接查询

datasette fixtures.db extra_database.db --crossdb

如果以这种方式启动，可以使用 /_memory 页面执行跨多个数据库的连接查询。

对附加数据库中表的引用应在其前面加上数据库名称和一个点号。

例如，此查询将显示上述两个数据库中的表列表

select
  'fixtures' as database, *
from
  [fixtures].sqlite_master
union
select
  'extra_database' as database, *
from
  [extra_database].sqlite_master

在此尝试.