JSON API

Datasette 为您的 SQLite 数据库提供了 JSON API。您可以通过 Datasette 用户界面执行的任何操作，也可以通过 API 以 JSON 格式访问。

要访问页面的 API，您可以点击该页面上的 .json 链接，或者编辑 URL 并添加 .json 扩展名。

如果您使用 --cors 选项启动 Datasette，每个 JSON 端点都将包含以下额外的 HTTP 头

Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: Authorization
Access-Control-Expose-Headers: Link

这意味着在任何域上运行的 JavaScript 都能够发出跨域请求来获取数据。

如果您启动 Datasette 时不带 --cors 选项，则只有与 Datasette 在同一域上运行的 JavaScript 才能访问 API。

不同格式

SQLite 表或自定义查询数据的默认 JSON 表示形式如下所示

{
    "database": "sf-trees",
    "table": "qSpecies",
    "columns": [
        "id",
        "value"
    ],
    "rows": [
        [
            1,
            "Myoporum laetum :: Myoporum"
        ],
        [
            2,
            "Metrosideros excelsa :: New Zealand Xmas Tree"
        ],
        [
            3,
            "Pinus radiata :: Monterey Pine"
        ]
    ],
    "truncated": false,
    "next": "100",
    "next_url": "http://127.0.0.1:8001/sf-trees-02c8ef1/qSpecies.json?_next=100",
    "query_ms": 1.9571781158447266
}

columns 键列出了返回的列，而 rows 键则返回一个列表的列表，其中每个子列表代表一行。每一行中值的顺序与列的顺序相对应。

可以使用 _shape 参数来访问 rows 键的替代格式，这可能对您的应用程序更方便。有以下几种选项：

• ?_shape=arrays - "rows" 是默认选项，如上所示

• ?_shape=objects - "rows" 是一个 JSON 键/值对象列表

• ?_shape=array - 一个 JSON 对象数组

• ?_shape=array&_nl=on - 一个以换行符分隔的 JSON 对象列表

• ?_shape=arrayfirst - 一个只包含每行第一个值的扁平 JSON 数组

• ?_shape=object - 一个使用行主键作为键的 JSON 对象

_shape=objects 格式如下所示

{
    "database": "sf-trees",
    ...
    "rows": [
        {
            "id": 1,
            "value": "Myoporum laetum :: Myoporum"
        },
        {
            "id": 2,
            "value": "Metrosideros excelsa :: New Zealand Xmas Tree"
        },
        {
            "id": 3,
            "value": "Pinus radiata :: Monterey Pine"
        }
    ]
}

_shape=array 格式如下所示

[
    {
        "id": 1,
        "value": "Myoporum laetum :: Myoporum"
    },
    {
        "id": 2,
        "value": "Metrosideros excelsa :: New Zealand Xmas Tree"
    },
    {
        "id": 3,
        "value": "Pinus radiata :: Monterey Pine"
    }
]

_shape=array&_nl=on 格式如下所示

{"id": 1, "value": "Myoporum laetum :: Myoporum"}
{"id": 2, "value": "Metrosideros excelsa :: New Zealand Xmas Tree"}
{"id": 3, "value": "Pinus radiata :: Monterey Pine"}

_shape=arrayfirst 格式如下所示

[1, 2, 3]

_shape=object 格式如下所示

{
    "1": {
        "id": 1,
        "value": "Myoporum laetum :: Myoporum"
    },
    "2": {
        "id": 2,
        "value": "Metrosideros excelsa :: New Zealand Xmas Tree"
    },
    "3": {
        "id": 3,
        "value": "Pinus radiata :: Monterey Pine"
    }
]

object 格式仅适用于针对表的查询，因为自定义 SQL 查询和视图没有明确的主键，所以无法使用这种格式返回。

object 键总是字符串。如果您的表有复合主键，则 object 键将是一个逗号分隔的字符串。

分页

默认的 JSON 表示形式包含一个 "next_url" 键，可用于访问下一页结果。如果该键为 null 或缺失，则表示您已到达结果的最后一页。

其他表示形式将分页信息包含在 link HTTP 头中。该头大致如下所示

link: <https://latest.datasette.io/fixtures/sortable.json?_next=d%2Cv>; rel="next"

这是一个使用 requests 构建的 Python 函数示例，它返回从这些 API 端点之一分页获取的所有项目的列表

