[PYTHON] Manage your data with AWS RDS

Introduction

--There are the following ways to work with relational databases on AWS.

--Build AWS EC2 and put DB on it --Use AWS RDS

AWS RDS Free Tier

--The free frame is as follows

--750 hours / month db.t2.micro database usage (applicable DB engine) --20 GB general purpose (SSD) database storage --20 GB database backup and storage for DB snapshots

Easy to create-MySQL-

--Create a MySQL DB instance and connect to the database

Work on EC2 (added 2020.05.02)

Install mysql-client

$ sudo apt install mysql-client-core-5.7

Connect to EC2-> RDS

python

$ mysql -h RDS endpoint-u Users configured with RDS-p Password set in RDS

Work on the mysql side (added 2020.05.02)

Creating a user database

python

mysql> CREATE USER 'mysql'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR 'mysql'@'%'='MyPassw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE awsfree;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT ALL ON awsfree.* TO 'mysql'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE staff (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    staff_name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT charset = utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO staff (staff_name) VALUES ('Nun Taro');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO staff (staff_name) VALUES ('Nun Hanako');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM staff;
+----+---------------+
| id | staff_name    |
+----+---------------+
|  1 |Nun Taro|
|  2 |Nun Hanako|
+----+---------------+
2 rows in set (0.00 sec)

Operation check (added 2020.05.02)

Install pymysql

$ pip install pymysql

python-> mysql connection test

rds_config.py

rds_host = 'RDS endpoint'
db_user = 'mysql'
db_password = 'MyPassw0rd'
db_name = 'awsfree'

rds_test.py

import pymysql
import rds_config as RDS

con = pymysql.connect(
        host=RDS.rds_host,
        user=RDS.db_user,
        password=RDS.db_password,
        db=RDS.db_name,
        cursorclass=pymysql.cursors.DictCursor
)

try:
    with con.cursor() as cur:
        sql = 'SELECT * FROM staff'
        cur.execute(sql)
        result = cur.fetchall()
        print(result)
except:
    print('Error !!!!')

staff added

sql = 'INSERT INTO staff (staff_name) VALUES (%s)'
cur.execute(sql, ('Nun Jiro'))

con.commit()

Connection test

$ python rds_test.py

Execution result

[{'id': 1, 'staff_name': 'Nun Taro'}, {'id': 2, 'staff_name': 'Nun Hanako'}]

in conclusion

--Using AWS RDS is convenient because you can build an environment with buttons and clicks. --Added the connection test to EC2-> RDS. (2020.05.02)