Python Forum

Full Version: python code (embedded in Redshift) to return result of the query
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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