# rqdb
This is an unofficial python client for [rqlite](https://github.com/rqlite/rqlite), a
lightweight distributed relational database based on SQLite.
This client supports SQLite syntax, true parameterized queries, and a
calling syntax reminiscent of DB API 2.0.
Furthermore, this client has convenient asynchronous methods which match
the underlying rqlite API.
## Installation
```py
pip install rqdb
```
## Usage
Synchronous queries:
```py
import rqdb
import secrets
conn = rqdb.connect(['127.0.0.1:4001'])
cursor = conn.cursor()
cursor.execute('CREATE TABLE persons (id INTEGER PRIMARY KEY, uid TEXT UNIQUE NOT NULL, name TEXT NOT NULL)')
cursor.execute('CREATE TABLE pets (id INTEGER PRIMARY KEY, name TEXT NOT NULL, owner_id INTEGER NOT NULL REFERENCES persons(id) ON DELETE CASCADE)')
# standard execute
cursor.execute('INSERT INTO persons (uid, name) VALUES (?, ?)', (secrets.token_hex(8), 'Jane Doe'))
assert cursor.rows_affected == 1
# The following is stored in a single Raft entry and executed within a transaction.
person_name = 'John Doe'
person_uid = secrets.token_urlsafe(16)
pet_name = 'Fido'
result = cursor.executemany3((
(
'INSERT INTO persons (uid, name) VALUES (?, ?)',
(person_uid, person_name)
),
(
'INSERT INTO pets (name, owner_id) '
'SELECT'
' ?, persons.id '
'FROM persons '
'WHERE uid = ?',
(pet_name, person_uid)
)
)).raise_on_error()
assert result[0].rows_affected == 1
assert result[1].rows_affected == 1
```
Asynchronous queries:
```py
import rqdb
import secrets
async def main():
async with rqdb.connect_async(['127.0.0.1:4001']) as conn:
cursor = conn.cursor()
result = await cursor.execute(
'INSERT INTO persons (uid, name) VALUES (?, ?)',
(secrets.token_hex(8), 'Jane Doe')
)
assert result.rows_affected == 1
```
## Additional Features
### Explain
Quickly get a formatted query plan from the current leader for a query:
```py
import rqdb
conn = rqdb.connect(['127.0.0.1:4001'])
cursor = conn.cursor()
cursor.execute('CREATE TABLE persons (id INTEGER PRIMARY KEY, uid TEXT UNIQUE NOT NULL, given_name TEXT NOT NULL, family_name TEXT NOT NULL)')
cursor.explain("SELECT id FROM persons WHERE TRIM(given_name || ' ' || family_name) LIKE ?", ('john d%',), out='print')
# --SCAN persons
cursor.execute("CREATE INDEX persons_name_idx ON persons(TRIM(given_name || ' ' || family_name) COLLATE NOCASE)")
cursor.explain("SELECT id FROM persons WHERE TRIM(given_name || ' ' || family_name) LIKE ?", ('john d%',), out='print')
# --SEARCH persons USING INDEX persons_name_idx (<expr>>? AND <expr><?)
```
### Read Consistency
Selecting read consistency is done at the cursor level, either by passing
`read_consistency` to the cursor constructor (`conn.cursor()`) or by setting
the instance variable `read_consistency` directly. The available consistencies
are `strong`, `weak`, and `none`. You may also indicate the `freshness` value
at the cursor level.
See [CONSISTENCY.md](https://github.com/rqlite/rqlite/blob/master/DOC/CONSISTENCY.md) for
details.
The default consistency is `weak`.
### Foreign Keys
Foreign key support in rqlite is disabled by default, to match sqlite. This is a common source
of confusion. It cannot be configured by the client reliably. Foreign key support
is enabled as described in
[FOREIGN_KEY_CONSTRAINTS.md](https://github.com/rqlite/rqlite/blob/master/DOC/FOREIGN_KEY_CONSTRAINTS.md)
### Nulls
Substituting "NULL" in parametrized queries can be error-prone. In particular,
sqlite needs null sent in a very particular way, which the rqlite server has
historically not handled properly.
By default, if you attempt to use "None" as a parameter to a query, this package
will perform string substition with the value "NULL" in the correct spot. Be
careful however - you will still need to handle nulls properly in the query,
since "col = NULL" and "col IS NULL" are not the same. In particular, `NULL = NULL`
is `NULL`, which evaluates to false. One way this could be handled is
```py
name: Optional[str] = None
# never matches a row since name is None, even if the rows name is null
cursor.execute('SELECT * FROM persons WHERE name = ?', (name,))
# works as expected
cursor.execute('SELECT * FROM persons WHERE ((? IS NULL AND name IS NULL) OR name = ?)', (name, name))
```
### Backup
Backups can be initiated using `conn.backup(filepath: str, raw: bool = False)`.
The download will be streamed to the given filepath. Both the sql format and a
compressed sqlite format are supported.
### Logging
By default this will log using the standard `logging` module. This can be disabled
using `log=False` in the `connect` call. If logging is desired but just needs to be
configured slightly, it can be done as follows:
```py
import rqdb
import logging
conn = rqdb.connect(
['127.0.0.1:4001'],
log=rqdb.LogConfig(
# Started a SELECT query
read_start={
'enabled': True,
'level': logging.DEBUG, # alternatively, 'method': logging.debug
},
# Started a UPDATE/INSERT query
write_start={
'enabled': True,
'level': logging.DEBUG,
},
# Got the response from the database for a SELECT query
read_response={
'enabled': True,
'level': logging.DEBUG,,
'max_length': 1024, # limits how much of the response we log
},
# Got the response from the database for a UPDATE/INSERT query
write_response={
'enabled': True,
'level': logging.DEBUG,
},
# Failed to connect to one of the nodes.
connect_timeout={
'enabled': True,
'level': logging.WARNING,
},
# Failed to connect to any node for a query
hosts_exhausted={
'enabled': True,
'level': logging.CRITICAL,
},
# The node returned a status code other than 200-299 or
# a redirect when a redirect is allowed.
non_ok_response={
'enabled': True,
'level': logging.WARNING
}
)
)
```
## Limitations
### Slow Transactions
The primary limitations is that by the connectionless nature of rqlite, while
transactions are possible, the entire transaction must be specified upfront.
That is, you cannot open a transaction, perform a query, and then use the
result of that query to perform another query before closing the transaction.
This can also be seen as a blessing, as these types of transactions are the most
common source of performance issues in traditional applications. They require
long-held locks that can easily lead to N^2 performance. The same behavior can
almost always be achieved with uids, as shown in the example. The repeated UID
lookup causes a consistent overhead, which is highly preferable to the
unpredictable negative feedback loop nature of long transactions.
## Other Notes
It is often helpful to combine this library with a sql builder such
as [pypika](https://pypika.readthedocs.io/en/latest/) when manipulating
complex queries.
Raw data
{
"_id": null,
"home_page": "https://github.com/tjstretchalot/rqdb",
"name": "rqdb",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Timothy Moore",
"author_email": "mtimothy984@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/fe/90/1e70b5d3df8cb1aeb5770d8dbf8d5506f9bdff43128455144dbe4d0b67ec/rqdb-1.6.0.tar.gz",
"platform": null,
"description": "# rqdb\r\n\r\nThis is an unofficial python client for [rqlite](https://github.com/rqlite/rqlite), a\r\nlightweight distributed relational database based on SQLite.\r\n\r\nThis client supports SQLite syntax, true parameterized queries, and a\r\ncalling syntax reminiscent of DB API 2.0.\r\n\r\nFurthermore, this client has convenient asynchronous methods which match\r\nthe underlying rqlite API.\r\n\r\n## Installation\r\n\r\n```py\r\npip install rqdb\r\n```\r\n\r\n## Usage\r\n\r\nSynchronous queries:\r\n\r\n```py\r\nimport rqdb\r\nimport secrets\r\n\r\nconn = rqdb.connect(['127.0.0.1:4001'])\r\ncursor = conn.cursor()\r\ncursor.execute('CREATE TABLE persons (id INTEGER PRIMARY KEY, uid TEXT UNIQUE NOT NULL, name TEXT NOT NULL)')\r\ncursor.execute('CREATE TABLE pets (id INTEGER PRIMARY KEY, name TEXT NOT NULL, owner_id INTEGER NOT NULL REFERENCES persons(id) ON DELETE CASCADE)')\r\n\r\n# standard execute\r\ncursor.execute('INSERT INTO persons (uid, name) VALUES (?, ?)', (secrets.token_hex(8), 'Jane Doe'))\r\nassert cursor.rows_affected == 1\r\n\r\n# The following is stored in a single Raft entry and executed within a transaction.\r\n\r\nperson_name = 'John Doe'\r\nperson_uid = secrets.token_urlsafe(16)\r\npet_name = 'Fido'\r\nresult = cursor.executemany3((\r\n (\r\n 'INSERT INTO persons (uid, name) VALUES (?, ?)',\r\n (person_uid, person_name)\r\n ),\r\n (\r\n 'INSERT INTO pets (name, owner_id) '\r\n 'SELECT'\r\n ' ?, persons.id '\r\n 'FROM persons '\r\n 'WHERE uid = ?',\r\n (pet_name, person_uid)\r\n )\r\n)).raise_on_error()\r\nassert result[0].rows_affected == 1\r\nassert result[1].rows_affected == 1\r\n```\r\n\r\nAsynchronous queries:\r\n\r\n```py\r\nimport rqdb\r\nimport secrets\r\n\r\nasync def main():\r\n async with rqdb.connect_async(['127.0.0.1:4001']) as conn:\r\n cursor = conn.cursor()\r\n\r\n result = await cursor.execute(\r\n 'INSERT INTO persons (uid, name) VALUES (?, ?)',\r\n (secrets.token_hex(8), 'Jane Doe')\r\n )\r\n assert result.rows_affected == 1\r\n```\r\n\r\n## Additional Features\r\n\r\n### Explain\r\n\r\nQuickly get a formatted query plan from the current leader for a query:\r\n\r\n```py\r\nimport rqdb\r\n\r\nconn = rqdb.connect(['127.0.0.1:4001'])\r\ncursor = conn.cursor()\r\ncursor.execute('CREATE TABLE persons (id INTEGER PRIMARY KEY, uid TEXT UNIQUE NOT NULL, given_name TEXT NOT NULL, family_name TEXT NOT NULL)')\r\ncursor.explain(\"SELECT id FROM persons WHERE TRIM(given_name || ' ' || family_name) LIKE ?\", ('john d%',), out='print')\r\n# --SCAN persons\r\ncursor.execute(\"CREATE INDEX persons_name_idx ON persons(TRIM(given_name || ' ' || family_name) COLLATE NOCASE)\")\r\ncursor.explain(\"SELECT id FROM persons WHERE TRIM(given_name || ' ' || family_name) LIKE ?\", ('john d%',), out='print')\r\n# --SEARCH persons USING INDEX persons_name_idx (<expr>>? AND <expr><?)\r\n```\r\n\r\n### Read Consistency\r\n\r\nSelecting read consistency is done at the cursor level, either by passing\r\n`read_consistency` to the cursor constructor (`conn.cursor()`) or by setting\r\nthe instance variable `read_consistency` directly. The available consistencies\r\nare `strong`, `weak`, and `none`. You may also indicate the `freshness` value\r\nat the cursor level.\r\n\r\nSee [CONSISTENCY.md](https://github.com/rqlite/rqlite/blob/master/DOC/CONSISTENCY.md) for\r\ndetails.\r\n\r\nThe default consistency is `weak`.\r\n\r\n### Foreign Keys\r\n\r\nForeign key support in rqlite is disabled by default, to match sqlite. This is a common source\r\nof confusion. It cannot be configured by the client reliably. Foreign key support\r\nis enabled as described in\r\n[FOREIGN_KEY_CONSTRAINTS.md](https://github.com/rqlite/rqlite/blob/master/DOC/FOREIGN_KEY_CONSTRAINTS.md)\r\n\r\n### Nulls\r\n\r\nSubstituting \"NULL\" in parametrized queries can be error-prone. In particular,\r\nsqlite needs null sent in a very particular way, which the rqlite server has\r\nhistorically not handled properly.\r\n\r\nBy default, if you attempt to use \"None\" as a parameter to a query, this package\r\nwill perform string substition with the value \"NULL\" in the correct spot. Be\r\ncareful however - you will still need to handle nulls properly in the query,\r\nsince \"col = NULL\" and \"col IS NULL\" are not the same. In particular, `NULL = NULL`\r\nis `NULL`, which evaluates to false. One way this could be handled is\r\n\r\n```py\r\nname: Optional[str] = None\r\n\r\n# never matches a row since name is None, even if the rows name is null\r\ncursor.execute('SELECT * FROM persons WHERE name = ?', (name,))\r\n\r\n# works as expected\r\ncursor.execute('SELECT * FROM persons WHERE ((? IS NULL AND name IS NULL) OR name = ?)', (name, name))\r\n```\r\n\r\n### Backup\r\n\r\nBackups can be initiated using `conn.backup(filepath: str, raw: bool = False)`.\r\nThe download will be streamed to the given filepath. Both the sql format and a\r\ncompressed sqlite format are supported.\r\n\r\n### Logging\r\n\r\nBy default this will log using the standard `logging` module. This can be disabled\r\nusing `log=False` in the `connect` call. If logging is desired but just needs to be\r\nconfigured slightly, it can be done as follows:\r\n\r\n```py\r\nimport rqdb\r\nimport logging\r\n\r\nconn = rqdb.connect(\r\n ['127.0.0.1:4001'],\r\n log=rqdb.LogConfig(\r\n # Started a SELECT query\r\n read_start={\r\n 'enabled': True,\r\n 'level': logging.DEBUG, # alternatively, 'method': logging.debug\r\n },\r\n\r\n # Started a UPDATE/INSERT query\r\n write_start={\r\n 'enabled': True,\r\n 'level': logging.DEBUG,\r\n },\r\n\r\n # Got the response from the database for a SELECT query\r\n read_response={\r\n 'enabled': True,\r\n 'level': logging.DEBUG,,\r\n 'max_length': 1024, # limits how much of the response we log\r\n },\r\n\r\n # Got the response from the database for a UPDATE/INSERT query\r\n write_response={\r\n 'enabled': True,\r\n 'level': logging.DEBUG,\r\n },\r\n\r\n # Failed to connect to one of the nodes.\r\n connect_timeout={\r\n 'enabled': True,\r\n 'level': logging.WARNING,\r\n },\r\n\r\n # Failed to connect to any node for a query\r\n hosts_exhausted={\r\n 'enabled': True,\r\n 'level': logging.CRITICAL,\r\n },\r\n\r\n # The node returned a status code other than 200-299 or\r\n # a redirect when a redirect is allowed.\r\n non_ok_response={\r\n 'enabled': True,\r\n 'level': logging.WARNING\r\n }\r\n )\r\n)\r\n```\r\n\r\n## Limitations\r\n\r\n### Slow Transactions\r\n\r\nThe primary limitations is that by the connectionless nature of rqlite, while\r\ntransactions are possible, the entire transaction must be specified upfront.\r\nThat is, you cannot open a transaction, perform a query, and then use the\r\nresult of that query to perform another query before closing the transaction.\r\n\r\nThis can also be seen as a blessing, as these types of transactions are the most\r\ncommon source of performance issues in traditional applications. They require\r\nlong-held locks that can easily lead to N^2 performance. The same behavior can\r\nalmost always be achieved with uids, as shown in the example. The repeated UID\r\nlookup causes a consistent overhead, which is highly preferable to the\r\nunpredictable negative feedback loop nature of long transactions.\r\n\r\n## Other Notes\r\n\r\nIt is often helpful to combine this library with a sql builder such\r\nas [pypika](https://pypika.readthedocs.io/en/latest/) when manipulating\r\ncomplex queries.\r\n",
"bugtrack_url": null,
"license": "",
"summary": "An unofficial python client for RQLite",
"version": "1.6.0",
"project_urls": {
"Homepage": "https://github.com/tjstretchalot/rqdb"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "fe901e70b5d3df8cb1aeb5770d8dbf8d5506f9bdff43128455144dbe4d0b67ec",
"md5": "6ec433507e606499033a885e90587179",
"sha256": "0cc20ac72504d3f902ebbb02399269f278fa95f48a4029cc4a0f8afce81dcdab"
},
"downloads": -1,
"filename": "rqdb-1.6.0.tar.gz",
"has_sig": false,
"md5_digest": "6ec433507e606499033a885e90587179",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 32143,
"upload_time": "2024-01-30T14:45:40",
"upload_time_iso_8601": "2024-01-30T14:45:40.355633Z",
"url": "https://files.pythonhosted.org/packages/fe/90/1e70b5d3df8cb1aeb5770d8dbf8d5506f9bdff43128455144dbe4d0b67ec/rqdb-1.6.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-01-30 14:45:40",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "tjstretchalot",
"github_project": "rqdb",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"requirements": [],
"lcname": "rqdb"
}
JSON
