Sierra SQL to CSV

sierra-sql-to-csv.py

"""
Setup Instructions:
1. Create and activate a Python virtual environment:
   - Linux/macOS:
     python3 -m venv venv
     source venv/bin/activate
   - Windows:
     python -m venv venv
     .\venv\Scripts\activate

2. Install the required package:
   pip install psycopg2-binary

3. Run the script:
   python sierra-sql-to-csv.py
"""

import csv
import datetime
import json
import psycopg2

# config file is a JSON text file that has a dsn (data source name or URI) that
# looks like the following:
# {"database_uri": "postgresql://USERNAME:[email protected]:1032/iii"}
# NOTE: make sure to protect this file -- don't commit to git repo, etc. 
with open('/home/jovyan/.config/sierra/db-test.json') as f:
    config = json.load(f)
    
dsn = config.get('database_uri')  # e.g. 'postgresql://USERNAME:[email protected]:1032/iii'
timestamp = datetime.datetime.now().isoformat()

output_file = f"output_{timestamp}.csv"  # e.g. `output_2025-01-15T19:16:39.167072.csv`
chunk_size = 10_000                      # number of rows to export at a time

# place your SQL query below:
sql = """\
SELECT
    *
FROM
    sierra_view.circ_trans
;
"""

try:
    conn = psycopg2.connect(dsn=config.get('database_uri'))
    print("Successfully connected to the iii database")
    
    # Using get_dsn_parameters to get connection details
    connection_params = conn.get_dsn_parameters()
    print("Connection parameters:")
    for key, value in connection_params.items():
        print(f"  {key}: {value}")
    
    with conn.cursor(name='csv_export_cursor') as cursor:
        cursor.execute(sql)
        print("Successfully executed query\nWriting Rows ", end='')
        
        with open(output_file, 'w') as csvfile:
            writer = csv.writer(csvfile, dialect='excel')
         
            header = False  # only write the csv header once
            while rows := cursor.fetchmany(chunk_size):
                if not header:
                    # header needs to be written
                    header = [description[0] for description in cursor.description]
                    writer.writerow(header)
                
                writer.writerows(rows)
                print('.', end='')
                
        print(f"\nFile written: {output_file}")
                
except psycopg2.Error as e:
        print(f"Error: {e}")
        
finally:
    if 'conn' in locals() and conn:
        conn.close()
        print("Database connection closed")