Creating an AWS RDS MySQL instance and connect to it using Python.

MYSQL RDS instance

As AWS is widely used cloud system which help us to build application and use its hassle free service, here is how we can create an AWS RDS MySQL instance and connect to it by using python. We will also see how we can perform CRUD operations on it.

Steps:

1. Create an RDS MYSQL instance.
2. Connect to it by using Python perform CRUD operations
3. Connect to it through tools like DBeaver

Pre-requisites:

1. You have python 3 and Jupyter notebook installed
2. You have DBeaver installed.
3. You have account on AWS.

1) Create an RDS MYSQL instance:

Here are the steps through which we can create an RDS instance.

1. Login to AWS console.
2. Go to services ->Database->RDS->There will be a button “Create Database”.
3. Click on “Create Database”->Choose creation method as Standard Create
4. Choose Engine option as MySQL. you will see the version selection dropdown. choose MySQL 8.2.20
5. Choose the Template as Free tier if you want to use the free service.
6. In the settings section, add DB instance identifier.
7. In credential setting add the Username and Password you need this information while connecting to this instance.
8. in the Connectivity section, change the Public access option to YES and also we need to create a Security group which will allow us to connect to this instance, but time being you can select the default one.
9. In the additional configuration section check if port is 3306
10. Set the database authentication as password authentication. Keep rest of the settings as default.
11. Click on Create Database. It will take a while to create the RDS instance.
12. As your instance is getting created , we need to add a new security group. Go to EC2 service there add a new security group and add inbound and outbound rules as All Traffic and from anywhere can connect to it.
13. Once your instance is created , select your instance Click on Modify button and got to connectivity section. Remove the default security group and select the one you created. We need so that we can connect to this instance from anywhere. Click on continue and select the immediately option to apply the changes.
14. you instance is ready and you can see all the details by clicking on the instance on Dashboard. Make sure you note the Endpoint(host), username(user), port and DB identifier as we need the same while making the connection

2) Connecting to RDS instance using python on your local machine

1. Create a folder on your local machine and open Jupyter notebook.
2. Create a new notebook for python3
3. Now for connecting to MYSQL RDS instance through python we need to install a package and use that package below is the python code

!pip install PyMySQL
# Now we will import that package
import pymysql
#We will use connect() to connect to RDS Instance
#host is the endpoint of your RDS instance
#user is the username you have given while creating the RDS instance
#Password is Master pass word you have given 
db = pymysql.connect(host="Your RDS endpoint", user = "Username added in RDS instance", password="Master password")
# you have cursor instance here
cursor = db.cursor()
cursor.execute("select version()")
#now you will get the version of MYSQL you have selected on instance
data = cursor.fetchone()
#Lets's create a DB
sql = '''create database kTestDb'''
cursor.execute(sql)
cursor.connection.commit()
#Create a table 
sql = '''
create table person ( id int not null auto_increment,fname text, lname text, primary key (id) )'''
cursor.execute(sql)
#Check if our table is created or not 
sql = '''show tables'''
cursor.execute(sql)
cursor.fetchall()
#Output of above will be (('person',),)
#Insert some records in the table 
sql = ''' insert into person(fname, lname) values('%s', '%s')''' % ('XXX', 'YYY')
cursor.execute(sql)
db.commit()
#Lets select the data from above added table
sql = '''select * from person'''
cursor.execute(sql)
cursor.fetchall()
#Output of above will be ((1, 'XXX', 'YYY'),)

4.Once you are connected you can see the connections on RDS instance on Dashboard on AWS console.

3) Connect through Tool DBeaver

1. Install DBeaver from https://dbeaver.io/download/ . Select Community Edition installer as per your need.
2. Once installed Open it click on + icon below File Menu and select your DB type. In this case we have used MY SQL. Click on Next
3. Add your RDS install details here . Server host is your RDS instance End Point, Database is the DB you created through python code. Give Authentication username and password which you have used while creating the RDS instance.
4. Click on Finish and you can see your DB as well as table created.

NOTE: Please make sure you delete all RDS instances you have created if you doing this for hands on purpose.