SqliteWrapper¶

class privex.db.sqlite.SqliteWrapper(db: str = None, isolation_level=None, **kwargs)[source]¶

Lightweight wrapper class for interacting with Sqlite3 databases.

Simple direct class usage

>>> db_path = expanduser('~/.my_app/my_db.db')
>>> db = SqliteWrapper(db=db_path)
>>> users = db.fetchall("SELECT * FROM users;")

Usage

Below is an example wrapper class which uses SqliteWrapper as it’s parent class.

It overrides the class attributes DEFAULT_DB_FOLDER, DEFAULT_DB_NAME, and DEFAULT_DB - so that if no database path is passed to MyManager, then the database file path contained in MyManager.DEFAULT_DB will be used as a default.

It also overrides SCHEMAS to define 2 tables (users and items) which will be automatically created when the class is instantiated, unless they already exist.

It adds two methods get_items (returns an iterator

class MyManager(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, name TEXT);"),
        ('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]);

__init__(db: str = None, isolation_level=None, **kwargs)[source]

Parameters

db (str) – Relative / absolute path to SQLite3 database file to use.
isolation_level – Isolation level for SQLite3 connection. Defaults to None (autocommit). See the Python SQLite3 Docs for more information.

Key int db_timeout

Amount of time to wait for any SQLite3 locks to expire before giving up

Key str query_mode

Either 'flat' (query returns tuples) or 'dict' (query returns dicts). More details in PyDoc block under query_mode

Key bool memory_persist

Use a shared in-memory database, which can be accessed by other instances of this class (in this process) - which is cleared after all memory connections are closed. Shortcut for db='file::memory:?cache=shared'

DEFAULT_DB = '/home/docs/.privex_sqlite/privex_sqlite.db'¶: Combined DEFAULT_DB_FOLDER and DEFAULT_DB_NAME used as default absolute path for the sqlite3 database

DEFAULT_DB_FOLDER = '/home/docs/.privex_sqlite'¶: If an absolute path isn’t given, store the sqlite3 database file in this folder

DEFAULT_DB_NAME = 'privex_sqlite.db'¶: If no database is specified to __init__(), then use this (appended to DEFAULT_DB_FOLDER)

property conn¶: Get or create an SQLite3 connection using DB file db and return it

db = None¶: Path to the SQLite3 database for this class instance

insert(_table: str, _cursor: sqlite3.Cursor = None, **fields) → Union[privex.helpers.collections.DictObject, sqlite3.Cursor][source]¶

Builds and executes an insert query into the table _table using the keyword arguments for column names and values.

>>> db = GenericDBWrapper(db='SomeDB')
>>> cur = db.insert('users', first_name='John', last_name='Doe', phone='+1-800-123-4567')
>>> cur.lastrowid
15

Parameters

_table (str) – The table to insert into
_cursor (GenericCursor) – Optionally, specify a cursor to use, instead of the default cursor
fields – Keyword args mapping column names to values

Return DictObject cur

If no custom cursor was specified, the cursor used to execute the query is converted into a DictObject before closing it, then the dict is returned.

Return GenericCursor cur

If a custom cursor (_cursor) was specified, then that cursor will NOT be auto-closed, and the original cursor instance will be returned.

Methods¶

Methods

__init__([db, isolation_level])

param str db: Relative / absolute path to SQLite3 database file to use.

builder(table)

Attributes¶

Attributes

`DEFAULT_DB`	Combined `DEFAULT_DB_FOLDER` and `DEFAULT_DB_NAME` used as default absolute path for the sqlite3 database
`DEFAULT_DB_FOLDER`	If an absolute path isn’t given, store the sqlite3 database file in this folder
`DEFAULT_DB_NAME`	If no database is specified to `__init__()`, then use this (appended to `DEFAULT_DB_FOLDER`)
`DEFAULT_TABLE_LIST_QUERY`
`DEFAULT_TABLE_QUERY`
`conn`	Get or create an SQLite3 connection using DB file `db` and return it