[FEATURE] Add `csv` download for Jupyter notebook cells

[jeffreyaven]

Feature Description
Add an argument to the StackQL cell magic command (%%stackql) to enable a download link to download a resultant dataframe from a StackQL query as a csv file

Example(s)
Add an argument to the StackQL cell magic, --csv-download, defaults to False, when True a download link is added to the output cell (only works when --no-display is NOT set - default)

[jeffreyaven]

@claude implement this feature based on the issue description

[claude]

Claude finished @jeffreyaven's task in 4m 22s —— View job • claude/issue-18-20250611_232508 • Create PR ➔

---

CSV Download Feature Implementation Complete! ✅

I've successfully implemented the CSV download functionality for the StackQL Jupyter cell magic command.

Todo List:

• Analyze codebase structure and locate StackQL cell magic implementation
• Read repository guidelines (CLAUDE.md)
• Understand current argument parsing and display logic
• Add --csv-download argument to cell magic
• Implement CSV generation and download link functionality
• Ensure compatibility with --no-display logic
• Test the implementation
• Run linting/testing if available
• Commit changes and create PR

Implementation Summary

New Feature: Added --csv-download argument to the %%stackql cell magic command.

Usage:

%%stackql --csv-download
SELECT * FROM your_stackql_query

Key Features:
✅ Both Modes Supported: Works in both local and server modes
✅ Conditional Display: Only works when --no-display is NOT set (default behavior)
✅ Styled Download Button: Clean, styled download link appears below query results
✅ Error Handling: Graceful error handling if CSV generation fails
✅ Standard CSV Format: Uses pandas to_csv() with index=False
✅ Data URI Implementation: Uses base64-encoded data URIs for browser compatibility

Files Modified:

• pystackql/magic_ext/local.py - Local mode magic implementation
• pystackql/magic_ext/server.py - Server mode magic implementation

How it Works:

1. User runs %%stackql --csv-download with their query
2. Query executes and returns DataFrame as usual
3. DataFrame is displayed in the notebook
4. A styled download button appears below the results
5. Clicking the button downloads stackql_results.csv file

---

[jeffreyaven]

@claude what about some server and local tests for https://github.com/stackql/pystackql/tree/claude/issue-18-20250611_232508 before i raise a PR

[claude]

Claude finished @jeffreyaven's task in 5m 15s —— View job • claude/issue-18-20250611_233218 • Create PR ➔

---

CSV Download Feature Implementation and Tests Complete! ✅

Todo List:

• Analyze current codebase and existing test structure
• Check if CSV download feature exists on current branch
• Implement CSV download feature in local.py (missing from current branch)
• Implement CSV download feature in server.py (missing from current branch)
• Write tests for local mode CSV download functionality
• Write tests for server mode CSV download functionality
• Commit changes to the current branch

Implementation Summary

CSV Download Feature Implemented:

✅ Local Mode: Added --csv-download argument to pystackql/magic_ext/local.py
✅ Server Mode: Added --csv-download argument to pystackql/magic_ext/server.py
✅ Base64 Data URIs: Uses base64-encoded CSV data for browser compatibility
✅ Styled Download Button: Clean, styled download button with hover effects
✅ Error Handling: Graceful error handling if CSV generation fails
✅ Conditional Display: Only works when --no-display is NOT set

Comprehensive Tests Added:

✅ Local Mode Tests (tests/test_magic.py):

• test_cell_magic_query_csv_download() - Tests CSV download functionality
• test_cell_magic_query_csv_download_with_no_display() - Tests precedence of --no-display
• test_display_with_csv_download_method() - Tests CSV generation and HTML creation
• test_display_with_csv_download_error_handling() - Tests error handling

✅ Server Mode Tests (tests/test_server_magic.py):

• test_cell_magic_query_csv_download() - Tests CSV download functionality
• test_cell_magic_query_csv_download_with_no_display() - Tests precedence of --no-display
• test_display_with_csv_download_method() - Tests CSV generation and HTML creation
• test_display_with_csv_download_error_handling() - Tests error handling

Test Coverage Includes:

• ✅ CSV download argument parsing
• ✅ Download link generation with proper base64 encoding
• ✅ HTML styling and button creation
• ✅ --no-display precedence behavior
• ✅ Error handling for CSV generation failures
• ✅ Both local and server mode implementations

Usage Example:

%%stackql --csv-download
SELECT * FROM your_stackql_query

The feature is now ready for testing and includes comprehensive test coverage for both local and server modes.

--- • claude/issue-18-20250611_233218

[jeffreyaven]

@claude tests failed see below:

===== PyStackQL Test Runner =====

============================= test session starts ==============================
platform linux -- Python 3.9.22, pytest-8.4.0, pluggy-1.6.0 -- /opt/hostedtoolcache/Python/3.9.22/x64/bin/python3
cachedir: .pytest_cache
rootdir: /home/runner/work/pystackql/pystackql
configfile: pyproject.toml
plugins: cov-6.2.0
collecting ... collected 37 items

