Common helper utility functions used when connecting to sqlite databases and binding SQL values from python variables.
Project description
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))
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
Close
Hashes for utils_jinja_sqlite-0.0.4-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | de4a29b883921a93ca8d94e00bec17aa92671c731c3faa90f1fef25209b6d353 |
|
MD5 | b5809c68c20b82531728820cbcadae70 |
|
BLAKE2b-256 | 7f4e128f8b151bc827dd76e00f16e0509a3c9de495d074972ba220420e34e690 |