# 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
- [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`
Raw data
{
"_id": null,
"home_page": "https://github.com/simonw/sqlite-utils",
"name": "sqlite-utils",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.7",
"maintainer_email": "",
"keywords": "",
"author": "Simon Willison",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/ae/70/dc7c74592f30ac20be23eaeeb2a84ee6e2c12c21beb07a3eb53ead77de1f/sqlite-utils-3.36.tar.gz",
"platform": null,
"description": "# sqlite-utils\n\n[](https://pypi.org/project/sqlite-utils/)\n[](https://sqlite-utils.datasette.io/en/stable/changelog.html)\n[](https://pypi.org/project/sqlite-utils/)\n[](https://github.com/simonw/sqlite-utils/actions?query=workflow%3ATest)\n[](http://sqlite-utils.datasette.io/en/stable/?badge=stable)\n[](https://codecov.io/gh/simonw/sqlite-utils)\n[](https://github.com/simonw/sqlite-utils/blob/main/LICENSE)\n[](https://discord.gg/Ass7bCAMDw)\n\nPython CLI utility and library for manipulating SQLite databases.\n\n## Some feature highlights\n\n- [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\n- [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\n- [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\n- 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\n- [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\n- [Install plugins](https://sqlite-utils.datasette.io/en/stable/plugins.html) to add custom SQL functions and additional features\n\nRead 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/).\n\n## Installation\n\n pip install sqlite-utils\n\nOr if you use [Homebrew](https://brew.sh/) for macOS:\n\n brew install sqlite-utils\n\n## Using as a CLI tool\n\nNow you can do things with the CLI utility like this:\n\n $ sqlite-utils memory dogs.csv \"select * from t\"\n [{\"id\": 1, \"age\": 4, \"name\": \"Cleo\"},\n {\"id\": 2, \"age\": 2, \"name\": \"Pancakes\"}]\n\n $ sqlite-utils insert dogs.db dogs dogs.csv --csv\n [####################################] 100%\n\n $ sqlite-utils tables dogs.db --counts\n [{\"table\": \"dogs\", \"count\": 2}]\n\n $ sqlite-utils dogs.db \"select id, name from dogs\"\n [{\"id\": 1, \"name\": \"Cleo\"},\n {\"id\": 2, \"name\": \"Pancakes\"}]\n\n $ sqlite-utils dogs.db \"select * from dogs\" --csv\n id,age,name\n 1,4,Cleo\n 2,2,Pancakes\n\n $ sqlite-utils dogs.db \"select * from dogs\" --table\n id age name\n ---- ----- --------\n 1 4 Cleo\n 2 2 Pancakes\n\nYou can import JSON data into a new database table like this:\n\n $ curl https://api.github.com/repos/simonw/sqlite-utils/releases \\\n | sqlite-utils insert releases.db releases - --pk id\n\nOr for data in a CSV file:\n\n $ sqlite-utils insert dogs.db dogs dogs.csv --csv\n\n`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:\n\n $ cat dogs.csv | sqlite-utils memory - \"select name, age from stdin\"\n\nSee the [full CLI documentation](https://sqlite-utils.datasette.io/en/stable/cli.html) for comprehensive coverage of many more commands.\n\n## Using as a library\n\nYou can also `import sqlite_utils` and use it as a Python library like this:\n\n```python\nimport sqlite_utils\ndb = sqlite_utils.Database(\"demo_database.db\")\n# This line creates a \"dogs\" table if one does not already exist:\ndb[\"dogs\"].insert_all([\n {\"id\": 1, \"age\": 4, \"name\": \"Cleo\"},\n {\"id\": 2, \"age\": 2, \"name\": \"Pancakes\"}\n], pk=\"id\")\n```\n\nCheck 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.\n\n## Related projects\n\n* [Datasette](https://datasette.io/): A tool for exploring and publishing data\n* [csvs-to-sqlite](https://github.com/simonw/csvs-to-sqlite): Convert CSV files into a SQLite database\n* [db-to-sqlite](https://github.com/simonw/db-to-sqlite): CLI tool for exporting a MySQL or PostgreSQL database as a SQLite file\n* [dogsheep](https://dogsheep.github.io/): A family of tools for personal analytics, built on top of `sqlite-utils`\n",
"bugtrack_url": null,
"license": "Apache License, Version 2.0",
"summary": "CLI tool and Python library for manipulating SQLite databases",
"version": "3.36",
"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"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "3a70c1917860d717d50c9cbb3c616afbf5c124e90a1609dccde4540375da3a2d",
"md5": "9fde218ceeb96a13bbeffe31efb6cb23",
"sha256": "b71e829755c2efbdcd6931a31968dee4e8bd71b3c14f0fe648b22377027c5bec"
},
"downloads": -1,
"filename": "sqlite_utils-3.36-py3-none-any.whl",
"has_sig": false,
"md5_digest": "9fde218ceeb96a13bbeffe31efb6cb23",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.7",
"size": 67658,
"upload_time": "2023-12-08T05:36:18",
"upload_time_iso_8601": "2023-12-08T05:36:18.136262Z",
"url": "https://files.pythonhosted.org/packages/3a/70/c1917860d717d50c9cbb3c616afbf5c124e90a1609dccde4540375da3a2d/sqlite_utils-3.36-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "ae70dc7c74592f30ac20be23eaeeb2a84ee6e2c12c21beb07a3eb53ead77de1f",
"md5": "f045c6cef0875ad4ee2a28ec7d1d9604",
"sha256": "dcc311394fe86dc16f65037b0075e238efcfd2e12e65d53ed196954502996f3c"
},
"downloads": -1,
"filename": "sqlite-utils-3.36.tar.gz",
"has_sig": false,
"md5_digest": "f045c6cef0875ad4ee2a28ec7d1d9604",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.7",
"size": 211586,
"upload_time": "2023-12-08T05:36:20",
"upload_time_iso_8601": "2023-12-08T05:36:20.967704Z",
"url": "https://files.pythonhosted.org/packages/ae/70/dc7c74592f30ac20be23eaeeb2a84ee6e2c12c21beb07a3eb53ead77de1f/sqlite-utils-3.36.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-12-08 05:36:20",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "simonw",
"github_project": "sqlite-utils",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlite-utils"
}
JSON
