Python Forum
python code (embedded in Redshift) to return result of the query
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
Reply


Messages In This Thread
python code (embedded in Redshift) to return result of the query - by Mel - Aug-24-2018, 06:12 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  This result object does not return rows. It has been closed automatically dawid294 5 681 Jan-10-2024, 10:55 PM
Last Post: deanhystad
  How to run detectron2, as python embedded code in C++, on GPU? hassaniqbal931 3 1,039 Nov-02-2023, 04:45 PM
Last Post: blabling2
  Adding libraries to embedded Python as a ZIP The_Oman 0 1,192 May-05-2023, 04:05 PM
Last Post: The_Oman
  list the files using query in python arjunaram 0 649 Mar-28-2023, 02:39 PM
Last Post: arjunaram
  python sql query single quote in a string mg24 1 994 Nov-18-2022, 08:01 PM
Last Post: deanhystad
  help me simple code result min and max number abrahimusmaximus 2 870 Nov-12-2022, 07:52 AM
Last Post: buran
  "SUMIF" type query in Python (help required) BlainEillimatta 0 802 Oct-06-2022, 09:08 AM
Last Post: BlainEillimatta
Bug Embedded Python Memory Leaks Alexei 1 1,001 Sep-16-2022, 01:15 PM
Last Post: Alexei
  Embedded Python in C++ Xeno 19 3,398 Aug-03-2022, 07:29 AM
Last Post: Gribouillis
  Can a program execute code in iPython shell and get result? deanhystad 3 1,663 Jun-17-2022, 03:45 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020