[PYTHON] Manage your data with AWS RDS


--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


$ 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


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 (
    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_host = 'RDS endpoint'
db_user = 'mysql'
db_password = 'MyPassw0rd'
db_name = 'awsfree'


import pymysql
import rds_config as RDS

con = pymysql.connect(

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

staff added

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


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)

Recommended Posts

Manage your data with AWS RDS
Overwrite data in RDS with AWS Glue
Manage your Amazon CloudWatch loggroup retention with AWS Lambda
Import your own functions on AWS Glue
Overwrite data in RDS with AWS Glue
Declaratively manage your environment with Nix and home-manager
Manage AWS nicely with the Python library Boto
Divide your data into project-like units with Django
Data analysis with python 2
Get Amazon RDS (PostgreSQL) data using SQL with pandas
Visualize data with Streamlit
Put AWS data in Google Spreadsheet with boto + gspread
[blackbird-rds] Monitoring AWS RDS
Reading data with TensorFlow
Aggregate AWS S3 data
Data visualization with pandas
Data manipulation with Pandas!
Single sign-on to your Django application with AWS SSO
Shuffle data with pandas
Data Augmentation with openCV
AWS CDK with Python
Build AWS EC2 and RDS with Terraform Terraform 3 minutes cooking
Normarize data with Scipy
Delete DynamoDB data after 5 minutes with AWS Step Functions
Data analysis with Python
LOAD DATA with PyMysql
Try to get data while port forwarding to RDS with anaconda.
Sample data created with python
Embed audio data with Jupyter
Manage cron jobs with python
Artificial data generation with numpy
Manage python environment with virtualenv
Extract Twitter data with CSV
Get Youtube data with python
Clustering ID-POS data with LDA
Learn new data with PaintsChainer
Binarize photo data with OpenCV
Graph Excel data with matplotlib (2)
Operate your website with Python_Webbrowser
Linux fastest learning with AWS
Save tweet data with Django
AWS Lambda with PyTorch [Lambda import]
Data processing tips with Pandas
Interpolate 2D data with scipy.interpolate.griddata
Use AWS interpreter with Pycharm
Read json data with python
[AWS] Search and acquire necessary data from S3 files with S3 Select
Manipulating kintone data with Python & C Data ODBC Driver from AWS Lambda