Python SQLite Client and Server
Project description
sqlite_rx
Background
SQLite is a lightweight database written in C. The Python programming language has in-built support to interact with the database(locally) which is either stored on disk or in memory.
Introducing sqlite_rx (SQLite remote execution)
With sqlite_rx
, clients should be able to communicate with an SQLiteServer
in a fast, simple and secure manner and execute queries remotely.
Key Features
- Python Client and Server for SQLite database built using ZeroMQ as the transport layer and msgpack for serialization/deserialization.
- Supports authentication using ZeroMQ Authentication Protocol (ZAP)
- Supports encryption using CurveZMQ
- Allows the users to define a generic authorization policy during server startup
Please note that detailed documentation(explaining the configuration options) for both Client and Server is in-progress. Below you can find the steps to quickly get started
Install
Currently, only Python 3 is supported.
pip install sqlite_rx
Examples
Server
SQLiteServer
runs in a single thread and follows an event-driven concurrency model (using tornado's
event loop) which minimizes the cost of concurrent client connections.
from sqlite_rx.server import SQLiteServer
def main():
# database is a path-like object giving the pathname
# of the database file to be opened.
# You can use ":memory:" to open a database connection to a database
# that resides in RAM instead of on disk
server = SQLiteServer(database=":memory:",
bind_address="tcp://127.0.0.1:5000")
try:
server.start()
except KeyboardInterrupt:
server.stop()
if __name__ == '__main__':
main()
Client
SQLiteClient
is a thin client with a single method called execute
The execute
method reacts to the following keyword arguments:
-
execute_many
: True if you want to insert multiple rows with one execute call. -
execute_script
: True if you want to execute a script with multiple SQL commands. -
request_timeout
: Time in ms to wait for a response before retrying. Default is 2500 ms -
retries
: Number of times to retry before abandoning the request. Default is 5
Below are a few examples
Instantiate a client
from sqlite_rx.client import SQLiteClient
client = SQLiteClient(connect_address="tcp://127.0.0.1:5000")
SELECT statement: (Table not present)
from pprint import pprint
result = client.execute("SELECT * FROM IDOLS")
pprint(result)
OUTPUT
{'error': {'message': 'sqlite3.OperationalError: no such table: IDOLS',
'type': 'sqlite3.OperationalError'},
'items': []}
CREATE TABLE statement
result = client.execute("CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)")
pprint(result)
OUTPUT
{'error': None, 'items': []}
INSERT MANY rows
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'XOM', 500, 53.00),
]
result = client.execute("INSERT INTO stocks VALUES (?,?,?,?,?)", *purchases, execute_many=True)
pprint(result)
OUTPUT
{'error': None, 'items': [], 'row_count': 27}
SELECT with WHERE clause
args = ('IBM',)
result = client.execute("SELECT * FROM stocks WHERE symbol = ?", *args)
pprint(result)
OUTPUT
{'error': None,
'items': [['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0],
['2006-03-28', 'BUY', 'IBM', 1000.0, 45.0]]}
Execute a SCRIPT
script = '''CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT);
CREATE TABLE accounts(id INTEGER PRIMARY KEY, description TEXT);
INSERT INTO users(name, phone) VALUES ('John', '5557241'),
('Adam', '5547874'), ('Jack', '5484522');'''
result = client.execute(script, execute_script=True)
pprint(result)
OUTPUT
{'error': None, 'items': []}
Select the rows inserted using the above sql_script
result = client.execute("SELECT * FROM users")
pprint(result)
OUTPUT
{'error': None, 'items': [[2, 'Adam', '5547874'],
[3, 'Jack', '5484522']]}
DROP a TABLE
Note: In the default authorization setting, a client is not allowed to drop any table.
result = client.execute("DROP TABLE stocks")
pprint(result)
OUTPUT
{'error': {'message': 'sqlite3.DatabaseError: not authorized',
'type': 'sqlite3.DatabaseError'},
'items': []}
Generic Default Authorization Policy
DEFAULT_AUTH_CONFIG = {
sqlite3.SQLITE_OK: {
sqlite3.SQLITE_CREATE_INDEX,
sqlite3.SQLITE_CREATE_TABLE,
sqlite3.SQLITE_CREATE_TEMP_INDEX,
sqlite3.SQLITE_CREATE_TEMP_TABLE,
sqlite3.SQLITE_CREATE_TEMP_TRIGGER,
sqlite3.SQLITE_CREATE_TEMP_VIEW,
sqlite3.SQLITE_CREATE_TRIGGER,
sqlite3.SQLITE_CREATE_VIEW,
sqlite3.SQLITE_INSERT,
sqlite3.SQLITE_READ,
sqlite3.SQLITE_SELECT,
sqlite3.SQLITE_TRANSACTION,
sqlite3.SQLITE_UPDATE,
sqlite3.SQLITE_ATTACH,
sqlite3.SQLITE_DETACH,
sqlite3.SQLITE_ALTER_TABLE,
sqlite3.SQLITE_REINDEX,
sqlite3.SQLITE_ANALYZE,
},
sqlite3.SQLITE_DENY: {
sqlite3.SQLITE_DELETE,
sqlite3.SQLITE_DROP_INDEX,
sqlite3.SQLITE_DROP_TABLE,
sqlite3.SQLITE_DROP_TEMP_INDEX,
sqlite3.SQLITE_DROP_TEMP_TABLE,
sqlite3.SQLITE_DROP_TEMP_TRIGGER,
sqlite3.SQLITE_DROP_TEMP_VIEW,
sqlite3.SQLITE_DROP_TRIGGER,
sqlite3.SQLITE_DROP_VIEW,
},
sqlite3.SQLITE_IGNORE: {
sqlite3.SQLITE_PRAGMA
}
}
You can define your own authorization policy in a python dictionary(as shown above) and pass it to the SQLiteServer
class
as auth_config
parameter.
It is recommended you do not override the SQLITE_PRAGMA
action as the database starts in pragma journal_mode=wal
mode
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.
Source Distribution
Built Distribution
Hashes for sqlite_rx-0.9.91-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 1ea557f481021f5dcd2634c1fdb9f22fabf92fd5648b6a6deee6d32350be65de |
|
MD5 | fc683a9ab859a8c29f69e89e6482409f |
|
BLAKE2b-256 | 3ee3c8f42f703be2f4dfef9597e5e89c90965528157cbfb7073c43616f2f7539 |