# AsyncSQL
AsyncSQL aims to provide simple and efficient way to perform PostgreSQL
requests with aiohttp.
## Install
As python library AsyncSQL may be installed as follows:
```bash
(venv)$ pip install asyncsql
```
## Settings
To connect to PostgreSQL common env vars should use.
For example the following values can be used when develop:
```bash
$ export PGHOST=172.19.0.2 # depends on which IP postgres container is using
$ export PGDATABASE=asyncsql
$ export PGUSER=postgres
$ export PGPASSWORD=xxx
```
By default listing data is paginated. The page size can be specify as follows:
```bash
$ export ASYNCSQL_PER_PAGE=25 # default: 50
```
Folder containing .sql files can be specify as follows:
```bash
$ export ASYNCSQL_SQL_DIR=./tests/data # default: ./sql_files
```
## Migrate
To ease db setup a simple `migrate` command is provided by AsyncSQL.
For example, we can load tests data as follows:
```bash
(venv)$ python -m asyncsql.migrate -d ./tests/data jobs # file-2 file-3
jobs... ok
```
No magic bullet here, files order matters and idempotency too.
## Usage
Let's perform some queries on our `jobs` test table.
First we need to define a `Model` object to work with in our python code:
```python
from datetime import datetime
from typing import Optional
from uuid import UUID
from asyncsql.models import Model
class Job(Model):
id: Optional[UUID] = None
enabled: bool = False
func: str
name: str
created_at: Optional[datetime] = None
updated_at: Optional[datetime] = None
```
Then we need a `Queries` instance:
```python
from asyncsql.queries import Queries
jobs_queries = Queries(
"jobs",
model_cls=Job,
order_fields=("name",)
)
```
Let's connect as follows:
```python
import asyncio
from asyncsql.backends import sql_backend
conn = await sql_backend.conn
```
As the db is empty simple `select` should return an empty list and `has_next`
flag to `False`:
```python
await jobs_queries.select(conn)
# ([], False)
```
Let's insert some data:
```python
for x in range(10):
await jobs_queries.insert(conn, Job(func="ping", name=f"ping-{x}"))
```
We should now have the following data:
```python
[j.name for j in (await jobs_queries.select(conn))[0]]
# ['ping-0',
# 'ping-1',
# 'ping-2',
# 'ping-3',
# 'ping-4',
# 'ping-5',
# 'ping-6',
# 'ping-7',
# 'ping-8',
# 'ping-9']
```
We can limit the result changing the `per_page` value as follows:
```python
jobs_queries.per_page = 3
jobs, has_next = await jobs_queries.select(conn)
[j.name for j in jobs], has_next
# (['ping-0', 'ping-1', 'ping-2'], True)
```
As we would do in an API, we can get the next page with a `Cursor` object
as follows:
```python
from asyncsql.cursor import Cursor
Cursor(fields=("name",), obj=jobs[-1])
# gASV6wAAAAAAAAAojANhc2OUfZQojAJpZJSMJDRlNTM1YTQ4LWJmMjgtMTFlYi05ZDc3LTAyNDJhYzEzMDAwMpSMB2VuYWJsZWSUiYwEZnVuY5SMBHBpbmeUjARuYW1llIwGcGluZy0ylIwKY3JlYXRlZF9hdJSMCGRhdGV0aW1llIwIZGF0ZXRpbWWUk5RDCgflBRsUDycNJDGUaAqMCHRpbWV6b25llJOUaAqMCXRpbWVkZWx0YZSTlEsASwBLAIeUUpSFlFKUhpRSlIwKdXBkYXRlZF9hdJRoDEMKB+UFGxQPJw0kMZRoFYaUUpR1KX2UdJQu
where, values, _ = jobs_queries.get_where_from_cursor(_)
where, values
# ('name >= $1 AND id != $2', ['ping-2', '4e535a48-bf28-11eb-9d77-0242ac130002'])
jobs, has_next = await jobs_queries.select(conn, values=values, where=where)
[j.name for j in jobs], has_next
# (['ping-3', 'ping-4', 'ping-5'], True)
```
`Job` object can be use for update too:
```python
job = (await jobs_queries.select(conn, values=("ping-9",), where="name = $1"))[0][0]
job.id, job.name
# ('4e5692d0-bf28-11eb-9d77-0242ac130002', 'ping-9')
job.name = "ping-x"
new_job = await jobs_queries.update(conn, job)
new_job.name
# ping-x
```
Let's clean this demo:
```python
jobs_queries.per_page = 10
for j in (await jobs_queries.select(conn))[0]:
await jobs_queries.delete_by_id(conn, j.id)
```
## Ideas
- make smaller cursor: serializing the whole object may be an overhead
- work with templated .sql files instead of hard coded `sql` strings in `Queries`
## Contributing
Contribution is welcome. It may be simple but tested as it started.
## License
MIT
Raw data
{
"_id": null,
"home_page": "https://gitlab.com/toopy/asyncsql",
"name": "asyncsql",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": "asyncio, sql",
"author": "Florent Pigout",
"author_email": "florent@toopy.org",
"download_url": "https://files.pythonhosted.org/packages/28/ac/0de2da7d1448477e3f7af6affa0d3a5ff475cda67e8d6400e7703ba650dd/asyncsql-0.1.4.tar.gz",
"platform": null,
"description": "# AsyncSQL\n\nAsyncSQL aims to provide simple and efficient way to perform PostgreSQL\nrequests with aiohttp.\n\n## Install\n\nAs python library AsyncSQL may be installed as follows:\n\n```bash\n(venv)$ pip install asyncsql\n```\n\n## Settings\n\nTo connect to PostgreSQL common env vars should use.\n\nFor example the following values can be used when develop:\n\n```bash\n$ export PGHOST=172.19.0.2 # depends on which IP postgres container is using\n$ export PGDATABASE=asyncsql\n$ export PGUSER=postgres\n$ export PGPASSWORD=xxx\n```\n\nBy default listing data is paginated. The page size can be specify as follows:\n\n```bash\n$ export ASYNCSQL_PER_PAGE=25 # default: 50\n```\n\nFolder containing .sql files can be specify as follows:\n\n```bash\n$ export ASYNCSQL_SQL_DIR=./tests/data # default: ./sql_files\n```\n\n## Migrate\n\nTo ease db setup a simple `migrate` command is provided by AsyncSQL.\n\nFor example, we can load tests data as follows:\n\n```bash\n(venv)$ python -m asyncsql.migrate -d ./tests/data jobs # file-2 file-3\njobs... ok\n```\n\nNo magic bullet here, files order matters and idempotency too.\n\n## Usage\n\nLet's perform some queries on our `jobs` test table.\n\nFirst we need to define a `Model` object to work with in our python code:\n\n```python\nfrom datetime import datetime\nfrom typing import Optional\nfrom uuid import UUID\n\nfrom asyncsql.models import Model\n\nclass Job(Model):\n id: Optional[UUID] = None\n enabled: bool = False\n func: str\n name: str\n created_at: Optional[datetime] = None\n updated_at: Optional[datetime] = None\n```\n\nThen we need a `Queries` instance:\n\n```python\nfrom asyncsql.queries import Queries\n\njobs_queries = Queries(\n \"jobs\",\n model_cls=Job,\n order_fields=(\"name\",)\n)\n```\n\nLet's connect as follows:\n\n```python\nimport asyncio\nfrom asyncsql.backends import sql_backend\n\nconn = await sql_backend.conn\n```\n\nAs the db is empty simple `select` should return an empty list and `has_next`\nflag to `False`:\n\n```python\nawait jobs_queries.select(conn)\n# ([], False)\n```\n\nLet's insert some data:\n\n```python\nfor x in range(10):\n await jobs_queries.insert(conn, Job(func=\"ping\", name=f\"ping-{x}\"))\n```\n\nWe should now have the following data:\n\n```python\n[j.name for j in (await jobs_queries.select(conn))[0]]\n# ['ping-0',\n# 'ping-1',\n# 'ping-2',\n# 'ping-3',\n# 'ping-4',\n# 'ping-5',\n# 'ping-6',\n# 'ping-7',\n# 'ping-8',\n# 'ping-9']\n```\n\nWe can limit the result changing the `per_page` value as follows:\n\n```python\njobs_queries.per_page = 3\n\njobs, has_next = await jobs_queries.select(conn)\n[j.name for j in jobs], has_next \n# (['ping-0', 'ping-1', 'ping-2'], True)\n```\n\nAs we would do in an API, we can get the next page with a `Cursor` object\nas follows:\n\n```python\nfrom asyncsql.cursor import Cursor\nCursor(fields=(\"name\",), obj=jobs[-1])\n# gASV6wAAAAAAAAAojANhc2OUfZQojAJpZJSMJDRlNTM1YTQ4LWJmMjgtMTFlYi05ZDc3LTAyNDJhYzEzMDAwMpSMB2VuYWJsZWSUiYwEZnVuY5SMBHBpbmeUjARuYW1llIwGcGluZy0ylIwKY3JlYXRlZF9hdJSMCGRhdGV0aW1llIwIZGF0ZXRpbWWUk5RDCgflBRsUDycNJDGUaAqMCHRpbWV6b25llJOUaAqMCXRpbWVkZWx0YZSTlEsASwBLAIeUUpSFlFKUhpRSlIwKdXBkYXRlZF9hdJRoDEMKB+UFGxQPJw0kMZRoFYaUUpR1KX2UdJQu\n\nwhere, values, _ = jobs_queries.get_where_from_cursor(_)\nwhere, values\n# ('name >= $1 AND id != $2', ['ping-2', '4e535a48-bf28-11eb-9d77-0242ac130002'])\n\njobs, has_next = await jobs_queries.select(conn, values=values, where=where)\n[j.name for j in jobs], has_next \n# (['ping-3', 'ping-4', 'ping-5'], True)\n```\n\n`Job` object can be use for update too:\n\n```python\njob = (await jobs_queries.select(conn, values=(\"ping-9\",), where=\"name = $1\"))[0][0]\njob.id, job.name\n# ('4e5692d0-bf28-11eb-9d77-0242ac130002', 'ping-9')\n\njob.name = \"ping-x\"\n\nnew_job = await jobs_queries.update(conn, job)\nnew_job.name\n# ping-x\n```\n\nLet's clean this demo:\n\n```python\njobs_queries.per_page = 10\n\nfor j in (await jobs_queries.select(conn))[0]:\n await jobs_queries.delete_by_id(conn, j.id)\n```\n\n## Ideas\n\n- make smaller cursor: serializing the whole object may be an overhead\n- work with templated .sql files instead of hard coded `sql` strings in `Queries`\n\n## Contributing\n\nContribution is welcome. It may be simple but tested as it started.\n\n## License\n\nMIT\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Yet another async SQL lib.",
"version": "0.1.4",
"project_urls": {
"Homepage": "https://gitlab.com/toopy/asyncsql"
},
"split_keywords": [
"asyncio",
" sql"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "522f33ee678117cb31cd4bb5a628c6f8389caea1db30ce824ec41437c6a1c696",
"md5": "3171d7bdd051602b4a882417a5c895bb",
"sha256": "111a42631fcf46069823d9b51b8a74f2a142870aba26f37836db114f90f72e84"
},
"downloads": -1,
"filename": "asyncsql-0.1.4-py2.py3-none-any.whl",
"has_sig": false,
"md5_digest": "3171d7bdd051602b4a882417a5c895bb",
"packagetype": "bdist_wheel",
"python_version": "py2.py3",
"requires_python": null,
"size": 10607,
"upload_time": "2024-09-22T13:14:18",
"upload_time_iso_8601": "2024-09-22T13:14:18.228839Z",
"url": "https://files.pythonhosted.org/packages/52/2f/33ee678117cb31cd4bb5a628c6f8389caea1db30ce824ec41437c6a1c696/asyncsql-0.1.4-py2.py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "28ac0de2da7d1448477e3f7af6affa0d3a5ff475cda67e8d6400e7703ba650dd",
"md5": "5777b1491fd1499049abb3a612b33c8c",
"sha256": "d666e8f68762131a83be2e168a7548d2a89964e2f7a3b9cfe92a6591e9dc4095"
},
"downloads": -1,
"filename": "asyncsql-0.1.4.tar.gz",
"has_sig": false,
"md5_digest": "5777b1491fd1499049abb3a612b33c8c",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 11066,
"upload_time": "2024-09-22T13:14:20",
"upload_time_iso_8601": "2024-09-22T13:14:20.017787Z",
"url": "https://files.pythonhosted.org/packages/28/ac/0de2da7d1448477e3f7af6affa0d3a5ff475cda67e8d6400e7703ba650dd/asyncsql-0.1.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-22 13:14:20",
"github": false,
"gitlab": true,
"bitbucket": false,
"codeberg": false,
"gitlab_user": "toopy",
"gitlab_project": "asyncsql",
"lcname": "asyncsql"
}
JSON