repos: 140912432
This data as json
| id | node_id | name | full_name | private | owner | html_url | description | fork | created_at | updated_at | pushed_at | homepage | size | stargazers_count | watchers_count | language | has_issues | has_projects | has_downloads | has_wiki | has_pages | forks_count | archived | disabled | open_issues_count | license | topics | forks | open_issues | watchers | default_branch | permissions | temp_clone_token | organization | network_count | subscribers_count | readme | readme_html | allow_forking | visibility | is_template | template_repository | web_commit_signoff_required | has_discussions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 140912432 | MDEwOlJlcG9zaXRvcnkxNDA5MTI0MzI= | sqlite-utils | simonw/sqlite-utils | 0 | 9599 | https://github.com/simonw/sqlite-utils | Python CLI utility and library for manipulating SQLite databases | 0 | 2018-07-14T03:21:46Z | 2022-11-15T18:12:16Z | 2022-11-15T15:53:38Z | https://sqlite-utils.datasette.io | 1437 | 1029 | 1029 | Python | 1 | 1 | 1 | 1 | 0 | 79 | 0 | 0 | 72 | apache-2.0 | ["cli", "click", "datasette", "datasette-io", "datasette-tool", "python", "sqlite", "sqlite-database"] | 79 | 72 | 1029 | main | {"admin": false, "maintain": false, "push": false, "triage": false, "pull": false} | 79 | 16 | # sqlite-utils [](https://pypi.org/project/sqlite-utils/) [](https://sqlite-utils.datasette.io/en/stable/changelog.html) [](https://pypi.org/project/sqlite-utils/) [](https://github.com/simonw/sqlite-utils/actions?query=workflow%3ATest) [](http://sqlite-utils.datasette.io/en/stable/?badge=stable) [](https://codecov.io/gh/simonw/sqlite-utils) [](https://github.com/simonw/sqlite-utils/blob/main/LICENSE) [](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 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` | <div id="readme" class="md" data-path="README.md"><article class="markdown-body entry-content container-lg" itemprop="text"><h1 dir="auto"><a id="user-content-sqlite-utils" class="anchor" aria-hidden="true" href="#user-content-sqlite-utils"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>sqlite-utils</h1> <p dir="auto"><a href="https://pypi.org/project/sqlite-utils/" rel="nofollow"><img src="https://camo.githubusercontent.com/f691f8124616f99c7978ecb2a58841aa6a7ace31234b0c87d392c4ef8db853e4/68747470733a2f2f696d672e736869656c64732e696f2f707970692f762f73716c6974652d7574696c732e737667" alt="PyPI" data-canonical-src="https://img.shields.io/pypi/v/sqlite-utils.svg" style="max-width: 100%;"></a> <a href="https://sqlite-utils.datasette.io/en/stable/changelog.html" rel="nofollow"><img src="https://camo.githubusercontent.com/b114e00dffe1e5b24d21c5cd11dc97f8a6ab83da51acbb971e5daebeeb47d690/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f72656c656173652f73696d6f6e772f73716c6974652d7574696c733f696e636c7564655f70726572656c6561736573266c6162656c3d6368616e67656c6f67" alt="Changelog" data-canonical-src="https://img.shields.io/github/v/release/simonw/sqlite-utils?include_prereleases&label=changelog" style="max-width: 100%;"></a> <a href="https://pypi.org/project/sqlite-utils/" rel="nofollow"><img src="https://camo.githubusercontent.com/2413fbc1359bcdcf500aa31b497c8c25acf7db3f3fc3a33c540e809c984a189e/68747470733a2f2f696d672e736869656c64732e696f2f707970692f707976657273696f6e732f73716c6974652d7574696c732e7376673f6c6f676f3d707974686f6e266c6f676f436f6c6f723d7768697465" alt="Python 3.x" data-canonical-src="https://img.shields.io/pypi/pyversions/sqlite-utils.svg?logo=python&logoColor=white" style="max-width: 100%;"></a> <a href="https://github.com/simonw/sqlite-utils/actions?query=workflow%3ATest"><img src="https://github.com/simonw/sqlite-utils/workflows/Test/badge.svg" alt="Tests" style="max-width: 100%;"></a> <a href="http://sqlite-utils.datasette.io/en/stable/?badge=stable" rel="nofollow"><img src="https://camo.githubusercontent.com/ebfbbefc97415a2c49a4757ef1f7836a0771eb543ba3c1d1267e466ab0ed8bd1/68747470733a2f2f72656164746865646f63732e6f72672f70726f6a656374732f73716c6974652d7574696c732f62616467652f3f76657273696f6e3d737461626c65" alt="Documentation Status" data-canonical-src="https://readthedocs.org/projects/sqlite-utils/badge/?version=stable" style="max-width: 100%;"></a> <a href="https://codecov.io/gh/simonw/sqlite-utils" rel="nofollow"><img src="https://camo.githubusercontent.com/762e8034bbc8654dab7f15e85ae5eb836d07b86ebf68ab955a8c18d8035fbf13/68747470733a2f2f636f6465636f762e696f2f67682f73696d6f6e772f73716c6974652d7574696c732f6272616e63682f6d61696e2f67726170682f62616467652e737667" alt="codecov" data-canonical-src="https://codecov.io/gh/simonw/sqlite-utils/branch/main/graph/badge.svg" style="max-width: 100%;"></a> <a href="https://github.com/simonw/sqlite-utils/blob/main/LICENSE"><img src="https://camo.githubusercontent.com/1698104e976c681143eb0841f9675c6f802bb7aa832afc0c7a4e719b1f3cf955/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d417061636865253230322e302d626c75652e737667" alt="License" data-canonical-src="https://img.shields.io/badge/license-Apache%202.0-blue.svg" style="max-width: 100%;"></a> <a href="https://discord.gg/Ass7bCAMDw" rel="nofollow"><img src="https://camo.githubusercontent.com/1e67d99839ed78183f50f78dee6bb4897d95ff44a4b7182d4bfd82c505d8d835/68747470733a2f2f696d672e736869656c64732e696f2f646973636f72642f3832333937313238363330383335363135373f6c6162656c3d646973636f7264" alt="discord" data-canonical-src="https://img.shields.io/discord/823971286308356157?label=discord" style="max-width: 100%;"></a></p> <p dir="auto">Python CLI utility and library for manipulating SQLite databases.</p> <h2 dir="auto"><a id="user-content-some-feature-highlights" class="anchor" aria-hidden="true" href="#user-content-some-feature-highlights"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Some feature highlights</h2> <ul dir="auto"> <li><a href="https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-json-data" rel="nofollow">Pipe JSON</a> (or <a href="https://sqlite-utils.datasette.io/en/stable/cli.html#inserting-csv-or-tsv-data" rel="nofollow">CSV or TSV</a>) directly into a new SQLite database file, automatically creating a table with the appropriate schema</li> <li><a href="https://sqlite-utils.datasette.io/en/stable/cli.html#querying-data-directly-using-an-in-memory-database" rel="nofollow">Run in-memory SQL queries</a>, including joins, directly against data in CSV, TSV or JSON files and view the results</li> <li><a href="https://sqlite-utils.datasette.io/en/stable/cli.html#configuring-full-text-search" rel="nofollow">Configure SQLite full-text search</a> against your database tables and run search queries against them, ordered by relevance</li> <li>Run <a href="https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables" rel="nofollow">transformations against your tables</a> to make schema changes that SQLite <code>ALTER TABLE</code> does not directly support, such as changing the type of a column</li> <li><a href="https://sqlite-utils.datasette.io/en/stable/cli.html#extracting-columns-into-a-separate-table" rel="nofollow">Extract columns</a> into separate tables to better normalize your existing data</li> </ul> <p dir="auto">Read more on my blog, in this series of posts on <a href="https://simonwillison.net/series/sqlite-utils-features/" rel="nofollow">New features in sqlite-utils</a> and other <a href="https://simonwillison.net/tags/sqliteutils/" rel="nofollow">entries tagged sqliteutils</a>.</p> <h2 dir="auto"><a id="user-content-installation" class="anchor" aria-hidden="true" href="#user-content-installation"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Installation</h2> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="pip install sqlite-utils"><pre class="notranslate"><code>pip install sqlite-utils </code></pre></div> <p dir="auto">Or if you use <a href="https://brew.sh/" rel="nofollow">Homebrew</a> for macOS:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="brew install sqlite-utils"><pre class="notranslate"><code>brew install sqlite-utils </code></pre></div> <h2 dir="auto"><a id="user-content-using-as-a-cli-tool" class="anchor" aria-hidden="true" href="#user-content-using-as-a-cli-tool"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using as a CLI tool</h2> <p dir="auto">Now you can do things with the CLI utility like this:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="$ 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"><pre class="notranslate"><code>$ 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 </code></pre></div> <p dir="auto">You can import JSON data into a new database table like this:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="$ curl https://api.github.com/repos/simonw/sqlite-utils/releases \ | sqlite-utils insert releases.db releases - --pk id"><pre class="notranslate"><code>$ curl https://api.github.com/repos/simonw/sqlite-utils/releases \ | sqlite-utils insert releases.db releases - --pk id </code></pre></div> <p dir="auto">Or for data in a CSV file:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="$ sqlite-utils insert dogs.db dogs dogs.csv --csv"><pre class="notranslate"><code>$ sqlite-utils insert dogs.db dogs dogs.csv --csv </code></pre></div> <p dir="auto"><code>sqlite-utils memory</code> lets you import CSV or JSON data into an in-memory database and run SQL queries against it in a single command:</p> <div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="$ cat dogs.csv | sqlite-utils memory - "select name, age from stdin""><pre class="notranslate"><code>$ cat dogs.csv | sqlite-utils memory - "select name, age from stdin" </code></pre></div> <p dir="auto">See the <a href="https://sqlite-utils.datasette.io/en/stable/cli.html" rel="nofollow">full CLI documentation</a> for comprehensive coverage of many more commands.</p> <h2 dir="auto"><a id="user-content-using-as-a-library" class="anchor" aria-hidden="true" href="#user-content-using-as-a-library"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Using as a library</h2> <p dir="auto">You can also <code>import sqlite_utils</code> and use it as a Python library like this:</p> <div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="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")"><pre><span class="pl-k">import</span> <span class="pl-s1">sqlite_utils</span> <span class="pl-s1">db</span> <span class="pl-c1">=</span> <span class="pl-s1">sqlite_utils</span>.<span class="pl-v">Database</span>(<span class="pl-s">"demo_database.db"</span>) <span class="pl-c"># This line creates a "dogs" table if one does not already exist:</span> <span class="pl-s1">db</span>[<span class="pl-s">"dogs"</span>].<span class="pl-en">insert_all</span>([ {<span class="pl-s">"id"</span>: <span class="pl-c1">1</span>, <span class="pl-s">"age"</span>: <span class="pl-c1">4</span>, <span class="pl-s">"name"</span>: <span class="pl-s">"Cleo"</span>}, {<span class="pl-s">"id"</span>: <span class="pl-c1">2</span>, <span class="pl-s">"age"</span>: <span class="pl-c1">2</span>, <span class="pl-s">"name"</span>: <span class="pl-s">"Pancakes"</span>} ], <span class="pl-s1">pk</span><span class="pl-c1">=</span><span class="pl-s">"id"</span>)</pre></div> <p dir="auto">Check out the <a href="https://sqlite-utils.datasette.io/en/stable/python-api.html" rel="nofollow">full library documentation</a> for everything else you can do with the Python library.</p> <h2 dir="auto"><a id="user-content-related-projects" class="anchor" aria-hidden="true" href="#user-content-related-projects"><svg class="octicon octicon-link" viewBox="0 0 16 16" version="1.1" width="16" height="16" aria-hidden="true"><path fill-rule="evenodd" d="M7.775 3.275a.75.75 0 001.06 1.06l1.25-1.25a2 2 0 112.83 2.83l-2.5 2.5a2 2 0 01-2.83 0 .75.75 0 00-1.06 1.06 3.5 3.5 0 004.95 0l2.5-2.5a3.5 3.5 0 00-4.95-4.95l-1.25 1.25zm-4.69 9.64a2 2 0 010-2.83l2.5-2.5a2 2 0 012.83 0 .75.75 0 001.06-1.06 3.5 3.5 0 00-4.95 0l-2.5 2.5a3.5 3.5 0 004.95 4.95l1.25-1.25a.75.75 0 00-1.06-1.06l-1.25 1.25a2 2 0 01-2.83 0z"></path></svg></a>Related projects</h2> <ul dir="auto"> <li><a href="https://datasette.io/" rel="nofollow">Datasette</a>: A tool for exploring and publishing data</li> <li><a href="https://github.com/simonw/csvs-to-sqlite">csvs-to-sqlite</a>: Convert CSV files into a SQLite database</li> <li><a href="https://github.com/simonw/db-to-sqlite">db-to-sqlite</a>: CLI tool for exporting a MySQL or PostgreSQL database as a SQLite file</li> <li><a href="https://dogsheep.github.io/" rel="nofollow">dogsheep</a>: A family of tools for personal analytics, built on top of <code>sqlite-utils</code></li> </ul> </article></div> | 1 | public | 0 | 0 | 0 |
Links from other tables
- 104 rows from repo in releases