def paginate(url):
    items = []
    while url:
        response = requests.get(url)
        try:
            url = response.links.get("next").get("url")
        except AttributeError:
            url = None
        items.extend(response.json())
    return items

特殊的 JSON 参数

每个可以返回 JSON 的 Datasette 端点也接受以下查询字符串参数

?_shape=SHAPE

要返回的 JSON 的格式，如上文所述。

?_nl=on

与 ?_shape=array 一起使用时，生成以换行符分隔的 JSON 对象。

?_json=COLUMN1&_json=COLUMN2

如果您的任何 SQLite 列包含 JSON 值，您可以使用一个或多个 _json= 参数请求将这些列作为常规 JSON 返回。如果没有此参数，这些列将作为 JSON 对象返回，这些对象已被双重编码为 JSON 字符串值。

比较没有此参数的查询与使用此参数的查询

?_json_infinity=on

如果您的数据包含正无穷大或负无穷大值，Datasette 在将其作为 JSON 返回时会将其替换为 None。如果您传递 _json_infinity=1，Datasette 将改为将其返回为 Infinity 或 -Infinity，这虽然不是有效的 JSON，但可以由某些自定义 JSON 解析器处理。

?_timelimit=MS

设置查询的自定义时间限制（以毫秒为单位）。您可以将其用于乐观查询，如果您希望查询耗时过长时 Datasette 放弃，例如，如果您想实现自动补全搜索，但仅限于执行时间少于 10ms 的情况。

?_ttl=SECONDS

此响应应被 HTTP 代理缓存多少秒？使用 ?_ttl=0 完全禁用此请求的 HTTP 缓存。

?_trace=1

为此页面启用追踪：请求期间执行的 SQL 查询将被收集并包含在响应中，对于 JSON 响应，将位于新的 "_traces" 键中，如果响应是 HTML，则位于页面底部。

此处返回的数据结构应被视为高度不稳定，并且很可能发生变化。

仅当trace_debug 设置启用时可用。

表格参数

Datasette 表格视图接受许多特殊的查询字符串参数。

列过滤参数

您可以使用查询字符串参数，根据列值过滤表格返回的数据。

?column__exact=value 或 ?_column=value

返回指定列与值完全匹配的行。

?column__not=value

返回指定列与值不匹配的行。

?column__contains=value

字符串列包含指定值的行（在 SQL 中是 column like "%value%"）。

?column__endswith=value

字符串列以指定值结尾的行（在 SQL 中是 column like "%value"）。

?column__startswith=value

字符串列以指定值开头的行（在 SQL 中是 column like "value%"）。

?column__gt=value

大于指定值的行。

?column__gte=value

大于或等于指定值的行。

?column__lt=value

小于指定值的行。

?column__lte=value

小于或等于指定值的行。

?column__like=value

使用 LIKE 子句匹配行，不区分大小写，并使用 % 作为通配符。

?column__notlike=value

匹配与提供的 LIKE 子句不匹配的行。

?column__glob=value

类似于 LIKE，但使用 Unix 通配符语法且区分大小写。

?column__in=value1,value2,value3

匹配提供的任一值的行。

您可以使用逗号分隔的字符串，或者使用 JSON 数组。

如果您的匹配值之一本身包含逗号，则 JSON 数组选项非常有用

?column__in=["value","value,with,commas"]

?column__notin=value1,value2,value3

列与提供的任何值都不匹配的行。__in= 的反义。也支持 JSON 数组。

?column__arraycontains=value

适用于包含 JSON 数组的列 - 如果该数组中的任何值与提供的值匹配则匹配。

这仅在启用 json1 SQLite 扩展时可用。

?column__arraynotcontains=value

适用于包含 JSON 数组的列 - 如果该数组中的任何值都不与提供的值匹配则匹配。

这仅在启用 json1 SQLite 扩展时可用。

?column__date=value

列是一个发生在指定 YYYY-MM-DD 日期的日期戳，例如 2018-01-02。

?column__isnull=1

匹配列为 null 的行。

?column__notnull=1

匹配列不为 null 的行。

?column__isblank=1

匹配列为空白的行，即 null 或空字符串。

?column__notblank=1

匹配列不为空白的行。

特殊的表格参数

?_col=COLUMN1&_col=COLUMN2

列出要显示的特定列。这些列将与任何主键一起显示。

