| Name | sqlite-utils-fast-fks JSON |
| Version |
0.1
JSON |
| download |
| home_page | |
| Summary | Fast foreign key addition for sqlite-utils |
| upload_time | 2023-08-18 17:01:44 |
| maintainer | |
| docs_url | None |
| author | Simon Willison |
| requires_python | |
| license | Apache-2.0 |
| keywords |
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# sqlite-utils-fast-fks
[](https://pypi.org/project/sqlite-utils-fast-fks/)
[](https://github.com/simonw/sqlite-utils-fast-fks/releases)
[](https://github.com/simonw/sqlite-utils-fast-fks/actions?query=workflow%3ATest)
[](https://github.com/simonw/sqlite-utils-fast-fks/blob/main/LICENSE)
Fast foreign key addition for [sqlite-utils](https://sqlite-utils.datasette.io/).
## Background
SQLite does not yet have a built-in method for adding foreign key constraints to an existing table.
There are two workarounds for this limitation:
1. You can create brand new table with the new foreign keys, copy the data across, then drop the old table and rename the new one. This method is implemented by the [sqlite-utils table.transform() method](https://sqlite-utils.datasette.io/en/stable/python-api.html#transforming-a-table).
2. You can set `PRAGMA writable_schema = 1`, then directly modify the schema stored in the `sqlite_master` table for that table. Then increment the `schema_version`, set `writable_schema = 0` again and run a vacuum against the database.
For tables with large numbers of rows that second option is a lot faster, as you don't need to create an entirely new copy of all of the data.
Prior to version 3.35 [sqlite-utils](https://sqlite-utils.datasette.io/) implemented the latter pattern as part of its `table.add_foreign_key()` and `db.add_foreign_keys()` methods.
It turned out these caused `table sqlite_master may not be modified` errors on some Python installations, primarily on macOS where the ability to modify the `sqlite_master` table is sometimes disabled by default.
This plugin brings the same functionality back again. You can use this if you want fast foreign key addition and you know that your platform does not suffer from the `table sqlite_master may not be modified` error.
## Installation
Install this plugin in the same environment as sqlite-utils.
```bash
sqlite-utils install sqlite-utils-fast-fks
```
Or install using `pip`:
```bash
pip install sqlite-utils-fast-fks
```
## Python library
To add foreign keys in Python code, use the `add_foreign_keys(db, foreign_keys)` function. Here's an example:
```python
from sqlite_utils_fast_fks import add_foreign_keys
from sqlite_utils import Database
db = Database("my_database.db")
db["country"].insert_all([{"id": 1, "name": "United Kingdom"}])
db["continent"].insert_all([{"id": 1, "name": "Europe"}])
db["places"].insert(
{
"id": 1,
"name": "London",
"country_id": 1,
"continent_id": 1,
}
)
# Now modify that places table to have two foreign keys:
add_foreign_keys(
db,
[
("places", "country_id", "country", "id"),
("places", "continent_id", "continent", "id"),
],
)
```
The `foreign_keys` argument is a list of tuples, each containing four values:
- The table to add the foreign key to
- The column in that table that will be a foreign key
- The other table that the column should reference
- The column in that other table that should be referenced
## Command-line tool
When installed as a [sqlite-utils plugin](https://sqlite-utils.datasette.io/en/stable/plugins.html), this library adds a new `sqlite-utils fast-fks` command. It can be used like this:
```bash
sqlite-utils fast-fks my_database.db places country_id country id
```
The command takes a path to a database, then the table name, the column name, the other table name and the other column name.
You can specify multiple foreign keys to add at once by repeating the last four arguments:
```bash
sqlite-utils fast-fks my_database.db \
places country_id country id \
places continent_id continent id
```
## Development
To set up this plugin locally, first checkout the code. Then create a new virtual environment:
cd sqlite-utils-fast-fks
python3 -m venv venv
source venv/bin/activate
Now install the dependencies and test dependencies:
pip install -e '.[test]'
To run the tests:
pytest
Raw data
{
"_id": null,
"home_page": "",
"name": "sqlite-utils-fast-fks",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Simon Willison",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/2d/d4/4e8fcc621d3373be387da948e57ff420e7a359aabdfbd547efbcdfd8d191/sqlite-utils-fast-fks-0.1.tar.gz",
"platform": null,
"description": "# sqlite-utils-fast-fks\n\n[](https://pypi.org/project/sqlite-utils-fast-fks/)\n[](https://github.com/simonw/sqlite-utils-fast-fks/releases)\n[](https://github.com/simonw/sqlite-utils-fast-fks/actions?query=workflow%3ATest)\n[](https://github.com/simonw/sqlite-utils-fast-fks/blob/main/LICENSE)\n\nFast foreign key addition for [sqlite-utils](https://sqlite-utils.datasette.io/).\n\n## Background\n\nSQLite does not yet have a built-in method for adding foreign key constraints to an existing table.\n\nThere are two workarounds for this limitation:\n\n1. You can create brand new table with the new foreign keys, copy the data across, then drop the old table and rename the new one. This method is implemented by the [sqlite-utils table.transform() method](https://sqlite-utils.datasette.io/en/stable/python-api.html#transforming-a-table).\n2. You can set `PRAGMA writable_schema = 1`, then directly modify the schema stored in the `sqlite_master` table for that table. Then increment the `schema_version`, set `writable_schema = 0` again and run a vacuum against the database.\n\nFor tables with large numbers of rows that second option is a lot faster, as you don't need to create an entirely new copy of all of the data.\n\nPrior to version 3.35 [sqlite-utils](https://sqlite-utils.datasette.io/) implemented the latter pattern as part of its `table.add_foreign_key()` and `db.add_foreign_keys()` methods. \n\nIt turned out these caused `table sqlite_master may not be modified` errors on some Python installations, primarily on macOS where the ability to modify the `sqlite_master` table is sometimes disabled by default.\n\nThis plugin brings the same functionality back again. You can use this if you want fast foreign key addition and you know that your platform does not suffer from the `table sqlite_master may not be modified` error.\n\n## Installation\n\nInstall this plugin in the same environment as sqlite-utils.\n```bash\nsqlite-utils install sqlite-utils-fast-fks\n```\nOr install using `pip`:\n```bash\npip install sqlite-utils-fast-fks\n```\n\n## Python library\n\nTo add foreign keys in Python code, use the `add_foreign_keys(db, foreign_keys)` function. Here's an example:\n```python\nfrom sqlite_utils_fast_fks import add_foreign_keys\nfrom sqlite_utils import Database\n\ndb = Database(\"my_database.db\")\ndb[\"country\"].insert_all([{\"id\": 1, \"name\": \"United Kingdom\"}])\ndb[\"continent\"].insert_all([{\"id\": 1, \"name\": \"Europe\"}])\ndb[\"places\"].insert(\n {\n \"id\": 1,\n \"name\": \"London\",\n \"country_id\": 1,\n \"continent_id\": 1,\n }\n)\n\n# Now modify that places table to have two foreign keys:\nadd_foreign_keys(\n db,\n [\n (\"places\", \"country_id\", \"country\", \"id\"),\n (\"places\", \"continent_id\", \"continent\", \"id\"),\n ],\n)\n```\nThe `foreign_keys` argument is a list of tuples, each containing four values:\n\n- The table to add the foreign key to\n- The column in that table that will be a foreign key\n- The other table that the column should reference\n- The column in that other table that should be referenced\n\n## Command-line tool\n\nWhen installed as a [sqlite-utils plugin](https://sqlite-utils.datasette.io/en/stable/plugins.html), this library adds a new `sqlite-utils fast-fks` command. It can be used like this:\n\n```bash\nsqlite-utils fast-fks my_database.db places country_id country id\n```\nThe command takes a path to a database, then the table name, the column name, the other table name and the other column name.\n\nYou can specify multiple foreign keys to add at once by repeating the last four arguments:\n\n```bash\nsqlite-utils fast-fks my_database.db \\\n places country_id country id \\\n places continent_id continent id\n```\n## Development\n\nTo set up this plugin locally, first checkout the code. Then create a new virtual environment:\n\n cd sqlite-utils-fast-fks\n python3 -m venv venv\n source venv/bin/activate\n\nNow install the dependencies and test dependencies:\n\n pip install -e '.[test]'\n\nTo run the tests:\n\n pytest\n",
"bugtrack_url": null,
"license": "Apache-2.0",
"summary": "Fast foreign key addition for sqlite-utils",
"version": "0.1",
"project_urls": {
"CI": "https://github.com/simonw/sqlite-utils-fast-fks/actions",
"Changelog": "https://github.com/simonw/sqlite-utils-fast-fks/releases",
"Homepage": "https://github.com/simonw/sqlite-utils-fast-fks",
"Issues": "https://github.com/simonw/sqlite-utils-fast-fks/issues"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "2b01a7ce32a7bc3540e6a9707a26426203ce79906f3e84db496f05e4f3d2430e",
"md5": "9ffd55190ebf851aa932afcc61cbef24",
"sha256": "130b0582a5d8b9b3a4ac7f8d72d1c4efc4c8b99388e22cfceccf3e1574f7d706"
},
"downloads": -1,
"filename": "sqlite_utils_fast_fks-0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "9ffd55190ebf851aa932afcc61cbef24",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 4869,
"upload_time": "2023-08-18T17:01:43",
"upload_time_iso_8601": "2023-08-18T17:01:43.405676Z",
"url": "https://files.pythonhosted.org/packages/2b/01/a7ce32a7bc3540e6a9707a26426203ce79906f3e84db496f05e4f3d2430e/sqlite_utils_fast_fks-0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "2dd44e8fcc621d3373be387da948e57ff420e7a359aabdfbd547efbcdfd8d191",
"md5": "8dc9d145060cf57174ffa46179583bed",
"sha256": "8e3827ad1ab37f8d88b651729ba6661c6359eeabd03cdac547cdd4e8e7785e86"
},
"downloads": -1,
"filename": "sqlite-utils-fast-fks-0.1.tar.gz",
"has_sig": false,
"md5_digest": "8dc9d145060cf57174ffa46179583bed",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 5000,
"upload_time": "2023-08-18T17:01:44",
"upload_time_iso_8601": "2023-08-18T17:01:44.503142Z",
"url": "https://files.pythonhosted.org/packages/2d/d4/4e8fcc621d3373be387da948e57ff420e7a359aabdfbd547efbcdfd8d191/sqlite-utils-fast-fks-0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-08-18 17:01:44",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "simonw",
"github_project": "sqlite-utils-fast-fks",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlite-utils-fast-fks"
}
