utils-jinja-sqlite 0.0.4

Common helper utility functions used when connecting to sqlite databases and binding SQL values from python variables.

Utils for Jinja & sqlite

Must setup an .env pointing to the database folder, e.g.:

LOCAL_PATH="code/corpus-db/ph.db"

This will enable creating a connection via setup_connection():

from utils_jinja_sqlite import setup_connection
conn = setup_connection()

Setup Jinja Environment to fetch via .sql file

Create a basic Jinja environment with setup_env():

from utils_jinja_sqlite import setup_env, get_rows, get_row
env = setup_env("corpus_db") # will utilize templates found in corpus_db
params = dict(a="'hello world'")
sql_stmt = env.get_template("test.sql").render(params)
get_rows(sql_stmt=sql_stmt) # will open a connection, yield results of the query
get_row(sql_stmt=sql_stmt) # gets only the first row

Note that this will not work:

for first_stmt_row in get_rows(sql_stmt=sql_stmt1):
    for second_stmt_row in get_rows(sql_stmt=sql_stmt2):
        ... # the first sql_stmt has not yet terminated

SQL string literal binder funcs

Instead of quoting a string literal can use a helper function

from utils_jinja_sqlite import quote_sql_string
params = dict(a=quote_sql_string(hello world))

Cleaner syntax

sqlparse is not included but the following can be used as a template for debugging statements:

import sqlparse
from jinja2 import Environment
from rich.syntax import Syntax


def format_sql(sql_stmt: str) -> Syntax:
    """Helper debug function to display the SQL properly; often used in Jupyter
    notebooks."""
    parsed = sqlparse.format(
        sql_stmt,
        comma_first=True,
        reindent=True,
        wrap_after=60,
    )
    return Syntax(code=parsed, lexer="sql")


def nb_stmt(env: Environment, path: str, params={}):
    """Display Jinja-configured and SQLParse-prettified query statement via
    Rich in Jupyter notebooks."""
    return format_sql(env.get_template(path).render(params))