?_nocol=COLUMN1&_nocol=COLUMN2

列出要隐藏的特定列 - 未列出的任何列都将显示。主键不能被隐藏。

?_labels=on/off

为每个可能的外键列展开外键引用。见下文。

?_label=COLUMN1&_label=COLUMN2

为一个或多个指定列展开外键引用。

?_size=1000 或 ?_size=max

设置自定义页面大小。此值不能超过传递给 datasette serve 的 max_returned_rows 限制。使用 max 获取 max_returned_rows 的值。

?_sort=COLUMN

按指定列对结果进行排序。

?_sort_desc=COLUMN

按指定列对结果进行降序排序。

?_search=keywords

对于配置了全文搜索的 SQLite 表，使用提供的关键字执行搜索。

?_search_COLUMN=keywords

类似于 _search=，但允许您指定要搜索的列，而不是搜索 FTS 已索引的所有列。

?_searchmode=raw

使用此选项，传递给 ?_search= 或 ?_search_COLUMN= 的查询将不会转义特殊字符。这意味着您可以利用完整的高级 SQLite FTS 语法，但如果语法错误，这可能会导致错误。

?_where=SQL-fragment

如果启用了execute-sql 权限，此参数可用于传递一个或多个额外的 SQL 片段，以用于查询表所使用的 SQL 的 WHERE 子句中。

这对于正在构建需要进行一些创意操作，但仍希望利用表格视图提供的其他便利（例如分面）的 JavaScript 应用程序特别有用，因此无需构建完全自定义的 SQL 查询。

一些例子

• facetable?_where=_neighborhood like "%c%"&_where=_city_id=3

• facetable?_where=_city_id in (select id from facet_cities where name != "Detroit")

?_through={json}

这可用于通过与另一张表的 JOIN 来过滤行。

JSON 参数必须包含三个键：table、column 和 value。

table 必须是当前表通过外键关系关联到的表。

column 必须是那张其他表中的一列。

value 是您要匹配的值。

例如，要过滤 roadside_attractions 以仅显示具有“博物馆”特征的景点，您可以构建此 JSON

{
    "table": "roadside_attraction_characteristics",
    "column": "characteristic_id",
    "value": "1"
}

作为一个 URL，它看起来像这样

?_through={%22table%22:%22roadside_attraction_characteristics%22,%22column%22:%22characteristic_id%22,%22value%22:%221%22}

这是一个例子。

?_next=TOKEN

通过继续令牌进行分页 - 传递前一页在 "next" 属性中返回的令牌。

?_facet=column

按列分面。可以多次应用，参见分面。仅在默认 JSON 输出上有效，不适用于任何自定义格式。

?_facet_size=100

增加每个分面返回的分面结果数量。使用 ?_facet_size=max 获取最大可用大小，该大小由max_returned_rows 确定。

?_nofacet=1

禁用此页面的所有分面和分面建议，包括在 metadata.json 中定义的分面。

?_nosuggest=1

禁用此页面的分面建议。

?_nocount=1

禁用此页面使用的 select count(*) 查询 - 将改为返回计数为 None。

展开外键引用

Datasette 可以检测外键关系并将这些引用解析为标签。HTML 界面默认对每个检测到的外键列执行此操作 - 您可以使用 ?_labels=off 将其关闭。

您可以使用 _labels=on 或 _label=COLUMN 特殊查询字符串参数请求在 JSON 中展开外键。下面是一个展开的行示例：

[
    {
        "rowid": 1,
        "TreeID": 141565,
        "qLegalStatus": {
            "value": 1,
            "label": "Permitted Site"
        },
        "qSpecies": {
            "value": 1,
            "label": "Myoporum laetum :: Myoporum"
        },
        "qAddress": "501X Baker St",
        "SiteOrder": 1
    }
]

在外键表中用作标签的列可以在 metadata.json 中指定 - 参见为表指定标签列。

发现页面的 JSON

Datasette 提供的大多数 HTML 页面都提供了使用 HTML link 机制发现其 JSON 等效项的机制。

您可以在这些页面的源代码顶部附近找到，如下所示

<link rel="alternate"
  type="application/json+datasette"
  href="https://latest.datasette.io/fixtures/sortable.json">

该页面的 JSON URL 也通过 Link HTTP 头提供

Link: https://latest.datasette.io/fixtures/sortable.json; rel="alternate"; type="application/json+datasette"