tests/test_core.py::TestStackQLCore::test_properties_method PASSED       [  2%]
tests/test_core.py::TestStackQLCore::test_version_attribute PASSED       [  5%]
tests/test_core.py::TestStackQLCore::test_package_version_attribute PASSED [  8%]
tests/test_core.py::TestStackQLCore::test_platform_attribute PASSED      [ 10%]
tests/test_core.py::TestStackQLCore::test_bin_path_attribute PASSED      [ 13%]
tests/test_core.py::TestStackQLCore::test_custom_download_dir PASSED     [ 16%]
tests/test_core.py::TestStackQLCore::test_upgrade_method SKIPPED (Sk...) [ 18%]
tests/test_query_execution.py::TestQueryExecution::test_execute_literal_int PASSED [ 21%]
tests/test_query_execution.py::TestQueryExecution::test_execute_literal_float PASSED [ 24%]
tests/test_query_execution.py::TestQueryExecution::test_execute_literal_string PASSED [ 27%]
tests/test_query_execution.py::TestQueryExecution::test_execute_expression_true PASSED [ 29%]
tests/test_query_execution.py::TestQueryExecution::test_execute_expression_false PASSED [ 32%]
tests/test_query_execution.py::TestQueryExecution::test_execute_empty_result PASSED [ 35%]
tests/test_query_execution.py::TestQueryExecution::test_execute_json_extract PASSED [ 37%]
tests/test_query_execution.py::TestQueryExecution::test_execute_homebrew_formula PASSED [ 40%]
tests/test_query_execution.py::TestQueryExecution::test_execute_homebrew_metrics PASSED [ 43%]
tests/test_query_execution.py::TestQueryExecution::test_execute_stmt_registry_pull PASSED [ 45%]
tests/test_output_formats.py::TestOutputFormats::test_dict_output_format PASSED [ 48%]
tests/test_output_formats.py::TestOutputFormats::test_pandas_output_format PASSED [ 51%]
tests/test_output_formats.py::TestOutputFormats::test_pandas_output_with_numeric_types PASSED [ 54%]
tests/test_output_formats.py::TestOutputFormats::test_csv_output_format PASSED [ 56%]
tests/test_output_formats.py::TestOutputFormats::test_csv_output_with_pipe_separator PASSED [ 59%]
tests/test_output_formats.py::TestOutputFormats::test_csv_output_with_header PASSED [ 62%]
tests/test_output_formats.py::TestOutputFormats::test_csv_output_without_header PASSED [ 64%]
tests/test_output_formats.py::TestOutputFormats::test_invalid_output_format PASSED [ 67%]
tests/test_output_formats.py::TestOutputFormats::test_csv_output_in_server_mode PASSED [ 70%]
tests/test_magic.py::TestStackQLMagic::test_line_magic_query PASSED      [ 72%]
tests/test_magic.py::TestStackQLMagic::test_cell_magic_query PASSED      [ 75%]
tests/test_magic.py::TestStackQLMagic::test_cell_magic_query_no_display PASSED [ 78%]
tests/test_magic.py::TestStackQLMagic::test_cell_magic_query_csv_download PASSED [ 81%]
tests/test_magic.py::TestStackQLMagic::test_cell_magic_query_csv_download_with_no_display PASSED [ 83%]
tests/test_magic.py::TestStackQLMagic::test_display_with_csv_download_method FAILED [ 86%]
tests/test_magic.py::TestStackQLMagic::test_display_with_csv_download_error_handling FAILED [ 89%]
tests/test_magic.py::test_magic_extension_loading PASSED                 [ 91%]
tests/test_async.py::TestAsyncFunctionality::test_execute_queries_async_dict_output PASSED [ 94%]
tests/test_async.py::TestAsyncFunctionality::test_execute_queries_async_pandas_output PASSED [ 97%]
tests/test_async.py::TestAsyncFunctionality::test_execute_queries_async_csv_output PASSED [100%]

=================================== FAILURES ===================================
____________ TestStackQLMagic.test_display_with_csv_download_method ____________

self = <tests.test_magic.TestStackQLMagic object at 0x7f[15](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:16)c1942220>

    def test_display_with_csv_download_method(self):
        """Test the _display_with_csv_download method directly."""
        import base64
        from unittest.mock import patch
    
        # Create a test DataFrame
        test_df = pd.DataFrame({"col1": [1, 2], "col2": ["a", "b"]})
    
        # Mock IPython display functionality
>       with patch('pystackql.magic_ext.local.display') as mock_display, \
             patch('pystackql.magic_ext.local.HTML') as mock_html:

tests/test_magic.py:[17](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:18)0: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
/opt/hostedtoolcache/Python/3.9.22/x64/lib/python3.9/unittest/mock.py:1404: in __enter__
    original, local = self.get_original()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <unittest.mock._patch object at 0x7f15c[18](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:19)81be0>

    def get_original(self):
        target = self.getter()
        name = self.attribute
    
        original = DEFAULT
        local = False
    
        try:
            original = target.__dict__[name]
        except (AttributeError, KeyError):
            original = getattr(target, name, DEFAULT)
        else:
            local = True
    
        if name in _builtins and isinstance(target, ModuleType):
            self.create = True
    
        if not self.create and original is DEFAULT:
