sqlite-query.py

#!/usr/bin/env python3
"""
Run a sqlite query on the given database and print the results using pandas.

Also adds user-defined confidence interval functions to the database.

Usage:
    sqlite-query.py database.db "SELECT * FROM table WHERE column = 'value'"
"""

import sys
import sqlite3
import pandas as pd
from scipy.stats import binomtest


def confidence_interval(successes: int, total: int, p: float = 0.95):
    result = binomtest(successes, total)
    ci = result.proportion_ci(confidence_level=0.95)
    return ci


def confidence_interval_low(successes: int, total: int, p: float = 0.95):
    return confidence_interval(successes, total, p).low


def confidence_interval_high(successes: int, total: int, p: float = 0.95):
    return confidence_interval(successes, total, p).high


def get_dataframe(db, query):
    conn = sqlite3.connect(db)
    conn.create_function("confidence_interval_95_low", 2, confidence_interval_low)
    conn.create_function("confidence_interval_95_high", 2, confidence_interval_high)
    conn.create_function("confidence_interval_low", 3, confidence_interval_low)
    conn.create_function("confidence_interval_high", 3, confidence_interval_high)

    return pd.read_sql_query(query, conn)


if __name__ == "__main__":
    db = sys.argv[1]
    query = sys.argv[2]
    df = get_dataframe(db, query)
    print(df)