Code snippit to export Atrex table metadata

export_atrex_table_metadata.py

import datetime
import logging
import sys

import pyodbc
import pandas as pd

logging.basicConfig(stream=sys.stdout, level=logging.INFO, datefmt="%Y-%m-%dT%H:%M:%S",
                    format='%(asctime)s.%(msecs)03d - %(levelname)s - %(message)s')

DSN = "<update to the correct DSN>"
QUERY = "select * from information.Tables"

try:
    logging.info("Initializing Connection")
    connection = pyodbc.connect(f"DSN={DSN}")
    logging.info("Getting cursor")
    cursor = connection.cursor()
    logging.info(f"Executing {QUERY}")
    cursor.execute(QUERY)
    columns = [x[0] for x in cursor.description]
    results = list()

    logging.info("Fetching records")
    records = cursor.fetchall()
    logging.info(f"Result set contains {len(records)} records.")
    logging.info("Processing results of cursor.fetchall()")
    for row in records:
        results.append(dict(zip(columns, row)))

    cursor.close()

    logging.info("Outputting to json")
    pd.DataFrame(results).to_json(f'data/raw/{datetime.date.today()}-table-meta.json', 'records')
except pyodbc.Error as err:
    logging.error(repr(err))
finally:
    if connection:
        connection.close()
logging.info("Execution complete")