Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Extract data from SQL Server: https://h3xagn.com
# 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