{ "cells": [ { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "!pip install pymysql" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sys\n", "import pymysql\n", "import json\n", "import decimal\n", "import boto3\n", "import uuid\n", "import logging\n", "import os\n", "import time\n", "from datetime import datetime, timedelta, date\n", "from dateutil.relativedelta import relativedelta\n", "from botocore.exceptions import ClientError" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "class DecimalnDateTimeEncoder(json.JSONEncoder):\n", " def default(self, obj):\n", " if isinstance(obj, decimal.Decimal) or isinstance(obj, datetime):\n", " return str(obj)\n", " return json.JSONEncoder.default(self, obj)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "def get_secret():\n", " secret_name = \"ambt-preden-gurigalmae-valleydb\"\n", " region_name = \"ap-northeast-2\"\n", "\n", " # Create a Secrets Manager client\n", " session = boto3.session.Session()\n", " client = session.client(\n", " service_name='secretsmanager',\n", " region_name=region_name\n", " )\n", "\n", " # In this sample we only handle the specific exceptions for the 'GetSecretValue' API.\n", " # See https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_GetSecretValue.html\n", " # We rethrow the exception by default.\n", "\n", " try:\n", " get_secret_value_response = client.get_secret_value(\n", " SecretId=secret_name\n", " )\n", " except ClientError as e:\n", " if e.response['Error']['Code'] == 'DecryptionFailureException':\n", " # Secrets Manager can't decrypt the protected secret text using the provided KMS key.\n", " # Deal with the exception here, and/or rethrow at your discretion.\n", " raise e\n", " elif e.response['Error']['Code'] == 'InternalServiceErrorException':\n", " # An error occurred on the server side.\n", " # Deal with the exception here, and/or rethrow at your discretion.\n", " raise e\n", " elif e.response['Error']['Code'] == 'InvalidParameterException':\n", " # You provided an invalid value for a parameter.\n", " # Deal with the exception here, and/or rethrow at your discretion.\n", " raise e\n", " elif e.response['Error']['Code'] == 'InvalidRequestException':\n", " # You provided a parameter value that is not valid for the current state of the resource.\n", " # Deal with the exception here, and/or rethrow at your discretion.\n", " raise e\n", " elif e.response['Error']['Code'] == 'ResourceNotFoundException':\n", " # We can't find the resource that you asked for.\n", " # Deal with the exception here, and/or rethrow at your discretion.\n", " raise e\n", " else:\n", " # Decrypts secret using the associated KMS CMK.\n", " # Depending on whether the secret is a string or binary, one of these fields will be populated.\n", " if 'SecretString' in get_secret_value_response:\n", " secret = get_secret_value_response['SecretString']\n", " return json.loads(secret)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "os.environ['TZ'] = 'Asia/Seoul'\n", "time.tzset()\n", "\n", "logger = logging.getLogger()\n", "logger.setLevel(logging.INFO)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "secret = get_secret()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " conn = pymysql.connect(host=secret['host'], port=int(secret['port']), user=secret['username'], passwd=secret['password'], db=secret['dbname'], connect_timeout=5)\n", "except:\n", " logger.error(\"ERROR: Unexpected error: Could not connect to MySql instance.\")\n", " sys.exit()\n", "logger.info(\"SUCCESS: Connection to RDS mysql instance succeeded\")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "s3=boto3.resource('s3')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sql = \"SELECT CREATED_AT FROM DAYENERGY ORDER BY CREATED_AT ASC LIMIT 1\"\n", "with conn.cursor(pymysql.cursors.DictCursor) as cur:\n", " cur.execute(sql)\n", " logger.info(sql)\n", " row = cur.fetchone()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "oldestdate = row['CREATED_AT']" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "while oldestdate.date() != date.today() :\n", " sql = \"SELECT * FROM DAYENERGY WHERE ENERGY_YEAR={} AND ENERGY_MONTH={} AND ENERGY_DAY={}\".format(oldestdate.year, oldestdate.month, oldestdate.day)\n", " print (sql)\n", " with conn.cursor(pymysql.cursors.DictCursor) as cur:\n", " cur.execute(sql)\n", " rows = cur.fetchall()\n", " data = \"\"\n", " for row in rows:\n", " data += json.dumps(row, cls=DecimalnDateTimeEncoder) + '\\n'\n", " #logger.info(row)\n", " object = s3.Object('hdci-ambt-homenetserver-raw','dev/table_name=dayenergy/site_name=gurigalmae/year={}/month={}/dayenergy_{}{:02d}{:02d}'.format(oldestdate.year, oldestdate.month, oldestdate.year, oldestdate.month, oldestdate.day))\n", " object.put(Body=data)\n", " oldestdate += timedelta(days=1)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "sql = \"SELECT CREATED_AT FROM MONTHENERGY ORDER BY CREATED_AT ASC LIMIT 1\"\n", "with conn.cursor(pymysql.cursors.DictCursor) as cur:\n", " cur.execute(sql)\n", " #logger.info(sql)\n", " row = cur.fetchone()" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "oldestdate = row['CREATED_AT']" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "oldestdate.date()" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "scrolled": true }, "outputs": [], "source": [ "while oldestdate.date().strftime('%Y%m') != date.today().strftime('%Y%m') :\n", " sql = \"SELECT * FROM MONTHENERGY WHERE ENERGY_YEAR={} AND ENERGY_MONTH={}\".format(oldestdate.year, oldestdate.month)\n", " print (sql)\n", " with conn.cursor(pymysql.cursors.DictCursor) as cur:\n", " cur.execute(sql)\n", " rows = cur.fetchall()\n", " data = \"\"\n", " for row in rows:\n", " data += json.dumps(row, cls=DecimalnDateTimeEncoder) + '\\n'\n", " #logger.info(row)\n", " object = s3.Object('hdci-ambt-homenetserver-raw','dev/site_name=gurigalmae/table_name=monthenergy/year={}/monthenergy_{}{:02d}'.format(oldestdate.year, oldestdate.year, oldestdate.month)\n", " object.put(Body=data)\n", " oldestdate += relativedelta(months=1)" ] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.13" } }, "nbformat": 4, "nbformat_minor": 4 }