I'm writing unit-tests with pytest for sqlite database and reporting functions and I need to arrange them by inserting minimal dummy data into required columns. In order to avoid repeating the names of the columns I generate the query and the parameters from the function's signature and inject them into that function as the last two arguments with yet another decorator.
My tests use this fixture eposing database APIs (one example of many)
from typing import Dict, Any
class TempDb:
def __init__(self, connection: sqlite3.Connection):
self.connection = connection
@generate_insert("table_name")
def insert_person(
self,
first_name: str,
last_name: str,
query: str = None,
params: Dict[str, Any] = None
) -> None:
self.connection.execute(query, params)
self.connection.commit()
@pytest.fixture(scope="module")
def temp_db() -> sqlite3.Connection:
"""Creates a test database and initializes it with the schema."""
if os.path.exists(TEST_DB):
os.remove(TEST_DB)
schema.DB_FILE_NAME = TEST_DB # override the default database file-name
with schema.connect() as connection:
schema.create()
yield TempDb(connection)
that are decorated with this function. It uses jinja2 and a template to generate the inserts.
def generate_insert(table: str):
jinja = jinja2.Environment(loader=jinja2.FileSystemLoader(searchpath=r"..\templates"), autoescape=jinja2.select_autoescape())
template = jinja.get_template("insert.jinja2")
def decorate(decoratee):
columns = inspect.getfullargspec(decoratee).args[1:-2] # skip (self, ..., query, params)
@functools.wraps(decoratee)
def decorator(*decoratee_args, **decoratee_kwargs):
values = list(decoratee_args)[1:] # skip (self, ...)
sql = template.render(table=table, columns=columns)
parameters = {c: v for c, v in zip(columns, values)}
decoratee_kwargs["query"] = sql
decoratee_kwargs["params"] = parameters
return decoratee(*decoratee_args, **decoratee_kwargs)
decorator.__signature__ = inspect.signature(decoratee)
return decorator
return decorate
This is how the template is implemented:
insert into {{ table }}(
{% for column in columns -%}
{{ column }}{% if not loop.last %},{% endif %}
{% endfor -%}
)values(
{% for column in columns -%}
:{{ column }}{% if not loop.last %},{% endif %}
{% endfor -%}
)
Is this an efficient way of generating sql queries or can you think of any improvements?
