Export Accela Expressions (from expression builder) via ODBC and Python

export_expressions.py

import os
import re
import pyodbc
from datetime import datetime

# From https://gist.github.com/michaelachrisco/13a939dde0597fdbcf72b3c809f8d364
# Connection settings
server = 'totallyRealServer'
database = 'totallyRealDatabase'
username = 'totallyRealUsername'
password = 'totallyRealPassword'  # Replace with your actual password
driver = '{ODBC Driver 17 for SQL Server}'  # Or 18 depending on your system

conn_str = (
    f"DRIVER={driver};"
    f"SERVER={server};"
    f"DATABASE={database};"
    f"UID={username};"
    f"PWD={password};"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
    "Authentication=SqlPassword;"
)

# Output folder
output_dir = "expressions"
os.makedirs(output_dir, exist_ok=True)

# Sanitize filename
def sanitize_filename(name):
    name = name.replace(" ", "_")
    return re.sub(r'[\\/*?:"<>|]', "_", name)

# Connect and query
with pyodbc.connect(conn_str) as conn:
    cursor = conn.cursor()
    cursor.execute("""
SELECT [SERV_PROV_CODE]
      ,[EXPRESSION_NAME]
      ,[VIEW_ID]
      ,[R1_CHCKBOX_CODE]
      ,[SCRIPT_TEXT]
      ,[REC_DATE]
      ,[REC_FUL_NAM]
      ,[REC_STATUS]
      ,[EXECUTE_IN]
      ,[ENTITY_KEY1]
      ,[ENTITY_KEY2]
      ,[EXPRESSION_BEHAVIOR]
      ,[EXPRESSION_VERSION]
      ,[RES_ID]
      ,[EXPRESSION_MODE]
      ,[EXECUTE_ORDER]
      ,[ENTITY_KEY3]
  FROM [FRESNO-ASYNC-PROD-DB.US.ERDB.ACCELA.COM,14332].[FRESNO].[dbo].[REXPRESSION]
    """)

    rows = cursor.fetchall()
    columns = [column[0] for column in cursor.description]
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    for index, row in enumerate(rows, start=1):
        try:
            record = dict(zip(columns, row))
            expression_name = record["EXPRESSION_NAME"] or f"unnamed_expression_{index}"
            filename = sanitize_filename(expression_name) + ".js"
            filepath = os.path.join(output_dir, filename)

            # Build header comment
            header_lines = [f"/* Initial Export {now_str}"]
            for key, value in record.items():
                if key != "SCRIPT_TEXT":
                    header_lines.append(f" * {key}: {value}")
            header_lines.append("**/\n\n")

            # Write to file
            with open(filepath, "w", encoding="utf-8") as f:
                f.write("\n".join(header_lines))
                f.write(record["SCRIPT_TEXT"] or "")

        except Exception as e:
            print(f"[ERROR] Failed to process record #{index} (EXPRESSION_NAME: {record.get('EXPRESSION_NAME')}): {e}")

Creates files within expressions in the form:

/* Initial Export 2025-11-25 11:36:00
 * SERV_PROV_CODE: AGENCY
 * EXPRESSION_NAME: Name Of Expression
 * VIEW_ID: 117
 * R1_CHCKBOX_CODE: None
 * REC_DATE: 2021-08-16 16:01:17.510000
 * REC_FUL_NAM: TheName
 * REC_STATUS: A
 * EXECUTE_IN: ACA
 * ENTITY_KEY1: None
 * ENTITY_KEY2: None
 * EXPRESSION_BEHAVIOR: None
 * EXPRESSION_VERSION: 660
 * RES_ID: None
 * EXPRESSION_MODE: Manual
 * EXECUTE_ORDER: 102
 * ENTITY_KEY3: None
**/

/* Name Of Expression */
expression in js here