Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 python code (embedded in Redshift) to return result of the query
#1
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
buran wrote Aug-24-2018, 06:57 PM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Post JSON from python to PHP don't give expected result pgagnebin 1 85 Sep-04-2019, 10:29 PM
Last Post: micseydel
  SQLite Query in Python rowyourboat 2 359 Apr-26-2019, 02:24 PM
Last Post: Larz60+
  Trying to implement Python into embedded OS thesurya7 2 236 Apr-02-2019, 06:38 PM
Last Post: ebolisa
  Multiple calls to Python interpreter embedded in C++ application yield segmentation f mmoelle1 0 284 Mar-21-2019, 08:54 PM
Last Post: mmoelle1
  Python Turtle and order of implementation query Parsleigh 2 340 Mar-04-2019, 02:43 PM
Last Post: Parsleigh
  SQLAlchemy query return last row only pascale 0 416 Mar-01-2019, 05:47 PM
Last Post: pascale
  My code is taking longer time to give result rajeshwin 4 370 Feb-20-2019, 08:18 PM
Last Post: ichabod801
  python hmac gave different result than php hash_hmac nadavvin 2 308 Feb-18-2019, 03:35 PM
Last Post: nadavvin
  Catching exceptions in embedded code no longer works on 3.7.2? FFMG 5 378 Feb-02-2019, 10:15 AM
Last Post: Larz60+
  LDAP code to query for host not returning data burvil 2 559 Oct-17-2018, 10:03 PM
Last Post: burvil

Forum Jump:


Users browsing this thread: 1 Guest(s)