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.
pip install rqdbSynchronous 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 == 1Asynchronous 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 == 1Quickly get a formatted query plan from the current leader for a query:
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><?)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, linearizable and none. You may also indicate the
freshness value at the cursor level.
See Read Consistency for details.
The default consistency is weak.
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.
However you can enable foreign key constraints in rqlite via the command line option -fk. Setting this command line will enable Foreign Key constraints on all connections that rqlite makes to the underlying SQLite database.
Issuing the PRAGMA foreign_keys = boolean command usually results in unpredictable behaviour, since rqlite doesn't offer connection-level control of the underlying SQLite database. It is not recommended.
Substituting "NULL" in parametrized queries can be error-prone. In particular, sqlite needs null sent in a very particular way. In very old versions of rqlite, this wasn't handled well.
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))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.
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
}
)
)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.
It is often helpful to combine this library with a sql builder such as pypika when manipulating complex queries.