# pypi_packages

name: github-to-sqlite
summary: Save data from GitHub to a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/dogsheep/github-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/github-to-sqlite/
project_url: https://pypi.org/project/github-to-sqlite/
project_urls: {"Homepage": "https://github.com/dogsheep/github-to-sqlite"}
release_url: https://pypi.org/project/github-to-sqlite/2.9/
version: 2.9
description:
  # github-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/github-to-sqlite.svg)](https://pypi.org/project/github-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/github-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/github-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/github-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/github-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/github-to-sqlite/blob/main/LICENSE)

  Save data from GitHub to a SQLite database.

  <!-- toc -->

  - [Demo](#demo)
  - [How to install](#how-to-install)
  - [Authentication](#authentication)
  - [Fetching issues for a repository](#fetching-issues-for-a-repository)
  - [Fetching pull requests for a repository](#fetching-pull-requests-for-a-repository)
  - [Fetching issue comments for a repository](#fetching-issue-comments-for-a-repository)
  - [Fetching commits for a repository](#fetching-commits-for-a-repository)
  - [Fetching releases for a repository](#fetching-releases-for-a-repository)
  - [Fetching tags for a repository](#fetching-tags-for-a-repository)
  - [Fetching contributors to a repository](#fetching-contributors-to-a-repository)
  - [Fetching repos belonging to a user or organization](#fetching-repos-belonging-to-a-user-or-organization)
  - [Fetching specific repositories](#fetching-specific-repositories)
  - [Fetching repos that have been starred by a user](#fetching-repos-that-have-been-starred-by-a-user)
  - [Fetching users that have starred specific repos](#fetching-users-that-have-starred-specific-repos)
  - [Fetching GitHub Actions workflows](#fetching-github-actions-workflows)
  - [Scraping dependents for a repository](#scraping-dependents-for-a-repository)
  - [Fetching emojis](#fetching-emojis)
  - [Making authenticated API calls](#making-authenticated-api-calls)

  <!-- tocstop -->

  ## Demo

  https://github-to-sqlite.dogsheep.net/ hosts a [Datasette](https://datasette.io/) demo of a database created by [running this tool](https://github.com/dogsheep/github-to-sqlite/blob/main/.github/workflows/deploy-demo.yml#L40-L60) against all of the repositories in the [Dogsheep GitHub organization](https://github.com/dogsheep), plus the [datasette](https://github.com/simonw/datasette) and [sqlite-utils](https://github.com/simonw/sqlite-utils) repositories.

  ## How to install

      $ pip install github-to-sqlite

  ## Authentication

  Create a GitHub personal access token: https://github.com/settings/tokens

  Run this command and paste in your new token:

      $ github-to-sqlite auth

  This will create a file called `auth.json` in your current directory containing the required value. To save the file at a different path or filename, use the `--auth=myauth.json` option.

  As an alternative to using an `auth.json` file you can add your access token to an environment variable called `GITHUB_TOKEN`.

  ## Fetching issues for a repository

  The `issues` command retrieves all of the issues belonging to a specified repository.

      $ github-to-sqlite issues github.db simonw/datasette

  If an `auth.json` file is present it will use the token from that file. It works without authentication for public repositories but you should be aware that GitHub have strict IP-based rate limits for unauthenticated requests.

  You can point to a different location of `auth.json` using `-a`:

      $ github-to-sqlite issues github.db simonw/datasette -a /path/to/auth.json

  You can use the `--issue` option one or more times to load specific issues:

      $ github-to-sqlite issues github.db simonw/datasette --issue=1

  Example: [issues table](https://github-to-sqlite.dogsheep.net/github/issues)

  ## Fetching pull requests for a repository

  While pull requests are a type of issue, you will get more information on pull requests by pulling them separately. For example, whether a pull request has been merged and when.

  Following the API of issues, the `pull-requests` command retrieves all of the pull requests belonging to a specified repository.

      $ github-to-sqlite pull-requests github.db simonw/datasette

  You can use the `--pull-request` option one or more times to load specific pull request:

      $ github-to-sqlite pull-requests github.db simonw/datasette --pull-request=81

  Note that the `merged_by` column on the `pull_requests` table will only be populated for pull requests that are loaded using the `--pull-request` option - the GitHub API does not return this field for pull requests that are loaded in bulk.

  You can load only pull requests in a certain state with the `--state` option:

      $ github-to-sqlite pull-requests --state=open github.db simonw/datasette

  Pull requests across an entire organization (or more than one) can be loaded with `--org`:

      $ github-to-sqlite pull-requests --state=open --org=psf --org=python github.db

  You can use a search query to find pull requests.  Note that no more than 1000 will be loaded (this is a GitHub API limitation), and some data will be missing (base and head SHAs).  When using searches, other filters are ignored; put all criteria into the search itself:

      $ github-to-sqlite pull-requests --search='org:python defaultdict state:closed created:<2023-09-01' github.db

  Example: [pull_requests table](https://github-to-sqlite.dogsheep.net/github/pull_requests)

  ## Fetching issue comments for a repository

  The `issue-comments` command retrieves all of the comments on all of the issues in a repository.

  It is recommended you run `issues` first, so that each imported comment can have a foreign key pointing to its issue.

      $ github-to-sqlite issues github.db simonw/datasette
      $ github-to-sqlite issue-comments github.db simonw/datasette

  You can use the `--issue` option to only load comments for a specific issue within that repository, for example:

      $ github-to-sqlite issue-comments github.db simonw/datasette --issue=1

  Example: [issue_comments table](https://github-to-sqlite.dogsheep.net/github/issue_comments)

  ## Fetching commits for a repository

  The `commits` command retrieves details of all of the commits for one or more repositories. It currently fetches the SHA, commit message and author and committer details; it does not retrieve the full commit body.

      $ github-to-sqlite commits github.db simonw/datasette simonw/sqlite-utils

  The command accepts one or more repositories.

  By default it will stop as soon as it sees a commit that has previously been retrieved. You can force it to retrieve all commits (including those that have been previously inserted) using `--all`.

  Example: [commits table](https://github-to-sqlite.dogsheep.net/github/commits)

  ## Fetching releases for a repository

  The `releases` command retrieves the releases for one or more repositories.

      $ github-to-sqlite releases github.db simonw/datasette simonw/sqlite-utils

  The command accepts one or more repositories.

  Example: [releases table](https://github-to-sqlite.dogsheep.net/github/releases)

  ## Fetching tags for a repository

  The `tags` command retrieves all of the tags for one or more repositories.

      $ github-to-sqlite tags github.db simonw/datasette simonw/sqlite-utils

  Example: [tags table](https://github-to-sqlite.dogsheep.net/github/tags)

  ## Fetching contributors to a repository

  The `contributors` command retrieves details of all of the contributors for one or more repositories.

      $ github-to-sqlite contributors github.db simonw/datasette simonw/sqlite-utils

  The command accepts one or more repositories. It populates a `contributors` table, with foreign keys to `repos` and `users` and a `contributions` table listing the number of commits to that repository for each contributor.

  Example: [contributors table](https://github-to-sqlite.dogsheep.net/github/contributors)

  ## Fetching repos belonging to a user or organization

  The `repos` command fetches repos belonging to a user or organization.

  Without any other arguments, this command will fetch all repos that the currently authenticated user owns, collaborates on or can access via one of their organizations:

      $ github-to-sqlite repos github.db

  To fetch repos belonging to a specific user or organization, provide their username as an argument:

      $ github-to-sqlite repos github.db dogsheep # organization
      $ github-to-sqlite repos github.db simonw # user

  You can pass more than one username to fetch for multiple users or organizations at once:

      $ github-to-sqlite repos github.db simonw dogsheep

  Add the `--readme` option to save the README for the repo in a column called `readme`. Add `--readme-html` to save the HTML rendered version of the README into a column called `readme_html`.

  Example: [repos table](https://github-to-sqlite.dogsheep.net/github/repos)

  ## Fetching specific repositories

  You can use `-r` with the `repos` command one or more times to fetch just specific repositories.

      $ github-to-sqlite repos github.db -r simonw/datasette -r dogsheep/github-to-sqlite

  ## Fetching repos that have been starred by a user

  The `starred` command fetches the repos that have been starred by a user.

      $ github-to-sqlite starred github.db simonw

  If you are using an `auth.json` file you can omit the username to retrieve the starred repos for the authenticated user.

  Example: [stars table](https://github-to-sqlite.dogsheep.net/github/stars)

  ## Fetching users that have starred specific repos

  The `stargazers` command fetches the users that have starred the specified repos.

      $ github-to-sqlite stargazers github.db simonw/datasette dogsheep/github-to-sqlite

  You can specify one or more repository using `owner/repo` syntax.

  Users fetched using this command will be inserted into the `users` table. Many-to-many records showing which repository they starred will be added to the `stars` table.

  ## Fetching GitHub Actions workflows

  The `workflows` command fetches the YAML workflow configurations from each repository's `.github/workflows` directory and parses them to populate `workflows`, `jobs` and `steps` tables.

      $ github-to-sqlite workflows github.db simonw/datasette dogsheep/github-to-sqlite

  You can specify one or more repository using `owner/repo` syntax.

  Example: [workflows table](https://github-to-sqlite.dogsheep.net/github/workflows), [jobs table](https://github-to-sqlite.dogsheep.net/github/jobs), [steps table](https://github-to-sqlite.dogsheep.net/github/steps)

  ## Scraping dependents for a repository

  The GitHub dependency graph can show other GitHub projects that depend on a specific repo, for example [simonw/datasette/network/dependents](https://github.com/simonw/datasette/network/dependents).

  This data is not yet available through the GitHub API. The `scrape-dependents` command scrapes those pages and uses the GitHub API to load full versions of the dependent repositories.

      $ github-to-sqlite scrape-dependents github.db simonw/datasette

  The command accepts one or more repositories.

  Add `-v` for verbose output.

  Example: [dependents table](https://github-to-sqlite.dogsheep.net/github/dependents?_sort_desc=first_seen_utc)

  ## Fetching emojis

  You can fetch a list of every emoji supported by GitHub using the `emojis` command:

      $ github-to-sqlite emojis github.db

  This will create a table called `emojis` with a primary key `name` and a `url` column.

  If you add the `--fetch` option the command will also fetch the binary content of the images and place them in an `image` column:

      $ github-to-sqlite emojis emojis.db -f
      [########----------------------------]  397/1799   22%  00:03:43

  You can then use the [datasette-render-images](https://github.com/simonw/datasette-render-images) plugin to browse them visually.

  Example: [emojis table](https://github-to-sqlite.dogsheep.net/github/emojis)

  ## Making authenticated API calls

  The `github-to-sqlite get` command provides a convenient shortcut for making authenticated calls to the API. Once you have created your `auth.json` file (or set a `GITHUB_TOKEN` environment variable) you can use it like this:

      $ github-to-sqlite get https://api.github.com/gists

  This will make an authenticated call to the URL you provide and pretty-print the resulting JSON to the console.

  You can omit the `https://api.github.com/` prefix, for example:

      $ github-to-sqlite get /gists

  Many GitHub APIs are [paginated using the HTTP Link header](https://docs.github.com/en/rest/guides/traversing-with-pagination). You can follow this pagination and output a list of all of the resulting items using `--paginate`:

      $ github-to-sqlite get /users/simonw/repos --paginate

  You can outline newline-delimited JSON for each item using `--nl`. This can be useful for streaming items into another tool.

      $ github-to-sqlite get /users/simonw/repos --nl

requires_dist:
  ["sqlite-utils >=2.7.2", "requests", "PyYAML", "pytest ; extra == 'test'", "requests-mock ; extra == 'test'", "bs4 ; extra == 'test'"]


name: google-drive-to-sqlite
summary: Create a SQLite database containing metadata from Google Drive
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/google-drive-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/google-drive-to-sqlite/
project_url: https://pypi.org/project/google-drive-to-sqlite/
release_url: https://pypi.org/project/google-drive-to-sqlite/0.4/
requires_python: >=3.6
version: 0.4
description:
  # google-drive-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/google-drive-to-sqlite.svg)](https://pypi.org/project/google-drive-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/google-drive-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/google-drive-to-sqlite/releases)
  [![Tests](https://github.com/simonw/google-drive-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/google-drive-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/google-drive-to-sqlite/blob/master/LICENSE)

  Create a SQLite database containing metadata from [Google Drive](https://www.google.com/drive)

  If you use Google Drive, and especially if you have shared drives with other people there's a good chance you have hundreds or even thousands of files that you may not be fully aware of.

  This tool can download metadata about those files - their names, sizes, folders, content types, permissions, creation dates and more - and store them in a SQLite database.

  This lets you use SQL to analyze your Google Drive contents, using [Datasette](https://datasette.io/) or the SQLite command-line tool or any other SQLite database browsing software.

  ## Installation

  Install this tool using `pip`:

      pip install google-drive-to-sqlite

  ## Quickstart

  Authenticate with Google Drive by running:

      google-drive-to-sqlite auth

  Now create a SQLite database with metadata about all of the files you have starred using:

      google-drive-to-sqlite files starred.db --starred

  You can explore the resulting database using [Datasette](https://datasette.io/):

      $ pip install datasette
      $ datasette starred.db
      INFO:     Started server process [24661]
      INFO:     Uvicorn running on http://127.0.0.1:8001

  ## Authentication

  > :warning: **This application has not yet been verified by Google** - you may find you are unable to authenticate until that verification is complete. [#10](https://github.com/simonw/google-drive-to-sqlite/issues/10)
  >
  > You can work around this issue by [creating your own OAuth client ID key](https://til.simonwillison.net/googlecloud/google-oauth-cli-application) and passing it to the `auth` command using `--google-client-id` and `--google-client-secret`.

  First, authenticate with Google Drive using the `auth` command:

      $ google-drive-to-sqlite auth
      Visit the following URL to authenticate with Google Drive

      https://accounts.google.com/o/oauth2/v2/auth?...

      Then return here and paste in the resulting code:
      Paste code here: 

  Follow the link, sign in with Google Drive and then copy and paste the resulting code back into the tool.

  This will save an authentication token to the file called `auth.json` in the current directory.

  To specify a different location for that file, use the `--auth` option:

      google-drive-to-sqlite auth --auth ~/google-drive-auth.json

  The `auth` command also provides options for using a different scope, Google client ID and Google client secret. You can use these to create your own custom authentication tokens that can work with other Google APIs, see [issue #5](https://github.com/simonw/google-drive-to-sqlite/issues/5) for details.

  Full `--help`:

  <!-- [[[cog
  import cog
  from google_drive_to_sqlite import cli
  from click.testing import CliRunner
  runner = CliRunner()
  result = runner.invoke(cli.cli, ["auth", "--help"])
  help = result.output.replace("Usage: cli", "Usage: google-drive-to-sqlite")
  cog.out(
      "```\n{}\n```\n".format(help)
  )
  ]]] -->
  ```
  Usage: google-drive-to-sqlite auth [OPTIONS]

    Authenticate user and save credentials

  Options:
    -a, --auth FILE              Path to save token, defaults to auth.json
    --google-client-id TEXT      Custom Google client ID
    --google-client-secret TEXT  Custom Google client secret
    --scope TEXT                 Custom token scope
    --help                       Show this message and exit.

  ```
  <!-- [[[end]]] -->

  To revoke the token that is stored in `auth.json`, such that it cannot be used to access Google Drive in the future, run the `revoke` command:

      google-drive-to-sqlite revoke

  Or if your token is stored in another location:

      google-drive-to-sqlite revoke -a ~/google-drive-auth.json

  You will need to obtain a fresh token using the `auth` command in order to continue using this tool.

  ## google-drive-to-sqlite files

  To retrieve metadata about the files in your Google Drive, or a folder or search within it, use the `google-drive-to-sqlite files` command.

  This will default to writing details about every file in your Google Drive to a SQLite database:

      google-drive-to-sqlite files files.db

  Files and folders will be written to databases tables, which will be created if they do not yet exist. The database schema is [shown below](#database-schema).

  If a file or folder already exists, based on a matching `id`, it will be replaced with fresh data.

  Instead of writing to SQLite you can use `--json` to output as JSON, or `--nl` to output as newline-delimited JSON:

      google-drive-to-sqlite files --nl

  Use `--folder ID` to retrieve everything in a specified folder and its sub-folders:

      google-drive-to-sqlite files files.db --folder 1E6Zg2X2bjjtPzVfX8YqdXZDCoB3AVA7i

  Use `--q QUERY` to use a [custom search query](https://developers.google.com/drive/api/v3/reference/query-ref):

      google-drive-to-sqlite files files.db -q "viewedByMeTime > '2022-01-01'"

  The following shortcut options help build queries:

  - `--full-text TEXT` to search for files where the full text matches a search term
  - `--starred` for files and folders you have starred
  - `--trashed` for files and folders in the trash
  - `--shared-with-me` for files and folders that have been shared with you
  - `--apps` for Google Apps documents, spreadsheets, presentations and drawings (equivalent to setting all  of the next four options)
  - `--docs` for Google Apps documents
  - `--sheets` for Google Apps spreadsheets
  - `--presentations` for Google Apps presentations
  - `--drawings` for Google Apps drawings

  You can combine these - for example, this returns all files that you have starred and that were shared with you:

      google-drive-to-sqlite files highlights.db \
        --starred --shared-with-me

  Multiple options are treated as AND, with the exception of the Google Apps options which are treated as OR - so the following would retrieve all spreadsheets and presentations that have also been starred:

      google-drive-to-sqlite files highlights.db \
        --starred --sheets --presentations

  You can use `--stop-after X` to stop after retrieving X files, useful for trying out a new search pattern and seeing results straight away.

  The `--import-json` and `--import-nl` options are mainly useful for testing and developing this tool. They allow you to replay the JSON or newline-delimited JSON that was previously fetched using `--json` or `--nl` and use it to create a fresh SQLite database, without needing to make any outbound API calls:

      # Fetch all starred files from the API, write to starred.json
      google-drive-to-sqlite files -q 'starred = true' --json > starred.json
      # Now import that data into a new SQLite database file
      google-drive-to-sqlite files starred.db --import-json starred.json

  Full `--help`:

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["files", "--help"])
  help = result.output.replace("Usage: cli", "Usage: google-drive-to-sqlite")
  cog.out(
      "```\n{}\n```\n".format(help)
  )
  ]]] -->
  ```
  Usage: google-drive-to-sqlite files [OPTIONS] [DATABASE]

    Retrieve metadata for files in Google Drive, and write to a SQLite database or
    output as JSON.

        google-drive-to-sqlite files files.db

    Use --json to output JSON, --nl for newline-delimited JSON:

        google-drive-to-sqlite files files.db --json

    Use a folder ID to recursively fetch every file in that folder and its sub-
    folders:

        google-drive-to-sqlite files files.db --folder
        1E6Zg2X2bjjtPzVfX8YqdXZDCoB3AVA7i

    Fetch files you have starred:

        google-drive-to-sqlite files starred.db --starred

  Options:
    -a, --auth FILE       Path to auth.json token file
    --folder TEXT         Files in this folder ID and its sub-folders
    -q TEXT               Files matching this query
    --full-text TEXT      Search for files with text match
    --starred             Files you have starred
    --trashed             Files in the trash
    --shared-with-me      Files that have been shared with you
    --apps                Google Apps docs, spreadsheets, presentations and
                          drawings
    --docs                Google Apps docs
    --sheets              Google Apps spreadsheets
    --presentations       Google Apps presentations
    --drawings            Google Apps drawings
    --json                Output JSON rather than write to DB
    --nl                  Output newline-delimited JSON rather than write to DB
    --stop-after INTEGER  Stop paginating after X results
    --import-json FILE    Import from this JSON file instead of the API
    --import-nl FILE      Import from this newline-delimited JSON file
    -v, --verbose         Send verbose output to stderr
    --help                Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## google-drive-to-sqlite download FILE_ID

  The `download` command can be used to download files from Google Drive.

  You'll need one or more file IDs, which look something like `0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB`.

  To download the file, run this:

      google-drive-to-sqlite download 0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB

  This will detect the content type of the file and use that as the extension - so if this file is a JPEG the file would be downloaded as:

      0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB.jpeg

  You can pass multiple file IDs to the command at once.

  To hide the progress bar and filename output, use `-s` or `--silent`.

  If you are downloading a single file you can use the `-o` output to specify a filename and location:

      google-drive-to-sqlite download 0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB \
        -o my-image.jpeg

  Use `-o -` to write the file contents to standard output:

      google-drive-to-sqlite download 0B32uDVNZfiEKLUtIT1gzYWN2NDI4SzVQYTFWWWxCWUtvVGNB \
        -o - > my-image.jpeg

  Full `--help`:

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["download", "--help"])
  help = result.output.replace("Usage: cli", "Usage: google-drive-to-sqlite")
  cog.out(
      "```\n{}\n```\n".format(help)
  )
  ]]] -->
  ```
  Usage: google-drive-to-sqlite download [OPTIONS] FILE_IDS...

    Download one or more files to disk, based on their file IDs.

    The file content will be saved to a file with the name:

        FILE_ID.ext

    Where the extension is automatically picked based on the type of file.

    If you are downloading a single file you can specify a filename with -o:

        google-drive-to-sqlite download MY_FILE_ID -o myfile.txt

  Options:
    -a, --auth FILE    Path to auth.json token file
    -o, --output FILE  File to write to, or - for standard output
    -s, --silent       Hide progress bar and filename
    --help             Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## google-drive-to-sqlite export FORMAT FILE_ID

  The `export` command can be used to export Google Docs documents, spreadsheets and presentations in a number of different formats.

  You'll need one or more document IDs, which look something like `10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU`. You can find these by looking at the URL of your document on the Google Docs site.

  To export that document as PDF, run this:

      google-drive-to-sqlite export pdf 10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU

  The file will be exported as:

      10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU-export.pdf

  You can pass multiple file IDs to the command at once.

  For the `FORMAT` option you can use any of the mime type options listed [on this page](https://developers.google.com/drive/api/v3/ref-export-formats) - for example, to export as an Open Office document you could use:

      google-drive-to-sqlite export \
       application/vnd.oasis.opendocument.text \
       10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU

  For convenience the following shortcuts for common file formats are provided:

  - Google Docs: `html`, `txt`, `rtf`, `pdf`, `doc`, `zip`, `epub`
  - Google Sheets: `xls`, `pdf`, `csv`, `tsv`, `zip`
  - Presentations: `ppt`, `pdf`, `txt`
  - Drawings: `jpeg`, `png`, `svg`

  The `zip` option returns a zip file of HTML. `txt` returns plain text. The others should be self-evident.

  To hide the filename output, use `-s` or `--silent`.

  If you are exporting a single file you can use the `-o` output to specify a filename and location:

      google-drive-to-sqlite export pdf 10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU \
        -o my-document.pdf

  Use `-o -` to write the file contents to standard output:

      google-drive-to-sqlite export pdf 10BOHGDUYa7lBjUSo26YFCHTpgEmtXabdVFaopCTh1vU \
        -o - > my-document.pdf

  Full `--help`:

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["export", "--help"])
  help = result.output.replace("Usage: cli", "Usage: google-drive-to-sqlite")
  cog.out(
      "```\n{}\n```\n".format(help)
  )
  ]]] -->
  ```
  Usage: google-drive-to-sqlite export [OPTIONS] FORMAT FILE_IDS...

    Export one or more files to the specified format.

    Usage:

        google-drive-to-sqlite export pdf FILE_ID_1 FILE_ID_2

    The file content will be saved to a file with the name:

        FILE_ID-export.ext

    Where the extension is based on the format you specified.

    Available export formats can be seen here:
    https://developers.google.com/drive/api/v3/ref-export-formats

    Or you can use one of the following shortcuts:

    - Google Docs: html, txt, rtf, pdf, doc, zip, epub
    - Google Sheets: xls, pdf, csv, tsv, zip
    - Presentations: ppt, pdf, txt
    - Drawings: jpeg, png, svg

    "zip" returns a zip file of HTML.

    If you are exporting a single file you can specify a filename with -o:

        google-drive-to-sqlite export zip MY_FILE_ID -o myfile.zip

  Options:
    -a, --auth FILE    Path to auth.json token file
    -o, --output FILE  File to write to, or - for standard output
    -s, --silent       Hide progress bar and filename
    --help             Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## google-drive-to-sqlite get URL

  The `get` command makes authenticated requests to the specified URL, using credentials derived from the `auth.json` file.

  For example:

      $ google-drive-to-sqlite get 'https://www.googleapis.com/drive/v3/about?fields=*'
      {
          "kind": "drive#about",
          "user": {
              "kind": "drive#user",
              "displayName": "Simon Willison",
      # ...

  If the resource you are fetching supports pagination you can use `--paginate key` to paginate through all of the rows in a specified key. For example, the following API has a `nextPageToken` key and a `files` list, suggesting it supports pagination:

      $ google-drive-to-sqlite get https://www.googleapis.com/drive/v3/files
      {
          "kind": "drive#fileList",
          "nextPageToken": "~!!~AI9...wogHHYlc=",
          "incompleteSearch": false,
          "files": [
              {
                  "kind": "drive#file",
                  "id": "1YEsITp_X8PtDUJWHGM0osT-TXAU1nr0e7RSWRM2Jpyg",
                  "name": "Title of a spreadsheet",
                  "mimeType": "application/vnd.google-apps.spreadsheet"
              },

  To paginate through everything in the `files` list you would use `--paginate files` like this:

      $ google-drive-to-sqlite get https://www.googleapis.com/drive/v3/files --paginate files
      [
        {
          "kind": "drive#file",
          "id": "1YEsITp_X8PtDUJWHGM0osT-TXAU1nr0e7RSWRM2Jpyg",
          "name": "Title of a spreadsheet",
          "mimeType": "application/vnd.google-apps.spreadsheet"
        },
        # ...

  Add `--nl` to stream paginated data as newline-delimited JSON:

      $ google-drive-to-sqlite get https://www.googleapis.com/drive/v3/files --paginate files --nl
      {"kind": "drive#file", "id": "1YEsITp_X8PtDUJWHGM0osT-TXAU1nr0e7RSWRM2Jpyg", "name": "Title of a spreadsheet", "mimeType": "application/vnd.google-apps.spreadsheet"}
      {"kind": "drive#file", "id": "1E6Zg2X2bjjtPzVfX8YqdXZDCoB3AVA7i", "name": "Subfolder", "mimeType": "application/vnd.google-apps.folder"}

  Add `--stop-after 5` to stop after 5 records - useful for testing.

  Full `--help`:

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["get", "--help"])
  help = result.output.replace("Usage: cli", "Usage: google-drive-to-sqlite")
  cog.out(
      "```\n{}\n```\n".format(help)
  )
  ]]] -->
  ```
  Usage: google-drive-to-sqlite get [OPTIONS] URL

    Make an authenticated HTTP GET to the specified URL

  Options:
    -a, --auth FILE       Path to auth.json token file
    --paginate TEXT       Paginate through all results in this key
    --nl                  Output paginated data as newline-delimited JSON
    --stop-after INTEGER  Stop paginating after X results
    -v, --verbose         Send verbose output to stderr
    --help                Show this message and exit.

  ```
  <!-- [[[end]]] -->


  ## Database schema

  The database created by this tool has the following schema:

  <!-- [[[cog
  import tempfile, pathlib, sqlite_utils
  tmpdir = pathlib.Path(tempfile.mkdtemp())
  db_path = str(tmpdir / "docs.db")
  result = runner.invoke(cli.cli, [
      "files", db_path, "--import-json", "tests/folder-and-children.json"
  ])
  cog.out("```sql\n")
  schema = sqlite_utils.Database(db_path).schema
  # Tidy up some formatting
  schema = schema.replace(", [", ",\n   [")
  schema = schema.replace("\n,\n", ",\n")
  schema = schema.replace("TEXT);", "TEXT\n);")
  cog.out(schema)
  cog.out("\n```")
  ]]] -->
  ```sql
  CREATE TABLE [drive_users] (
     [permissionId] TEXT PRIMARY KEY,
     [kind] TEXT,
     [displayName] TEXT,
     [photoLink] TEXT,
     [me] INTEGER,
     [emailAddress] TEXT
  );
  CREATE TABLE [drive_folders] (
     [id] TEXT PRIMARY KEY,
     [_parent] TEXT,
     [_owner] TEXT,
     [lastModifyingUser] TEXT,
     [kind] TEXT,
     [name] TEXT,
     [mimeType] TEXT,
     [starred] INTEGER,
     [trashed] INTEGER,
     [explicitlyTrashed] INTEGER,
     [parents] TEXT,
     [spaces] TEXT,
     [version] TEXT,
     [webViewLink] TEXT,
     [iconLink] TEXT,
     [hasThumbnail] INTEGER,
     [thumbnailVersion] TEXT,
     [viewedByMe] INTEGER,
     [createdTime] TEXT,
     [modifiedTime] TEXT,
     [modifiedByMe] INTEGER,
     [shared] INTEGER,
     [ownedByMe] INTEGER,
     [viewersCanCopyContent] INTEGER,
     [copyRequiresWriterPermission] INTEGER,
     [writersCanShare] INTEGER,
     [folderColorRgb] TEXT,
     [quotaBytesUsed] TEXT,
     [isAppAuthorized] INTEGER,
     [linkShareMetadata] TEXT,
     FOREIGN KEY([_parent]) REFERENCES [drive_folders]([id]),
     FOREIGN KEY([_owner]) REFERENCES [drive_users]([permissionId]),
     FOREIGN KEY([lastModifyingUser]) REFERENCES [drive_users]([permissionId])
  );
  CREATE TABLE [drive_files] (
     [id] TEXT PRIMARY KEY,
     [_parent] TEXT,
     [_owner] TEXT,
     [lastModifyingUser] TEXT,
     [kind] TEXT,
     [name] TEXT,
     [mimeType] TEXT,
     [starred] INTEGER,
     [trashed] INTEGER,
     [explicitlyTrashed] INTEGER,
     [parents] TEXT,
     [spaces] TEXT,
     [version] TEXT,
     [webViewLink] TEXT,
     [iconLink] TEXT,
     [hasThumbnail] INTEGER,
     [thumbnailVersion] TEXT,
     [viewedByMe] INTEGER,
     [createdTime] TEXT,
     [modifiedTime] TEXT,
     [modifiedByMe] INTEGER,
     [shared] INTEGER,
     [ownedByMe] INTEGER,
     [viewersCanCopyContent] INTEGER,
     [copyRequiresWriterPermission] INTEGER,
     [writersCanShare] INTEGER,
     [quotaBytesUsed] TEXT,
     [isAppAuthorized] INTEGER,
     [linkShareMetadata] TEXT,
     FOREIGN KEY([_parent]) REFERENCES [drive_folders]([id]),
     FOREIGN KEY([_owner]) REFERENCES [drive_users]([permissionId]),
     FOREIGN KEY([lastModifyingUser]) REFERENCES [drive_users]([permissionId])
  );
  ```
  <!-- [[[end]]] -->

  ## Thumbnails

  You can construct a thumbnail image for a known file ID using the following URL:

      https://drive.google.com/thumbnail?sz=w800-h800&id=FILE_ID

  Users who are signed into Google Drive and have permission to view a file will be redirected to a thumbnail version of that file. You can tweak the `w800` and `h800` parameters to request different thumbnail sizes.

  ## Privacy policy

  This tool requests access to your Google Drive account in order to retrieve metadata about your files there. It also offers a feature that can download the content of those files.

  The credentials used to access your account are stored in the auth.json file on your computer. The metadata and content retrieved from Google Drive is also stored only on your own personal computer.

  At no point to the developers of this tool gain access to any of your data.

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd google-drive-to-sqlite
      python -m venv venv
      source venv/bin/activate

  Or if you are using `pipenv`:

      pipenv shell

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/google-drive-to-sqlite/actions", "Changelog": "https://github.com/simonw/google-drive-to-sqlite/releases", "Homepage": "https://github.com/simonw/google-drive-to-sqlite", "Issues": "https://github.com/simonw/google-drive-to-sqlite/issues"}

requires_dist:
  ["click", "httpx", "sqlite-utils", "pytest ; extra == 'test'", "pytest-httpx ; extra == 'test'", "pytest-mock ; extra == 'test'", "cogapp ; extra == 'test'"]


name: google-takeout-to-sqlite
summary: Save data from Google Takeout to a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/dogsheep/google-takeout-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/google-takeout-to-sqlite/
project_url: https://pypi.org/project/google-takeout-to-sqlite/
project_urls: {"Homepage": "https://github.com/dogsheep/google-takeout-to-sqlite"}
release_url: https://pypi.org/project/google-takeout-to-sqlite/0.2/
requires_dist: ["sqlite-utils (~=1.11)", "pytest ; extra == 'test'"]
version: 0.2
description:
  # google-takeout-to-sqlite

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

  Save data from google-takeout to a SQLite database.

  ## How to install

      $ pip install google-takeout-to-sqlite

  Request your Google data from https://takeout.google.com/ - wait for the email and download the zip file.

  This tool only supports a subset of the available options. More will be added over time.

  ## My Activity

  You can request the "My Activity" export and then import it with the following command:

      $ google-takeout-to-sqlite my-activity takeout.db ~/Downloads/takeout-20190530.zip

  This will create a database file called `takeout.db` if one does not already exist.

  ## Location History

  Your location history records latitude, longitude and timestame for where Google has tracked your location. You can import it using this command:

      $ google-takeout-to-sqlite location-history takeout.db ~/Downloads/takeout-20190530.zip

  ## Browsing your data with Datasette

  Once you have imported Google data into a SQLite database file you can browse your data using [Datasette](https://github.com/simonw/datasette). Install Datasette like so:

      $ pip install datasette

  Now browse your data by running this and then visiting `http://localhost:8001/`

      $ datasette takeout.db

  Install the [datasette-cluster-map](https://github.com/simonw/datasette-cluster-map) plugin to see your location history on a map:

      $ pip install datasette-cluster-map


name: hacker-news-to-sqlite
summary: Create a SQLite database containing data pulled from Hacker News
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/dogsheep/hacker-news-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/hacker-news-to-sqlite/
project_url: https://pypi.org/project/hacker-news-to-sqlite/
project_urls: {"Homepage": "https://github.com/dogsheep/hacker-news-to-sqlite"}
release_url: https://pypi.org/project/hacker-news-to-sqlite/0.4/
version: 0.4
description:
  # hacker-news-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/hacker-news-to-sqlite.svg)](https://pypi.org/project/hacker-news-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/hacker-news-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/hacker-news-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/hacker-news-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/hacker-news-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/hacker-news-to-sqlite/blob/main/LICENSE)

  Create a SQLite database containing data fetched from [Hacker News](https://news.ycombinator.com/).

  ## How to install

      $ pip install hacker-news-to-sqlite

  ## Usage

      $ hacker-news-to-sqlite user hacker-news.db your-username
      Importing items:  37%|███████████                        | 845/2297 [05:09<11:02,  2.19it/s]

  Imports all of your Hacker News submissions and comments into a SQLite database called `hacker-news.db`.

      $ hacker-news-to-sqlite trees hacker-news.db 22640038 22643218

  Fetches the entire comments tree in which any of those content IDs appears.

  ## Browsing your data with Datasette

  You can use [Datasette](https://datasette.readthedocs.org/) to browse your data. Install Datasette like this:

      $ pip install datasette

  Now run it against your `hacker-news.db` file like so:

      $ datasette hacker-news.db

  Visit `https://localhost:8001/` to search and explore your data.

  You can improve the display of your data usinng the [datasette-render-timestamps](https://github.com/simonw/datasette-render-timestamps) and [datasette-render-html](https://github.com/simonw/datasette-render-html) plugins. Install them like this:

      $ pip install datasette-render-timestamps datasette-render-html

  Now save the following configuration in a file called `metadata.json`:

  ```json
  {
      "databases": {
          "hacker-news": {
              "tables": {
                  "items": {
                      "plugins": {
                          "datasette-render-html": {
                              "columns": [
                                  "text"
                              ]
                          },
                          "datasette-render-timestamps": {
                              "columns": [
                                  "time"
                              ]
                          }
                      }
                  },
                  "users": {
                      "plugins": {
                          "datasette-render-timestamps": {
                              "columns": [
                                  "created"
                              ]
                          }
                      }
                  }
              }
          }
      }
  }
  ```
  Run Datasette like this:

      $ datasette -m metadata.json hacker-news.db

  The timestamp columns will now be rendered as human-readable dates, and any HTML in your posts will be displayed as rendered HTML.

requires_dist:
  ["sqlite-utils", "click", "requests", "tqdm", "pytest ; extra == 'test'", "requests-mock ; extra == 'test'"]


name: healthkit-to-sqlite
summary: Convert an Apple Healthkit export zip to a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://datasette.io/tools/healthkit-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/healthkit-to-sqlite/
project_url: https://pypi.org/project/healthkit-to-sqlite/
release_url: https://pypi.org/project/healthkit-to-sqlite/1.0.1/
requires_dist: ["sqlite-utils (>=2.4.4)", "pytest ; extra == 'test'"]
version: 1.0.1
description:
  # healthkit-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/healthkit-to-sqlite.svg)](https://pypi.org/project/healthkit-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/healthkit-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/healthkit-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/healthkit-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/healthkit-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/healthkit-to-sqlite/blob/main/LICENSE)

  Convert an Apple Healthkit export zip to a SQLite database

  ## How to install

      $ pip install healthkit-to-sqlite

  ## How to use

  First you need to export your Apple HealthKit data.

  1. On your iPhone, open the "Health" app
  2. Click the profile icon in the top right
  3. Click "Export Health Data" at the bottom of that page
  4. Save the resulting file somewhere you can access it, or AirDrop it directly to your laptop.

  Now you can convert the resulting `export.zip` file to SQLite like so:

      $ healthkit-to-sqlite export.zip healthkit.db

  A progress bar will be displayed. You can disable this using `--silent`.

  ```
  Importing from HealthKit  [#-------------]    5%  00:01:33
  ```

  You can explore the resulting data using [Datasette](https://datasette.readthedocs.io/) like this:

      $ datasette healthkit.db

project_urls:
  {"CI": "https://github.com/dogsheep/healthkit-to-sqlite/actions", "Changelog": "https://github.com/dogsheep/healthkit-to-sqlite/releases", "Homepage": "https://datasette.io/tools/healthkit-to-sqlite", "Issues": "https://github.com/dogsheep/healthkit-to-sqlite/issues"}


name: inaturalist-to-sqlite
summary: Create a SQLite database containing your observation history from iNaturalist
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/dogsheep/inaturalist-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/inaturalist-to-sqlite/
project_url: https://pypi.org/project/inaturalist-to-sqlite/
project_urls: {"Homepage": "https://github.com/dogsheep/inaturalist-to-sqlite"}
release_url: https://pypi.org/project/inaturalist-to-sqlite/0.2.1/
requires_dist: ["sqlite-utils (>=2.0)", "click", "requests", "pytest ; extra == 'test'"]
version: 0.2.1
description:
  # inaturalist-to-sqlite

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

  Create a SQLite database containing your observation history from [iNaturalist](https://www.inaturalist.org/).

  ## How to install

      $ pip install inaturalist-to-sqlite

  ## Usage

      $ inaturalist-to-sqlite inaturalist.db yourusername

  (Or try `simonw` if you don't yet have an iNaturalist account)

  This will import all of your iNaturalist observations into a SQLite database called `inaturalist.db`.


name: json-to-files
summary: Create separate files on disk based on a JSON object
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/json-to-files
license: Apache License, Version 2.0
package_url: https://pypi.org/project/json-to-files/
project_url: https://pypi.org/project/json-to-files/
release_url: https://pypi.org/project/json-to-files/0.1/
requires_dist: ["click", "pytest ; extra == 'test'"]
requires_python: >=3.7
version: 0.1
description:
  # json-to-files

  [![PyPI](https://img.shields.io/pypi/v/json-to-files.svg)](https://pypi.org/project/json-to-files/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/json-to-files?include_prereleases&label=changelog)](https://github.com/simonw/json-to-files/releases)
  [![Tests](https://github.com/simonw/json-to-files/workflows/Test/badge.svg)](https://github.com/simonw/json-to-files/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/json-to-files/blob/master/LICENSE)

  Create separate files on disk based on a JSON object

  ## Installation

  Install this tool using `pip`:

      pip install json-to-files

  ## Usage

  This tool takes a JSON file that looks like this:

  ```json
  {
      "foo.txt": "The contents of foo.txt",
      "bar/baz.txt": "The contents of baz.txt"
  }
  ```
  And uses it to write out the following files on disk:

  - `foo.txt` containing "The contents of foo.txt"
  - `bar/baz.txt` containing "The contents of baz.txt"

  You can run it like this:

      json-to-files bundle.json

  Or you can specify a directory to write those files to:

      json-to-files bundle.json -d /tmp/other-directory

  The `bundles.json` file name is optional - if omitted, this tool will read from standard input:

      cat bundle.json | json-to-files

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd json-to-files
      python -m venv venv
      source venv/bin/activate

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/json-to-files/actions", "Changelog": "https://github.com/simonw/json-to-files/releases", "Homepage": "https://github.com/simonw/json-to-files", "Issues": "https://github.com/simonw/json-to-files/issues"}


name: llm
author: Simon Willison
description_content_type: text/markdown
dynamic: ["License-File"]
license_expression: Apache-2.0
license_files: ["LICENSE"]
package_url: https://pypi.org/project/llm/
project_url: https://pypi.org/project/llm/
release_url: https://pypi.org/project/llm/0.31/
requires_python: >=3.10
version: 0.31
summary:
  CLI utility and Python library for interacting with Large Language Models from organizations like OpenAI, Anthropic and Gemini plus local models installed on your own machine.

classifiers:
  ["Development Status :: 4 - Beta", "Intended Audience :: Developers", "Intended Audience :: End Users/Desktop", "Intended Audience :: Science/Research", "Programming Language :: Python :: 3", "Programming Language :: Python :: 3.10", "Programming Language :: Python :: 3.11", "Programming Language :: Python :: 3.12", "Programming Language :: Python :: 3.13", "Programming Language :: Python :: 3.14", "Topic :: Scientific/Engineering :: Artificial Intelligence", "Topic :: Text Processing :: Linguistic", "Topic :: Utilities"]

description:
  <!-- [[[cog
  # README.md is generated from docs/index.md using sphinx_markdown_builder
  import tempfile
  import subprocess
  from pathlib import Path

  readme_markdown = ''

  with tempfile.TemporaryDirectory() as tmpdir:
      tmp_path = Path(tmpdir)
      # Run: sphinx-build -M markdown ./docs ./tmpdir
      subprocess.run([
          "sphinx-build",
          "-M", "markdown",
          "./docs",
          str(tmp_path)
      ], check=True)
      index_file = tmp_path / "markdown" / "index.md"
      readme_markdown = index_file.read_text(encoding="utf-8")

  cog.out(readme_markdown)
  ]]] -->
  # LLM

  [![GitHub repo](https://img.shields.io/badge/github-repo-green)](https://github.com/simonw/llm)
  [![PyPI](https://img.shields.io/pypi/v/llm.svg)](https://pypi.org/project/llm/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/llm?include_prereleases&label=changelog)](https://llm.datasette.io/en/stable/changelog.html)
  [![Tests](https://github.com/simonw/llm/workflows/Test/badge.svg)](https://github.com/simonw/llm/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/llm/blob/main/LICENSE)
  [![Discord](https://img.shields.io/discord/823971286308356157?label=discord)](https://datasette.io/discord-llm)
  [![Homebrew](https://img.shields.io/homebrew/installs/dy/llm?color=yellow&label=homebrew&logo=homebrew)](https://formulae.brew.sh/formula/llm)

  A CLI tool and Python library for interacting with **OpenAI**, **Anthropic’s Claude**, **Google’s Gemini**, **Meta’s Llama** and dozens of other Large Language Models, both via remote APIs and with models that can be installed and run on your own machine.

  Watch **[Language models on the command-line](https://www.youtube.com/watch?v=QUXQNi6jQ30)** on YouTube for a demo or [read the accompanying detailed notes](https://simonwillison.net/2024/Jun/17/cli-language-models/).

  With LLM you can:

  - [Run prompts from the command-line](https://llm.datasette.io/en/stable/usage.html#usage-executing-prompts)
  - [Store prompts and responses in SQLite](https://llm.datasette.io/en/stable/logging.html#logging)
  - [Generate and store embeddings](https://llm.datasette.io/en/stable/embeddings/index.html#embeddings)
  - [Extract structured content from text and images](https://llm.datasette.io/en/stable/schemas.html#schemas)
  - [Grant models the ability to execute tools](https://llm.datasette.io/en/stable/tools.html#tools)
  - … and much, much more

  ## Quick start

  First, install LLM using `pip` or Homebrew or `pipx` or `uv`:

  ```bash
  pip install llm
  ```

  Or with Homebrew (see [warning note](https://llm.datasette.io/en/stable/setup.html#homebrew-warning)):

  ```bash
  brew install llm
  ```

  Or with [pipx](https://pypa.github.io/pipx/):

  ```bash
  pipx install llm
  ```

  Or with [uv](https://docs.astral.sh/uv/guides/tools/)

  ```bash
  uv tool install llm
  ```

  If you have an [OpenAI API key](https://platform.openai.com/api-keys) key you can run this:

  ```bash
  # Paste your OpenAI API key into this
  llm keys set openai

  # Run a prompt (with the default gpt-4o-mini model)
  llm "Ten fun names for a pet pelican"

  # Extract text from an image
  llm "extract text" -a scanned-document.jpg

  # Use a system prompt against a file
  cat myfile.py | llm -s "Explain this code"
  ```

  Run prompts against [Gemini](https://aistudio.google.com/apikey) or [Anthropic](https://console.anthropic.com/) with their respective plugins:

  ```bash
  llm install llm-gemini
  llm keys set gemini
  # Paste Gemini API key here
  llm -m gemini-2.0-flash 'Tell me fun facts about Mountain View'

  llm install llm-anthropic
  llm keys set anthropic
  # Paste Anthropic API key here
  llm -m claude-4-opus 'Impress me with wild facts about turnips'
  ```

  You can also [install a plugin](https://llm.datasette.io/en/stable/plugins/installing-plugins.html#installing-plugins) to access models that can run on your local device. If you use [Ollama](https://ollama.com/):

  ```bash
  # Install the plugin
  llm install llm-ollama

  # Download and run a prompt against the Orca Mini 7B model
  ollama pull llama3.2:latest
  llm -m llama3.2:latest 'What is the capital of France?'
  ```

  To start [an interactive chat](https://llm.datasette.io/en/stable/usage.html#usage-chat) with a model, use `llm chat`:

  ```bash
  llm chat -m gpt-4.1
  ```

  ```default
  Chatting with gpt-4.1
  Type 'exit' or 'quit' to exit
  Type '!multi' to enter multiple lines, then '!end' to finish
  Type '!edit' to open your default editor and modify the prompt.
  Type '!fragment <my_fragment> [<another_fragment> ...]' to insert one or more fragments
  > Tell me a joke about a pelican
  Why don't pelicans like to tip waiters?

  Because they always have a big bill!
  ```

  More background on this project:

  - [llm, ttok and strip-tags—CLI tools for working with ChatGPT and other LLMs](https://simonwillison.net/2023/May/18/cli-tools-for-llms/)
  - [The LLM CLI tool now supports self-hosted language models via plugins](https://simonwillison.net/2023/Jul/12/llm/)
  - [LLM now provides tools for working with embeddings](https://simonwillison.net/2023/Sep/4/llm-embeddings/)
  - [Build an image search engine with llm-clip, chat with models with llm chat](https://simonwillison.net/2023/Sep/12/llm-clip-and-chat/)
  - [You can now run prompts against images, audio and video in your terminal using LLM](https://simonwillison.net/2024/Oct/29/llm-multi-modal/)
  - [Structured data extraction from unstructured content using LLM schemas](https://simonwillison.net/2025/Feb/28/llm-schemas/)
  - [Long context support in LLM 0.24 using fragments and template plugins](https://simonwillison.net/2025/Apr/7/long-context-llm/)

  See also [the llm tag](https://simonwillison.net/tags/llm/) on my blog.

  ## Contents

  * [Setup](https://llm.datasette.io/en/stable/setup.html)
    * [Installation](https://llm.datasette.io/en/stable/setup.html#installation)
    * [Upgrading to the latest version](https://llm.datasette.io/en/stable/setup.html#upgrading-to-the-latest-version)
    * [Using uvx](https://llm.datasette.io/en/stable/setup.html#using-uvx)
    * [A note about Homebrew and PyTorch](https://llm.datasette.io/en/stable/setup.html#a-note-about-homebrew-and-pytorch)
    * [Installing plugins](https://llm.datasette.io/en/stable/setup.html#installing-plugins)
    * [API key management](https://llm.datasette.io/en/stable/setup.html#api-key-management)
      * [Saving and using stored keys](https://llm.datasette.io/en/stable/setup.html#saving-and-using-stored-keys)
      * [Passing keys using the –key option](https://llm.datasette.io/en/stable/setup.html#passing-keys-using-the-key-option)
      * [Keys in environment variables](https://llm.datasette.io/en/stable/setup.html#keys-in-environment-variables)
    * [Configuration](https://llm.datasette.io/en/stable/setup.html#configuration)
      * [Setting a custom default model](https://llm.datasette.io/en/stable/setup.html#setting-a-custom-default-model)
      * [Setting a custom directory location](https://llm.datasette.io/en/stable/setup.html#setting-a-custom-directory-location)
      * [Turning SQLite logging on and off](https://llm.datasette.io/en/stable/setup.html#turning-sqlite-logging-on-and-off)
  * [Usage](https://llm.datasette.io/en/stable/usage.html)
    * [Executing a prompt](https://llm.datasette.io/en/stable/usage.html#executing-a-prompt)
      * [Model options](https://llm.datasette.io/en/stable/usage.html#model-options)
      * [Attachments](https://llm.datasette.io/en/stable/usage.html#attachments)
      * [System prompts](https://llm.datasette.io/en/stable/usage.html#system-prompts)
      * [Tools](https://llm.datasette.io/en/stable/usage.html#tools)
      * [Extracting fenced code blocks](https://llm.datasette.io/en/stable/usage.html#extracting-fenced-code-blocks)
      * [Schemas](https://llm.datasette.io/en/stable/usage.html#schemas)
      * [Fragments](https://llm.datasette.io/en/stable/usage.html#fragments)
      * [Continuing a conversation](https://llm.datasette.io/en/stable/usage.html#continuing-a-conversation)
      * [Tips for using LLM with Bash or Zsh](https://llm.datasette.io/en/stable/usage.html#tips-for-using-llm-with-bash-or-zsh)
      * [Completion prompts](https://llm.datasette.io/en/stable/usage.html#completion-prompts)
    * [Starting an interactive chat](https://llm.datasette.io/en/stable/usage.html#starting-an-interactive-chat)
    * [Listing available models](https://llm.datasette.io/en/stable/usage.html#listing-available-models)
    * [Setting default options for models](https://llm.datasette.io/en/stable/usage.html#setting-default-options-for-models)
  * [OpenAI models](https://llm.datasette.io/en/stable/openai-models.html)
    * [Configuration](https://llm.datasette.io/en/stable/openai-models.html#configuration)
    * [OpenAI language models](https://llm.datasette.io/en/stable/openai-models.html#openai-language-models)
    * [Model features](https://llm.datasette.io/en/stable/openai-models.html#model-features)
    * [OpenAI embedding models](https://llm.datasette.io/en/stable/openai-models.html#openai-embedding-models)
    * [OpenAI completion models](https://llm.datasette.io/en/stable/openai-models.html#openai-completion-models)
    * [Adding more OpenAI models](https://llm.datasette.io/en/stable/openai-models.html#adding-more-openai-models)
  * [Other models](https://llm.datasette.io/en/stable/other-models.html)
    * [Installing and using a local model](https://llm.datasette.io/en/stable/other-models.html#installing-and-using-a-local-model)
    * [OpenAI-compatible models](https://llm.datasette.io/en/stable/other-models.html#openai-compatible-models)
      * [Extra HTTP headers](https://llm.datasette.io/en/stable/other-models.html#extra-http-headers)
  * [Tools](https://llm.datasette.io/en/stable/tools.html)
    * [How tools work](https://llm.datasette.io/en/stable/tools.html#how-tools-work)
    * [Trying out tools](https://llm.datasette.io/en/stable/tools.html#trying-out-tools)
    * [LLM’s implementation of tools](https://llm.datasette.io/en/stable/tools.html#llm-s-implementation-of-tools)
    * [Default tools](https://llm.datasette.io/en/stable/tools.html#default-tools)
    * [Tips for implementing tools](https://llm.datasette.io/en/stable/tools.html#tips-for-implementing-tools)
  * [Schemas](https://llm.datasette.io/en/stable/schemas.html)
    * [Schemas tutorial](https://llm.datasette.io/en/stable/schemas.html#schemas-tutorial)
      * [Getting started with dogs](https://llm.datasette.io/en/stable/schemas.html#getting-started-with-dogs)
      * [Extracting people from a news articles](https://llm.datasette.io/en/stable/schemas.html#extracting-people-from-a-news-articles)
    * [Using JSON schemas](https://llm.datasette.io/en/stable/schemas.html#using-json-schemas)
    * [Ways to specify a schema](https://llm.datasette.io/en/stable/schemas.html#ways-to-specify-a-schema)
    * [Concise LLM schema syntax](https://llm.datasette.io/en/stable/schemas.html#concise-llm-schema-syntax)
    * [Saving reusable schemas in templates](https://llm.datasette.io/en/stable/schemas.html#saving-reusable-schemas-in-templates)
    * [Browsing logged JSON objects created using schemas](https://llm.datasette.io/en/stable/schemas.html#browsing-logged-json-objects-created-using-schemas)
  * [Templates](https://llm.datasette.io/en/stable/templates.html)
    * [Getting started with <code>–save</code>](https://llm.datasette.io/en/stable/templates.html#getting-started-with-save)
    * [Using a template](https://llm.datasette.io/en/stable/templates.html#using-a-template)
    * [Listing available templates](https://llm.datasette.io/en/stable/templates.html#listing-available-templates)
    * [Templates as YAML files](https://llm.datasette.io/en/stable/templates.html#templates-as-yaml-files)
      * [System prompts](https://llm.datasette.io/en/stable/templates.html#system-prompts)
      * [Fragments](https://llm.datasette.io/en/stable/templates.html#fragments)
      * [Options](https://llm.datasette.io/en/stable/templates.html#options)
      * [Tools](https://llm.datasette.io/en/stable/templates.html#tools)
      * [Schemas](https://llm.datasette.io/en/stable/templates.html#schemas)
      * [Additional template variables](https://llm.datasette.io/en/stable/templates.html#additional-template-variables)
      * [Specifying default parameters](https://llm.datasette.io/en/stable/templates.html#specifying-default-parameters)
      * [Configuring code extraction](https://llm.datasette.io/en/stable/templates.html#configuring-code-extraction)
      * [Setting a default model for a template](https://llm.datasette.io/en/stable/templates.html#setting-a-default-model-for-a-template)
    * [Template loaders from plugins](https://llm.datasette.io/en/stable/templates.html#template-loaders-from-plugins)
  * [Fragments](https://llm.datasette.io/en/stable/fragments.html)
    * [Using fragments in a prompt](https://llm.datasette.io/en/stable/fragments.html#using-fragments-in-a-prompt)
    * [Using fragments in chat](https://llm.datasette.io/en/stable/fragments.html#using-fragments-in-chat)
    * [Browsing fragments](https://llm.datasette.io/en/stable/fragments.html#browsing-fragments)
    * [Setting aliases for fragments](https://llm.datasette.io/en/stable/fragments.html#setting-aliases-for-fragments)
    * [Viewing fragments in your logs](https://llm.datasette.io/en/stable/fragments.html#viewing-fragments-in-your-logs)
    * [Using fragments from plugins](https://llm.datasette.io/en/stable/fragments.html#using-fragments-from-plugins)
    * [Listing available fragment prefixes](https://llm.datasette.io/en/stable/fragments.html#listing-available-fragment-prefixes)
  * [Model aliases](https://llm.datasette.io/en/stable/aliases.html)
    * [Listing aliases](https://llm.datasette.io/en/stable/aliases.html#listing-aliases)
    * [Adding a new alias](https://llm.datasette.io/en/stable/aliases.html#adding-a-new-alias)
    * [Removing an alias](https://llm.datasette.io/en/stable/aliases.html#removing-an-alias)
    * [Viewing the aliases file](https://llm.datasette.io/en/stable/aliases.html#viewing-the-aliases-file)
  * [Embeddings](https://llm.datasette.io/en/stable/embeddings/index.html)
    * [Embedding with the CLI](https://llm.datasette.io/en/stable/embeddings/cli.html)
      * [llm embed](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-embed)
      * [llm embed-multi](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-embed-multi)
      * [llm similar](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-similar)
      * [llm embed-models](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-embed-models)
      * [llm collections list](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-collections-list)
      * [llm collections delete](https://llm.datasette.io/en/stable/embeddings/cli.html#llm-collections-delete)
    * [Using embeddings from Python](https://llm.datasette.io/en/stable/embeddings/python-api.html)
      * [Working with collections](https://llm.datasette.io/en/stable/embeddings/python-api.html#working-with-collections)
      * [Retrieving similar items](https://llm.datasette.io/en/stable/embeddings/python-api.html#retrieving-similar-items)
      * [SQL schema](https://llm.datasette.io/en/stable/embeddings/python-api.html#sql-schema)
    * [Writing plugins to add new embedding models](https://llm.datasette.io/en/stable/embeddings/writing-plugins.html)
      * [`EmbeddingModel`](https://llm.datasette.io/en/stable/embeddings/writing-plugins.html#llm.EmbeddingModel)
      * [Embedding binary content](https://llm.datasette.io/en/stable/embeddings/writing-plugins.html#embedding-binary-content)
    * [Embedding storage format](https://llm.datasette.io/en/stable/embeddings/storage.html)
  * [Plugins](https://llm.datasette.io/en/stable/plugins/index.html)
    * [Installing plugins](https://llm.datasette.io/en/stable/plugins/installing-plugins.html)
      * [Listing installed plugins](https://llm.datasette.io/en/stable/plugins/installing-plugins.html#listing-installed-plugins)
      * [Running with a subset of plugins](https://llm.datasette.io/en/stable/plugins/installing-plugins.html#running-with-a-subset-of-plugins)
    * [Plugin directory](https://llm.datasette.io/en/stable/plugins/directory.html)
      * [Local models](https://llm.datasette.io/en/stable/plugins/directory.html#local-models)
      * [Remote APIs](https://llm.datasette.io/en/stable/plugins/directory.html#remote-apis)
      * [Tools](https://llm.datasette.io/en/stable/plugins/directory.html#tools)
      * [Fragments and template loaders](https://llm.datasette.io/en/stable/plugins/directory.html#fragments-and-template-loaders)
      * [Embedding models](https://llm.datasette.io/en/stable/plugins/directory.html#embedding-models)
      * [Extra commands](https://llm.datasette.io/en/stable/plugins/directory.html#extra-commands)
      * [Just for fun](https://llm.datasette.io/en/stable/plugins/directory.html#just-for-fun)
    * [Plugin hooks](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html)
      * [register_commands(cli)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-commands-cli)
      * [register_models(register, model_aliases)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-models-register-model-aliases)
      * [register_embedding_models(register)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-embedding-models-register)
      * [register_tools(register)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-tools-register)
      * [register_template_loaders(register)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-template-loaders-register)
      * [register_fragment_loaders(register)](https://llm.datasette.io/en/stable/plugins/plugin-hooks.html#register-fragment-loaders-register)
    * [Developing a model plugin](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html)
      * [The initial structure of the plugin](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#the-initial-structure-of-the-plugin)
      * [Installing your plugin to try it out](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#installing-your-plugin-to-try-it-out)
      * [Building the Markov chain](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#building-the-markov-chain)
      * [Executing the Markov chain](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#executing-the-markov-chain)
      * [Adding that to the plugin](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#adding-that-to-the-plugin)
      * [Understanding execute()](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#understanding-execute)
      * [Prompts and responses are logged to the database](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#prompts-and-responses-are-logged-to-the-database)
      * [Adding options](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#adding-options)
      * [Distributing your plugin](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#distributing-your-plugin)
      * [GitHub repositories](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#github-repositories)
      * [Publishing plugins to PyPI](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#publishing-plugins-to-pypi)
      * [Adding metadata](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#adding-metadata)
      * [What to do if it breaks](https://llm.datasette.io/en/stable/plugins/tutorial-model-plugin.html#what-to-do-if-it-breaks)
    * [Advanced model plugins](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html)
      * [Tip: lazily load expensive dependencies](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#tip-lazily-load-expensive-dependencies)
      * [Models that accept API keys](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#models-that-accept-api-keys)
      * [Async models](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#async-models)
      * [Supporting schemas](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#supporting-schemas)
      * [Supporting tools](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#supporting-tools)
      * [Attachments for multi-modal models](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#attachments-for-multi-modal-models)
      * [Tracking token usage](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#tracking-token-usage)
      * [Tracking resolved model names](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#tracking-resolved-model-names)
      * [LLM_RAISE_ERRORS](https://llm.datasette.io/en/stable/plugins/advanced-model-plugins.html#llm-raise-errors)
    * [Utility functions for plugins](https://llm.datasette.io/en/stable/plugins/plugin-utilities.html)
      * [llm.get_key()](https://llm.datasette.io/en/stable/plugins/plugin-utilities.html#llm-get-key)
      * [llm.user_dir()](https://llm.datasette.io/en/stable/plugins/plugin-utilities.html#llm-user-dir)
      * [llm.ModelError](https://llm.datasette.io/en/stable/plugins/plugin-utilities.html#llm-modelerror)
      * [Response.fake()](https://llm.datasette.io/en/stable/plugins/plugin-utilities.html#response-fake)
  * [Python API](https://llm.datasette.io/en/stable/python-api.html)
    * [Basic prompt execution](https://llm.datasette.io/en/stable/python-api.html#basic-prompt-execution)
      * [System prompts](https://llm.datasette.io/en/stable/python-api.html#system-prompts)
      * [Attachments](https://llm.datasette.io/en/stable/python-api.html#attachments)
      * [Tools](https://llm.datasette.io/en/stable/python-api.html#tools)
      * [Schemas](https://llm.datasette.io/en/stable/python-api.html#schemas)
      * [Fragments](https://llm.datasette.io/en/stable/python-api.html#fragments)
      * [Model options](https://llm.datasette.io/en/stable/python-api.html#model-options)
      * [Passing an API key](https://llm.datasette.io/en/stable/python-api.html#passing-an-api-key)
      * [Models from plugins](https://llm.datasette.io/en/stable/python-api.html#models-from-plugins)
      * [Accessing the underlying JSON](https://llm.datasette.io/en/stable/python-api.html#accessing-the-underlying-json)
      * [Token usage](https://llm.datasette.io/en/stable/python-api.html#token-usage)
      * [Streaming responses](https://llm.datasette.io/en/stable/python-api.html#streaming-responses)
    * [Async models](https://llm.datasette.io/en/stable/python-api.html#async-models)
      * [`AsyncResponse`](https://llm.datasette.io/en/stable/python-api.html#llm.AsyncResponse)
      * [Tool functions can be sync or async](https://llm.datasette.io/en/stable/python-api.html#tool-functions-can-be-sync-or-async)
      * [Tool use for async models](https://llm.datasette.io/en/stable/python-api.html#tool-use-for-async-models)
    * [Conversations](https://llm.datasette.io/en/stable/python-api.html#conversations)
      * [Conversations using tools](https://llm.datasette.io/en/stable/python-api.html#conversations-using-tools)
    * [Listing models](https://llm.datasette.io/en/stable/python-api.html#listing-models)
    * [Running code when a response has completed](https://llm.datasette.io/en/stable/python-api.html#running-code-when-a-response-has-completed)
    * [Other functions](https://llm.datasette.io/en/stable/python-api.html#other-functions)
      * [set_alias(alias, model_id)](https://llm.datasette.io/en/stable/python-api.html#set-alias-alias-model-id)
      * [remove_alias(alias)](https://llm.datasette.io/en/stable/python-api.html#remove-alias-alias)
      * [set_default_model(alias)](https://llm.datasette.io/en/stable/python-api.html#set-default-model-alias)
      * [get_default_model()](https://llm.datasette.io/en/stable/python-api.html#get-default-model)
      * [set_default_embedding_model(alias) and get_default_embedding_model()](https://llm.datasette.io/en/stable/python-api.html#set-default-embedding-model-alias-and-get-default-embedding-model)
  * [Logging to SQLite](https://llm.datasette.io/en/stable/logging.html)
    * [Viewing the logs](https://llm.datasette.io/en/stable/logging.html#viewing-the-logs)
      * [-s/–short mode](https://llm.datasette.io/en/stable/logging.html#s-short-mode)
      * [Logs for a conversation](https://llm.datasette.io/en/stable/logging.html#logs-for-a-conversation)
      * [Searching the logs](https://llm.datasette.io/en/stable/logging.html#searching-the-logs)
      * [Filtering past a specific ID](https://llm.datasette.io/en/stable/logging.html#filtering-past-a-specific-id)
      * [Filtering by model](https://llm.datasette.io/en/stable/logging.html#filtering-by-model)
      * [Filtering by prompts that used specific fragments](https://llm.datasette.io/en/stable/logging.html#filtering-by-prompts-that-used-specific-fragments)
      * [Filtering by prompts that used specific tools](https://llm.datasette.io/en/stable/logging.html#filtering-by-prompts-that-used-specific-tools)
      * [Browsing data collected using schemas](https://llm.datasette.io/en/stable/logging.html#browsing-data-collected-using-schemas)
    * [Browsing logs using Datasette](https://llm.datasette.io/en/stable/logging.html#browsing-logs-using-datasette)
    * [Backing up your database](https://llm.datasette.io/en/stable/logging.html#backing-up-your-database)
    * [SQL schema](https://llm.datasette.io/en/stable/logging.html#sql-schema)
  * [Related tools](https://llm.datasette.io/en/stable/related-tools.html)
    * [strip-tags](https://llm.datasette.io/en/stable/related-tools.html#strip-tags)
    * [ttok](https://llm.datasette.io/en/stable/related-tools.html#ttok)
    * [Symbex](https://llm.datasette.io/en/stable/related-tools.html#symbex)
  * [CLI reference](https://llm.datasette.io/en/stable/help.html)
    * [llm  –help](https://llm.datasette.io/en/stable/help.html#llm-help)
      * [llm prompt –help](https://llm.datasette.io/en/stable/help.html#llm-prompt-help)
      * [llm chat –help](https://llm.datasette.io/en/stable/help.html#llm-chat-help)
      * [llm keys –help](https://llm.datasette.io/en/stable/help.html#llm-keys-help)
      * [llm logs –help](https://llm.datasette.io/en/stable/help.html#llm-logs-help)
      * [llm models –help](https://llm.datasette.io/en/stable/help.html#llm-models-help)
      * [llm templates –help](https://llm.datasette.io/en/stable/help.html#llm-templates-help)
      * [llm schemas –help](https://llm.datasette.io/en/stable/help.html#llm-schemas-help)
      * [llm tools –help](https://llm.datasette.io/en/stable/help.html#llm-tools-help)
      * [llm aliases –help](https://llm.datasette.io/en/stable/help.html#llm-aliases-help)
      * [llm fragments –help](https://llm.datasette.io/en/stable/help.html#llm-fragments-help)
      * [llm plugins –help](https://llm.datasette.io/en/stable/help.html#llm-plugins-help)
      * [llm install –help](https://llm.datasette.io/en/stable/help.html#llm-install-help)
      * [llm uninstall –help](https://llm.datasette.io/en/stable/help.html#llm-uninstall-help)
      * [llm embed –help](https://llm.datasette.io/en/stable/help.html#llm-embed-help)
      * [llm embed-multi –help](https://llm.datasette.io/en/stable/help.html#llm-embed-multi-help)
      * [llm similar –help](https://llm.datasette.io/en/stable/help.html#llm-similar-help)
      * [llm embed-models –help](https://llm.datasette.io/en/stable/help.html#llm-embed-models-help)
      * [llm collections –help](https://llm.datasette.io/en/stable/help.html#llm-collections-help)
      * [llm openai –help](https://llm.datasette.io/en/stable/help.html#llm-openai-help)
  * [Contributing](https://llm.datasette.io/en/stable/contributing.html)
    * [Updating recorded HTTP API interactions and associated snapshots](https://llm.datasette.io/en/stable/contributing.html#updating-recorded-http-api-interactions-and-associated-snapshots)
    * [Debugging tricks](https://llm.datasette.io/en/stable/contributing.html#debugging-tricks)
    * [Documentation](https://llm.datasette.io/en/stable/contributing.html#documentation)
    * [Release process](https://llm.datasette.io/en/stable/contributing.html#release-process)

  * [Changelog](https://llm.datasette.io/en/stable/changelog.html)
  <!-- [[[end]]] -->

project_urls:
  {"CI": "https://github.com/simonw/llm/actions", "Changelog": "https://github.com/simonw/llm/releases", "Documentation": "https://llm.datasette.io/", "Homepage": "https://github.com/simonw/llm", "Issues": "https://github.com/simonw/llm/issues"}

requires_dist:
  ["click", "condense-json>=0.1.3", "openai>=2.32.0", "click-default-group>=1.2.3", "sqlite-utils>=3.37", "sqlite-migrate>=0.1a2", "pydantic>=2.0.0", "PyYAML", "pluggy", "python-ulid", "setuptools", "pip", "pyreadline3; sys_platform == \"win32\"", "puremagic"]


name: markdown-to-sqlite
summary: CLI tool for loading markdown files into a SQLite database
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/markdown-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/markdown-to-sqlite/
project_url: https://pypi.org/project/markdown-to-sqlite/
release_url: https://pypi.org/project/markdown-to-sqlite/1.0/
requires_dist: ["yamldown", "markdown", "sqlite-utils", "click", "pytest ; extra == 'test'"]
requires_python: >=3.6
version: 1.0
classifiers:
  ["Intended Audience :: Developers", "Intended Audience :: End Users/Desktop", "Intended Audience :: Science/Research", "License :: OSI Approved :: Apache Software License", "Programming Language :: Python :: 3.6", "Programming Language :: Python :: 3.7", "Topic :: Database"]

description:
  # markdown-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/markdown-to-sqlite.svg)](https://pypi.python.org/pypi/markdown-to-sqlite)
  [![Changelog](https://img.shields.io/github/v/release/simonw/markdown-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/markdown-to-sqlite/releases)
  [![Tests](https://github.com/simonw/markdown-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/markdown-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/markdown-to-sqlite/blob/main/LICENSE)

  CLI tool for loading markdown files into a SQLite database.

  YAML embedded in the markdown files will be used to populate additional columns.

      Usage: markdown-to-sqlite [OPTIONS] DBNAME TABLE PATHS...

  For example:

      $ markdown-to-sqlite docs.db documents file1.md file2.md

  ## Breaking change

  Prior to version 1.0 this argument order was different - markdown files were listed before the database and table.

project_urls:
  {"CI": "https://github.com/simonw/markdown-to-sqlite/actions", "Changelog": "https://github.com/simonw/markdown-to-sqlite/releases", "Homepage": "https://github.com/simonw/markdown-to-sqlite", "Issues": "https://github.com/simonw/markdown-to-sqlite/issues"}


name: mbox-to-sqlite
summary: Load email from .mbox files into SQLite
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/mbox-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/mbox-to-sqlite/
project_url: https://pypi.org/project/mbox-to-sqlite/
release_url: https://pypi.org/project/mbox-to-sqlite/0.1a0/
requires_dist: ["click", "sqlite-utils", "pytest ; extra == 'test'"]
requires_python: >=3.7
version: 0.1a0
description:
  # mbox-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/mbox-to-sqlite.svg)](https://pypi.org/project/mbox-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/mbox-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/mbox-to-sqlite/releases)
  [![Tests](https://github.com/simonw/mbox-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/mbox-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/mbox-to-sqlite/blob/master/LICENSE)

  Load email from .mbox files into SQLite

  ## Installation

  Install this tool using `pip`:

      pip install mbox-to-sqlite

  ## Usage

  Use the `mbox` command to import a `.mbox` file into a SQLite database:

      mbox-to-sqlite mbox emails.db path/to/messages.mbox

  You can try this out against an example containing a sample of 3,266 emails from the [Enron corpus](https://en.wikipedia.org/wiki/Enron_Corpus) like this:

      curl -O https://raw.githubusercontent.com/ivanhb/EMA/master/server/data/mbox/enron/mbox-enron-white-s-all.mbox
      mbox-to-sqlite mbox enron.db mbox-enron-white-s-all.mbox

  You can then explore the resulting database using [Datasette](https://datasette.io/):

      datasette enron.db

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd mbox-to-sqlite
      python -m venv venv
      source venv/bin/activate

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/mbox-to-sqlite/actions", "Changelog": "https://github.com/simonw/mbox-to-sqlite/releases", "Homepage": "https://github.com/simonw/mbox-to-sqlite", "Issues": "https://github.com/simonw/mbox-to-sqlite/issues"}


name: openai-to-sqlite
summary: Save OpenAI API results to a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/openai-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/openai-to-sqlite/
project_url: https://pypi.org/project/openai-to-sqlite/
release_url: https://pypi.org/project/openai-to-sqlite/0.4.2/
requires_python: >=3.8
version: 0.4.2
description:
  # openai-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/openai-to-sqlite.svg)](https://pypi.org/project/openai-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/openai-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/openai-to-sqlite/releases)
  [![Tests](https://github.com/simonw/openai-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/openai-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/openai-to-sqlite/blob/main/LICENSE)

  This tool provides utilities for interacting with OpenAI APIs and storing the results in a SQLite database.

  See [Semantic search answers: Q&A against documentation with GPT3 + OpenAI embeddings](https://simonwillison.net/2023/Jan/13/semantic-search-answers/) for background on this project.

  For a tutorial on using this for related content, see [Storing and serving related documents with openai-to-sqlite and embeddings](https://til.simonwillison.net/llms/openai-embeddings-related-content).

  ## Installation

  Install this tool using `pip`:
  ```bash
  pip install openai-to-sqlite
  ```
  ## Configuration

  You will need an OpenAI API key to use this tool.

  You can create one at https://beta.openai.com/account/api-keys

  You can then either set the API key as an environment variable:
  ```bash
  export OPENAI_API_KEY=sk-...
  ```
  Or pass it to each command using the `--token sk-...` option.

  ## Calling OpenAI APIs with SQL functions

  The `openai-to-sqlite query` command can be used to execute SQL queries that call OpenAI APIs.

  Functions available are:

  - `chatgpt(prompt)` - call the OpenAI Chat API using model `gpt-3.5-turbo` with the specified prompt.
  - `chatgpt(prompt, system)` - call that API with the prompt and the specified system prompt.

  More functions are planned in the future.

  Here's how to use this command to run basic sentiment analysis against content in a table:
  ```bash
  openai-to-sqlite query database.db "
    update messages set sentiment = chatgpt(
      'Sentiment analysis for this message: ' || message ||
      ' - ONLY return a lowercase string from: positive, negative, neutral, unknown'
    )
    where sentiment not in ('positive', 'negative', 'neutral', 'unknown')
      or sentiment is null
  "
  ```
  This updates the `sentiment` column in a table called `messages`. It populates it with the response from the specified prompt.

  The command will display a progress bar indicating how many rows are being processed.

  You can add an empty `sentiment` column to a table using [sqlite-utils](https://sqlite-utils.datasette.io/) like this:

  ```bash
  sqlite-utils add-column database.db messages sentiment
  ```

  ## Embeddings

  The `embeddings` command can be used to calculate and store [OpenAI embeddings](https://beta.openai.com/docs/guides/embeddings) for strings of text.

  Each embedding has a cost, so be sure to familiarize yourself with [the pricing](https://openai.com/api/pricing/) for the embedding model.

  The command can accept data in four different ways:

  - As a JSON file containing a list of objects
  - As a CSV file
  - As a TSV file
  - By running queries against a SQLite database

  For all of these formats there should be an `id` column, followed by one or more text columns.

  The ID will be stored as the content ID. Any other columns will be concatenated together and used as the text to be embedded.

  The embeddings from the API will then be saved as binary blobs in the `embeddings` table of the specified SQLite database - or another table, if you pass the `-t/--table` option.

  ### JSON, CSV and TSV

  Given a CSV file like this:
  ```csv
  id,content
  1,This is a test
  2,This is another test
  ```
  Embeddings can be stored like so:
  ```bash
  openai-to-sqlite embeddings embeddings.db data.csv
  ```
  The resulting schema looks like this:
  ```sql
  CREATE TABLE [embeddings] (
     [id] TEXT PRIMARY KEY,
     [embedding] BLOB
  );
  ```
  The same data can be provided as TSV data:
  ```
  id    content
  1     This is a test
  2     This is another test
  ```
  Then imported like this:
  ```bash
  openai-to-sqlite embeddings embeddings.db data.tsv
  ```
  Or as JSON data:
  ```json
  [
    {"id": 1, "content": "This is a test"},
    {"id": 2, "content": "This is another test"}
  ]
  ```
  Imported like this:
  ```bash
  openai-to-sqlite embeddings embeddings.db data.json
  ```
  In each of these cases the tool automatically detects the format of the data. It does this by inspecting the data itself - it does not consider the file extension.

  If the automatic detection is not working, you can pass `--format json`, `csv` or `tsv` to explicitly specify a format:

  ```bash
  openai-to-sqlite embeddings embeddings.db data.tsv --format tsv
  ```
  ### Importing data from standard input

  You can use a filename of `-` to pipe data in to standard input:

  ```bash
  cat data.tsv | openai-to-sqlite embeddings embeddings.db -
  ```

  ### Data from a SQL query

  The `--sql` option can be used to read data to be embedded from the attached SQLite database. The query must return an `id` column and one or more text columns to be embedded.

  ```bash
  openai-to-sqlite embeddings content.db \
    --sql "select id, title from documents"
  ```
  This will create a `embeddings` table in the `content.db` database and populate it with embeddings calculated from the `title` column in that query.

  You can also store embeddings in one database while reading data from another database, using the `--attach alias filename.db` option:

  ```bash
  openai-to-sqlite embeddings embeddings.db \
    --attach documents documents.db \
    --sql "select id, title from documents.documents"
  ```
  A progress bar will be displayed when using `--sql` that indicates how long the embeddings are likely to take to calculate.

  The CSV/TSV/JSON options do not correctly display the progress bar. You can work around this by importing your data into SQLite first (e.g. [using sqlite-utils](https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data)) and then running the embeddings using `--sql`.

  ### Batching

  Embeddings will be sent to the OpenAI embeddings API in batches of 100. If you know that your data is short strings you can increase the batch size, up to 2048, using the `--batch-size` option:

  ```bash
  openai-to-sqlite embeddings embeddings.db data.csv --batch-size 2048
  ```

  ### Working with the stored embeddings

  The `embedding` column is a SQLite blob containing 1536 floating point numbers encoded as a sequence of 4 byte values.

  You can extract them back to an array of floating point values in Python like this:
  ```python
  import struct

  vector = struct.unpack(
      "f" * 1536, binary_embedding
  )
  ```

  ### Searching embeddings with the search command

  Having saved the embeddings for content, you can run searches using the `search` command:
  ```bash
  openai-to-sqlite search embeddings.db 'this is my search term'
  ```
  The output will be a list of cosine similarity scores and content IDs:
  ```bash
  openai-to-sqlite search blog.db 'cool datasette demo'
  ```
  ```
  0.843 7849
  0.830 8036
  0.828 8195
  0.826 8098
  0.818 8086
  0.817 8171
  0.816 8121
  0.815 7860
  0.815 7872
  0.814 8169
  ```

  Add the `-t/--table` option if your embeddings are stored in a different table:
  ```bash
  openai-to-sqlite search content.db 'this is my search term' -t documents

  Add `--count 20` to retrieve 20 results (the default is 10).
  ```

  ### Search for similar content with the similar command

  Having saved the embeddings for content, you can search for similar content with the `similar` command:
  ```bash
  oopenai-to-sqlite similar embeddings.db '<content identifier>'
  ```
  The output will be a list of cosine similarity scores and content IDs:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db '23G Gose'
  ```
  ```
  23G Gose
    1.000 23G Gose
    0.929 24A Witbier
    0.921 23A Berliner Weisse
    0.909 05B Kölsch
    0.907 01D American Wheat Beer
    0.906 27 Historical Beer: Lichtenhainer
    0.905 23D Lambic
    0.905 10A Weissbier
    0.904 04B Festbier
    0.904 01B American Lager
  ```
  You can pass more than one IDs to see similarities calculated for each one:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db \
    '23G Gose' '01A American Light Lager'
  ```
  Or pass `--all` to run similarity for every item in the database. This runs similarity calculations for the number of items squared so it can be quite a long running operation:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db --all
  ```

  ### Saving similarity calculations to the database

  To save these calculations to a `similarities` table in the database, use the `--save` option:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db --all --save
  ```
  The `--save` option disables output. You can re-enable output with `--print`:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db --all --save --print
  ```
  To save to a database table with a name other than `similarities`, use `--table`:
  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db \
    --all --save --table my_similarities
  ```

  ### --recalculate-for-matches

  Re-calculating similarities for every row in the database can be quite a lengthy operation.

  If you know which rows have just been added, you can speed things up using `--recalculate-for-matches`.

  This tells `openai-to-sqlite similar` to only re-calculate similarities for rows that are close matches to the specified rows.

  This means you can add one or two additional records and then trigger an update of the saved similarity scores for just those new records plus for the twenty closest matches to those new records like this:

  ```bash
  openai-to-sqlite similar embeddings-bjcp-2021.db \
    --save '23G Gose' '01A American Light Lager' \
    --recalculate-for-matches \
    --count 20 \
    --print
  ```

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:
  ```bash
  cd openai-to-sqlite
  python -m venv venv
  source venv/bin/activate
  ```
  Now install the dependencies and test dependencies:
  ```bash
  pip install -e '.[test]'
  ```
  To run the tests:
  ```bash
  pytest
  ```

project_urls:
  {"CI": "https://github.com/simonw/openai-to-sqlite/actions", "Changelog": "https://github.com/simonw/openai-to-sqlite/releases", "Homepage": "https://github.com/simonw/openai-to-sqlite", "Issues": "https://github.com/simonw/openai-to-sqlite/issues"}

requires_dist:
  ["click", "httpx", "sqlite-utils>=3.28", "openai", "tiktoken", "pytest; extra == \"test\"", "pytest-httpx; extra == \"test\"", "pytest-mock; extra == \"test\""]


name: pocket-to-sqlite
summary: Create a SQLite database containing data from your Pocket account
classifiers: ["License :: OSI Approved :: Apache Software License"]
author: Simon Willison
description_content_type: text/markdown
license: Apache-2.0
package_url: https://pypi.org/project/pocket-to-sqlite/
project_url: https://pypi.org/project/pocket-to-sqlite/
release_url: https://pypi.org/project/pocket-to-sqlite/0.2.3/
requires_dist: ["sqlite-utils (>=2.4.4)", "click", "requests", "pytest ; extra == 'test'"]
requires_python: >3.7
version: 0.2.3
description:
  # pocket-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/pocket-to-sqlite.svg)](https://pypi.org/project/pocket-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/pocket-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/pocket-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/pocket-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/pocket-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/pocket-to-sqlite/blob/main/LICENSE)

  Create a SQLite database containing data from your [Pocket](https://getpocket.com/) account.

  ## How to install
  ```bash
  pip install pocket-to-sqlite
  ```
  ## Usage

  You will need to first obtain a valid OAuth token for your Pocket account. You can do this by running the `auth` command and following the prompts:
  ```bash
  pocket-to-sqlite auth
  ```
  Which looks like this:
  ```
  Visit this page and sign in with your Pocket account:

  https://getpocket.com/auth/author...

  Once you have signed in there, hit <enter> to continue
  Authentication tokens written to auth.json
  ```

  Now you can fetch all of your items from Pocket like this:

  ```bash
  pocket-to-sqlite fetch pocket.db
  ```

  The first time you run this command it will fetch all of your items, and display a progress bar while it does it.

  On subsequent runs it will only fetch new items.

  You can force it to fetch everything from the beginning again using `--all`. Use `--silent` to disable the progress bar.

  ## Using with Datasette

  The SQLite database produced by this tool is designed to be browsed using [Datasette](https://datasette.readthedocs.io/). Use the [datasette-render-timestamps](https://github.com/simonw/datasette-render-timestamps) plugin to improve the display of the timestamp values.

project_urls:
  {"CI": "https://github.com/dogsheep/pocket-to-sqlite/actions", "Changelog": "https://github.com/dogsheep/pocket-to-sqlite/releases", "Issues": "https://github.com/dogsheep/pocket-to-sqlite/issues", "homepage": "https://github.com/dogsheep/pocket-to-sqlite"}


name: pypi-to-sqlite
summary: Load data about Python packages from PyPI into SQLite
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/pypi-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/pypi-to-sqlite/
project_url: https://pypi.org/project/pypi-to-sqlite/
release_url: https://pypi.org/project/pypi-to-sqlite/0.2.3/
requires_python: >=3.7
version: 0.2.3
description:
  # pypi-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/pypi-to-sqlite.svg)](https://pypi.org/project/pypi-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/pypi-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/pypi-to-sqlite/releases)
  [![Tests](https://github.com/simonw/pypi-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/pypi-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/pypi-to-sqlite/blob/master/LICENSE)

  Load data about Python packages from PyPI into SQLite

  ## Installation

  Install this tool using `pip`:

      pip install pypi-to-sqlite

  ## Usage

  To create a SQLite database with details of one or more packages, run:

      pypi-to-sqlite pypi.db datasette sqlite-utils

  You can also process JSON that you have previously saved to disk like so:

      curl -o datasette.json https://pypi.org/pypi/datasette/json
      pypi-to-sqlite pypi.db -f datasette.json

  The tool will create three tables: `packages`, `versions` and `releases`. The full table schema is shown below.

  To create the tables with a prefix, use `--prefix prefix`. For example:

      pypi-to-sqlite pypi.db datasette --prefix pypi_

  This will create tables called `pypi_packages`, `pypi_versions` and `pypi_releases`.

  ## Demo

  You can see examples of tables created using this tool running in [Datasette](https://datasette.io/) here:

  - [packages](https://datasette.io/content/pypi_packages)
  - [versions](https://datasette.io/content/pypi_versions)
  - [releases](https://datasette.io/content/pypi_releases)

  ## Database schema

  <!-- [[[cog
  import cog, json
  from pypi_to_sqlite import cli
  from click.testing import CliRunner
  import sqlite_utils
  import tempfile, pathlib
  tmpdir = pathlib.Path(tempfile.mkdtemp())
  db_path = str(tmpdir / "pypi.db")
  runner = CliRunner()
  result = runner.invoke(cli.cli, [db_path, "-f", "tests/datasette-block.json"])
  cog.out("```sql\n")
  cog.out(sqlite_utils.Database(db_path).schema)
  cog.out("\n```")
  ]]] -->
  ```sql
  CREATE TABLE [packages] (
     [name] TEXT PRIMARY KEY,
     [summary] TEXT,
     [classifiers] TEXT,
     [description] TEXT,
     [author] TEXT,
     [author_email] TEXT,
     [description_content_type] TEXT,
     [home_page] TEXT,
     [keywords] TEXT,
     [license] TEXT,
     [maintainer] TEXT,
     [maintainer_email] TEXT,
     [package_url] TEXT,
     [platform] TEXT,
     [project_url] TEXT,
     [project_urls] TEXT,
     [release_url] TEXT,
     [requires_dist] TEXT,
     [requires_python] TEXT,
     [version] TEXT,
     [yanked] INTEGER,
     [yanked_reason] TEXT
  );
  CREATE TABLE [versions] (
     [id] TEXT PRIMARY KEY,
     [package] TEXT REFERENCES [packages]([name]),
     [name] TEXT
  );
  CREATE TABLE [releases] (
     [md5_digest] TEXT PRIMARY KEY,
     [package] TEXT REFERENCES [packages]([name]),
     [version] TEXT REFERENCES [versions]([id]),
     [packagetype] TEXT,
     [filename] TEXT,
     [comment_text] TEXT,
     [digests] TEXT,
     [has_sig] INTEGER,
     [python_version] TEXT,
     [requires_python] TEXT,
     [size] INTEGER,
     [upload_time] TEXT,
     [upload_time_iso_8601] TEXT,
     [url] TEXT,
     [yanked] INTEGER,
     [yanked_reason] TEXT
  );
  ```
  <!-- [[[end]]] -->

  ## pypi-to-sqlite --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["--help"])
  cog.out("```\n")
  cog.out(result.output.replace("Usage: cli", "Usage: pypi-to-sqlite"))
  cog.out("\n```")
  ]]] -->
  ```
  Usage: pypi-to-sqlite [OPTIONS] DB_PATH [PACKAGE]...

    Load data about Python packages from PyPI into SQLite

    Usage example:

        pypi-to-sqlite pypy.db datasette sqlite-utils

    Use -f to load data from a JSON file instead:

        pypi-to-sqlite pypy.db -f datasette.json

    Created tables will be packages, versions and releases

    To create tables called pypi_packages, pypi_versions, pypi_releases use
    --prefix pypi_:

        pypi-to-sqlite pypy.db datasette sqlite-utils --prefix pypi_

  Options:
    --version            Show the version and exit.
    -f, --file FILENAME  Import JSON from this file
    -d, --delay FLOAT    Wait this many seconds between requests
    --prefix TEXT        Prefix to use for the created database tables
    --help               Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd pypi-to-sqlite
      python -m venv venv
      source venv/bin/activate

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/pypi-to-sqlite/actions", "Changelog": "https://github.com/simonw/pypi-to-sqlite/releases", "Homepage": "https://github.com/simonw/pypi-to-sqlite", "Issues": "https://github.com/simonw/pypi-to-sqlite/issues"}

requires_dist:
  ["click", "sqlite-utils", "httpx", "pytest ; extra == 'test'", "pytest-httpx ; extra == 'test'", "cogapp ; extra == 'test'"]


name: s3-credentials
summary: A tool for creating credentials for accessing S3 buckets
classifiers: []
author: Simon Willison
description_content_type: text/markdown
dynamic: ["License-File"]
license: Apache-2.0
license_files: ["LICENSE"]
package_url: https://pypi.org/project/s3-credentials/
project_url: https://pypi.org/project/s3-credentials/
release_url: https://pypi.org/project/s3-credentials/0.17/
requires_dist: ["click", "boto3"]
requires_python: >=3.10
version: 0.17
description:
  # s3-credentials

  [![PyPI](https://img.shields.io/pypi/v/s3-credentials.svg)](https://pypi.org/project/s3-credentials/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/s3-credentials?include_prereleases&label=changelog)](https://github.com/simonw/s3-credentials/releases)
  [![Tests](https://github.com/simonw/s3-credentials/workflows/Test/badge.svg)](https://github.com/simonw/s3-credentials/actions?query=workflow%3ATest)
  [![Documentation Status](https://readthedocs.org/projects/s3-credentials/badge/?version=latest)](https://s3-credentials.readthedocs.org/)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/s3-credentials/blob/master/LICENSE)

  A tool for creating credentials for accessing S3 buckets

  For project background, see [s3-credentials: a tool for creating credentials for S3 buckets](https://simonwillison.net/2021/Nov/3/s3-credentials/) on my blog.

  ## Installation

      pip install s3-credentials

  ## Basic usage

  To create a new S3 bucket and output credentials that can be used with only that bucket:
  ```
  % s3-credentials create my-new-s3-bucket --create-bucket
  Created bucket:  my-new-s3-bucket
  Created user: s3.read-write.my-new-s3-bucket with permissions boundary: arn:aws:iam::aws:policy/AmazonS3FullAccess
  Attached policy s3.read-write.my-new-s3-bucket to user s3.read-write.my-new-s3-bucket
  Created access key for user: s3.read-write.my-new-s3-bucket
  {
      "UserName": "s3.read-write.my-new-s3-bucket",
      "AccessKeyId": "AKIAWXFXAIOZOYLZAEW5",
      "Status": "Active",
      "SecretAccessKey": "...",
      "CreateDate": "2021-11-03 01:38:24+00:00"
  }
  ```
  The tool can do a lot more than this. See the [documentation](https://s3-credentials.readthedocs.io/) for details.

  ## Documentation

  - [Full documentation](https://s3-credentials.readthedocs.io/)
  - [Command help reference](https://s3-credentials.readthedocs.io/en/stable/help.html)
  - [Release notes](https://github.com/simonw/s3-credentials/releases)

project_urls:
  {"CI": "https://github.com/simonw/s3-credentials/actions", "Changelog": "https://github.com/simonw/s3-credentials/releases", "Homepage": "https://github.com/simonw/s3-credentials", "Issues": "https://github.com/simonw/s3-credentials/issues"}


name: s3-ocr
summary: Tools for running OCR against files stored in S3
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/s3-ocr
license: Apache License, Version 2.0
package_url: https://pypi.org/project/s3-ocr/
project_url: https://pypi.org/project/s3-ocr/
release_url: https://pypi.org/project/s3-ocr/0.6.3/
requires_python: >=3.7
version: 0.6.3
description:
  # s3-ocr

  [![PyPI](https://img.shields.io/pypi/v/s3-ocr.svg)](https://pypi.org/project/s3-ocr/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/s3-ocr?include_prereleases&label=changelog)](https://github.com/simonw/s3-ocr/releases)
  [![Tests](https://github.com/simonw/s3-ocr/workflows/Test/badge.svg)](https://github.com/simonw/s3-ocr/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/s3-ocr/blob/master/LICENSE)

  Tools for running OCR against files stored in S3

  Background on this project: [s3-ocr: Extract text from PDF files stored in an S3 bucket](https://simonwillison.net/2022/Jun/30/s3-ocr/)

  ## Installation

  Install this tool using `pip`:

      pip install s3-ocr

  ## Demo

  You can see the results of running this tool against three PDFs from the Internet Archive ([one](https://archive.org/details/unmaskingrobert00houdgoog), [two](https://archive.org/details/practicalmagicia00harr), [three](https://archive.org/details/latestmagicbeing00hoff)) in [this example table](https://s3-ocr-demo.datasette.io/pages/pages?_facet=path#facet-path) hosted using [Datasette](https://datasette.io/).

  ## Starting OCR against PDFs in a bucket

  The `start` command takes a list of keys and submits them to [Textract](https://aws.amazon.com/textract/) for OCR processing.

  You need to have AWS configured using environment variables, credentials file in your home directory or a JSON or INI file generated using [s3-credentials](https://datasette.io/tools/s3-credentials).

  You can start the process running like this:

      s3-ocr start name-of-your-bucket my-pdf-file.pdf

  The paths you specify should be paths within the bucket. If you stored your PDF files in folders inside the bucket it should look like this:

      s3-ocr start name-of-your-bucket path/to/one.pdf path/to/two.pdf

  OCR can take some time. The results of the OCR will be stored in `textract-output` in your bucket.

  To process every file in the bucket with a `.pdf` extension use `--all`:

      s3-ocr start name-of-bucket --all

  To process every file with a `.pdf` extension within a specific folder, use `--prefix`:

      s3-ocr start name-of-bucket --prefix path/to/folder

  ### s3-ocr start --help

  <!-- [[[cog
  import cog
  from s3_ocr import cli
  from click.testing import CliRunner
  runner = CliRunner()
  result = runner.invoke(cli.cli, ["start", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help)
  )
  ]]] -->
  ```
  Usage: s3-ocr start [OPTIONS] BUCKET [KEYS]...

    Start OCR tasks for PDF files in an S3 bucket

        s3-ocr start name-of-bucket path/to/one.pdf path/to/two.pdf

    To process every file with a .pdf extension:

        s3-ocr start name-of-bucket --all

    To process every .pdf in the PUBLIC/ folder:

        s3-ocr start name-of-bucket --prefix PUBLIC/

  Options:
    --all                 Process all PDF files in the bucket
    --prefix TEXT         Process all PDF files within this prefix
    --dry-run             Show what this would do, but don't actually do it
    --no-retry            Don't retry failed requests
    --access-key TEXT     AWS access key ID
    --secret-key TEXT     AWS secret access key
    --session-token TEXT  AWS session token
    --endpoint-url TEXT   Custom endpoint URL
    -a, --auth FILENAME   Path to JSON/INI file containing credentials
    --help                Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## Checking status

  The `s3-ocr status <bucket-name>` command shows a rough indication of progress through the tasks:

  ```
  % s3-ocr status sfms-history
  153 complete out of 532 jobs
  ```
  It compares the jobs that have been submitted, based on `.s3-ocr.json` files, to the jobs that have their results written to the `textract-output/` folder.

  ### s3-ocr status --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["status", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr status [OPTIONS] BUCKET

    Show status of OCR jobs for a bucket

  Options:
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Inspecting a job

  The `s3-ocr inspect-job <job_id>` command can be used to check the status of a specific job ID:
  ```
  % s3-ocr inspect-job b267282745685226339b7e0d4366c4ff6887b7e293ed4b304dc8bb8b991c7864
  {
    "DocumentMetadata": {
      "Pages": 583
    },
    "JobStatus": "SUCCEEDED",
    "DetectDocumentTextModelVersion": "1.0"
  }
  ```

  ### s3-ocr inspect-job --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["inspect-job", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr inspect-job [OPTIONS] JOB_ID

    Show the current status of an OCR job

        s3-ocr inspect-job <job_id>

  Options:
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Fetching the results

  Once an OCR job has completed you can download the resulting JSON using the `fetch` command:

      s3-ocr fetch name-of-bucket path/to/file.pdf

  This will save files in the current directory with names like this:

  - `4d9b5cf580e761fdb16fd24edce14737ebc562972526ef6617554adfa11d6038-1.json`
  - `4d9b5cf580e761fdb16fd24edce14737ebc562972526ef6617554adfa11d6038-2.json`

  The number of files will vary depending on the length of the document.

  If you don't want separate files you can combine them together using the `-c/--combine` option:

      s3-ocr fetch name-of-bucket path/to/file.pdf --combine output.json

  The `output.json` file will then contain data that looks something like this:

  ```
  {
    "Blocks": [
      {
        "BlockType": "PAGE",
        "Geometry": {...}
        "Page": 1,
        ...
      },
      {
        "BlockType": "LINE",
        "Page": 1,
        ...
        "Text": "Barry",
      },
  ```
  ### s3-ocr fetch --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["fetch", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr fetch [OPTIONS] BUCKET KEY

    Fetch the OCR results for a specified file

        s3-ocr fetch name-of-bucket path/to/key.pdf

    This will save files in the current directory called things like

        a806e67e504fc15f...48314e-1.json     a806e67e504fc15f...48314e-2.json

    To combine these together into a single JSON file with a specified name, use:

        s3-ocr fetch name-of-bucket path/to/key.pdf --combine output.json

    Use "--output -" to print the combined JSON to standard output instead.

  Options:
    -c, --combine FILENAME  Write combined JSON to file
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Fetching just the text of a page

  If you don't want to deal with the JSON directly, you can use the `text` command to retrieve just the text extracted from a PDF:

      s3-ocr text name-of-bucket path/to/file.pdf

  This will output plain text to standard output.

  To save that to a file, use this:

      s3-ocr text name-of-bucket path/to/file.pdf > text.txt

  Separate pages will be separated by three newlines. To separate them using a `----` horizontal divider instead add `--divider`:

      s3-ocr text name-of-bucket path/to/file.pdf --divider

  ### s3-ocr text --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["text", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr text [OPTIONS] BUCKET KEY

    Retrieve the text from an OCRd PDF file

        s3-ocr text name-of-bucket path/to/key.pdf

  Options:
    --divider             Add ---- between pages
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Avoiding processing duplicates

  If you move files around within your S3 bucket `s3-ocr` can lose track of which files have already been processed. This can lead to additional Textract charges for processing should you run `s3-ocr start` against those new files.

  The `s3-ocr dedupe` command addresses this by scanning your bucket for files that have a new name but have previously been processed. It does this by looking at the `ETag` for each file, which represents the MD5 hash of the file contents.

  The command will write out new `.s3ocr.json` files for each detected duplicate. This will avoid those duplicates being run those duplicates through OCR a second time should yo run `s3-ocr start`.

      s3-ocr dedupe name-of-bucket

  Add `--dry-run` for a preview of the changes that will be made to your bucket.

  ### s3-ocr dedupe --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["dedupe", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr dedupe [OPTIONS] BUCKET

    Scan every file in the bucket checking for duplicates - files that have not
    yet been OCRd but that have the same contents (based on ETag) as a file that
    HAS been OCRd.

        s3-ocr dedupe name-of-bucket

  Options:
    --dry-run             Show output without writing anything to S3
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Changes made to your bucket

  To keep track of which files have been submitted for processing, `s3-ocr` will create a JSON file for every file that it adds to the OCR queue.

  This file will be called:

      path-to-file/name-of-file.pdf.s3-ocr.json

  Each of these JSON files contains data that looks like this:

  ```json
  {
    "job_id": "a34eb4e8dc7e70aa9668f7272aa403e85997364199a654422340bc5ada43affe",
    "etag": "\"b0c77472e15500347ebf46032a454e8e\""
  }
  ```
  The recorded `job_id` can be used later to associate the file with the results of the OCR task in `textract-output/`.

  The `etag` is the ETag of the S3 object at the time it was submitted. This can be used later to determine if a file has changed since it last had OCR run against it.

  This design for the tool, with the `.s3-ocr.json` files tracking jobs that have been submitted, means that it is safe to run `s3-ocr start` against the same bucket multiple times without the risk of starting duplicate OCR jobs.

  ## Creating a SQLite index of your OCR results

  The `s3-ocr index <bucket> <database_file>` command creates a SQLite database containing the results of the OCR, and configures SQLite full-text search against the text:

  ```
  % s3-ocr index sfms-history index.db
  Fetching job details  [####################################]  100%
  Populating pages table  [####################----------------]   55%  00:03:18
  ```
  The schema of the resulting database looks like this (excluding the FTS tables):
  ```sql
  CREATE TABLE [pages] (
     [path] TEXT,
     [page] INTEGER,
     [folder] TEXT,
     [text] TEXT,
     PRIMARY KEY ([path], [page])
  );
  CREATE TABLE [ocr_jobs] (
     [key] TEXT PRIMARY KEY,
     [job_id] TEXT,
     [etag] TEXT,
     [s3_ocr_etag] TEXT
  );
  CREATE TABLE [fetched_jobs] (
     [job_id] TEXT PRIMARY KEY
  );
  ```
  The database is designed to be used with [Datasette](https://datasette.io).

  ### s3-ocr index --help

  <!-- [[[cog
  result = runner.invoke(cli.cli, ["index", "--help"])
  help = result.output.replace("Usage: cli", "Usage: s3-ocr")
  cog.out(
      "```\n{}\n```".format(help.split("--access-key")[0] + "--access-key ...")
  )
  ]]] -->
  ```
  Usage: s3-ocr index [OPTIONS] BUCKET DATABASE

    Create a SQLite database with OCR results for files in a bucket

  Options:
    --access-key ...
  ```
  <!-- [[[end]]] -->

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd s3-ocr
      python -m venv venv
      source venv/bin/activate

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

  To regenerate the README file with the latest `--help`:

      cog -r README.md

project_urls:
  {"CI": "https://github.com/simonw/s3-ocr/actions", "Changelog": "https://github.com/simonw/s3-ocr/releases", "Homepage": "https://github.com/simonw/s3-ocr", "Issues": "https://github.com/simonw/s3-ocr/issues"}

requires_dist:
  ["click (>=8.0)", "boto3", "sqlite-utils", "pytest ; extra == 'test'", "moto[s3,textract] ; extra == 'test'", "cogapp ; extra == 'test'", "pytest-mock ; extra == 'test'"]


name: shapefile-to-sqlite
summary: Load shapefiles into a SQLite (optionally SpatiaLite) database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/shapefile-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/shapefile-to-sqlite/
project_url: https://pypi.org/project/shapefile-to-sqlite/
release_url: https://pypi.org/project/shapefile-to-sqlite/0.4.2/
requires_dist: ["sqlite-utils (>=2.2)", "Shapely", "Fiona", "pyproj", "pytest ; extra == 'test'"]
version: 0.4.2
description:
  # shapefile-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/shapefile-to-sqlite.svg)](https://pypi.org/project/shapefile-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/shapefile-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/shapefile-to-sqlite/releases)
  [![Tests](https://github.com/simonw/shapefile-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/shapefile-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/shapefile-to-sqlite/blob/main/LICENSE)

  Load shapefiles into a SQLite (optionally SpatiaLite) database.

  Project background: [Things I learned about shapefiles building shapefile-to-sqlite](https://simonwillison.net/2020/Feb/19/shapefile-to-sqlite/)

  ## How to install

      $ pip install shapefile-to-sqlite

  ## How to use

  You can run this tool against a shapefile file like so:

      $ shapefile-to-sqlite my.db features.shp

  This will load the geometries as GeoJSON in a text column.

  ## Using with SpatiaLite

  If you have [SpatiaLite](https://www.gaia-gis.it/fossil/libspatialite/index) available you can load them as SpatiaLite geometries like this:

      $ shapefile-to-sqlite my.db features.shp --spatialite

  The data will be loaded into a table called `features` - based on the name of the shapefile. You can specify an alternative table name using `--table`:

      $ shapefile-to-sqlite my.db features.shp --table=places --spatialite

  The tool will search for the SpatiaLite module in the following locations:

  - `/usr/lib/x86_64-linux-gnu/mod_spatialite.so`
  - `/usr/local/lib/mod_spatialite.dylib`

  If you have installed the module in another location, you can use the `--spatialite_mod=xxx` option to specify where:

      $ shapefile-to-sqlite my.db features.shp \
          --spatialite_mod=/usr/lib/mod_spatialite.dylib

  You can use the `--spatial-index` option to create a spatial index on the `geometry` column:

      $ shapefile-to-sqlite my.db features.shp --spatial-index

  You can omit `--spatialite` if you use either `--spatialite-mod` or `--spatial-index`.

  ## Projections

  By default, this tool will attempt to convert geometries in the shapefile to the WGS 84 projection, for best conformance with the [GeoJSON specification](https://tools.ietf.org/html/rfc7946).

  If you want it to leave the data in whatever projection was used by the shapefile, use the `--crs=keep` option.

  You can convert the data to another output projection by passing it to the `--crs` option. For example, to convert to [EPSG:2227](https://epsg.io/2227) (California zone 3) use `--crs=espg:2227`.

  The full list of formats accepted by the `--crs` option is [documented here](https://pyproj4.github.io/pyproj/stable/api/crs.html#pyproj.crs.CRS.__init__).

  ## Extracting columns

  If your data contains columns with a small number of heavily duplicated values - the names of specific agencies responsible for parcels of land for example - you can extract those columns into separate lookup tables referenced by foreign keys using the `-c` option:

      $ shapefile-to-sqlite my.db features.shp -c agency

  This will create a `agency` table with `id` and `name` columns, and will create the `agency` column in your main table as an integer foreign key reference to that table.

  The `-c` option can be used multiple times.

  [CPAD_2020a_Units](https://calands.datasettes.com/calands/CPAD_2020a_Units) is an example of a table created using the `-c` option.

project_urls:
  {"CI": "https://github.com/simonw/shapefile-to-sqlite/actions", "Changelog": "https://github.com/simonw/shapefile-to-sqlite/releases", "Homepage": "https://github.com/simonw/shapefile-to-sqlite", "Issues": "https://github.com/simonw/shapefile-to-sqlite/issues"}


name: shot-scraper
summary: A command-line utility for scraping and taking automated screenshots of websites
classifiers: []
author: Simon Willison
description_content_type: text/markdown
license_expression: Apache-2.0
package_url: https://pypi.org/project/shot-scraper/
project_url: https://pypi.org/project/shot-scraper/
release_url: https://pypi.org/project/shot-scraper/1.9.1/
requires_dist: ["click", "pyyaml", "playwright", "click-default-group"]
requires_python: >=3.10
version: 1.9.1
description:
  # shot-scraper

  [![PyPI](https://img.shields.io/pypi/v/shot-scraper.svg)](https://pypi.org/project/shot-scraper/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/shot-scraper?include_prereleases&label=changelog)](https://github.com/simonw/shot-scraper/releases)
  [![Tests](https://github.com/simonw/shot-scraper/workflows/Test/badge.svg)](https://github.com/simonw/shot-scraper/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/shot-scraper/blob/master/LICENSE)
  [![discord](https://img.shields.io/discord/823971286308356157?label=discord)](https://discord.gg/EE7Hx4Kbny)

  A command-line utility for taking automated screenshots of websites

  For background on this project see [shot-scraper: automated screenshots for documentation, built on Playwright](https://simonwillison.net/2022/Mar/10/shot-scraper/).

  ## Documentation

  - [Full documentation for shot-scraper](https://shot-scraper.datasette.io/)
  - [Tutorial: Automating screenshots for the Datasette documentation using shot-scraper](https://simonwillison.net/2022/Oct/14/automating-screenshots/)
  - [Release notes](https://github.com/simonw/shot-scraper/releases)

  ## Get started with GitHub Actions

  To get started without installing any software, use the [shot-scraper-template](https://github.com/simonw/shot-scraper-template) template to create your own GitHub repository which takes screenshots of a page using `shot-scraper`. See [Instantly create a GitHub repository to take screenshots of a web page](https://simonwillison.net/2022/Mar/14/shot-scraper-template/) for details.

  ## Quick installation

  You can install the `shot-scraper` CLI tool using [pip](https://pip.pypa.io/):

      pip install shot-scraper
      # Now install the browser it needs:
      shot-scraper install

  ## Taking your first screenshot

  You can take a screenshot of a web page like this:

      shot-scraper https://datasette.io/

  This will create a screenshot in a file called `datasette-io.png`.

  Many more options are available, see [Taking a screenshot](https://shot-scraper.datasette.io/en/stable/screenshots.html) for details.

  ## Examples

  - The [shot-scraper-demo](https://github.com/simonw/shot-scraper-demo) repository uses this tool to capture recently spotted owls in El Granada, CA according to [this page](https://www.owlsnearme.com/?place=127871), and to  generate an annotated screenshot illustrating a Datasette feature as described [in my blog](https://simonwillison.net/2022/Mar/10/shot-scraper/#a-complex-example).
  - The [Datasette Documentation](https://docs.datasette.io/en/latest/) uses screenshots taken by `shot-scraper` running in the [simonw/datasette-screenshots](https://github.com/simonw/datasette-screenshots) GitHub repository, described in detail in [Automating screenshots for the Datasette documentation using shot-scraper](https://simonwillison.net/2022/Oct/14/automating-screenshots/).
  - Ben Welsh built [@newshomepages](https://twitter.com/newshomepages), a Twitter bot that uses `shot-scraper` and GitHub Actions to take screenshots of news website homepages and publish them to Twitter. The code for that lives in [palewire/news-homepages](https://github.com/palewire/news-homepages).
  - [scrape-hacker-news-by-domain](https://github.com/simonw/scrape-hacker-news-by-domain) uses `shot-scraper javascript` to scrape a web page. See [Scraping web pages from the command-line with shot-scraper](https://simonwillison.net/2022/Mar/14/scraping-web-pages-shot-scraper/) for details of how this works.
  - Reuters uses shot-scraper to generate regularly updating data dashboards [for email newsletters](https://twitter.com/palewire/status/1658069533763026944).

project_urls:
  {"CI": "https://github.com/simonw/shot-scraper/actions", "Changelog": "https://github.com/simonw/shot-scraper/releases", "Homepage": "https://shot-scraper.datasette.io/", "Issues": "https://github.com/simonw/shot-scraper/issues"}


name: sphinx-to-sqlite
summary: Create a SQLite database from Sphinx documentation
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/sphinx-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/sphinx-to-sqlite/
project_url: https://pypi.org/project/sphinx-to-sqlite/
release_url: https://pypi.org/project/sphinx-to-sqlite/0.1a1/
requires_dist: ["click", "sqlite-utils", "pytest ; extra == 'test'"]
version: 0.1a1
description:
  # sphinx-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/sphinx-to-sqlite.svg)](https://pypi.org/project/sphinx-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sphinx-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/sphinx-to-sqlite/releases)
  [![Tests](https://github.com/simonw/sphinx-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/sphinx-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/sphinx-to-sqlite/blob/master/LICENSE)

  Create a SQLite database from Sphinx documentation.

  ## Demo

  You can see the results of running this tool against the [Datasette documentation](https://docs.datasette.io/) at https://latest-docs.datasette.io/docs/sections

  ## Installation

  Install this tool using `pip`:

      $ pip install sphinx-to-sqlite

  ## Usage

  First run `sphinx-build` with the `-b xml` option to create XML files in your `_build/` directory.

  Then run:

      $ sphinx-to-sqlite docs.db path/to/_build

  To build the SQLite database.

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd sphinx-to-sqlite
      python -mvenv venv
      source venv/bin/activate

  Or if you are using `pipenv`:

      pipenv shell

  Now install the dependencies and tests:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/sphinx-to-sqlite/actions", "Changelog": "https://github.com/simonw/sphinx-to-sqlite/releases", "Homepage": "https://github.com/simonw/sphinx-to-sqlite", "Issues": "https://github.com/simonw/sphinx-to-sqlite/issues"}


name: sqlite-colorbrewer
summary: A custom function to use ColorBrewer scales in SQLite queries
classifiers: ["Framework :: Datasette", "License :: OSI Approved :: Apache Software License"]
author: Chris Amico
description_content_type: text/markdown
home_page: https://github.com/eyeseast/sqlite-colorbrewer
license: Apache License, Version 2.0
package_url: https://pypi.org/project/sqlite-colorbrewer/
project_url: https://pypi.org/project/sqlite-colorbrewer/
release_url: https://pypi.org/project/sqlite-colorbrewer/0.2/
requires_dist: ["datasette ; extra == 'test'", "pytest ; extra == 'test'", "pytest-asyncio ; extra == 'test'"]
requires_python: >=3.6
version: 0.2
description:
  # sqlite-colorbrewer

  [![PyPI](https://img.shields.io/pypi/v/sqlite-colorbrewer.svg)](https://pypi.org/project/sqlite-colorbrewer/)
  [![Changelog](https://img.shields.io/github/v/release/eyeseast/sqlite-colorbrewer?include_prereleases&label=changelog)](https://github.com/eyeseast/sqlite-colorbrewer/releases)
  [![Tests](https://github.com/eyeseast/sqlite-colorbrewer/workflows/Test/badge.svg)](https://github.com/eyeseast/sqlite-colorbrewer/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/eyeseast/sqlite-colorbrewer/blob/main/LICENSE)

  A custom function to use [ColorBrewer](https://colorbrewer2.org/) scales in SQLite queries.

  Colors are exported from [here](https://colorbrewer2.org/export/colorbrewer.json).

  ## Installation

  To install as a Python library and use with the [standard SQLite3 module](https://docs.python.org/3/library/sqlite3.html):

      pip install sqlite-colorbrewer

  To install this plugin in the same environment as Datasette.

      datasette install sqlite-colorbrewer

  ## Usage

  If you're using this library with Datasette, it will be automatically registered as a plugin and available for use in SQL queries, like so:

  ```sql
  SELECT colorbrewer('Blues', 9, 0);
  ```

  That will return a single value: `"rgb(247,251,255)"`

  To use with a SQLite connection outside of Datasette, use the `register` function:

  ```python
  >>> import sqlite3
  >>> import sqlite_colorbrewer

  >>> conn = sqlite3.connect(':memory')
  >>> sqlite_colorbrewer.register(conn)

  >>> cursor = conn.execute("SELECT colorbrewer('Blues', 9, 0);")
  >>> result = next(cursor)
  >>> print(result)
  rgb(247,251,255)
  ```

  ## Development

  To set up this plugin locally, first checkout the code. Then create a new virtual environment:

      cd sqlite-colorbrewer
      python3 -mvenv venv
      source venv/bin/activate

  Or if you are using `pipenv`:

      pipenv shell

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

  To build `sqlite_colorbrewer/colorbrewer.py`:

      ./json_to_python.py
      black . # to format the resulting file

  ## ColorBrewer

  Copyright (c) 2002 Cynthia Brewer, Mark Harrower, and The Pennsylvania State University.

  Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

  http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

  See the [ColorBrewer updates](http://www.personal.psu.edu/cab38/ColorBrewer/ColorBrewer_updates.html) for updates to copyright information.

project_urls:
  {"CI": "https://github.com/eyeseast/sqlite-colorbrewer/actions", "Changelog": "https://github.com/eyeseast/sqlite-colorbrewer/releases", "Homepage": "https://github.com/eyeseast/sqlite-colorbrewer", "Issues": "https://github.com/eyeseast/sqlite-colorbrewer/issues"}


name: sqlite-comprehend
summary: Tools for running data in a SQLite database through AWS Comprehend
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/sqlite-comprehend
license: Apache License, Version 2.0
package_url: https://pypi.org/project/sqlite-comprehend/
project_url: https://pypi.org/project/sqlite-comprehend/
release_url: https://pypi.org/project/sqlite-comprehend/0.2.2/
requires_python: >=3.7
version: 0.2.2
description:
  # sqlite-comprehend

  [![PyPI](https://img.shields.io/pypi/v/sqlite-comprehend.svg)](https://pypi.org/project/sqlite-comprehend/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sqlite-comprehend?include_prereleases&label=changelog)](https://github.com/simonw/sqlite-comprehend/releases)
  [![Tests](https://github.com/simonw/sqlite-comprehend/workflows/Test/badge.svg)](https://github.com/simonw/sqlite-comprehend/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/sqlite-comprehend/blob/master/LICENSE)

  Tools for running data in a SQLite database through [AWS Comprehend](https://aws.amazon.com/comprehend/)

  See [sqlite-comprehend: run AWS entity extraction against content in a SQLite database](https://simonwillison.net/2022/Jul/11/sqlite-comprehend/) for background on this project.

  ## Installation

  Install this tool using `pip`:

      pip install sqlite-comprehend

  ## Demo

  You can see examples of tables generated using this command here:

  - [comprehend_entities](https://datasette.simonwillison.net/simonwillisonblog/comprehend_entities) - the extracted entities, classified by type
  - [blog_entry_comprehend_entities](https://datasette.simonwillison.net/simonwillisonblog/blog_entry_comprehend_entities) - a table relating entities to the entries that they appear in
  - [comprehend_entity_types](https://datasette.simonwillison.net/simonwillisonblog/comprehend_entity_types) - a small lookup table of entity types

  ## Configuration

  You will need AWS credentials with the `comprehend:BatchDetectEntities` [IAM permission](https://docs.aws.amazon.com/comprehend/latest/dg/access-control-managing-permissions.html).

  You can configure credentials [using these instructions](https://boto3.amazonaws.com/v1/documentation/api/latest/guide/credentials.html). You can also save them to a JSON or INI configuration file and pass them to the command using `-a credentials.ini`, or pass them using the `--access-key` and `--secret-key` options.

  ## Entity extraction

  The `sqlite-comprehend entities` command runs entity extraction against every row in the specified table and saves the results to your database.

  Specify the database, the table and one or more columns containing text in that table. The following runs against the `text` column in the `pages` table of the `sfms.db` SQLite database:

      sqlite-comprehend sfms.db pages text

  Results will be written into a `pages_comprehend_entities` table. Change the name of the output table by passing `-o other_table_name`.

  You can run against a subset of rows by adding a `--where` clause:

      sqlite-comprehend sfms.db pages text --where 'id < 10'

  You can also used named parameters in your `--where` clause:

      sqlite-comprehend sfms.db pages text --where 'id < :maxid' -p maxid 10

  Only the first 5,000 characters for each row will be considered. Be sure to review [Comprehend's pricing](https://aws.amazon.com/comprehend/pricing/) - which starts at $0.0001 per hundred characters.

  If your context includes HTML tags, you can strip them out before extracting entities by adding `--strip-tags`:

      sqlite-comprehend sfms.db pages text --strip-tags

  Rows that have been processed are recorded in the `pages_comprehend_entities_done` table. If you run the command more than once it will only process rows that have been newly added.

  You can delete records from that `_done` table to run them again.

  ### sqlite-comprehend entities --help

  <!-- [[[cog
  from click.testing import CliRunner
  from sqlite_comprehend import cli
  runner = CliRunner()
  result = runner.invoke(cli.cli, ["entities", "--help"])
  help = result.output.replace("Usage: cli", "Usage: sqlite-comprehend")
  cog.out(
      "```\n{}\n```".format(help)
  )
  ]]] -->
  ```
  Usage: sqlite-comprehend entities [OPTIONS] DATABASE TABLE COLUMNS...

    Detect entities in columns in a table

    To extract entities from columns text1 and text2 in mytable:

        sqlite-comprehend entities my.db mytable text1 text2

    To run against just a subset of the rows in the table, add:

        --where "id < :max_id" -p max_id 50

    Results will be written to a table called mytable_comprehend_entities

    To specify a different output table, use -o custom_table_name

  Options:
    --where TEXT                WHERE clause to filter table
    -p, --param <TEXT TEXT>...  Named :parameters for SQL query
    -o, --output TEXT           Custom output table
    -r, --reset                 Start from scratch, deleting previous results
    --strip-tags                Strip HTML tags before extracting entities
    --access-key TEXT           AWS access key ID
    --secret-key TEXT           AWS secret access key
    --session-token TEXT        AWS session token
    --endpoint-url TEXT         Custom endpoint URL
    -a, --auth FILENAME         Path to JSON/INI file containing credentials
    --help                      Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## Schema

  Assuming an input table called `pages` the tables created by this tool will have the following schema:

  <!-- [[[cog
  import cog, json
  from sqlite_comprehend import cli
  from unittest.mock import patch
  from click.testing import CliRunner
  import sqlite_utils
  import tempfile, pathlib
  tmpdir = pathlib.Path(tempfile.mkdtemp())
  db_path = str(tmpdir / "data.db")
  db = sqlite_utils.Database(db_path)
  db["pages"].insert_all(
      [
          {
              "id": 1,
              "text": "John Bob",
          },
          {
              "id": 2,
              "text": "Sandra X",
          },
      ],
      pk="id",
  )
  with patch('boto3.client') as client:
      client.return_value.batch_detect_entities.return_value = {
          "ResultList": [
              {
                  "Index": 0,
                  "Entities": [
                      {
                          "Score": 0.8,
                          "Type": "PERSON",
                          "Text": "John Bob",
                          "BeginOffset": 0,
                          "EndOffset": 5,
                      },
                  ],
              },
              {
                  "Index": 1,
                  "Entities": [
                      {
                          "Score": 0.8,
                          "Type": "PERSON",
                          "Text": "Sandra X",
                          "BeginOffset": 0,
                          "EndOffset": 5,
                      },
                  ],
              },
          ],
          "ErrorList": [],
      }
      runner = CliRunner()
      result = runner.invoke(cli.cli, [
          "entities", db_path, "pages", "text"
      ])
  cog.out("```sql\n")
  cog.out(db.schema)
  cog.out("\n```")
  ]]] -->
  ```sql
  CREATE TABLE [pages] (
     [id] INTEGER PRIMARY KEY,
     [text] TEXT
  );
  CREATE TABLE [comprehend_entity_types] (
     [id] INTEGER PRIMARY KEY,
     [value] TEXT
  );
  CREATE TABLE [comprehend_entities] (
     [id] INTEGER PRIMARY KEY,
     [name] TEXT,
     [type] INTEGER REFERENCES [comprehend_entity_types]([id])
  );
  CREATE TABLE [pages_comprehend_entities] (
     [id] INTEGER REFERENCES [pages]([id]),
     [score] FLOAT,
     [entity] INTEGER REFERENCES [comprehend_entities]([id]),
     [begin_offset] INTEGER,
     [end_offset] INTEGER
  );
  CREATE UNIQUE INDEX [idx_comprehend_entity_types_value]
      ON [comprehend_entity_types] ([value]);
  CREATE UNIQUE INDEX [idx_comprehend_entities_type_name]
      ON [comprehend_entities] ([type], [name]);
  CREATE TABLE [pages_comprehend_entities_done] (
     [id] INTEGER PRIMARY KEY REFERENCES [pages]([id])
  );
  ```
  <!-- [[[end]]] -->

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd sqlite-comprehend
      python -m venv venv
      source venv/bin/activate

  Now install the dependencies and test dependencies:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/sqlite-comprehend/actions", "Changelog": "https://github.com/simonw/sqlite-comprehend/releases", "Homepage": "https://github.com/simonw/sqlite-comprehend", "Issues": "https://github.com/simonw/sqlite-comprehend/issues"}

requires_dist:
  ["click", "boto3", "sqlite-utils", "pytest ; extra == 'test'", "pytest-mock ; extra == 'test'", "cogapp ; extra == 'test'"]


name: sqlite-diffable
summary: Tools for dumping/loading a SQLite database to diffable directory structure
classifiers: []
author: Simon Willison
description_content_type: text/markdown
dynamic: ["License-File"]
license_expression: Apache-2.0
license_files: ["LICENSE"]
package_url: https://pypi.org/project/sqlite-diffable/
project_url: https://pypi.org/project/sqlite-diffable/
provides_extra: ["test"]
release_url: https://pypi.org/project/sqlite-diffable/0.6/
requires_dist: ["click", "sqlite-utils", "pytest; extra == \"test\"", "black; extra == \"test\""]
requires_python: >=3.9
version: 0.6
description:
  # sqlite-diffable

  [![PyPI](https://img.shields.io/pypi/v/sqlite-diffable.svg)](https://pypi.org/project/sqlite-diffable/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sqlite-diffable?include_prereleases&label=changelog)](https://github.com/simonw/sqlite-diffable/releases)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/sqlite-diffable/blob/main/LICENSE)

  Tools for dumping/loading a SQLite database to diffable directory structure

  ## Installation

      pip install sqlite-diffable

  ## Demo

  The repository at [simonw/simonwillisonblog-backup](https://github.com/simonw/simonwillisonblog-backup) contains a backup of the database on my blog, https://simonwillison.net/ - created using this tool.

  ## Dumping a database

  Given a SQLite database called `fixtures.db` containing a table `facetable`, the following will dump out that table to the `dump/` directory:
  ```bash
  sqlite-diffable dump fixtures.db dump/ facetable
  ```
  To dump out every table in that database, use `--all`:
  ```bash
  sqlite-diffable dump fixtures.db dump/ --all
  ```
  To dump all table except some specific ones, use `--exclude` one or more times:
  ```bash
  sqlite-diffable dump fixtures.db dump/ --all \
    --exclude unwanted_first_table \
    --exclude unwanted_second_table
  ```
  ## Loading a database

  To load a previously dumped database, run the following:
  ```bash
  sqlite-diffable load restored.db dump/
  ```
  This will show an error if any of the tables that are being restored already exist in the database file.

  You can replace those tables (dropping them before restoring them) using the `--replace` option:
  ```bash
  sqlite-diffable load restored.db dump/ --replace
  ```
  ## Converting to JSON objects

  Table rows are stored in the `.ndjson` files as newline-delimited JSON arrays, like this:

  ```
  ["a", "a", "a-a", 63, null, 0.7364712141640124, "$null"]
  ["a", "b", "a-b", 51, null, 0.6020187290499803, "$null"]
  ```

  Sometimes it can be more convenient to work with a list of JSON objects.

  The `sqlite-diffable objects` command can read a `.ndjson` file and its accompanying `.metadata.json` file and output JSON objects to standard output:
  ```bash
  sqlite-diffable objects fixtures.db dump/sortable.ndjson
  ```
  The output of that command looks something like this:
  ```
  {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"}
  {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"}
  ```

  Add `-o` to write that output to a file:
  ```bash
  sqlite-diffable objects fixtures.db dump/sortable.ndjson -o output.txt
  ```
  Add `--array` to output a JSON array of objects, as opposed to a newline-delimited file:
  ```bash
  sqlite-diffable objects fixtures.db dump/sortable.ndjson --array
  ```
  Output:
  ```
  [
  {"pk1": "a", "pk2": "a", "content": "a-a", "sortable": 63, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.7364712141640124, "text": "$null"},
  {"pk1": "a", "pk2": "b", "content": "a-b", "sortable": 51, "sortable_with_nulls": null, "sortable_with_nulls_2": 0.6020187290499803, "text": "$null"}
  ]
  ```

  ## Storage format

  Each table is represented as two files. The first, `table_name.metadata.json`, contains metadata describing the structure of the table. For a table called `redirects_redirect` that file might look like this:

  ```json
  {
      "name": "redirects_redirect",
      "columns": [
          "id",
          "domain",
          "path",
          "target",
          "created"
      ],
      "schema": "CREATE TABLE [redirects_redirect] (\n   [id] INTEGER PRIMARY KEY,\n   [domain] TEXT,\n   [path] TEXT,\n   [target] TEXT,\n   [created] TEXT\n)"
  }
  ```

  It is an object with three keys: `name` is the name of the table, `columns` is an array of column strings and `schema` is the SQL schema text used for tha table.

  The second file, `table_name.ndjson`, contains newline-delimited JSON (aka [JSON Lines](https://jsonlines.org/)) for every row in the table. Each row is represented as a JSON array with items corresponding to each of the columns defined in the metadata.

  That file for the `redirects_redirect.ndjson` table might look like this:

  ```
  [1, "feeds.simonwillison.net", "swn-everything", "https://simonwillison.net/atom/everything/", "2017-10-01T21:11:36.440537+00:00"]
  [2, "feeds.simonwillison.net", "swn-entries", "https://simonwillison.net/atom/entries/", "2017-10-01T21:12:32.478849+00:00"]
  [3, "feeds.simonwillison.net", "swn-links", "https://simonwillison.net/atom/links/", "2017-10-01T21:12:54.820729+00:00"]
  ```

project_urls:
  {"CI": "https://github.com/simonw/sqlite-diffable/actions", "Changelog": "https://github.com/simonw/sqlite-diffable/releases", "Homepage": "https://github.com/simonw/sqlite-diffable", "Issues": "https://github.com/simonw/sqlite-diffable/issues"}


name: sqlite-generate
summary: Tool for generating demo SQLite databases
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/sqlite-generate
license: Apache License, Version 2.0
package_url: https://pypi.org/project/sqlite-generate/
project_url: https://pypi.org/project/sqlite-generate/
release_url: https://pypi.org/project/sqlite-generate/1.1.1/
requires_dist: ["click", "Faker", "sqlite-utils", "pytest ; extra == 'test'"]
version: 1.1.1
description:
  # sqlite-generate

  [![PyPI](https://img.shields.io/pypi/v/sqlite-generate.svg)](https://pypi.org/project/sqlite-generate/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sqlite-generate?label=changelog)](https://github.com/simonw/sqlite-generate/releases)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/sqlite-generate/blob/master/LICENSE)

  Tool for generating demo SQLite databases

  ## Installation

  Install this plugin using `pip`:

      $ pip install sqlite-generate

  ## Demo

  You can see a demo of the database generated using this command running in [Datasette](https://github.com/simonw/datasette) at https://sqlite-generate-demo.datasette.io/

  The demo is generated using the following command:

      sqlite-generate demo.db --seed seed --fts --columns=10 --fks=0,3 --pks=0,2

  ## Usage

  To generate a SQLite database file called `data.db` with 10 randomly named tables in it, run the following:

      sqlite-generate data.db

  You can use the `--tables` option to generate a different number of tables:

      sqlite-generate data.db --tables 20

  You can run the command against the same database file multiple times to keep adding new tables, using different settings for each batch of generated tables.

  By default each table will contain a random number of rows between 0 and 200. You can customize this with the `--rows` option:

      sqlite-generate data.db --rows 20

  This will insert 20 rows into each table.

      sqlite-generate data.db --rows 500,2000

  This inserts a random number of rows between 500 and 2000 into each table.

  Each table will have 5 columns. You can change this using `--columns`:

      sqlite-generate data.db --columns 10

  `--columns` can also accept a range:

      sqlite-generate data.db --columns 5,15

  You can control the random number seed used with the `--seed` option. This will result in the exact same database file being created by multiple runs of the tool:

      sqlite-generate data.db --seed=myseed

  By default each table will contain between 0 and 2 foreign key columns to other tables. You can control this using the `--fks` option, with either a single number or a range:

      sqlite-generate data.db --columns=20 --fks=5,15

  Each table will have a single primary key column called `id`. You can use the `--pks=` option to change the number of primary key columns on each table. Drop it to 0 to generate [rowid tables](https://www.sqlite.org/rowidtable.html). Increase it above 1 to generate tables with compound primary keys. Or use a range to get a random selection of different primary key layouts:

      sqlite-generate data.db --pks=0,2

  To configure [SQLite full-text search](https://www.sqlite.org/fts5.html) for all columns of type text, use `--fts`:

      sqlite-generate data.db --fts

  This will use FTS5 by default. To use [FTS4](https://www.sqlite.org/fts3.html) instead, use `--fts4`.

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd sqlite-generate
      python -mvenv venv
      source venv/bin/activate

  Or if you are using `pipenv`:

      pipenv shell

  Now install the dependencies and tests:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/sqlite-generate/actions", "Changelog": "https://github.com/simonw/sqlite-generate/releases", "Homepage": "https://github.com/simonw/sqlite-generate", "Issues": "https://github.com/simonw/sqlite-generate/issues"}


name: sqlite-transform
summary: Tool for running transformations on columns in a SQLite database.
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/sqlite-transform
license: Apache License, Version 2.0
package_url: https://pypi.org/project/sqlite-transform/
project_url: https://pypi.org/project/sqlite-transform/
project_urls: {"Homepage": "https://github.com/simonw/sqlite-transform"}
release_url: https://pypi.org/project/sqlite-transform/1.2.1/
requires_dist: ["dateutils", "tqdm", "click", "sqlite-utils", "pytest ; extra == 'test'"]
version: 1.2.1
description:
  # sqlite-transform

  ![No longer maintained](https://img.shields.io/badge/no%20longer-maintained-red)
  [![PyPI](https://img.shields.io/pypi/v/sqlite-transform.svg)](https://pypi.org/project/sqlite-transform/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sqlite-transform?include_prereleases&label=changelog)](https://github.com/simonw/sqlite-transform/releases)
  [![Tests](https://github.com/simonw/sqlite-transform/workflows/Test/badge.svg)](https://github.com/simonw/sqlite-transform/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/sqlite-transform/blob/main/LICENSE)

  Tool for running transformations on columns in a SQLite database.

  > **:warning: This tool is no longer maintained**
  >
  > I added a new tool to [sqlite-utils](https://sqlite-utils.datasette.io/) called [sqlite-utils convert](https://sqlite-utils.datasette.io/en/stable/cli.html#converting-data-in-columns) which provides a super-set of the functionality originally provided here. `sqlite-transform` is no longer maintained, and I recommend switching to using `sqlite-utils convert` instead.

  ## How to install

      pip install sqlite-transform

  ## parsedate and parsedatetime

  These subcommands will run all values in the specified column through `dateutils.parser.parse()` and replace them with the result, formatted as an ISO timestamp or ISO date.

  For example, if a row in the database has an `opened` column which contains `10/10/2019 08:10:00 PM`, running the following command:

      sqlite-transform parsedatetime my.db mytable opened

  Will result in that value being replaced by `2019-10-10T20:10:00`.

  Using the `parsedate` subcommand here would result in `2019-10-10` instead.

  In the case of ambiguous dates such as `03/04/05` these commands both default to assuming American-style `mm/dd/yy` format. You can pass `--dayfirst` to specify that the day should be assumed to be first, or `--yearfirst` for the year.

  ## jsonsplit

  The `jsonsplit` subcommand takes columns that contain a comma-separated list, for example a `tags` column containing records like `"trees,park,dogs"` and converts it into a JSON array `["trees", "park", "dogs"]`.

  This is useful for taking advantage of Datasette's [Facet by JSON array](https://docs.datasette.io/en/stable/facets.html#facet-by-json-array) feature.

      sqlite-transform jsonsplit my.db mytable tags

  It defaults to splitting on commas, but you can specify a different delimiter character using the `--delimiter` option, for example:

      sqlite-transform jsonsplit \
          my.db mytable tags --delimiter ';'

  Values within the array will be treated as strings, so a column containing `123,552,775` will be converted into the JSON array `["123", "552", "775"]`.

  You can specify a different type for these values using `--type int` or `--type float`, for example:

      sqlite-transform jsonsplit \
          my.db mytable tags --type int

  This will result in that column being converted into `[123, 552, 775]`.

  ## lambda for executing your own code

  The `lambda` subcommand lets you specify Python code which will be executed against the column.

  Here's how to convert a column to uppercase:

      sqlite-transform lambda my.db mytable mycolumn --code='str(value).upper()'

  The code you provide will be compiled into a function that takes `value` as a single argument. You can break your function body into multiple lines, provided the last line is a `return` statement:

      sqlite-transform lambda my.db mytable mycolumn --code='value = str(value)
      return value.upper()'

  You can also specify Python modules that should be imported and made available to your code using one or more `--import` options:

      sqlite-transform lambda my.db mytable mycolumn \
          --code='"\n".join(textwrap.wrap(value, 10))' \
          --import=textwrap

  The `--dry-run` option will output a preview of the transformation against the first ten rows, without modifying the database.

  ## Saving the result to a separate column

  Each of these commands accepts optional `--output` and `--output-type` options. These can be used to save the result of the transformation to a separate column, which will be created if the column does not already exist.

  To save the result of `jsonsplit` to a new column called `json_tags`, use the following:

      sqlite-transform jsonsplit my.db mytable tags \
        --output json_tags

  The type of the created column defaults to `text`, but a different column type can be specified using `--output-type`. This example will create a new floating point column called `float_id` with a copy of each item's ID increased by 0.5:

      sqlite-transform lambda my.db mytable id \
        --code 'float(value) + 0.5' \
        --output float_id \
        --output-type float

  You can drop the original column at the end of the operation by adding `--drop`.

  ## Splitting a column into multiple columns

  Sometimes you may wish to convert a single column into multiple derived columns. For example, you may have a `location` column containing `latitude,longitude` values which you wish to split out into separate `latitude` and `longitude` columns.

  You can achieve this using the `--multi` option to `sqlite-transform lambda`. This option expects your `--code` function to return a Python dictionary: new columns well be created and populated for each of the keys in that dictionary.

  For the `latitude,longitude` example you would use the following:

      sqlite-transform lambda demo.db places location \
        --code 'return {
          "latitude": float(value.split(",")[0]),
          "longitude": float(value.split(",")[1]),
        }' --multi

  The type of the returned values will be taken into account when creating the new columns. In this example, the resulting database schema will look like this:

  ```sql
  CREATE TABLE [places] (
      [location] TEXT,
      [latitude] FLOAT,
      [longitude] FLOAT
  );
  ```
  The code function can also return `None`, in which case its output will be ignored.

  You can drop the original column at the end of the operation by adding `--drop`.

  ## Disabling the progress bar

  By default each command will show a progress bar. Pass `-s` or `--silent` to hide that progress bar.


name: sqlite-utils
summary: CLI tool and Python library for manipulating SQLite databases
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/sqlite-utils
license: Apache License, Version 2.0
license_files: ["LICENSE"]
package_url: https://pypi.org/project/sqlite-utils/
project_url: https://pypi.org/project/sqlite-utils/
provides_extra: ["test", "docs", "mypy", "flake8", "tui"]
release_url: https://pypi.org/project/sqlite-utils/3.39/
requires_python: >=3.10
version: 3.39
classifiers:
  ["Development Status :: 5 - Production/Stable", "Intended Audience :: Developers", "Intended Audience :: End Users/Desktop", "Intended Audience :: Science/Research", "Programming Language :: Python :: 3.10", "Programming Language :: Python :: 3.11", "Programming Language :: Python :: 3.12", "Programming Language :: Python :: 3.13", "Programming Language :: Python :: 3.14", "Topic :: Database"]

description:
  # sqlite-utils

  [![PyPI](https://img.shields.io/pypi/v/sqlite-utils.svg)](https://pypi.org/project/sqlite-utils/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/sqlite-utils?include_prereleases&label=changelog)](https://sqlite-utils.datasette.io/en/stable/changelog.html)
  [![Python 3.x](https://img.shields.io/pypi/pyversions/sqlite-utils.svg?logo=python&logoColor=white)](https://pypi.org/project/sqlite-utils/)
  [![Tests](https://github.com/simonw/sqlite-utils/workflows/Test/badge.svg)](https://github.com/simonw/sqlite-utils/actions?query=workflow%3ATest)
  [![Documentation Status](https://readthedocs.org/projects/sqlite-utils/badge/?version=stable)](http://sqlite-utils.datasette.io/en/stable/?badge=stable)
  [![codecov](https://codecov.io/gh/simonw/sqlite-utils/branch/main/graph/badge.svg)](https://codecov.io/gh/simonw/sqlite-utils)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/sqlite-utils/blob/main/LICENSE)
  [![discord](https://img.shields.io/discord/823971286308356157?label=discord)](https://discord.gg/Ass7bCAMDw)

  Python CLI utility and library for manipulating SQLite databases.

  ## Some feature highlights

  - [Pipe JSON](https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data) (or [CSV or TSV](https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-csv-or-tsv-data)) directly into a new SQLite database file, automatically creating a table with the appropriate schema
  - [Run in-memory SQL queries](https://sqlite-utils.datasette.io/en/stable/cli.html#querying-data-directly-using-an-in-memory-database), including joins, directly against data in CSV, TSV or JSON files and view the results
  - [Configure SQLite full-text search](https://sqlite-utils.datasette.io/en/stable/cli.html#configuring-full-text-search) against your database tables and run search queries against them, ordered by relevance
  - Run [transformations against your tables](https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables) to make schema changes that SQLite `ALTER TABLE` does not directly support, such as changing the type of a column
  - [Extract columns](https://sqlite-utils.datasette.io/en/stable/cli.html#extracting-columns-into-a-separate-table) into separate tables to better normalize your existing data
  - [Install plugins](https://sqlite-utils.datasette.io/en/stable/plugins.html) to add custom SQL functions and additional features

  Read more on my blog, in this series of posts on [New features in sqlite-utils](https://simonwillison.net/series/sqlite-utils-features/) and other [entries tagged sqliteutils](https://simonwillison.net/tags/sqliteutils/).

  ## Installation

      pip install sqlite-utils

  Or if you use [Homebrew](https://brew.sh/) for macOS:

      brew install sqlite-utils

  ## Using as a CLI tool

  Now you can do things with the CLI utility like this:

      $ sqlite-utils memory dogs.csv "select * from t"
      [{"id": 1, "age": 4, "name": "Cleo"},
       {"id": 2, "age": 2, "name": "Pancakes"}]

      $ sqlite-utils insert dogs.db dogs dogs.csv --csv
      [####################################]  100%

      $ sqlite-utils tables dogs.db --counts
      [{"table": "dogs", "count": 2}]

      $ sqlite-utils dogs.db "select id, name from dogs"
      [{"id": 1, "name": "Cleo"},
       {"id": 2, "name": "Pancakes"}]

      $ sqlite-utils dogs.db "select * from dogs" --csv
      id,age,name
      1,4,Cleo
      2,2,Pancakes

      $ sqlite-utils dogs.db "select * from dogs" --table
        id    age  name
      ----  -----  --------
         1      4  Cleo
         2      2  Pancakes

  You can import JSON data into a new database table like this:

      $ curl https://api.github.com/repos/simonw/sqlite-utils/releases \
          | sqlite-utils insert releases.db releases - --pk id

  Or for data in a CSV file:

      $ sqlite-utils insert dogs.db dogs dogs.csv --csv

  `sqlite-utils memory` lets you import CSV or JSON data into an in-memory database and run SQL queries against it in a single command:

      $ cat dogs.csv | sqlite-utils memory - "select name, age from stdin"

  See the [full CLI documentation](https://sqlite-utils.datasette.io/en/stable/cli.html) for comprehensive coverage of many more commands.

  ## Using as a library

  You can also `import sqlite_utils` and use it as a Python library like this:

  ```python
  import sqlite_utils
  db = sqlite_utils.Database("demo_database.db")
  # This line creates a "dogs" table if one does not already exist:
  db["dogs"].insert_all([
      {"id": 1, "age": 4, "name": "Cleo"},
      {"id": 2, "age": 2, "name": "Pancakes"}
  ], pk="id")
  ```

  Check out the [full library documentation](https://sqlite-utils.datasette.io/en/stable/python-api.html) for everything else you can do with the Python library.

  ## Related projects

  * [Datasette](https://datasette.io/): A tool for exploring and publishing data
  * [csvs-to-sqlite](https://github.com/simonw/csvs-to-sqlite): Convert CSV files into a SQLite database
  * [db-to-sqlite](https://github.com/simonw/db-to-sqlite): CLI tool for exporting a MySQL or PostgreSQL database as a SQLite file
  * [dogsheep](https://dogsheep.github.io/): A family of tools for personal analytics, built on top of `sqlite-utils`

dynamic:
  ["Author", "Classifier", "Description", "Description-Content-Type", "Home-Page", "License", "License-File", "Project-Url", "Provides-Extra", "Requires-Dist", "Requires-Python", "Summary"]

project_urls:
  {"CI": "https://github.com/simonw/sqlite-utils/actions", "Changelog": "https://sqlite-utils.datasette.io/en/stable/changelog.html", "Documentation": "https://sqlite-utils.datasette.io/en/stable/", "Homepage": "https://github.com/simonw/sqlite-utils", "Issues": "https://github.com/simonw/sqlite-utils/issues", "Source code": "https://github.com/simonw/sqlite-utils"}

requires_dist:
  ["sqlite-fts4", "click>=8.3.1", "click-default-group>=1.2.3", "tabulate", "python-dateutil", "pluggy", "pip", "pytest; extra == \"test\"", "black>=24.1.1; extra == \"test\"", "hypothesis; extra == \"test\"", "cogapp; extra == \"test\"", "furo; extra == \"docs\"", "sphinx-autobuild; extra == \"docs\"", "codespell; extra == \"docs\"", "sphinx-copybutton; extra == \"docs\"", "beanbag-docutils>=2.0; extra == \"docs\"", "pygments-csv-lexer; extra == \"docs\"", "mypy; extra == \"mypy\"", "types-click; extra == \"mypy\"", "types-tabulate; extra == \"mypy\"", "types-python-dateutil; extra == \"mypy\"", "types-pluggy; extra == \"mypy\"", "data-science-types; extra == \"mypy\"", "flake8; extra == \"flake8\"", "trogon; extra == \"tui\""]


name: strip-tags
summary: Strip tags from HTML, optionally from areas identified by CSS selectors
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/strip-tags
license: Apache License, Version 2.0
package_url: https://pypi.org/project/strip-tags/
project_url: https://pypi.org/project/strip-tags/
provides_extra: ["test"]
release_url: https://pypi.org/project/strip-tags/0.6/
requires_python: >=3.7
version: 0.6
description:
  # strip-tags

  [![PyPI](https://img.shields.io/pypi/v/strip-tags.svg)](https://pypi.org/project/strip-tags/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/strip-tags?include_prereleases&label=changelog)](https://github.com/simonw/strip-tags/releases)
  [![Tests](https://github.com/simonw/strip-tags/workflows/Test/badge.svg)](https://github.com/simonw/strip-tags/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/strip-tags/blob/master/LICENSE)

  Strip tags from HTML, optionally from areas identified by CSS selectors

  See [llm, ttok and strip-tags—CLI tools for working with ChatGPT and other LLMs](https://simonwillison.net/2023/May/18/cli-tools-for-llms/) for more on this project.

  ## Installation

  Install this tool using `pip`:
  ```bash
  pip install strip-tags
  ```
  ## Usage

  Pipe content into this tool to strip tags from it:
  ```bash
  cat input.html | strip-tags > output.txt
  ````
  Or pass a filename:
  ```bash
  strip-tags -i input.html > output.txt
  ```
  To run against just specific areas identified by CSS selectors:
  ```bash
  strip-tags '.content' -i input.html > output.txt
  ```
  This can be called with multiple selectors:
  ```bash
  cat input.html | strip-tags '.content' '.sidebar' > output.txt
  ```
  To return just the first element on the page that matches one of the selectors, use `--first`:
  ```bash
  cat input.html | strip-tags .content --first > output.txt
  ```
  To remove content contained by specific selectors - e.g. the `<nav>` section of a page, use `-r` or `--remove`:
  ```bash
  cat input.html | strip-tags -r nav > output.txt
  ```
  To minify whitespace - reducing multiple space and tab characters to a single space, removing any remaining blank lines - add `-m` or `--minify`:
  ```bash
  cat input.html | strip-tags -m > output.txt
  ```
  You can also run this command using `python -m` like this:
  ```bash
  python -m strip_tags --help
  ```
  ### Keeping the markup for specified tags

  When passing content to a language model, it can sometimes be useful to leave in a subset of HTML tags - `<h1>This is the heading</h1>` for example - to provide extra hints to the model.

  The `-t/--keep-tag` option can be passed multiple times to specify tags that should be kept.

  This example looks at the `<header>` section of https://datasette.io/ and keeps the tags around the list items and `<h1>` elements:

  ```
  curl -s https://datasette.io/ | strip-tags header -t h1 -t li
  ```
  ```html
  <li>Uses</li>
  <li>Documentation Docs</li>
  <li>Tutorials</li>
  <li>Examples</li>
  <li>Plugins</li>
  <li>Tools</li>
  <li>News</li>
  <h1>
      Datasette
  </h1>
  Find stories in data
  ```
  All attributes will be removed from the tags, except for the `id=` and `class=` attribute since those may provide further useful hints to the language model.

  The `href` attribute on links, the `alt` attribute on images and the `name` and `value` attributes on `meta` tags are kept as well.

  You can also specify a bundle of tags. For example, `strip-tags -t hs` will keep the tag markup for all levels of headings.

  The following bundles can be used:

  <!-- [[[cog
  import cog
  from strip_tags.lib import BUNDLES
  lines = []
  for name, tags in BUNDLES.items():
      lines.append("- `-t {}`: {}".format(name, ", ".join("`<{}>`".format(tag) for tag in tags)))
  cog.out("\n".join(lines))
  ]]] -->
  - `-t hs`: `<h1>`, `<h2>`, `<h3>`, `<h4>`, `<h5>`, `<h6>`
  - `-t metadata`: `<title>`, `<meta>`
  - `-t structure`: `<header>`, `<nav>`, `<main>`, `<article>`, `<section>`, `<aside>`, `<footer>`
  - `-t tables`: `<table>`, `<tr>`, `<td>`, `<th>`, `<thead>`, `<tbody>`, `<tfoot>`, `<caption>`, `<colgroup>`, `<col>`
  - `-t lists`: `<ul>`, `<ol>`, `<li>`, `<dl>`, `<dd>`, `<dt>`
  <!-- [[[end]]] -->

  ## As a Python library

  You can use `strip-tags` from Python code too. The function signature looks like this:

  <!-- [[[cog
  import ast
  module = ast.parse(open("strip_tags/lib.py").read())
  strip_tags = [
      fn for fn in module.body
      if getattr(fn, 'name', None) == 'strip_tags'
  ][0]
  code = ast.unparse(strip_tags)
  defline = code.split("\n")[0]
  code = (
      ',\n    '.join(defline.split(', ')).replace(") ->", "\n) ->").replace("strip_tags(", "strip_tags(\n    ")
  )
  cog.out("```python\n{}\n```".format(code))
  ]]] -->
  ```python
  def strip_tags(
      input: str,
      selectors: Optional[Iterable[str]]=None,
      *,
      removes: Optional[Iterable[str]]=None,
      minify: bool=False,
      remove_blank_lines: bool=False,
      first: bool=False,
      keep_tags: Optional[Iterable[str]]=None,
      all_attrs: bool=False
  ) -> str:
  ```
  <!-- [[[end]]] -->

  Here's an example:
  ```python
  from strip_tags import strip_tags

  html = """
  <div>
  <h1>This has tags</h1>

  <p>And whitespace too</p>
  </div>
  Ignore this bit.
  """
  stripped = strip_tags(html, ["div"], minify=True, keep_tags=["h1"])
  print(stripped)
  ```
  Output:
  ```
  <h1>This has tags</h1>

  And whitespace too
  ```
  Use `remove_blank_lines=True` to remove any remaining blank lines from the output.

  ## strip-tags --help

  <!-- [[[cog
  import cog
  from strip_tags import cli
  from click.testing import CliRunner
  runner = CliRunner()
  result = runner.invoke(cli.cli, ["--help"])
  help = result.output.replace("Usage: cli", "Usage: strip-tags")
  cog.out(
      "```\n{}\n```".format(help)
  )
  ]]] -->
  ```
  Usage: strip-tags [OPTIONS] [SELECTORS]...

    Strip tags from HTML, optionally from areas identified by CSS selectors

    Example usage:

        cat input.html | strip-tags > output.txt

    To run against just specific areas identified by CSS selectors:

        cat input.html | strip-tags .entry .footer > output.txt

  Options:
    --version             Show the version and exit.
    -r, --remove TEXT     Remove content in these selectors
    -i, --input FILENAME  Input file
    -m, --minify          Minify whitespace
    -t, --keep-tag TEXT   Keep these <tags>
    --all-attrs           Include all attributes on kept tags
    --first               First element matching the selectors
    --help                Show this message and exit.

  ```
  <!-- [[[end]]] -->

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:
  ```bash
  cd strip-tags
  python -m venv venv
  source venv/bin/activate
  ```
  Now install the dependencies and test dependencies:
  ```bash
  pip install -e '.[test]'
  ```
  To run the tests:
  ```bash
  pytest
  ```

dynamic:
  ["Author", "Description", "Description-Content-Type", "Home-Page", "License", "Project-Url", "Provides-Extra", "Requires-Dist", "Requires-Python", "Summary"]

project_urls:
  {"CI": "https://github.com/simonw/strip-tags/actions", "Changelog": "https://github.com/simonw/strip-tags/releases", "Homepage": "https://github.com/simonw/strip-tags", "Issues": "https://github.com/simonw/strip-tags/issues"}

requires_dist:
  ["click", "beautifulsoup4", "html5lib", "pytest; extra == \"test\"", "PyYAML; extra == \"test\"", "cogapp; extra == \"test\""]


name: swarm-to-sqlite
summary: Create a SQLite database containing your checkin history from Foursquare Swarm
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/dogsheep/swarm-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/swarm-to-sqlite/
project_url: https://pypi.org/project/swarm-to-sqlite/
release_url: https://pypi.org/project/swarm-to-sqlite/0.3.4/
requires_dist: ["sqlite-utils (>=3.3)", "click", "requests", "pytest ; extra == 'test'"]
version: 0.3.4
description:
  # swarm-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/swarm-to-sqlite.svg)](https://pypi.org/project/swarm-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/swarm-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/swarm-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/swarm-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/swarm-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/swarm-to-sqlite/blob/main/LICENSE)

  Create a SQLite database containing your checkin history from Foursquare Swarm.

  ## How to install

      $ pip install swarm-to-sqlite

  ## Usage

  You will need to first obtain a valid OAuth token for your Foursquare account. You can do so using this tool: https://your-foursquare-oauth-token.glitch.me/

  Simplest usage is to simply provide the name of the database file you wish to write to. The tool will prompt you to paste in your token, and will then download your checkins and store them in the specified database file.

      $ swarm-to-sqlite checkins.db
      Please provide your Foursquare OAuth token:
      Importing 3699 checkins  [#########-----------------------] 27% 00:02:31

  You can also pass the token as a command-line option:

      $ swarm-to-sqlite checkins.db --token=XXX

  Or as an environment variable:

      $ export FOURSQUARE_TOKEN=XXX
      $ swarm-to-sqlite checkins.db

  To retrieve just checkins within the past X hours, days or weeks, use the `--since=` option. For example, to pull only checkins that happened within the last 10 days use:

      $ swarm-to-sqlite checkins.db --token=XXX --since=10d

  Use `2w` for two weeks, `10h` for ten hours, `3d` for three days.

  In addition to saving the checkins to a database, you can also write them to a JSON file using the `--save` option:

      $ swarm-to-sqlite checkins.db --save=checkins.json

  Having done this, you can re-import checkins directly from that file (rather than making API calls to fetch data from Foursquare) like this:

      $ swarm-to-sqlite checkins.db --load=checkins.json

  ## Using with Datasette

  The SQLite database produced by this tool is designed to be browsed using [Datasette](https://datasette.io/).

  You can install the [datasette-cluster-map](https://datasette.io/plugins/datasette-cluster-map) plugin to view your checkins on a map.

project_urls:
  {"CI": "https://github.com/dogsheep/swarm-to-sqlite/actions", "Changelog": "https://github.com/dogsheep/swarm-to-sqlite/releases", "Homepage": "https://github.com/dogsheep/swarm-to-sqlite", "Issues": "https://github.com/dogsheep/swarm-to-sqlite/issues"}


name: tableau-to-sqlite
summary: Fetch data from Tableau into a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/tableau-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/tableau-to-sqlite/
project_url: https://pypi.org/project/tableau-to-sqlite/
release_url: https://pypi.org/project/tableau-to-sqlite/0.2.1/
requires_dist: ["click", "TableauScraper (==0.1.2)", "pytest ; extra == 'test'", "vcrpy ; extra == 'test'"]
requires_python: >=3.6
version: 0.2.1
description:
  # tableau-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/tableau-to-sqlite.svg)](https://pypi.org/project/tableau-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/tableau-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/tableau-to-sqlite/releases)
  [![Tests](https://github.com/simonw/tableau-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/tableau-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/tableau-to-sqlite/blob/master/LICENSE)

  Fetch data from Tableau into a SQLite database. A wrapper around [TableauScraper](https://github.com/bertrandmartel/tableau-scraping/).

  ## Installation

  Install this tool using `pip`:

      $ pip install tableau-to-sqlite

  ## Usage

  If you have the URL to a Tableau dashboard like this:

  https://results.mo.gov/t/COVID19/views/VaccinationsDashboard/Vaccinations

  You can pass that directly to the tool:

      tableau-to-sqlite tableau.db \
        https://results.mo.gov/t/COVID19/views/VaccinationsDashboard/Vaccinations

  This will create a SQLite database called `tableau.db` containing one table for each of the worksheets in that dashboard.

  If the dashboard is hosted on https://public.tableau.com/ you can instead provide the view name. This will be two strings separated by a `/` symbol - something like this:

      OregonCOVID-19VaccineProviderEnrollment/COVID-19VaccineProviderEnrollment

  Now run the tool like this:

      tableau-to-sqlite tableau.db \
          OregonCOVID-19VaccineProviderEnrollment/COVID-19VaccineProviderEnrollment

  ## Get the data as JSON or CSV

  If you're building a [git scraper](https://simonwillison.net/2020/Oct/9/git-scraping/) you may want to convert the data gathered by this tool to CSV or JSON to check into your repository.

  You can do that using [sqlite-utils](https://sqlite-utils.datasette.io/). Install it using `pip`:

      pip install sqlite-utils

  You can dump out a table as JSON like so:

      sqlite-utils rows tableau.db \
         'Admin Site and County Map Site No Info' > tableau.json

  Or as CSV like this:

      sqlite-utils rows tableau.db --csv \
         'Admin Site and County Map Site No Info' > tableau.csv

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

      cd tableau-to-sqlite
      python -mvenv venv
      source venv/bin/activate

  Or if you are using `pipenv`:

      pipenv shell

  Now install the dependencies and tests:

      pip install -e '.[test]'

  To run the tests:

      pytest

project_urls:
  {"CI": "https://github.com/simonw/tableau-to-sqlite/actions", "Changelog": "https://github.com/simonw/tableau-to-sqlite/releases", "Homepage": "https://github.com/simonw/tableau-to-sqlite", "Issues": "https://github.com/simonw/tableau-to-sqlite/issues"}


name: ttok
summary: Count and truncate text based on tokens
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/ttok
license: Apache License, Version 2.0
package_url: https://pypi.org/project/ttok/
project_url: https://pypi.org/project/ttok/
release_url: https://pypi.org/project/ttok/0.3/
requires_dist: ["click", "tiktoken", "pytest; extra == \"test\"", "cogapp; extra == \"test\""]
requires_python: >=3.8
version: 0.3
description:
  # ttok

  [![PyPI](https://img.shields.io/pypi/v/ttok.svg)](https://pypi.org/project/ttok/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/ttok?include_prereleases&label=changelog)](https://github.com/simonw/ttok/releases)
  [![Tests](https://github.com/simonw/ttok/workflows/Test/badge.svg)](https://github.com/simonw/ttok/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/ttok/blob/master/LICENSE)

  Count and truncate text based on tokens

  ## Background

  Large language models such as GPT-3.5 and GPT-4 work in terms of tokens.

  This tool can count tokens, using OpenAI's [tiktoken](https://github.com/openai/tiktoken) library.

  It can also truncate text to a specified number of tokens.

  See [llm, ttok and strip-tags—CLI tools for working with ChatGPT and other LLMs](https://simonwillison.net/2023/May/18/cli-tools-for-llms/) for more on this project.

  ## Installation

  Install this tool using `pip`:
  ```bash
  pip install ttok
  ```
  Or using Homebrew:
  ```bash
  brew install simonw/llm/ttok
  ```

  ## Counting tokens

  Provide text as arguments to this tool to count tokens:

  ```bash
  ttok Hello world
  ```
  ```
  2
  ```
  You can also pipe text into the tool:
  ```bash
  echo -n "Hello world" | ttok
  ```
  ```
  2
  ```
  Here the `echo -n` option prevents echo from adding a newline - without that you would get a token count of 3.

  To pipe in text and then append extra tokens from arguments, use the `-i -` option:

  ```bash
  echo -n "Hello world" | ttok more text -i -
  ```
  ```
  6
  ```
  ## Different models

  By default, the tokenizer model for GPT-3.5 and GPT-4 is used.

  To use the model for GPT-2 and GPT-3, add `--model gpt2`:

  ```bash
  ttok boo Hello there this is -m gpt2
  ```
  ```
  6
  ```
  Compared to GPT-3.5:
  ```bash
  ttok boo Hello there this is
  ```
  ```
  5
  ```
  Further model options are [documented here](https://github.com/openai/openai-cookbook/blob/main/examples/How_to_count_tokens_with_tiktoken.ipynb).

  ## Truncating text

  Use the `-t 10` or `--truncate 10` option to truncate text to a specified number of tokens:

  ```bash
  ttok This is too many tokens -t 3
  ```
  ```
  This is too
  ```

  ## Viewing tokens

  The `--encode` option can be used to view the integer token IDs for the incoming text:

  ```bash
  ttok Hello world --encode
  ```
  ```
  9906 1917
  ```
  The `--decode` method reverses this process:

  ```bash
  ttok 9906 1917 --decode
  ```
  ```
  Hello world
  ```
  Add `--tokens` to either of these options to see a detailed breakdown of the tokens:

  ```bash
  ttok Hello world --encode --tokens
  ```
  ```
  [b'Hello', b' world']
  ```

  ## Available models

  This is the full list of available models and their corresponding encodings. Model names and encoding names are valid for the `-m/--model` option.

  <!-- [[[cog
  import cog
  import tiktoken
  output = []
  for key, value in tiktoken.model.MODEL_TO_ENCODING.items():
      output.append("- `{}` (`{}`)".format(key, value))
  cog.out("\n".join(output))
  ]]] -->
  - `gpt-4` (`cl100k_base`)
  - `gpt-3.5-turbo` (`cl100k_base`)
  - `gpt-3.5` (`cl100k_base`)
  - `gpt-35-turbo` (`cl100k_base`)
  - `davinci-002` (`cl100k_base`)
  - `babbage-002` (`cl100k_base`)
  - `text-embedding-ada-002` (`cl100k_base`)
  - `text-embedding-3-small` (`cl100k_base`)
  - `text-embedding-3-large` (`cl100k_base`)
  - `text-davinci-003` (`p50k_base`)
  - `text-davinci-002` (`p50k_base`)
  - `text-davinci-001` (`r50k_base`)
  - `text-curie-001` (`r50k_base`)
  - `text-babbage-001` (`r50k_base`)
  - `text-ada-001` (`r50k_base`)
  - `davinci` (`r50k_base`)
  - `curie` (`r50k_base`)
  - `babbage` (`r50k_base`)
  - `ada` (`r50k_base`)
  - `code-davinci-002` (`p50k_base`)
  - `code-davinci-001` (`p50k_base`)
  - `code-cushman-002` (`p50k_base`)
  - `code-cushman-001` (`p50k_base`)
  - `davinci-codex` (`p50k_base`)
  - `cushman-codex` (`p50k_base`)
  - `text-davinci-edit-001` (`p50k_edit`)
  - `code-davinci-edit-001` (`p50k_edit`)
  - `text-similarity-davinci-001` (`r50k_base`)
  - `text-similarity-curie-001` (`r50k_base`)
  - `text-similarity-babbage-001` (`r50k_base`)
  - `text-similarity-ada-001` (`r50k_base`)
  - `text-search-davinci-doc-001` (`r50k_base`)
  - `text-search-curie-doc-001` (`r50k_base`)
  - `text-search-babbage-doc-001` (`r50k_base`)
  - `text-search-ada-doc-001` (`r50k_base`)
  - `code-search-babbage-code-001` (`r50k_base`)
  - `code-search-ada-code-001` (`r50k_base`)
  - `gpt2` (`gpt2`)
  - `gpt-2` (`gpt2`)
  <!-- [[[end]]] -->

  ## ttok --help

  <!-- [[[cog
  from ttok import cli
  from click.testing import CliRunner
  runner = CliRunner()
  result = runner.invoke(cli.cli, ["--help"])
  help = result.output.replace("Usage: cli", "Usage: ttok")
  cog.out(
      "```\n{}\n```".format(help)
  )
  ]]] -->
  ```
  Usage: ttok [OPTIONS] [PROMPT]...

    Count and truncate text based on tokens

    To count tokens for text passed as arguments:

        ttok one two three

    To count tokens from stdin:

        cat input.txt | ttok

    To truncate to 100 tokens:

        cat input.txt | ttok -t 100

    To truncate to 100 tokens using the gpt2 model:

        cat input.txt | ttok -t 100 -m gpt2

    To view token integers:

        cat input.txt | ttok --encode

    To convert tokens back to text:

        ttok 9906 1917 --decode

    To see the details of the tokens:

        ttok "hello world" --tokens

    Outputs:

        [b'hello', b' world']

  Options:
    --version               Show the version and exit.
    -i, --input FILENAME
    -t, --truncate INTEGER  Truncate to this many tokens
    -m, --model TEXT        Which model to use
    --encode, --tokens      Output token integers
    --decode                Convert token integers to text
    --tokens                Output full tokens
    --allow-special         Do not error on special tokens
    --help                  Show this message and exit.

  ```
  <!-- [[[end]]] -->

  You can also run this command using:

  ```bash
  python -m ttok --help
  ```

  ## Development

  To contribute to this tool, first checkout the code. Then create a new virtual environment:

  ```bash
  cd ttok
  python -m venv venv
  source venv/bin/activate
  ```

  Now install the dependencies and test dependencies:

  ```bash
  pip install -e '.[test]'
  ```

  To run the tests:

  ```bash
  pytest
  ```

project_urls:
  {"CI": "https://github.com/simonw/ttok/actions", "Changelog": "https://github.com/simonw/ttok/releases", "Homepage": "https://github.com/simonw/ttok", "Issues": "https://github.com/simonw/ttok/issues"}


name: twitter-to-sqlite
summary: Save data from Twitter to a SQLite database
classifiers: []
author: Simon Willison
description_content_type: text/markdown
home_page: https://datasette.io/tools/twitter-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/twitter-to-sqlite/
project_url: https://pypi.org/project/twitter-to-sqlite/
release_url: https://pypi.org/project/twitter-to-sqlite/0.22/
version: 0.22
description:
  # twitter-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/twitter-to-sqlite.svg)](https://pypi.org/project/twitter-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/dogsheep/twitter-to-sqlite?include_prereleases&label=changelog)](https://github.com/dogsheep/twitter-to-sqlite/releases)
  [![Tests](https://github.com/dogsheep/twitter-to-sqlite/workflows/Test/badge.svg)](https://github.com/dogsheep/twitter-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/dogsheep/twitter-to-sqlite/blob/main/LICENSE)

  Save data from Twitter to a SQLite database.

  <!-- toc -->

  - [How to install](#how-to-install)
  - [Authentication](#authentication)
  - [Retrieving tweets by specific accounts](#retrieving-tweets-by-specific-accounts)
  - [Retrieve user profiles in bulk](#retrieve-user-profiles-in-bulk)
  - [Retrieve tweets in bulk](#retrieve-tweets-in-bulk)
  - [Retrieving Twitter followers](#retrieving-twitter-followers)
  - [Retrieving friends](#retrieving-friends)
  - [Retrieving favorited tweets](#retrieving-favorited-tweets)
  - [Retrieving Twitter lists](#retrieving-twitter-lists)
  - [Retrieving Twitter list memberships](#retrieving-twitter-list-memberships)
  - [Retrieving just follower and friend IDs](#retrieving-just-follower-and-friend-ids)
  - [Retrieving tweets from your home timeline](#retrieving-tweets-from-your-home-timeline)
  - [Retrieving your mentions](#retrieving-your-mentions)
  - [Providing input from a SQL query with --sql and --attach](#providing-input-from-a-sql-query-with---sql-and---attach)
  - [Running searches](#running-searches)
  - [Capturing tweets in real-time with track and follow](#capturing-tweets-in-real-time-with-track-and-follow)
    * [track](#track)
    * [follow](#follow)
  - [Importing data from your Twitter archive](#importing-data-from-your-twitter-archive)
  - [Design notes](#design-notes)

  <!-- tocstop -->

  ## How to install

      $ pip install twitter-to-sqlite

  ## Authentication

  First, you will need to create a Twitter application at https://developer.twitter.com/en/apps. You may need to apply for a Twitter developer account - if so, you may find this [example of an email application](https://raw.githubusercontent.com/dogsheep/twitter-to-sqlite/main/email.png) useful that has been approved in the past.

  Once you have created your application, navigate to the "Keys and tokens" page and make note of the following:

  * Your API key
  * Your API secret key
  * Your access token
  * Your access token secret

  You will need to save all four of these values to a JSON file in order to use this tool.

  You can create that JSON file by running the following command and pasting in the values at the prompts:

      $ twitter-to-sqlite auth
      Create an app here: https://developer.twitter.com/en/apps
      Then navigate to 'Keys and tokens' and paste in the following:

      API key: xxx
      API secret key: xxx
      Access token: xxx
      Access token secret: xxx

  This will create a file called `auth.json` in your current directory containing the required values. To save the file at a different path or filename, use the `--auth=myauth.json` option.

  ## Retrieving tweets by specific accounts

  The `user-timeline` command retrieves all of the tweets posted by the specified user accounts. It defaults to the account belonging to the authenticated user:

      $ twitter-to-sqlite user-timeline twitter.db
      Importing tweets  [#####-------------------------------]  2799/17780  00:01:39

  All of these commands assume that there is an `auth.json` file in the current directory. You can provide the path to your `auth.json` file using `-a`:

      $ twitter-to-sqlite user-timeline twitter.db -a /path/to/auth.json

  To load tweets for other users, pass their screen names as arguments:

      $ twitter-to-sqlite user-timeline twitter.db cleopaws nichemuseums

  Twitter's API only returns up to around 3,200 tweets for most user accounts, but you may find that it returns all available tweets for your own user account.

  You can pass numeric Twitter user IDs instead of screen names using the `--ids` parameter.

  You can use `--since` to retrieve every tweet since the last time you imported for that user, or `--since_id=xxx` to retrieve every tweet since a specific tweet ID.

  This command also accepts `--sql` and `--attach` options, documented below.

  ## Retrieve user profiles in bulk

  If you have a list of Twitter screen names (or user IDs) you can bulk fetch their fully inflated Twitter profiles using the `users-lookup` command:

      $ twitter-to-sqlite users-lookup users.db simonw cleopaws

  You can pass user IDs instead using the `--ids` option:

      $ twitter-to-sqlite users-lookup users.db 12497 3166449535 --ids

  This command also accepts `--sql` and `--attach` options, documented below.

  ## Retrieve tweets in bulk

  If you have a list of tweet IDS you can bulk fetch them using the `statuses-lookup` command:

      $ twitter-to-sqlite statuses-lookup tweets.db 1122154819815239680 1122154178493575169

  The `--sql` and `--attach` options are supported.

  Here's a recipe to retrieve any tweets that existing tweets are in-reply-to which have not yet been stored in your database:

      $ twitter-to-sqlite statuses-lookup tweets.db \
          --sql='
              select in_reply_to_status_id
              from tweets
              where in_reply_to_status_id is not null' \
          --skip-existing

  The `--skip-existing` option means that tweets that have already been stored in the database will not be fetched again.

  ## Retrieving Twitter followers

  The `followers` command retrieves details of every follower of the specified accounts. You can use it to retrieve your own followers, or you can pass one or more screen names to pull the followers for other accounts.

  The following command pulls your followers and saves them in a SQLite database file called `twitter.db`:

      $ twitter-to-sqlite followers twitter.db

  This command is **extremely slow**, because Twitter impose a rate limit of no more than one request per minute to this endpoint! If you are running it against an account with thousands of followers you should expect this to take several hours.

  To retrieve followers for another account, use:

      $ twitter-to-sqlite followers twitter.db cleopaws

  This command also accepts the `--ids`, `--sql` and `--attach` options.

  See [Analyzing my Twitter followers with Datasette](https://simonwillison.net/2018/Jan/28/analyzing-my-twitter-followers/) for the original inspiration for this command.

  ## Retrieving friends

  The `friends` command works like the `followers` command, but retrieves the specified (or currently authenticated) user's friends - defined as accounts that the user is following.

      $ twitter-to-sqlite friends twitter.db

  It takes the same options as the `followers` command.

  ## Retrieving favorited tweets

  The `favorites` command retrieves tweets that have been favorited by a specified user. Called without any extra arguments it retrieves tweets favorited by the currently authenticated user:

      $ twitter-to-sqlite favorites faves.db

  You can also use the `--screen_name` or `--user_id` arguments to retrieve favorite tweets for another user:

      $ twitter-to-sqlite favorites faves-obama.db --screen_name=BarackObama

  Use the `--stop_after=xxx` argument to retrieve only the most recent number of favorites, e.g. to get the authenticated user's 50 most recent favorites:

      $ twitter-to-sqlite favorites faves.db --stop_after=50

  ## Retrieving Twitter lists

  The `lists` command retrieves all of the lists belonging to one or more users.

      $ twitter-to-sqlite lists lists.db simonw dogsheep

  This command also accepts the `--sql` and `--attach` and `--ids` options.

  To additionally fetch the list of members for each list, use `--members`.

  ## Retrieving Twitter list memberships

  The `list-members` command can be used to retrieve details of one or more Twitter lists, including all of their members.

      $ twitter-to-sqlite list-members members.db simonw/the-good-place

  You can pass multiple `screen_name/list_slug` identifiers.

  If you know the numeric IDs of the lists instead, you can use `--ids`:

      $ twitter-to-sqlite list-members members.db 927913322841653248 --ids

  ## Retrieving just follower and friend IDs

  It's also possible to retrieve just the numeric Twitter IDs of the accounts that specific users are following ("friends" in Twitter's API terminology) or followed-by:

      $ twitter-to-sqlite followers-ids members.db simonw cleopaws

  This will populate the `following` table with `followed_id`/`follower_id` pairs for the two specified accounts, listing every account ID that is following either of those two accounts.

      $ twitter-to-sqlite friends-ids members.db simonw cleopaws

  This will do the same thing but pull the IDs that those accounts are following.

  Both of these commands also support `--sql` and `--attach` as an alternative to passing screen names as direct command-line arguments. You can use `--ids` to process the inputs as user IDs rather than screen names.

  The underlying Twitter APIs have a rate limit of 15 requests every 15 minutes - though they do return up to 5,000 IDs in each call. By default both of these subcommands will wait for 61 seconds between API calls in order to stay within the rate limit - you can adjust this behaviour down to just one second delay if you know you will not be making many calls using `--sleep=1`.

  ## Retrieving tweets from your home timeline

  The `home-timeline` command retrieves up to 800 tweets from the home timeline of the authenticated user - generally this means tweets from people you follow.

      $ twitter-to-sqlite home-timeline twitter.db
      Importing timeline  [#################--------]  591/800  00:01:14

  The tweets are stored in the `tweets` table, and a record is added to the `timeline_tweets` table noting that this tweet came in due to being spotted in the timeline of your user.

  You can use `--since` to retrieve just tweets that have been posted since the last time this command was run, or `--since_id=xxx` to explicitly pass in a tweet ID to use as the last position.

  You can then view your timeline in Datasette using the following URL:

  `/tweets/tweets?_where=id+in+(select+tweet+from+[timeline_tweets])&_sort_desc=id&_facet=user`

  This will filter your tweets table to just tweets that appear in your timeline, ordered by most recent first and use faceting to show you which users are responsible for the most tweets.

  ## Retrieving your mentions

  The `mentions-timeline` command works like `home-timeline` except it retrieves tweets that mention the authenticated user's account. It records the user account that was mentioned in a `mentions_tweets` table.

  It supports `--since` and `--since_id` in the same was as `home-timeline` does.

  ## Providing input from a SQL query with --sql and --attach

  This option is available for some subcommands - run `twitter-to-sqlite command-name --help` to check.

  You can provide Twitter screen names (or user IDs or tweet IDs) directly as command-line arguments, or you can provide those screen names or IDs by executing a SQL query.

  For example: consider a SQLite database with an `attendees` table listing names and Twitter accounts - something like this:

  | First   | Last       | Twitter      |
  |---------|------------|--------------|
  | Simon   | Willison   | simonw       |
  | Avril   | Lavigne    | AvrilLavigne |

  You can run the `users-lookup` command to pull the Twitter profile of every user listed in that database by loading the screen names using a `--sql` query:

      $ twitter-to-sqlite users-lookup my.db --sql="select Twitter from attendees"

  If your database table contains Twitter IDs, you can select those IDs and pass the `--ids` argument. For example, to fetch the profiles of users who have had their user IDs inserted into the `following` table using the `twitter-to-sqlite friends-ids` command:

      $ twitter-to-sqlite users-lookup my.db --sql="select follower_id from following" --ids

  Or to avoid re-fetching users that have already been fetched:

      $ twitter-to-sqlite users-lookup my.db \
          --sql="select followed_id from following where followed_id not in (
              select id from users)" --ids

  If your data lives in a separate database file you can attach it using `--attach`. For example, consider the attendees example above but the data lives in an `attendees.db` file, and you want to fetch the user profiles into a `tweets.db` file. You could do that like this:

      $ twitter-to-sqlite users-lookup tweets.db \
          --attach=attendees.db \
          --sql="select Twitter from attendees.attendees"

  The filename (without the extension) will be used as the database alias within SQLite. If you want a different alias for some reason you can specify that with a colon like this:

      $ twitter-to-sqlite users-lookup tweets.db \
          --attach=foo:attendees.db \
          --sql="select Twitter from foo.attendees"

  ## Running searches

  The `search` command runs a search against the Twitter [standard search API](https://developer.twitter.com/en/docs/tweets/search/api-reference/get-search-tweets).

      $ twitter-to-sqlite search tweets.db "dogsheep"

  This will import up to around 320 tweets that match that search term into the `tweets` table. It will also create a record in the `search_runs` table recording that the search took place, and many-to-many records in the `search_runs_tweets` table recording which tweets were seen for that search at that time.

  You can use the `--since` parameter to check for previous search runs with the same arguments and only retrieve tweets that were posted since the last retrieved matching tweet.

  The following additional options for `search` are supported:

  * `--geocode`: `latitude,longitude,radius` where radius is a number followed by mi or km
  * `--lang`: ISO 639-1 language code e.g. `en` or `es`
  * `--locale`: Locale: only `ja` is currently effective
  * `--result_type`: `mixed`, `recent` or `popular`. Defaults to `mixed`
  * `--count`: Number of results per page, defaults to the maximum of 100
  * `--stop_after`: Stop after this many results
  * `--since_id`: Pull tweets since this Tweet ID. You probably want to use `--since` instead of this.

  ## Capturing tweets in real-time with track and follow

  This functionality is **experimental**. Please [file bug reports](https://github.com/dogsheep/twitter-to-sqlite/issues) if you find any!

  Twitter provides a real-time API which can be used to subscribe to tweets as they happen. `twitter-to-sqlite` can use this API to continually update a SQLite database with tweets matching certain keywords, or referencing specific users.

  ### track

  To track keywords, use the `track` command:

      $ twitter-to-sqlite track tweets.db kakapo

  This command will continue to run until you hit Ctrl+C. It will capture any tweets mentioning the keyword [kakapo](https://en.wikipedia.org/wiki/Kakapo) and store them in the `tweets.db` database file.

  You can pass multiple keywords as a space separated list. This will capture tweets matching either of those keywords:

      $ twitter-to-sqlite track tweets.db kakapo raccoon

  You can enclose phrases in quotes to search for tweets matching both of those keywords:

      $ twitter-to-sqlite track tweets.db 'trash panda'

  See [the Twitter track documentation](https://developer.twitter.com/en/docs/tweets/filter-realtime/guides/basic-stream-parameters#track) for advanced tips on using this command.

  Add the `--verbose` option to see matching tweets (in their verbose JSON form) displayed to the terminal as they are captured:

      $ twitter-to-sqlite track tweets.db raccoon --verbose

  ### follow

  The `follow` command will capture all tweets that are relevant to one or more specific Twitter users.

      $ twitter-to-sqlite follow tweets.db nytimes

  This includes tweets by those users, tweets that reply to or quote those users and retweets by that user. See [the Twitter follow documentation](https://developer.twitter.com/en/docs/tweets/filter-realtime/guides/basic-stream-parameters#follow) for full details.

  The command accepts one or more screen names.

  You can feed it numeric Twitter user IDs instead of screen names by using the `--ids` flag.

  The command also supports the `--sql` and `--attach` options, and the `--verbose` option for displaying tweets as they are captured.

  Here's how to start following tweets from every user ID currently represented as being followed in the `following` table (populated using the `friends-ids` command):

      $ twitter-to-sqlite follow tweets.db \
          --sql="select distinct followed_id from following" \
          --ids

  ## Importing data from your Twitter archive

  You can request an archive of your Twitter data by [following these instructions](https://help.twitter.com/en/managing-your-account/how-to-download-your-twitter-archive).

  Twitter will send you a link to download a `.zip` file. You can import the contents of that file into a set of tables in a new database file called `archive.db` (each table beginning with the `archive_` prefix) using the `import` command:

      $ twitter-to-sqlite import archive.db ~/Downloads/twitter-2019-06-25-b31f2.zip

  This command does not populate any of the regular tables, since Twitter's export data does not exactly match the schema returned by the Twitter API.

  It will delete and recreate the corresponding `archive_*` tables every time you run it. If this is not what you want, run the command against a new SQLite database file name rather than running it against one that already exists.

  If you have already decompressed your archive, you can run this against the directory that you decompressed it to:

      $ twitter-to-sqlite import archive.db ~/Downloads/twitter-2019-06-25-b31f2/

  You can also run it against one or more specific files within that folder. For example, to import just the follower.js and following.js files:

      $ twitter-to-sqlite import archive.db \
          ~/Downloads/twitter-2019-06-25-b31f2/follower.js \
          ~/Downloads/twitter-2019-06-25-b31f2/following.js

  You may want to use other commands to populate tables based on data from the archive. For example, to retrieve full API versions of each of the tweets you have favourited in your archive, you could run the following:

      $ twitter-to-sqlite statuses-lookup archive.db \
          --sql='select tweetId from archive_like' \
          --skip-existing

  If you want these imported tweets to then be reflected in the `favorited_by` table, you can do so by applying the following SQL query:

      $ sqlite3 archive.db
      SQLite version 3.22.0 2018-01-22 18:45:57
      Enter ".help" for usage hints.
      sqlite> INSERT OR IGNORE INTO favorited_by (tweet, user)
         ...>     SELECT tweetId, 'YOUR_TWITTER_ID' FROM archive_like;
      <Ctrl+D>

  Replace YOUR_TWITTER_ID with your numeric Twitter ID. If you don't know that ID you can find it out by running the following:

      $ twitter-to-sqlite fetch \
          "https://api.twitter.com/1.1/account/verify_credentials.json" \
          | grep '"id"' | head -n 1

  ## Design notes

  * Tweet IDs are stored as integers, to afford sorting by ID in a sensible way
  * While we configure foreign key relationships between tables, we do not ask SQLite to enforce them. This is used by the `following` table to allow the `followers-ids` and `friends-ids` commands to populate it with user IDs even if the user accounts themselves are not yet present in the `users` table.

project_urls:
  {"CI": "https://github.com/dogsheep/twitter-to-sqlite/actions", "Changelog": "https://github.com/dogsheep/twitter-to-sqlite/releases", "Homepage": "https://datasette.io/tools/twitter-to-sqlite", "Issues": "https://github.com/dogsheep/twitter-to-sqlite/issues"}

requires_dist:
  ["sqlite-utils (>=2.4.2)", "requests-oauthlib (~=1.2.0)", "python-dateutil", "pytest ; extra == 'test'"]


name: yaml-to-sqlite
summary: Utility for converting YAML files to SQLite
author: Simon Willison
description_content_type: text/markdown
home_page: https://github.com/simonw/yaml-to-sqlite
license: Apache License, Version 2.0
package_url: https://pypi.org/project/yaml-to-sqlite/
project_url: https://pypi.org/project/yaml-to-sqlite/
project_urls: {"Homepage": "https://github.com/simonw/yaml-to-sqlite"}
release_url: https://pypi.org/project/yaml-to-sqlite/1.0/
requires_dist: ["click", "PyYAML", "sqlite-utils (>=3.9.1)", "pytest ; extra == 'test'"]
version: 1.0
classifiers:
  ["Development Status :: 3 - Alpha", "Intended Audience :: Developers", "Intended Audience :: End Users/Desktop", "Intended Audience :: Science/Research", "License :: OSI Approved :: Apache Software License", "Programming Language :: Python :: 3.6", "Programming Language :: Python :: 3.7"]

description:
  # yaml-to-sqlite

  [![PyPI](https://img.shields.io/pypi/v/yaml-to-sqlite.svg)](https://pypi.org/project/yaml-to-sqlite/)
  [![Changelog](https://img.shields.io/github/v/release/simonw/yaml-to-sqlite?include_prereleases&label=changelog)](https://github.com/simonw/yaml-to-sqlite/releases)
  [![Tests](https://github.com/simonw/yaml-to-sqlite/workflows/Test/badge.svg)](https://github.com/simonw/yaml-to-sqlite/actions?query=workflow%3ATest)
  [![License](https://img.shields.io/badge/license-Apache%202.0-blue.svg)](https://github.com/simonw/yaml-to-sqlite/blob/main/LICENSE)

  Load the contents of a YAML file into a SQLite database table.

  ```
  $ yaml-to-sqlite --help
  Usage: yaml-to-sqlite [OPTIONS] DB_PATH TABLE YAML_FILE

    Convert YAML files to SQLite

  Options:
    --version             Show the version and exit.
    --pk TEXT             Column to use as a primary key
    --single-column TEXT  If YAML file is a list of values, populate this column
    --help                Show this message and exit.
  ```
  ## Usage

  Given a `news.yml` file containing the following:
  ```yaml
  - date: 2021-06-05
    body: |-
      [Datasette 0.57](https://docs.datasette.io/en/stable/changelog.html#v0-57) is out with an important security patch.
  - date: 2021-05-10
    body: |-
      [Django SQL Dashboard](https://simonwillison.net/2021/May/10/django-sql-dashboard/) is a new tool that brings a useful authenticated subset of Datasette to Django projects that are built on top of PostgreSQL.
  ```
  Running this command:
  ```bash
  $ yaml-to-sqlite news.db stories news.yml
  ```
  Will create a database file with this schema:
  ```bash
  $ sqlite-utils schema news.db
  CREATE TABLE [stories] (
     [date] TEXT,
     [body] TEXT
  );
  ```
  The `--pk` option can be used to set a column as the primary key for the table:

  ```bash
  $ yaml-to-sqlite news.db stories news.yml --pk date
  $ sqlite-utils schema news.db
  CREATE TABLE [stories] (
     [date] TEXT PRIMARY KEY,
     [body] TEXT
  );
  ```
  ## Single column YAML lists

  The `--single-column` option can be used when the YAML file is a list of values, for example a file called `dogs.yml` containing the following:

  ```yaml
  - Cleo
  - Pancakes
  - Nixie
  ```
  Running this command:
  ```bash
  $ yaml-to-sqlite dogs.db dogs.yaml --single-column=name
  ```
  Will create a single `dogs` table with a single `name` column that is the primary key:

  ```bash
  $ sqlite-utils schema dogs.db
  CREATE TABLE [dogs] (
     [name] TEXT PRIMARY KEY
  );
  $ sqlite-utils dogs.db 'select * from dogs' -t
  name
  --------
  Cleo
  Pancakes
  Nixie
  ```


