sqlite3-create-and-check-table 2024.1

Create an SQLite table if missing or check that the existing table matches the given creation statement.

---

title: README author: Jan-Michael Rye

Synopsis

A Python package that provides a simple utility function for creating SQLite tables. The function will compare an existing table's SQL statement from the sqlite_master table to the one expected for a given SQL table creation statement. If the existing table does not match the expected SQL statement then it can either be dropped an recreated, or a ValueError can be raised.

The primary purpose of this function is to facilitate the creation of tables for caching temporary data that can be discarded, such as memoizing calls to remote server APIs.

Links

GitLab

• Homepage
• Source
• Documentation
• Issues
• GitLab package registry

Usage

import sqlite3
from sqlite3_create_and_check_table import create_and_check_table

sql = "CREATE TABLE test (prim TEXT PRIMARY KEY, int INTEGER, blob BLOB NON NULL)"
conn = sqlite3.connect("/path/to/some/sqlite3/db.sqlite")
with conn as cur:
    # Create the table with the given SQL statement. If a table with the same
    # name exists but differs from what would result from the given SQL table
    # creation statement, it will be dropped and recreated to match the given
    # statement.
    # If drop is set to False then a ValueError will be raised instead.
    create_and_check_table(cur, sql, drop=True)

The package also provides a row factory function for returning rows in the table as dicts as well as an sqlite3 cursor context manager for temporarily setting the the row factory to the provided dict factory:

import sqlite3
from sqlite3_create_and_check_table import dict_factory, dict_factory_cursor

conn = sqlite3.connect("/path/to/some/sqlite3/db.sqlite")
# Set the row factory within a context and restore the previous row factory when
# leaving the context.
with dict_factory_cursor(conn) as cur:
    # cur.execute(...)

# Set the row factory for all queries.
conn.row_factory = dict_factory