Privex Python Database Wrappers (privex-db) documentation¶
Welcome to the documentation for Privex’s `Python Database Wrappers`_ - lightweight classes and functions to ease managing and interacting with various relation database systems (RDBMS’s), including SQLite3 and PostgreSQL.
This documentation is automatically kept up to date by ReadTheDocs, as it is automatically re-built each time a new commit is pushed to the Github Project
Contents
Quick install¶
Installing with Pipenv (recommended)
pipenv install privex-db
Installing with standard pip3
pip3 install privex-db
All Documentation¶
Installing Privex Python DB Wrappers¶
Download and install from PyPi using pipenv / pip (recommended)¶
Installing with Pipenv (recommended)
pipenv install privex-db
Installing with standard pip3
pip3 install privex-db
(Alternative) Manual install from Git¶
You may wish to use the alternative installation methods if:
You need a feature / fix from the Git repo which hasn’t yet released as a versioned PyPi package
You need to install privex-db on a system which has no network connection
You don’t trust / can’t access PyPi
For some reason you can’t use
piporpipenv
Option 1 - Use pip to install straight from Github
pip3 install git+https://github.com/Privex/python-db
Option 2 - Clone and install manually
# Clone the repository from Github
git clone https://github.com/Privex/python-db
cd python-db
# RECOMMENDED MANUAL INSTALL METHOD
# Use pip to install the source code
pip3 install .
# ALTERNATIVE MANUAL INSTALL METHOD
# If you don't have pip, or have issues with installing using it, then you can use setuptools instead.
python3 setup.py install
Examples / Basic Usage¶
Using the SQLite3 Manager + Query Builder¶
Basic / direct usage of SqliteWrapper¶
from os.path import expanduser
from typing import List, Tuple
from privex.db import SqliteWrapper
# Open or create the database file ~/.my_app/my_app.db
db = SqliteWrapper(expanduser("~/.my_app/my_app.db"))
# Create the table 'items' and insert some items
db.action("CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);")
db.action("INSERT INTO items (name) VALUES (?);", ["Cardboard Box"])
db.action("INSERT INTO items (name) VALUES (?);", ["Orange"])
db.action("INSERT INTO items (name) VALUES (?);", ["Banana"])
db.action("INSERT INTO items (name) VALUES (?);", ["Stack of Paper"])
item = db.fetchone("SELECT * FROM items WHERE name = ?", ['Orange'])
print(item.id, '-', item.name)
# Output: 2 - Orange
Using the query builder (SqliteQueryBuilder)¶
Once you have an instance of SqliteWrapper, you can easily create query builders via the .builder
function.
Privex-DB query builders work similarly to Django’s ORM, and are very simple to use.
q = db.builder('items')
# Privex QueryBuilder's support chaining similar to Django's ORM
q.select('id', 'name') \ # SELECT id, name
.where('name', 'Orange') \ # WHERE name = 'Orange'
.where_or('name', 'Banana') \ # OR name = 'Banana'
.order('name', 'id') # ORDER BY name, id DESC
# You can either iterate directly over the query builder object
for row in q:
print(f"ID: {row.id} Name: {row.name}")
# Output:
# ID: 3 Name: Banana
# ID: 2 Name: Orange
# Or you can use .fetch / .all to grab a single row, or all rows as a list
item = db.builder('items').where('name', 'Orange').fetch()
# {'id': 2, 'name': 'Orange'}
items = db.builder('items').all()
# [ {'id': 1, 'name': 'Cardboard Box'}, {'id': 2, 'name': 'Orange'}, ... ]
Sub-classing SqliteWrapper for your app¶
To make the most out of the wrapper classes, you’ll want to create a sub-class which is tuned for your application, including the table schemas that your application needs.
NOTE: SqliteWrapper runs in auto-commit mode by default. If you don’t want to use auto-commit, you
can pass isolation_level=XXX to the constructor to choose a custom isolation level without autocommit. See
the Python SQLite3 Docs for more information on isolation modes.
Below is an example of sub-classing SqliteWrapper to create two tables (users and items), with
some custom helper methods, then instantiating the class, inserting some rows, and querying them.
from os.path import expanduser, join
from typing import List, Tuple
from privex.db import SqliteWrapper
class MyDBWrapper(SqliteWrapper):
###
# If a database path isn't specified, then the class attribute DEFAULT_DB will be used.
###
DEFAULT_DB_FOLDER: str = expanduser('~/.my_app')
DEFAULT_DB_NAME: str = 'my_app.db'
DEFAULT_DB: str = join(DEFAULT_DB_FOLDER, DEFAULT_DB_NAME)
###
# The SCHEMAS class attribute contains a list of tuples, with each tuple containing the name of a
# table, as well as the SQL query required to create the table if it doesn't exist.
###
SCHEMAS: List[Tuple[str, str]] = [
('users', "CREATE TABLE users ("
"id INTEGER PRIMARY KEY AUTOINCREMENT, "
"first_name TEXT, "
"last_name TEXT, "
"address TEXT NULL"
");"),
('items', "CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);"),
]
def get_items(self):
# This is an example of a helper method you might want to define, which simply calls
# self.fetchall with a pre-defined SQL query
return self.fetchall("SELECT * FROM items;")
def find_item(self, id: int):
# This is an example of a helper method you might want to define, which simply calls
# self.fetchone with a pre-defined SQL query, and interpolates the 'id' parameter into
# the prepared statement.
return self.fetchone("SELECT * FROM items WHERE id = ?;", [id])
def get_users(self): return self.fetchall("SELECT * FROM users;")
def find_user(self, id: int): return self.fetchall("SELECT * FROM users WHERE id = ?;", [id])
# Once the class is constructed, it should've created the SQLite3 database ~/.my_app/my_app.db (if it didn't exist)
# and then created the tables 'users' and 'items' if they didn't already exist.
db = MyDBWrapper()
# The method .action runs a query, but doesn't attempt to fetch rows, it only returns the affected row count
# Note: By default, SqliteWrapper uses SQLite3 auto-commit mode
db.action("INSERT INTO users (first_name, last_name) VALUES (?, ?);", ['John', 'Doe'])
db.action("INSERT INTO users (first_name, last_name, address) VALUES (?, ?, ?);", ['Jane', 'Doe', '123 Ex St'])
db.action("INSERT INTO users (first_name, last_name) VALUES (?, ?);", ['Dave', 'Johnston'])
db.action("INSERT INTO users (first_name, last_name) VALUES (?, ?);", ['Aaron', 'Johnston'])
users = db.get_users()
for u in users:
print(f"User: ID {u.id} / First Name: {u.first_name} / Last Name: {u.last_name}")
If we then run this example, we get the output:
user@example ~ $ python3 example.py
User: ID 1 / First Name: John / Last Name: Doe
User: ID 2 / First Name: Jane / Last Name: Doe
User: ID 3 / First Name: Dave / Last Name: Johnston
User: ID 4 / First Name: Aaron / Last Name: Johnston
Using the query builder from your sub-class¶
We can also use SqliteQueryBuilder directly from our sub-class, which is a primitive ORM for building
and executing SQL queries.
Let’s build a slightly complex query to show how powerful it is. We’ll build a query to aggregate the number of users who share a given last name AND don’t have an address.
# Get an SqliteQueryBuilder instance for the table 'users'
q = db.builder('users')
# Privex QueryBuilder's support chaining similar to Django's ORM
q \
.select('last_name', 'COUNT(last_name) AS total') \
.where('address', None) \
.group_by('last_name')
print(f"\nQuery:\n\t{q.build_query()}\n")
results = q.all()
for r in results:
print('Result:', r)
If we then run this example, we get the output:
Query:
SELECT last_name, COUNT(last_name) AS total FROM users WHERE address IS NULL GROUP BY last_name;
Result: {'last_name': 'Doe', 'total': 1}
Result: {'last_name': 'Johnston', 'total': 2}
This module contains core functions/classes which are used across the module, as well as abstract classes / types. |
|
This module holds |
|
This module holds |
|
This module holds newly defined types which are used across the module, such as |
|
|
How to use the unit tests¶
This module contains test cases for Privex’s Python Database Wrappers (privex-db).
Testing pre-requisites¶
Ensure you have any mandatory requirements installed (see setup.py’s install_requires)
You should install
pytestto run the tests, it works much better than standard python unittest.You may wish to install any optional requirements listed in README.md for best results
Python 3.7 is recommended at the time of writing this. See README.md in-case this has changed.
For the best testing experience, it’s recommended to install the dev extra, which includes every optional
dependency, as well as development requirements such as pytest , coverage as well as requirements for
building the documentation.
Running via PyTest¶
To run the tests, we strongly recommend using the pytest tool (used by default for our Travis CI):
# Install PyTest if you don't already have it.
user@host: ~/privex-db $ pip3 install pytest
# We recommend adding the option ``-rxXs`` which will show information about why certain tests were skipped
# as well as info on xpass / xfail tests
# You can add `-v` for more detailed output, just like when running the tests directly.
user@host: ~/privex-db $ pytest -rxXs
# NOTE: If you're using a virtualenv, sometimes you may encounter strange conflicts between a global install
# of PyTest, and the virtualenv PyTest, resulting in errors related to packages not being installed.
# A simple workaround is just to call pytest as a module from the python3 executable:
user@host: ~/privex-db $ python3 -m pytest -rxXs
=============================================== test session starts ===============================================
platform darwin -- Python 3.8.0, pytest-5.3.1, py-1.8.0, pluggy-0.13.1
cachedir: .pytest_cache
rootdir: /home/user/privex-db, inifile: pytest.ini
plugins: cov-2.8.1
collected 23 items
tests/test_postgres_builder.py::TestPostgresBuilder::test_all_call SKIPPED [ 4%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_group_call SKIPPED [ 8%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_all SKIPPED [ 13%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_where_call SKIPPED [ 34%]
tests/test_sqlite_builder.py::TestSQLiteBuilder::test_all_call PASSED [ 39%]
tests/test_sqlite_builder.py::TestSQLiteBuilder::test_group_call PASSED [ 43%]
tests/test_sqlite_builder.py::TestSQLiteBuilder::test_query_select_col_where_group PASSED [ 56%]
tests/test_sqlite_builder.py::TestSQLiteBuilder::test_query_where_first_name_last_name PASSED [ 65%]
tests/test_sqlite_builder.py::TestSQLiteBuilder::test_where_call PASSED [ 69%]
tests/test_sqlite_wrapper.py::TestSQLiteWrapper::test_find_user_dict_mode PASSED [ 73%]
tests/test_sqlite_wrapper.py::TestSQLiteWrapper::test_insert_find_user PASSED [ 91%]
tests/test_sqlite_wrapper.py::TestSQLiteWrapper::test_tables_created PASSED [ 95%]
tests/test_sqlite_wrapper.py::TestSQLiteWrapper::test_tables_drop PASSED [100%]
============================================= short test summary info =============================================
SKIPPED [1] tests/test_postgres_builder.py:132: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:159: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:78: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:91: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:116: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:102: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:84: Library 'psycopg2' is not installed...
SKIPPED [1] tests/test_postgres_builder.py:146: Library 'psycopg2' is not installed...
========================================== 15 passed, 8 skipped in 0.13s ==========================================
Running individual test modules¶
Sometimes, you just want to run only a specific test file.
Thankfully, PyTest allows you to run individual test modules like this:
user@host: ~/privex-db $ pytest -rxXs -v tests/test_postgres_builder.py
=============================================== test session starts ===============================================
platform darwin -- Python 3.8.0, pytest-5.3.1, py-1.8.0, pluggy-0.13.1
cachedir: .pytest_cache
rootdir: /home/user/privex-db, inifile: pytest.ini
plugins: cov-2.8.1
collected 8 items
tests/test_postgres_builder.py::TestPostgresBuilder::test_all_call PASSED [ 12%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_group_call PASSED [ 25%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_all PASSED [ 37%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_select_col_where PASSED [ 50%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_select_col_where_group PASSED [ 62%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_select_col_where_order PASSED [ 75%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_query_where_first_name_last_name PASSED [ 87%]
tests/test_postgres_builder.py::TestPostgresBuilder::test_where_call PASSED [100%]
================================================ 8 passed in 0.17s ================================================
Copyright:
+===================================================+
| © 2019 Privex Inc. |
| https://www.privex.io |
+===================================================+
| |
| Privex's Python Database Library |
| License: X11 / MIT |
| |
| Originally Developed by Privex Inc. |
| Core Developer(s): |
| |
| (+) Chris (@someguy123) [Privex] |
| |
+===================================================+
Copyright 2019 Privex Inc. ( https://www.privex.io )
Unit Test List / Overview¶
This file contains code shared between tests, such as |
|
Tests related to |
|
Tests related to |
|
Tests related to |