sql_functions.py

import sqlite3
import uuid
from datetime import datetime

def create_database(db_name):
    return sqlite3.connect(f"{db_name}.db")

def create_table(conn, table_name, columns):
    conn.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})")
    conn.commit()

def uuidv7():
    timestamp = int(datetime.now().timestamp() * 1000)
    uuid_bytes = timestamp.to_bytes(6, 'big') + uuid.uuid4().bytes[6:]
    return str(uuid.UUID(bytes=uuid_bytes))

def select(conn, query):
    return conn.execute(query).fetchall()

def select_as(conn, table, columns_dict):
    cols = ', '.join([f"{k} AS {v}" for k, v in columns_dict.items()])
    return conn.execute(f"SELECT {cols} FROM {table}").fetchall()

def left_join(conn, table1, table2, on_condition, columns="*"):
    return conn.execute(f"SELECT {columns} FROM {table1} LEFT JOIN {table2} ON {on_condition}").fetchall()

def right_join(conn, table1, table2, on_condition, columns="*"):
    return conn.execute(f"SELECT {columns} FROM {table1} RIGHT JOIN {table2} ON {on_condition}").fetchall()

# Example usage
if __name__ == "__main__":
    conn = create_database("demo")
    create_table(conn, "users", "id TEXT PRIMARY KEY, name TEXT")
    create_table(conn, "orders", "id TEXT PRIMARY KEY, user_id TEXT, amount REAL")
    
    user_id = uuidv7()
    conn.execute("INSERT INTO users VALUES (?, ?)", (user_id, "Alice"))
    conn.execute("INSERT INTO orders VALUES (?, ?, ?)", (uuidv7(), user_id, 100.0))
    conn.commit()
    
    print("SELECT:", select(conn, "SELECT * FROM users"))
    print("AS:", select_as(conn, "users", {"name": "user_name"}))
    print("LEFT JOIN:", left_join(conn, "users", "orders", "users.id = orders.user_id"))
    
    conn.close()