When I work on a data mining project, I often skip the capabilities of pandas or sqlalchemy and start dealing with a simple sqlite database myself. This is not very convenient and simple details can lead to distraction: “Is this how I connect to sqlite?”, “How can I extract the data from the result set in less LOC?”, “Why is the database locked?”.

Here is a simple recipe. It works well when I’m working with Jupyter Notebooks. Maybe I write later about the details. No time now. There’s data waiting to be harvested.

import sqlite3
from contextlib import contextmanager

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

@contextmanager
def db_ops(dbname):
    conn = sqlite3.connect(dbname)
    conn.row_factory = dict_factory
    cur = conn.cursor()
    try:
        yield cur
    except Exception as e:
        print('Some error obscured: ', e)

    conn.commit()
    conn.close()

def fetchall(dbname, sql, params=[]):
    with db_ops(dbname) as cur:
        result = cur.execute(sql, params)
        rows = result.fetchall()
    return rows