My essentials for interactively working with sqlite in python.

sqlite_essentials.py

#!/usr/bin/env python
# by: Cody Kochmann
# license: MIT
# created: 2021-06-13

import sqlite3, sys
from sqlite3 import Cursor


''' My essentials for interactively working with sqlite in python. '''


def eprint(*a, **k):
    ''' short hand method to print to stderr '''
    k['file'] = sys.stderr
    return print(*a, **k)

def command_runner(cur):
    ''' returns a run() command that makes running
        commands against a sqlite database more
        interactive from a python shell
    '''
    assert isinstance(cur, Cursor), cur
    def run(*sql):
        if len(sql) > 1:
            for command in sql:
                run(command)
        else:
            assert len(sql) == 1, sql
            command = sql[0]
            assert isinstance(command, str), command
            eprint('|{}\n|{} running {}\n|   {}'.format(
                '-' * 39,
                '-' * int((40-len('running')-3)/2),
                '-' * int((40-len('running')-3)/2),
                command.strip().replace("\n","\n|   ")
            ))
            q = enumerate(cur.execute(command))
            for n, row in q:
                eprint('{}\n|{} output {}'.format(
                    '-' * 40,
                    '-' * int((40-len('output')-3)/2),
                    '-' * int((40-len('output')-3)/2+1)
                ))
                eprint('|', n, end=' | ')
                print(*row)
                break
            for n, row in q:
                eprint('|', n, end=' | ')
                print(*row)
    return run

def sqlite_essentials(path):
    db = sqlite3.connect(path)
    cur = db.cursor()
    run = command_runner(cur)
    return db, cur, run

if __name__ == '__main__':
    
    schema = ['''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    age INTEGER NOT NULL,
    CHECK (age > 0),
    CHECK (age < 120),
    CHECK (length(name) < 256)
)
''', '''
    insert into users (name, age) values ("john", 23)
''', '''
    insert into users (name, age) values ("jane", 22)
''']
    
    #assert __file__.endswith('.py'), __file__
    
    db, cur, run = sqlite_essentials(
        ':memory:'
        #__file__.replace('.py', '.db')
    )
    
    run(*schema)
    
    run('select * from users')