Extract data from SQL Server: https://h3xagn.com

extract-sql-03.py

# iterate through list of historic databases
for db in list_of_dbs:
    logger.info(f"--- Starting export for {db}")

    try:
        logger.info(f"--- Downloading analog table... ")
        query = f"set nocount on; print 'TimeStamp,TagID,TagValue,Quality'; select * from Historian_AnalogTagData"
        output_file = f"./data/csv/{site}_{db}_analog.csv"
        sqlcmd = (
            f'sqlcmd -S {server} -d {db} -U {username} -P {password} -Q "{query}" -s "," -h -1 -W -o "{output_file}"'
        )
        logger.info(sqlcmd)
        os.system(sqlcmd)
        logger.info("Done.")

        logger.info(f"--- Converting analog table... ")
        output_file = f"./data/csv/{site}_{db}_analog.csv"
        table = pv.read_csv(output_file, convert_options=convert_options)
        pq.write_table(
            table,
            f"./data/parquet/{site}_{db}_analog.parquet",
            coerce_timestamps="us",
            allow_truncated_timestamps=True,
        )
        logger.info("Done.")

        logger.info(f"--- Delete analog CSV file... ")
        os.remove(output_file)
        logger.info("Done.\n")
    except:
        logger.error(f"*** ERROR processing analog data for {db}! ***")

    try:
        logger.info(f"--- Downloading discrete table... ")
        query = f"set nocount on; print 'TimeStamp,TagID,TagValue,Quality'; select * from Historian_DiscreteTagData"
        output_file = f"./data/csv/{site}_{db}_discrete.csv"
        sqlcmd = (
            f'sqlcmd -S {server} -d {db} -U {username} -P {password} -Q "{query}" -s "," -h -1 -W -o "{output_file}"'
        )
        logger.info(sqlcmd)
        os.system(sqlcmd)
        logger.info("Done.")

        logger.info(f"--- Converting discrete table... ")
        output_file = f"./data/csv/{site}_{db}_discrete.csv"
        table = pv.read_csv(output_file, convert_options=convert_options)
        pq.write_table(
            table,
            f"./data/parquet/{site}_{db}_discrete.parquet",
            coerce_timestamps="us",
            allow_truncated_timestamps=True,
        )
        logger.info("Done.")

        logger.info(f"--- Delete discrete CSV file... ")
        os.remove(output_file)
        logger.info("Done.\n")
    except:
        logger.error(f"*** ERROR processing discrete data for {db}! ***\n")