python code (embedded in Redshift) to return result of the query - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: python code (embedded in Redshift) to return result of the query (/thread-12433.html) |
python code (embedded in Redshift) to return result of the query - Mel - Aug-24-2018 Below is the code that I have as part of databricks job running against snowflake table. It generates an error email in case the SQL code fails, not email otherwise. My goal is: To return result of the query run in the body of the email(The query is pulling the Clustering ratio of a Snowflake table, therefore I need the actual value of the clustering ratio like 12, 13 etc to be send in the email..) Present code: def sf_connect(usr, pwd, host): try: ctx = snowflake.connector.connect( user=usr, password=pwd, account=host ) return ctx except IOError as e: print ('I/O error !') fileOpen = open("/path/script_for_pulling_clustering_ratio.sql","r") sql_script =fileOpen.read() conn = sf_connect(usr = username, pwd = password_sf, host = 'URL') cs = conn.cursor() cs.execute('USE WAREHOUSE warehouse_name;') cs.execute('USE DATABASE db;') for sub_query in sql_script.split(';'): print(sub_query) logging.info(sub_query) try: db=cs.execute(sub_query) print (db.sqlstate) logging.info(db.sqlstate) except Exception as e: if sub_query in ['\n', '\r\n']: print('Executed') pass else: recipients = ["people's email ids"] addr_from = 'sender_id' msg = MIMEMultipart('alternative') msg['To'] = ", ".join(recipients) msg.attach(MIMEText('Debug the failed DB job at the link:corresponding to the failed run')) msg['From'] = addr_from msg['Subject'] = "clustering ratio pull-DB job-SQL FAILURE!" msg['X-Priority'] = '1' # Send the message via an SMTP server s = smtplib.SMTP('ponyex.capitalone.com') s.sendmail(addr_from, recipients, msg.as_string()) s.quit() print("###################################") print(e.args) status= 'Fail' print(status) raise |