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

    def insert_person(
            first_name: str,
            last_name: str,
            query: str = None,
            params: Dict[str, Any] = None
    ) -> None:
        self.connection.execute(query, params)

def temp_db() -> sqlite3.Connection:
    """Creates a test database and initializes it with the schema."""

    if os.path.exists(TEST_DB):

    schema.DB_FILE_NAME = TEST_DB  # override the default database file-name

    with schema.connect() as connection:
        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)

        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 -%}
    {% 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?

  • \$\begingroup\$ If you're worried about efficiency, no, this is not efficient. Especially for a test fixture, this seems overdesigned. A good unit test is one where the programmer clearly and explicitly writes out expectations (including queries where applicable), without a lot of autogenerated content. Otherwise, confidence in the test is undermined by the complexity of the test infrastructure. \$\endgroup\$
    – Reinderien
    Mar 26 at 16:20
  • \$\begingroup\$ @Reinderien not a fan of autogenerated stuff? ;-] I actually started with everything typed in by hand, but at the third query I thought I'll go nuts when I one more time repeat the same names three times in each API (signature + dictionary key + dictionary value). \$\endgroup\$
    – t3chb0t
    Mar 26 at 17:07
  • \$\begingroup\$ @Reinderien oh! Sorry, not three times. Five times. I forgot the inserts where you need the same names two more times. This is too much redundancy for my taste. \$\endgroup\$
    – t3chb0t
    Mar 26 at 17:12
  • \$\begingroup\$ I actually am (conditionally) a fan of autogenerated content - in production, particularly; but this is not production, and tests have different pressures than production does. In production you can afford to be clever and in testing you cannot. Tests must be obviously, provably correct, even if it means carrying some redundancy. \$\endgroup\$
    – Reinderien
    Mar 26 at 17:56
  • \$\begingroup\$ Have you considered using something like SQLAlchemy? You'll have less code to maintain (especially if you already have tables - sqlalchemy can automap your database tables to classes), and it will be portable across different SQL dialects. \$\endgroup\$
    – Blackhawk
    Mar 27 at 21:56


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.