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
  Converting SQL Code To Python Code Query eddywinch82 13 306 Feb-15-2020, 06:42 PM
Last Post: buran
  Embedded Python PyObject_CallObject function JRHeisey 1 149 Nov-27-2019, 01:50 AM
Last Post: casevh
  Palindrome in Python - result always false RavCOder 13 579 Oct-16-2019, 01:38 PM
Last Post: perfringo
  Post JSON from python to PHP don't give expected result pgagnebin 1 217 Sep-04-2019, 10:29 PM
Last Post: micseydel
  SQLite Query in Python rowyourboat 2 524 Apr-26-2019, 02:24 PM
Last Post: Larz60+
  Trying to implement Python into embedded OS thesurya7 2 342 Apr-02-2019, 06:38 PM
Last Post: ebolisa
  Multiple calls to Python interpreter embedded in C++ application yield segmentation f mmoelle1 0 430 Mar-21-2019, 08:54 PM
Last Post: mmoelle1
  Python Turtle and order of implementation query Parsleigh 2 495 Mar-04-2019, 02:43 PM
Last Post: Parsleigh
  SQLAlchemy query return last row only pascale 0 1,278 Mar-01-2019, 05:47 PM
Last Post: pascale
  My code is taking longer time to give result rajeshwin 4 505 Feb-20-2019, 08:18 PM
Last Post: ichabod801

Forum Jump:


Users browsing this thread: 1 Guest(s)