fake_sql

Module Path: pt_dev_utility.fake_sql

---

Description

Fake SQL utilities for loading CSV data as database-like records. This module provides functions to load CSV data and return it in formats that simulate SQL query results for Python 3.8+.

---

Functions

load_csv_as_records(csv_file_path: str) -> List[Tuple[Any, ...]]

Description: Load CSV data and return as list of tuples (simulating SQL records).

This function reads a CSV file using pandas and converts each row into a tuple, creating a list structure similar to SQL query results. This is useful for applications that expect SQL-like data structures.

Parameters:

• csv_file_path (str) - Path to the CSV file to be loaded

Returns: - List[Tuple[Any, ...]] - List of tuples where each tuple represents a data row

Raises: - Exception - If CSV loading or processing fails

Features: - Converts CSV rows to tuples (SQL-like records) - Maintains data types from pandas DataFrame - Memory efficient processing - Compatible with SQL result processing code - Handles various CSV formats automatically

Example Usage:

import pt_dev_utility as pdu

# Example 1: Basic usage
records = pdu.load_csv_as_records('employees.csv')
print(records)
# Output: [('John', 30, 'Engineer'), ('Jane', 25, 'Manager'), ('Bob', 35, 'Designer')]

# Example 2: Process like SQL results
records = pdu.load_csv_as_records('sales_data.csv')

# Iterate through records like SQL cursor
for record in records:
    employee_id, name, sales_amount, region = record
    print(f"Employee {name} (ID: {employee_id}) sold ${sales_amount} in {region}")

# Example 3: SQL-like data processing
def process_employee_records(csv_file):
    """Process employee data like SQL results."""
    records = pdu.load_csv_as_records(csv_file)

    # Skip header if needed (first record)
    data_records = records[1:] if records else []

    total_salary = 0
    employee_count = 0

    for record in data_records:
        name, age, department, salary = record
        total_salary += float(salary)
        employee_count += 1

        if age > 30:
            print(f"Senior employee: {name} ({age} years) - ${salary}")

    avg_salary = total_salary / employee_count if employee_count > 0 else 0
    print(f"Average salary: ${avg_salary:.2f}")

    return records

# Usage
employee_data = process_employee_records('employees.csv')

# Example 4: Convert to different formats
records = pdu.load_csv_as_records('products.csv')

# Convert to list of lists
list_data = [list(record) for record in records]
print("As list of lists:", list_data)

# Convert to dictionary (assuming first row is headers)
if records:
    headers = records[0]
    dict_data = []
    for record in records[1:]:
        row_dict = dict(zip(headers, record))
        dict_data.append(row_dict)
    print("As dictionaries:", dict_data)

# Example 5: Database-like operations
def filter_records(records, column_index, value):
    """Filter records by column value (like SQL WHERE clause)."""
    return [record for record in records if record[column_index] == value]

def sort_records(records, column_index, reverse=False):
    """Sort records by column (like SQL ORDER BY)."""
    return sorted(records, key=lambda x: x[column_index], reverse=reverse)

# Load and process data
records = pdu.load_csv_as_records('inventory.csv')

# Filter products with quantity > 100
high_stock = [r for r in records if len(r) > 2 and float(r[2]) > 100]

# Sort by price (assuming price is in column 3)
sorted_by_price = sort_records(records, 3)

print(f"High stock items: {len(high_stock)}")
print(f"Sorted by price: {sorted_by_price[:5]}")  # First 5 items

# Example 6: Error handling
def safe_load_csv_records(file_path):
    """Safely load CSV records with error handling."""
    try:
        records = pdu.load_csv_as_records(file_path)
        print(f"✅ Successfully loaded {len(records)} records from {file_path}")
        return records
    except FileNotFoundError:
        print(f"❌ File not found: {file_path}")
        return []
    except Exception as e:
        print(f"❌ Error loading {file_path}: {e}")
        return []

# Usage with error handling
data = safe_load_csv_records('data/sales.csv')
if data:
    print(f"Processing {len(data)} records...")
    # Process the data

---

Data Type Handling

The function preserves data types as interpreted by pandas:

• Strings - Text data remains as strings
• Numbers - Integers and floats are preserved
• Dates - Date strings remain as strings (use pandas date parsing if needed)
• Missing Values - NaN values are preserved as pandas NaN

Example:

# CSV content: name,age,salary,active
# John,30,50000.5,True
# Jane,25,45000,False

records = load_csv_as_records('employees.csv')
print(records)
# Output: [('name', 'age', 'salary', 'active'),
#          ('John', 30, 50000.5, True),
#          ('Jane', 25, 45000.0, False)]

---

Use Cases

• Legacy System Integration - Convert CSV data for systems expecting SQL-like tuples
• Data Migration - Transform CSV data to match database record formats
• Testing - Create test data that mimics SQL query results
• ETL Processes - Extract CSV data in SQL-compatible format
• Report Processing - Handle CSV reports like database query results
• API Development - Return CSV data in SQL-like format for APIs

---

Comparison with pt_csv

Feature	fake_sql.load_csv_as_records	pt_csv.read_csv_as_dict_list
Output Format	List of tuples	List of dictionaries
SQL Compatibility	High (tuple records)	Low (dict format)
Column Access	By index	By name
Memory Usage	Lower	Higher
Flexibility	Lower	Higher
Use Case	SQL-like processing	General data processing

---

Notes

• Uses pandas internally for robust CSV parsing
• Maintains original data types from pandas DataFrame
• First row typically contains headers (not automatically skipped)
• Tuples are immutable, making them suitable for SQL-like operations
• Memory efficient for large datasets
• Compatible with existing code that expects SQL cursor results
• Handles various CSV formats and encodings automatically