ambt-preden-wholemigration-dev.ipynb 8.3 KB

!pip install pymysql
import sys
import pymysql
import json
import decimal
import boto3
import uuid
import logging
import os
import time
from datetime import datetime, timedelta, date
from dateutil.relativedelta import relativedelta
from botocore.exceptions import ClientError
class DecimalnDateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, decimal.Decimal) or isinstance(obj, datetime):
            return str(obj)
        return json.JSONEncoder.default(self, obj)
def get_secret():
    secret_name = "ambt-preden-gurigalmae-valleydb"
    region_name = "ap-northeast-2"

    # Create a Secrets Manager client
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=region_name
    )

    # In this sample we only handle the specific exceptions for the 'GetSecretValue' API.
    # See https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_GetSecretValue.html
    # We rethrow the exception by default.

    try:
        get_secret_value_response = client.get_secret_value(
            SecretId=secret_name
        )
    except ClientError as e:
        if e.response['Error']['Code'] == 'DecryptionFailureException':
            # Secrets Manager can't decrypt the protected secret text using the provided KMS key.
            # Deal with the exception here, and/or rethrow at your discretion.
            raise e
        elif e.response['Error']['Code'] == 'InternalServiceErrorException':
            # An error occurred on the server side.
            # Deal with the exception here, and/or rethrow at your discretion.
            raise e
        elif e.response['Error']['Code'] == 'InvalidParameterException':
            # You provided an invalid value for a parameter.
            # Deal with the exception here, and/or rethrow at your discretion.
            raise e
        elif e.response['Error']['Code'] == 'InvalidRequestException':
            # You provided a parameter value that is not valid for the current state of the resource.
            # Deal with the exception here, and/or rethrow at your discretion.
            raise e
        elif e.response['Error']['Code'] == 'ResourceNotFoundException':
            # We can't find the resource that you asked for.
            # Deal with the exception here, and/or rethrow at your discretion.
            raise e
    else:
        # Decrypts secret using the associated KMS CMK.
        # Depending on whether the secret is a string or binary, one of these fields will be populated.
        if 'SecretString' in get_secret_value_response:
            secret = get_secret_value_response['SecretString']
        return json.loads(secret)
os.environ['TZ'] = 'Asia/Seoul'
time.tzset()

logger = logging.getLogger()
logger.setLevel(logging.INFO)
secret = get_secret()
try:
    conn = pymysql.connect(host=secret['host'], port=int(secret['port']), user=secret['username'], passwd=secret['password'], db=secret['dbname'], connect_timeout=5)
except:
    logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    sys.exit()
logger.info("SUCCESS: Connection to RDS mysql instance succeeded")
s3=boto3.resource('s3')
sql = "SELECT CREATED_AT FROM DAYENERGY ORDER BY CREATED_AT ASC LIMIT 1"
with conn.cursor(pymysql.cursors.DictCursor) as cur:
    cur.execute(sql)
    logger.info(sql)
    row = cur.fetchone()
oldestdate = row['CREATED_AT']
while oldestdate.date() != date.today() :
    sql = "SELECT * FROM DAYENERGY WHERE ENERGY_YEAR={} AND ENERGY_MONTH={} AND ENERGY_DAY={}".format(oldestdate.year, oldestdate.month, oldestdate.day)
    print (sql)
    with conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute(sql)
        rows = cur.fetchall()
    data = ""
    for row in rows:
        data += json.dumps(row, cls=DecimalnDateTimeEncoder) + '\n'
        #logger.info(row)
    object = s3.Object('hdci-ambt-homenetserver-raw','dev/site_name=gurigalmae/table_name=dayenergy/year={}/month={}/ambt-preden-lambda-migration-dev-{}-{}-{}-{}'.format(oldestdate.year, oldestdate.month, oldestdate.year, oldestdate.month, oldestdate.day, str(uuid.uuid4())))
    object.put(Body=data)
    oldestdate += timedelta(days=1)
sql = "SELECT CREATED_AT FROM MONTHENERGY ORDER BY CREATED_AT ASC LIMIT 1"
with conn.cursor(pymysql.cursors.DictCursor) as cur:
    cur.execute(sql)
    #logger.info(sql)
    row = cur.fetchone()
oldestdate = row['CREATED_AT']
oldestdate.date()
while oldestdate.date().strftime('%Y%m') != date.today().strftime('%Y%m') :
    sql = "SELECT * FROM MONTHENERGY WHERE ENERGY_YEAR={} AND ENERGY_MONTH={}".format(oldestdate.year, oldestdate.month)
    print (sql)
    with conn.cursor(pymysql.cursors.DictCursor) as cur:
        cur.execute(sql)
        rows = cur.fetchall()
    data = ""
    for row in rows:
        data += json.dumps(row, cls=DecimalnDateTimeEncoder) + '\n'
        #logger.info(row)
    object = s3.Object('hdci-ambt-homenetserver-raw','dev/site_name=gurigalmae/table_name=monthenergy/year={}/ambt-preden-lambda-migration-dev-{}-{}-{}'.format(oldestdate.year, oldestdate.year, oldestdate.month, str(uuid.uuid4())))
    object.put(Body=data)
    oldestdate += relativedelta(months=1)