aws flask_使用Flask的带MySQL的AWS RDS

最新推荐文章于 2024-09-25 20:59:21 发布

weixin_26737625

最新推荐文章于 2024-09-25 20:59:21 发布

阅读量446

点赞数

文章标签： mysql

原文链接：https://medium.com/aws-pocket/aws-rds-with-mysql-using-flask-f1c6d8cc7eff

版权

aws flask

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.

在本文中，我们将制作一个完整的注册和登录页面，该页面将连接到AWS RDS，并且将使用MySql Workbench。

Before we begin you must have

在我们开始之前，您必须具备

Python 3.6
Python 3.6
Flask
烧瓶
AWS free tier account to access the AWS services
AWS免费套餐帐户以访问AWS服务
My SQL Workbench installed and working
我SQL Workbench已安装且正在运行

Lets begin now !!!

让我们开始吧！

1. AWS RDS数据库创建 (1. AWS RDS Database Creation)

We will create our database first on RDS. The steps are:

我们将首先在RDS上创建数据库。这些步骤是：

Search for aws rds and click on it that will take you to RDS Dashboard
搜索aws rds，然后单击它，将带您进入RDS仪表板
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)
选择MySql (您也可以选择其他选项)
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.
单击“仅启用有资格使用RDS免费使用套餐的选项”复选框，以免从您的帐户中扣除不必要的费用，然后单击“ 下一步”。
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
输入主用户名和主密码，然后单击“ 下一步” (请记住这一点，因为我们将在My Sql Workbench连接中使用这些详细信息
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.
在数据库选项中，输入数据库名称，默认情况下端口必须为3306。
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.

如下图所示，没有端点和端口可用(蓝色框)。要激活该功能，请单击“ 安全性”部分右侧的链接(红色框)。单击后，您将被重定向到您的EC2实例。

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)

在EC2实例中，我们必须编辑入站规则。因此，单击入站规则选项卡(蓝色框)，然后单击编辑入站规则。(红色框)

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

在入站规则中，您需要将“源”下拉列表从“自定义”更改为“ 任意位置” (框编号为1)。您还必须通过单击添加规则按钮(编号为2的框)来添加新规则 。为新规则选择HTTP ，并将其来源也更改为“ 自定义”中的任意位置 (框编号为3)。

Now go back to your RDS database dashboard and you will see that your endpoint and port was created.

现在回到RDS数据库仪表板，您将看到端点和端口已创建。

This is how you create your RDS database

这就是您创建RDS数据库的方式

2.将RDS连接到My SQL Workbench (2. Connect RDS to My SQL Workbench)

Now that you have got your endpoint copy that endpoint and open up your My Sql workbench.

现在您已经有了端点，请复制该端点并打开My Sql工作台。

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.
在主机名字段中，粘贴先前复制的RDS端点。
Next is the username and password. Type the master username and master password that you entered while creating the RDS database.
接下来是用户名和密码。键入您在创建RDS数据库时输入的主用户名和主密码。
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.
在您的连接中，您还将看到在AWS上创建RDS数据库时输入的数据库名称。

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.

这是将RDS连接到My Sql工作台的方法。接下来，我们将研究一些SQL查询，以输入和读取My Sql工作台中的数据。

3.使用pymysql进行SQL查询 (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.

我们已经创建了数据库，并将其也连接到我们的工作台，但是它未连接到我们的python文件。我们将为此使用pymysql库。

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.

现在，我们将创建一个表，该表将具有与表单字段或输入字段相同的列名称。为此，我正在编写一个SQL查询，并将其作为参数传递给pymysql的execute函数。运行此部分，将创建一个表，您可以在工作台中看到该表。

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.

现在，我们将编写两个查询，这些查询将在您的数据库中插入详细信息并读取输入的详细信息。为此，请看以下两个函数，插入详细信息采用的参数是用户输入的(在下一节Flask中对此有更多的了解)。获取详细信息功能仅从表中读取数据。

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

Let’s look at the flask code now.

让我们现在看一下烧瓶代码。

4.瓶 (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.py文件中，创建在网站上呈现表单的第一条路线。我在index.html文件中有表格。所以，这就是我的app.route的样子……

@app.route('/')
def index():
    
    return render_template('index.html')

The index.html file includes:( Please note the text in bold )

index.html文件包括：(请注意粗体文本)

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

现在，当用户单击“提交”按钮时，它应将详细信息存储在表中。因此，表单的动作触发了一个称为insert的flask函数。它是这样的：

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

我们会将用户输入的所有输入都输入不同的变量。上面的粗体文本必须与您在index.html文件中看到的粗体部分匹配。这样，您将输入存储在不同的变量中。

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.

现在，我们只需要调用我们先前创建的函数即可将数据插入表中。我们将变量作为insert_details函数的参数传递。我们还通过调用get_details函数读取数据。最后，我们只返回相同的index.html页面，其中包含var变量中的详细信息。

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.

要查看网站上的详细信息，我们使用jinja 2。因此，无论您要在何处显示数据，都应在HTML文件中添加{{var}}这样的内容。

Great !! That’s all. This is how you can interact with your database and frontend.

太好了！就这样。这样便可以与数据库和前端进行交互。

You can find the entire code on my GitHub.

您可以在我的GitHub上找到整个代码。

I have also created videos in two parts explaining this. Check out our other videos on our HackerShrine.

我还创建了两个部分的视频来解释这一点。在我们的HackerShrine上查看其他视频。

Like | Share | Subscribe

喜欢| 分享| 订阅

演示地址