sqlite_helper_methods.py

import sqlite3 

db = 'example.sqlite'

def execute(sql, *params):
    with sqlite3.connect(db) as con:
        cur = con.execute(sql, params)
    return cur.rowcount


def execute_query(sql, *params):
    with sqlite3.connect(db) as con:
        results = con.execute(sql, params)
    return results 
    # TODO error handling

# Examples
# Provide 0 or more parameters to execute or execute_query
# The *params argument uses tuple unpacking https://www.geeksforgeeks.org/packing-and-unpacking-arguments-in-python/
# Whatever params are provided when the function is called, are combined into a tuple

execute('CREATE TABLE IF NOT EXISTS trees (name TEXT, height_feet INTEGER)')
execute('INSERT INTO trees VALUES (?, ?)', 'Giant Sequoia', 275)
execute('INSERT INTO trees VALUES (?, ?)', 'California Redwood', 370)
execute('INSERT INTO trees VALUES (?, ?)', 'Bonsai', 1)

all_trees = execute_query('SELECT * FROM trees') 
print('All trees:', all_trees.fetchall())

tall_trees = execute_query('SELECT * FROM trees WHERE height_feet > ?', 100)
print('Tall trees:', tall_trees.fetchall())

rows_deleted = execute('DELETE FROM trees WHERE name LIKE ?', 'Giant Sequoia')
print(f'{rows_deleted=}')   # F-string debugging - show variable name + value by appending =
execute('DROP TABLE trees')