Extract data from SQL Server: https://h3xagn.com
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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") |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment