slack-ops-test2-func: execute query

lambda_function.py

import os
import re
import json
import logging
from urllib import request, parse
import pymysql

token    = os.environ['TOKEN']
rds_host = os.environ['RDS_HOST']
name     = os.environ['NAME']
password = os.environ['PASSWORD']
db_name  = os.environ['DB_NAME']

logger = logging.getLogger()
logger.setLevel(logging.INFO)

try:
    logger.info("connecting: {}, {}, {}, {}".format(rds_host, name, password, db_name))
    conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=20)
    logger.info("connected")
except:
    logger.error("ERROR: Unexpected error")
    sys.exit()

def lambda_handler(event, context):
  logger.info(str(event))
  if ('challenge' in event):
    return {
      "statusCode": 200,
      "body": json.dumps({'challenge': event['challenge']})
      }
  else: 
    process_event(event['event'])
    return { "statusCode": 200 }

def process_event(event):
  item = event['item']
  channel = item['channel']
  ts = item['ts']
  reaction = event['reaction']
  logger.info('{}, {}, {}, {}'.format(item, channel, ts, reaction))

  text = retrieve_item_text(channel, ts)
  logger.info(text)
  query = extract_query(text)
  logger.info(query)
  result = exec_query(query)
  logger.info(result)
  post_result(channel, ts, result)

def extract_query(text):
  r = re.compile(".*```(.*)```.*", re.MULTILINE | re.DOTALL)
  return r.search(text).group(1).strip().replace('\n', ' ')

def retrieve_item_text(channel, ts):
  url    = 'https://slack.com/api/channels.history'
  params = {
    'token':     token,
    'channel':   channel,
    'count':     1,
    'inclusive': True,
    'latest':    ts
  }
  requestUrl = '{}?{}'.format(url, parse.urlencode(params))
  req        = request.Request(requestUrl)

  with request.urlopen(req) as res:
    body = res.read().decode('utf-8')

  return json.loads(body)['messages'][0]['text']

def post_result(channel, thread_ts, text):
  url    = 'https://slack.com/api/chat.postMessage'
  params = {
    'token':     token,
    'channel':   channel,
    'text':      text,
    'thread_ts': thread_ts
  }
  requestUrl = '{}?{}'.format(url, parse.urlencode(params))
  req = request.Request(requestUrl)

  with request.urlopen(req) as response:
    response_body = response.read().decode("utf-8")
  logger.info(str(response_body))

def exec_query(query):
  lines = []
  with conn.cursor() as cur:
      cur.execute(query)
      for row in cur:
          lines.append(str(row))
  return "\n".join(lines)