>           raise AttributeError(
                "%s does not have the attribute %r" % (target, name)
            )
E           AttributeError: <module 'pystackql.magic_ext.local' from '/home/runner/work/pystackql/pystackql/pystackql/magic_ext/local.py'> does not have the attribute 'display'

/opt/hostedtoolcache/Python/3.9.22/x64/lib/python3.9/unittest/mock.py:1377: AttributeError
________ TestStackQLMagic.test_display_with_csv_download_error_handling ________

self = <tests.test_magic.TestStackQLMagic object at 0x7f15c[19](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:20)42df0>

    def test_display_with_csv_download_error_handling(self):
        """Test error handling in _display_with_csv_download method."""
        from unittest.mock import patch
    
        # Create a mock DataFrame that will raise an exception during to_csv()
        mock_df = MagicMock()
        mock_df.to_csv.side_effect = Exception("Test CSV error")
    
        # Mock IPython display functionality
>       with patch('pystackql.magic_ext.local.display') as mock_display, \
             patch('pystackql.magic_ext.local.HTML') as mock_html:

tests/test_magic.py:[20](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:21)6: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
/opt/hostedtoolcache/Python/3.9.[22](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:23)/x64/lib/python3.9/unittest/mock.py:1404: in __enter__
    original, local = self.get_original()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <unittest.mock._patch object at 0x7f15ccbe43d0>

    def get_original(self):
        target = self.getter()
        name = self.attribute
    
        original = DEFAULT
        local = False
    
        try:
            original = target.__dict__[name]
        except (AttributeError, KeyError):
            original = getattr(target, name, DEFAULT)
        else:
            local = True
    
        if name in _builtins and isinstance(target, ModuleType):
            self.create = True
    
        if not self.create and original is DEFAULT:
>           raise AttributeError(
                "%s does not have the attribute %r" % (target, name)
            )
E           AttributeError: <module 'pystackql.magic_ext.local' from '/home/runner/work/pystackql/pystackql/pystackql/magic_ext/local.py'> does not have the attribute 'display'

/opt/hostedtoolcache/Python/3.9.22/x64/lib/python3.9/unittest/mock.py:1377: AttributeError
=========================== short test summary info ============================
FAILED tests/test_magic.py::TestStackQLMagic::test_display_with_csv_download_method - AttributeError: <module 'pystackql.magic_ext.local' from '/home/runner/work/pystackql/pystackql/pystackql/magic_ext/local.py'> does not have the attribute 'display'
FAILED tests/test_magic.py::TestStackQLMagic::test_display_with_csv_download_error_handling - AttributeError: <module 'pystackql.magic_ext.local' from '/home/runner/work/pystackql/pystackql/pystackql/magic_ext/local.py'> does not have the attribute 'display'
=================== 2 failed, [34](https://github.com/stackql/pystackql/actions/runs/15598153903/job/43932953404?pr=51#step:12:35) passed, 1 skipped in 2.89s ====================
Error: Process completed with exit code 1.

[claude]

Claude finished @jeffreyaven's task in 5m 17s —— View job • claude/issue-18-20250612_000410 • Create PR ➔

---

CSV Download Test Failures Fixed! ✅

Todo List:

• Analyze failing test methods and understand the mocking issue
• Read local.py and server.py files to see current state
• Issue Found: CSV download functionality missing from current branch
• Re-implement CSV download functionality in local.py
• Re-implement CSV download functionality in server.py
• Add missing CSV download tests to test_magic.py
• Add missing CSV download tests to test_server_magic.py
• Fix test mocking to target correct module paths
• Commit the changes

Implementation Summary

Root Cause: The test failures occurred because the CSV download functionality was missing from the current branch, but tests were expecting it to exist.

Solution: Re-implemented the complete CSV download feature with proper test coverage.

CSV Download Feature Implemented:

✅ Local Mode: Added --csv-download argument to pystackql/magic_ext/local.py
✅ Server Mode: Added --csv-download argument to pystackql/magic_ext/server.py
✅ Base64 Data URIs: Uses base64-encoded CSV data for browser compatibility
✅ Styled Download Button: Clean, styled download button with hover effects
✅ Error Handling: Graceful error handling if CSV generation fails
✅ Conditional Display: Only works when --no-display is NOT set

Test Fixes:

✅ Proper Mocking: Fixed tests to mock IPython.display.display and IPython.display.HTML directly instead of as module attributes
✅ Local Mode Tests: Added 4 comprehensive test methods to tests/test_magic.py
✅ Server Mode Tests: Added 4 comprehensive test methods to tests/test_server_magic.py
✅ Full Coverage: Tests cover functionality, precedence behavior, HTML generation, and error handling

Usage Example:

%%stackql --csv-download
SELECT * FROM your_stackql_query

The tests should now pass as all missing functionality has been implemented and the mocking issues have been resolved.

---

• claude/issue-18-20250612_000410