An unofficial python client for RQLite
Project description
rqdb
This is an unofficial python client for 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
pip install rqdb
Usage
Synchronous queries:
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:
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
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 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
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
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:
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 when manipulating complex queries.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.