Utility to query database in python

query.py

"""The database interface.

Please see schema.sql for the schema of the tables.
"""
from pathlib import Path
import sqlite3
import sys
from . import config

def query(q, parameters=[]):
    """Executes a SQL query and returns all the rows in the result.

        >>> query("select 1 as x, 2 as y from people")
        [{"x": 1, "y": 2}]

    Support you have a table person with columns name and age.

        >>> query("SELECT * FROM person")
        [
            {"name": "alice", "age": 10},
            {"name": "bob", "age": 20},
            {"name": "charlie", "age": 30},
            {"name": "dave", "age": 40}
        ]

    You can pass parameters to use in the query. The parameter will be marked using `?`
    in the query and the corresponding values will be passed as a list parameters.

        >>> query("select * from people WHERE age >= ?", [25])
        [
            {"name": "charlie", "age": 30},
            {"name": "dave", "age": 40}
        ]

    The following query find people with a certain age or more and limits the result to one single row.

        >>> query("select * from people WHERE age >= ? ORDER by age LIMIT ?", [25, 1])
        [
            {"name": "charlie", "age": 30},
        ]

    To insert something:

        >>> query("INSERT INTO table_name (col_one, col_two, col_three) VALUES (?, ?, ?)", [val_one, val_two, val_three])
        ...

    To update something:

        >>> query("UPDATE table_name SET col_one=? WHRE col_two=?", [val_one, val_two])
        ...
    """
    print(f"query: {q} {parameters}", file=sys.stderr)
    with sqlite3.connect(config.db_path) as conn:
        # configure sqlite to return each row as a dict-like object
        conn.row_factory = sqlite3.Row

        # execute the query
        cursor = conn.execute(q, parameters)

        # fetch all the rows and convert each one of them to a dictionary
        return [dict(row) for row in cursor]