# apswutils
<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->
> [!TIP]
>
> ### Where to find the complete documentation for this library
>
> If you want to learn about everything this project can do, we
> recommend reading the Python library section of the sqlite-utils
> project
> [here](https://sqlite-utils.datasette.io/en/stable/python-api.html).
>
> This project wouldn’t exist without Simon Willison and his excellent
> [sqlite-utils](https://github.com/simonw/sqlite-utils) project. Most
> of this project is his code, with some minor changes made to it.
## Install
pip install apswutils
## Use
First, import the apswutils library. Through the use of the **all**
attribute in our Python modules by using `import *` we only bring in the
`Database`, `Queryable`, `Table`, `View` classes. There’s no risk of
namespace pollution.
``` python
from apswutils.db import *
```
Then we create a SQLite database. For the sake of convienance we’re
doing it in-memory with the `:memory:` special string. If you wanted
something more persistent, name it something not surrounded by colons,
`data.db` is a common file name.
``` python
db = Database(":memory:")
```
Let’s drop (aka ‘delete’) any tables that might exist. These docs also
serve as a test harness, and we want to make certain we are starting
with a clean slate. This also serves as a handy sneak preview of some of
the features of this library.
``` python
for t in db.tables: t.drop()
```
User tables are a handy way to create a useful example with some
real-world meaning. To do this, we first instantiate the `users` table
object:
``` python
users = Table(db, 'Users')
users
```
<Table Users (does not exist yet)>
The table doesn’t exist yet, so let’s add some columns via the
`Table.create` method:
``` python
users.create(columns=dict(id=int, name=str, age=int))
users
```
<Table Users (id, name, age)>
What if we need to change the table structure?
For example User tables often include things like password field. Let’s
add that now by calling `create` again, but this time with
`transform=True`. We should now see that the `users` table now has the
`pwd:str` field added.
``` python
users.create(columns=dict(id=int, name=str, age=int, pwd=str), transform=True, pk='id')
users
```
<Table Users (id, name, age, pwd)>
``` python
print(db.schema)
```
CREATE TABLE "Users" (
[id] INTEGER PRIMARY KEY,
[name] TEXT,
[age] INTEGER,
[pwd] TEXT
);
## Queries
Let’s add some users to query:
``` python
users.insert(dict(name='Raven', age=8, pwd='s3cret'))
users.insert(dict(name='Magpie', age=5, pwd='supersecret'))
users.insert(dict(name='Crow', age=12, pwd='verysecret'))
users.insert(dict(name='Pigeon', age=3, pwd='keptsecret'))
users.insert(dict(name='Eagle', age=7, pwd='s3cr3t'))
```
<Table Users (id, name, age, pwd)>
A simple unfiltered select can be executed using `rows` property on the
table object.
``` python
users.rows
```
<generator object Queryable.rows_where>
Let’s iterate over that generator to see the results:
``` python
[o for o in users.rows]
```
[{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},
{'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},
{'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'},
{'id': 4, 'name': 'Pigeon', 'age': 3, 'pwd': 'keptsecret'},
{'id': 5, 'name': 'Eagle', 'age': 7, 'pwd': 's3cr3t'}]
Filtering can be done via the `rows_where` function:
``` python
[o for o in users.rows_where('age > 3')]
```
[{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},
{'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},
{'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'},
{'id': 5, 'name': 'Eagle', 'age': 7, 'pwd': 's3cr3t'}]
We can also `limit` the results:
``` python
[o for o in users.rows_where('age > 3', limit=2)]
```
[{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},
{'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'}]
The `offset` keyword can be combined with the `limit` keyword.
``` python
[o for o in users.rows_where('age > 3', limit=2, offset=1)]
```
[{'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},
{'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'}]
The `offset` must be used with `limit` or raise a `ValueError`:
``` python
try:
[o for o in users.rows_where(offset=1)]
except ValueError as e:
print(e)
```
Cannot use offset without limit
## Transactions
If you have any SQL calls outside an explicit transaction, they are
committed instantly.
To group 2 or more queries together into 1 transaction, wrap them in a
BEGIN and COMMIT, executing ROLLBACK if an exception is caught:
``` python
users.get(1)
```
{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'}
``` python
db.begin()
try:
users.delete([1])
db.execute('FNOOORD')
db.commit()
except Exception as e:
print(e)
db.rollback()
```
near "FNOOORD": syntax error
Because the transaction was rolled back, the user was not deleted:
``` python
users.get(1)
```
{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'}
Let’s do it again, but without the DB error, to check the transaction is
successful:
``` python
db.begin()
try:
users.delete([1])
db.commit()
except Exception as e: db.rollback()
```
``` python
try:
users.get(1)
print("Delete failed!")
except: print("Delete succeeded!")
```
Delete succeeded!
## Differences from sqlite-utils and sqlite-minutils
- WAL is the default
- Setting `Database(recursive_triggers=False)` works as expected
- Primary keys must be set on a table for it to be a target of a foreign
key
- Errors have been changed minimally, future PRs will change them
incrementally
## Differences in error handling
| Old/sqlite3/dbapi | New/APSW | Reason |
|----|----|----|
| IntegrityError | apsw.ConstraintError | Caused due to SQL transformation blocked on database constraints |
| sqlite3.dbapi2.OperationalError | apsw.Error | General error, OperationalError is now proxied to apsw.Error |
| sqlite3.dbapi2.OperationalError | apsw.SQLError | When an error is due to flawed SQL statements |
| sqlite3.ProgrammingError | apsw.ConnectionClosedError | Caused by an improperly closed database file |
Raw data
{
"_id": null,
"home_page": "https://github.com/AnswerDotAI/apswutils",
"name": "apswutils",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": null,
"keywords": "nbdev jupyter notebook python sqlite apsw",
"author": "Daniel and Audrey Roy Greenfeld, and Jeremy Howard",
"author_email": "info@fast.ai",
"download_url": "https://files.pythonhosted.org/packages/17/ed/7905ea5483769e26b1f222f17d5c0ae77ac9720fc9f28d743474000a7c64/apswutils-0.0.1.tar.gz",
"platform": null,
"description": "# apswutils\n\n\n<!-- WARNING: THIS FILE WAS AUTOGENERATED! DO NOT EDIT! -->\n\n> [!TIP]\n>\n> ### Where to find the complete documentation for this library\n>\n> If you want to learn about everything this project can do, we\n> recommend reading the Python library section of the sqlite-utils\n> project\n> [here](https://sqlite-utils.datasette.io/en/stable/python-api.html).\n>\n> This project wouldn\u2019t exist without Simon Willison and his excellent\n> [sqlite-utils](https://github.com/simonw/sqlite-utils) project. Most\n> of this project is his code, with some minor changes made to it.\n\n## Install\n\n pip install apswutils\n\n## Use\n\nFirst, import the apswutils library. Through the use of the **all**\nattribute in our Python modules by using `import *` we only bring in the\n`Database`, `Queryable`, `Table`, `View` classes. There\u2019s no risk of\nnamespace pollution.\n\n``` python\nfrom apswutils.db import *\n```\n\nThen we create a SQLite database. For the sake of convienance we\u2019re\ndoing it in-memory with the `:memory:` special string. If you wanted\nsomething more persistent, name it something not surrounded by colons,\n`data.db` is a common file name.\n\n``` python\ndb = Database(\":memory:\")\n```\n\nLet\u2019s drop (aka \u2018delete\u2019) any tables that might exist. These docs also\nserve as a test harness, and we want to make certain we are starting\nwith a clean slate. This also serves as a handy sneak preview of some of\nthe features of this library.\n\n``` python\nfor t in db.tables: t.drop()\n```\n\nUser tables are a handy way to create a useful example with some\nreal-world meaning. To do this, we first instantiate the `users` table\nobject:\n\n``` python\nusers = Table(db, 'Users')\nusers\n```\n\n <Table Users (does not exist yet)>\n\nThe table doesn\u2019t exist yet, so let\u2019s add some columns via the\n`Table.create` method:\n\n``` python\nusers.create(columns=dict(id=int, name=str, age=int))\nusers\n```\n\n <Table Users (id, name, age)>\n\nWhat if we need to change the table structure?\n\nFor example User tables often include things like password field. Let\u2019s\nadd that now by calling `create` again, but this time with\n`transform=True`. We should now see that the `users` table now has the\n`pwd:str` field added.\n\n``` python\nusers.create(columns=dict(id=int, name=str, age=int, pwd=str), transform=True, pk='id')\nusers\n```\n\n <Table Users (id, name, age, pwd)>\n\n``` python\nprint(db.schema)\n```\n\n CREATE TABLE \"Users\" (\n [id] INTEGER PRIMARY KEY,\n [name] TEXT,\n [age] INTEGER,\n [pwd] TEXT\n );\n\n## Queries\n\nLet\u2019s add some users to query:\n\n``` python\nusers.insert(dict(name='Raven', age=8, pwd='s3cret'))\nusers.insert(dict(name='Magpie', age=5, pwd='supersecret'))\nusers.insert(dict(name='Crow', age=12, pwd='verysecret'))\nusers.insert(dict(name='Pigeon', age=3, pwd='keptsecret'))\nusers.insert(dict(name='Eagle', age=7, pwd='s3cr3t'))\n```\n\n <Table Users (id, name, age, pwd)>\n\nA simple unfiltered select can be executed using `rows` property on the\ntable object.\n\n``` python\nusers.rows\n```\n\n <generator object Queryable.rows_where>\n\nLet\u2019s iterate over that generator to see the results:\n\n``` python\n[o for o in users.rows]\n```\n\n [{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},\n {'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},\n {'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'},\n {'id': 4, 'name': 'Pigeon', 'age': 3, 'pwd': 'keptsecret'},\n {'id': 5, 'name': 'Eagle', 'age': 7, 'pwd': 's3cr3t'}]\n\nFiltering can be done via the `rows_where` function:\n\n``` python\n[o for o in users.rows_where('age > 3')]\n```\n\n [{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},\n {'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},\n {'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'},\n {'id': 5, 'name': 'Eagle', 'age': 7, 'pwd': 's3cr3t'}]\n\nWe can also `limit` the results:\n\n``` python\n[o for o in users.rows_where('age > 3', limit=2)]\n```\n\n [{'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'},\n {'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'}]\n\nThe `offset` keyword can be combined with the `limit` keyword.\n\n``` python\n[o for o in users.rows_where('age > 3', limit=2, offset=1)]\n```\n\n [{'id': 2, 'name': 'Magpie', 'age': 5, 'pwd': 'supersecret'},\n {'id': 3, 'name': 'Crow', 'age': 12, 'pwd': 'verysecret'}]\n\nThe `offset` must be used with `limit` or raise a `ValueError`:\n\n``` python\ntry:\n [o for o in users.rows_where(offset=1)]\nexcept ValueError as e:\n print(e)\n```\n\n Cannot use offset without limit\n\n## Transactions\n\nIf you have any SQL calls outside an explicit transaction, they are\ncommitted instantly.\n\nTo group 2 or more queries together into 1 transaction, wrap them in a\nBEGIN and COMMIT, executing ROLLBACK if an exception is caught:\n\n``` python\nusers.get(1)\n```\n\n {'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'}\n\n``` python\ndb.begin()\ntry:\n users.delete([1])\n db.execute('FNOOORD')\n db.commit()\nexcept Exception as e:\n print(e)\n db.rollback()\n```\n\n near \"FNOOORD\": syntax error\n\nBecause the transaction was rolled back, the user was not deleted:\n\n``` python\nusers.get(1)\n```\n\n {'id': 1, 'name': 'Raven', 'age': 8, 'pwd': 's3cret'}\n\nLet\u2019s do it again, but without the DB error, to check the transaction is\nsuccessful:\n\n``` python\ndb.begin()\ntry:\n users.delete([1])\n db.commit()\nexcept Exception as e: db.rollback()\n```\n\n``` python\ntry:\n users.get(1)\n print(\"Delete failed!\")\nexcept: print(\"Delete succeeded!\")\n```\n\n Delete succeeded!\n\n## Differences from sqlite-utils and sqlite-minutils\n\n- WAL is the default\n- Setting `Database(recursive_triggers=False)` works as expected\n- Primary keys must be set on a table for it to be a target of a foreign\n key\n- Errors have been changed minimally, future PRs will change them\n incrementally\n\n## Differences in error handling\n\n| Old/sqlite3/dbapi | New/APSW | Reason |\n|----|----|----|\n| IntegrityError | apsw.ConstraintError | Caused due to SQL transformation blocked on database constraints |\n| sqlite3.dbapi2.OperationalError | apsw.Error | General error, OperationalError is now proxied to apsw.Error |\n| sqlite3.dbapi2.OperationalError | apsw.SQLError | When an error is due to flawed SQL statements |\n| sqlite3.ProgrammingError | apsw.ConnectionClosedError | Caused by an improperly closed database file |\n",
"bugtrack_url": null,
"license": "Apache Software License 2.0",
"summary": "A fork of sqlite-minutils for apsw",
"version": "0.0.1",
"project_urls": {
"Homepage": "https://github.com/AnswerDotAI/apswutils"
},
"split_keywords": [
"nbdev",
"jupyter",
"notebook",
"python",
"sqlite",
"apsw"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "1347ee551148e43db5f0f6a1a709289c78f32f81dcded7207c3a61a0821ee7bf",
"md5": "5af62161dc58b39b7c3aaed3a788f27e",
"sha256": "33671124540651b2f02a5aed74c7f33200e288de082cd22f80d43c0b67ef41d7"
},
"downloads": -1,
"filename": "apswutils-0.0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "5af62161dc58b39b7c3aaed3a788f27e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 80227,
"upload_time": "2024-12-22T00:49:53",
"upload_time_iso_8601": "2024-12-22T00:49:53.116607Z",
"url": "https://files.pythonhosted.org/packages/13/47/ee551148e43db5f0f6a1a709289c78f32f81dcded7207c3a61a0821ee7bf/apswutils-0.0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "17ed7905ea5483769e26b1f222f17d5c0ae77ac9720fc9f28d743474000a7c64",
"md5": "e9f6e623c46786c8d2302ee1013e0f04",
"sha256": "046c2bfc38ca800a6612b0f77ee75198effe5d1350be7c7f1c646708c834d85d"
},
"downloads": -1,
"filename": "apswutils-0.0.1.tar.gz",
"has_sig": false,
"md5_digest": "e9f6e623c46786c8d2302ee1013e0f04",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 50693,
"upload_time": "2024-12-22T00:49:55",
"upload_time_iso_8601": "2024-12-22T00:49:55.967406Z",
"url": "https://files.pythonhosted.org/packages/17/ed/7905ea5483769e26b1f222f17d5c0ae77ac9720fc9f28d743474000a7c64/apswutils-0.0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-12-22 00:49:55",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "AnswerDotAI",
"github_project": "apswutils",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "apswutils"
}
JSON
