In this article, we are going to make a complete sign up & login page which will be connected to the AWS RDS and we will use MySql Workbench.
Before we begin you must have
- Python 3.6
- Flask
- AWS free tier account to access the AWS services
- My SQL Workbench installed and working
Lets begin now !!!
1. AWS RDS Database Creation
We will create our database first on RDS. The steps are:
- Search for aws rds and click on it that will take you to RDS Dashboard
- On the left sidebar, click on databases
- We will create a new database. Click on Create Database on the right top corner
- Select MySql (you can go for other options as well)
- Click on the checkbox that says ‘Only enable options eligible for RDS Free Usage Tier’ — to avoid unnecessary deductions from your account and click Next.
- Keep the default settings for Instance Specifications
- In the Settings section, enter db instance name.
- Enter the master username and master password & click Next
(Remember this since we will use these details in My Sql Workbench connection - Keep the default settings for Network & Security section and check that Public accessibility is Yes.
- In the Database Options, enter a db name, port must be 3306 by default.
- Keep all the other stuff as it is and click Create Database.
- Great ! You have created your database.
Once your database is created, you can see it in your dashboard. Go to dashboard and click on the database you just created.
As you can see in the image below, there is no endpoint and port available (blue box). To activate that click on the link at the right in the Security section (red box ). After clicking on that you will be redirected to your EC2 instance.
In the EC2 instance, we have to edit the inbound rules. So, click on inbound rules tab (blue box) and then on edit inbound rules.( red box)
In the inbound rules, you need to change the Source dropdown from Custom to Anywhere(box numbered as 1). You also have to add a new rule by clicking on the Add rule button(box numbered as 2). Select HTTP for the new rule and change its source also to anywhere from Custom(box numbered as 3).
Now go back to your RDS database dashboard and you will see that your endpoint and port was created.
This is how you create your RDS database
2. Connect RDS to My SQL Workbench
Now that you have got your endpoint copy that endpoint and open up your My Sql workbench.
- Click on the + icon to establish a new connection.
- Enter a connection name of your choice
- In the hostname field, paste your RDS endpoint that you copied earlier.
- Next is the username and password. Type the master username and master password that you entered while creating the RDS database.
- Click on test connection.
- If everything went correctly then you will see connection successful window.
- In your connection, you will also see the database name you entered while creating the RDS db on AWS.
This is how you connect your RDS to your My Sql workbench. Next, We will look at some SQL queries to enter and read the data from My Sql workbench.
3. SQL Queries with pymysql
We have created the database and connected that to our Workbench as well but it is not connected to our python file. We will be using pymysql library for this.
import pymysql
conn = pymysql.connect(
host= 'endpoint link',
port = '3306',
user = 'master username',
password = 'master password',
db = 'db name',
)Now we will create a table that will have the column names as that of your form fields or input fields. To do this, I am writing a SQL query and passing it as a parameter to the execute function of pymysql. Run this part and a table will be created that you can see in your workbench.
Table Creation
cursor=conn.cursor()
create_table="""
create table Details (name varchar(200),email varchar(200),comment varchar(200),gender varchar(20) )
"""
cursor.execute(create_table)Now we are going to write two queries that will insert details in your database and also read the entered details. To do this, look at the following two functions, insert details takes parameters which are the input from the user ( more about this in the next section-Flask). The get details function just reads the data from the table.
#insert query
def insert_details(name,email,comment,gender):
cur=conn.cursor()
cur.execute("INSERT INTO Details (name,email,comment,gender) VALUES (%s,%s,%s,%s)", (name,email,comment,gender))
conn.commit()#read the data
def get_details():
cur=conn.cursor()
cur.execute("SELECT * FROM Details")
details = cur.fetchall()
return detailsLet’s look at the flask code now.
4. FLASK
In the app.py file create the first route that renders the form on the website. I have the form in an index.html file. So, this is how my app.route looks like…
@app.route('/')
def index():
return render_template('index.html')The index.html file includes:
( Please note the text in bold )
<h4>Fill the form</h4>
<p>The form below will store the details in your AWS RDS MySQL Workbench</p>
<form action="/insert" method="POST">
<div class="form-group">
<label for="usr">Name:</label>
<input name="name" type="text" class="form-control" id="usr">
</div>
<div class="form-group">
<label for="pwd">Email:</label>
<input name="email" type="email" class="form-control" id="pwd">
</div>
<div class="radio">
<label>Gender</label><br>
<label class="radio-inline"><input type="radio" name="optradio" value="Male">Male</label>
</div>
<div class="radio">
<label class="radio-inline"><input type="radio" name="optradio" value="Female">Female</label>
</div><div class="form-group">
<label for="comment">Comment:</label>
<textarea class="form-control" rows="5" id="comment" name="comment"></textarea>
</div>
<br>
<input type="submit" name="submit" value="Submit" class="btn btn-success">
</form>Now when the user clicks on the submit button it should store the details in your table. So, the action of the form triggers a flask function called as insert. This is how it looks:
@app.route('/insert',methods = ['post'])
def insert():
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
gender = request.form['optradio']
comment = request.form['comment']
db.insert_details(name,email,comment,gender)
details = db.get_details()
print(details)
for detail in details:
var = detail
return render_template('index.html',var=var)We will take all the input entered by the user into different variables. The bold text above must match with bold part that you see above in index.html file. This way you store the inputs in different variables.
Now we just need to call the functions that we created earlier to insert the data into the table. We pass the variables as the parameters for the insert_details function. We also read the data by calling the get_details function. In the end we just return the same index.html page with details in the var variable.
To view the details on the website, we use jinja 2 for it. So wherever you want to display the data, add {{ var }} like this in your html file.
Great !! That’s all. This is how you can interact with your database and frontend.
You can find the entire code on my GitHub.
I have also created videos in two parts explaining this. Check out our other videos on our HackerShrine.
Leave a Reply