pymysql使用_使用pymysql的AWS Lambda与RDS

最新推荐文章于 2023-10-21 16:33:55 发布

weixin_26755329

最新推荐文章于 2023-10-21 16:33:55 发布

阅读量729

点赞数

原文链接：https://levelup.gitconnected.com/aws-lambda-with-rds-using-pymysql-23ad3cde46c8

版权

pymysql使用

In this tutorial, I will show you how to create a Lambda function which queries data from your RDS database using the pymysql library.

在本教程中，我将向您展示如何创建Lambda函数，该函数使用pymysql库从RDS数据库查询数据。

1. AWS账户设置 (1. AWS Account Setup)

If you already have an AWS account setup, please skip ahead to section 2. If you do not, you can sign up for an account here. You will be required to enter payment details but you get 12 months of free tier access when signing up and nothing in this tutorial will exceed free tier limits.

如果您已经设置了AWS账户，请跳至第2部分。如果没有，请在此处注册一个账户。您将需要输入付款明细，但注册时可获得12个月的免费套餐访问权限，本教程中的任何内容都不会超出免费套餐限制。

Note: If you have exceeded your free tier usage for your account already, you may be charged for the usage of the AWS services described in this tutorial. If you keep more than one RDS instance running, you will exceed the free tier limit.

注意：如果您的帐户已超过免费套餐使用量，则可能会因使用本教程中所述的AWS服务而向您收费。如果保持多个RDS实例运行，则将超出免费套餐限制。

2.创建RDS实例 (2. Create RDS Instance)

In the AWS console, select RDS from the list of services. Select ‘Create database’ from the RDS dashboard. We will be creating a MySQL database so choose this as the Engine type, you can leave the Version as the default option.

在AWS控制台中，从服务列表中选择RDS。从RDS仪表板中选择“创建数据库”。我们将创建一个MySQL数据库，因此选择它作为Engine类型，您可以将Version保留为默认选项。

Note: When creating your database, ensure that you have the desired region selected in the top right of the AWS console (this is typically the region that is geographically closest to you).

注意：创建数据库时，请确保您已在AWS控制台的右上方选择了所需的区域(这通常是地理上距离您最近的区域)。

Select ‘Free tier’ as your Template. Under the Settings section, you need to give your RDS instance a name and provide a Master username and password which will be used for logging into your instance. Make sure that you remember the username and password that you use to set up your RDS instance.

选择“免费套餐”作为模板。在“设置”部分下，您需要为RDS实例命名，并提供用于登录实例的主用户名和密码。确保记住用于设置RDS实例的用户名和密码。

Leave the DB instance size, Storage and Availability & durability sections as the defaults. In the Connectivity section, expand the Additional connectivity configuration options and check the Public access option to Yes.

将数据库实例大小，“存储”和“可用性与持久性”部分保留为默认值。在“连接性”部分中，展开“其他连接性配置”选项，然后将“公共访问”选项选中为“是”。

The final configuration that you can optionally specify is in the Additional configuration section. If you want a database to be created when your RDS instance is created, you can specify an Initial database name in this section. I will create a database with the name ‘playlist’ in this tutorial. You can then click ‘Create database’. It may take a few minutes before your RDS instance is up and running (the Status is ‘Available’).

您可以选择指定的最终配置在“其他配置”部分中。如果要在创建RDS实例时创建数据库，则可以在此部分中指定初始数据库名称。在本教程中，我将创建一个名为“播放列表”的数据库。然后，您可以单击“创建数据库”。 RDS实例启动并运行可能需要几分钟，(状态为“可用”)。

3.从MySQL Workbench连接到数据库 (3. Connect to your database from MySQL Workbench)

If you don’t already have MySQL and Workbench installed, you will need to download and install it from here. At a minimum, MySQL server and Workbench will need to be installed.

如果尚未安装MySQL和Workbench，则需要从此处下载并安装它。至少需要安装MySQL服务器和Workbench。

Note: Some basic level understanding of MySQL is assumed for this tutorial.

注意：本教程假定您对MySQL有一些基本的了解。

When your database instance has been created, select the database you just created and go to the Connectivity & security section. You will see the Endpoint and Port for your database instance here, which will be needed to connect to your database.

创建数据库实例后，选择刚创建的数据库，然后转到“连接和安全性”部分。您将在此处看到数据库实例的“端点和端口”，这是连接到数据库所必需的。

Before we can connect to our database instance from MySQL Workbench, we must edit the VPC security group rules for our RDS instance. Click on the default VPC security group for your instance in the Connectivity & security section. Select the Inbound rules tab for the security group and click ‘Edit inbound rules’. The security group is configured by default to allow all traffic from within the VPC security group, so we need to add another inbound rule to allow all traffic from any IP address.

从MySQL Workbench连接到数据库实例之前，我们必须为RDS实例编辑VPC安全组规则。在“连接性和安全性”部分中，为您的实例单击默认的VPC安全组。选择安全组的“入站规则”选项卡，然后单击“编辑入站规则”。默认情况下，安全组配置为允许来自VPC安全组内的所有流量，因此我们需要添加另一个入站规则以允许来自任何IP地址的所有流量。

Save the security group settings. You will now be able to add the connection in MySQL Workbench and connect to your RDS instance using the Endpoint address as the Hostname and the master username and password that you created.

保存安全组设置。现在，您可以在MySQL Workbench中添加连接，并使用端点地址作为主机名以及您创建的主用户名和密码连接到RDS实例。

Open the connection to the RDS instance and you will notice that the ‘playlist’ database has already been created for us in the SCHEMAS tab on the left. We will run a few simple SQL commands to create a table in our database and add some data to our table.

打开与RDS实例的连接，您会注意到在左侧的SCHEMAS选项卡中已经为我们创建了“播放列表”数据库。我们将运行一些简单SQL命令在数据库中创建一个表并将一些数据添加到表中。

use playlist;create table playlist (
    playlist_name varchar(100) not null,
    song_name varchar(100) not null,
    artist varchar(100) not null,
    album varchar(100),
    primary key (playlist_name, song_name, artist)
);insert into playlist (playlist_name, song_name, artist) values ('#1', 'Your Song', 'Elton John');
insert into playlist (playlist_name, song_name, artist) values ('#1', 'Hey Jude', 'The Beatles');
insert into playlist (playlist_name, song_name, artist, album) values ('#1', 'Hotel California', 'The Eagles', 'Hotel California');
insert into playlist (playlist_name, song_name, artist, album) values ('#1', 'Stairway to Heaven', 'Led Zeppelin', 'Led Zeppelin IV');

Now that we have created our database and inserted data into the table, we can switch our attention to setting up our Lambda function.

现在我们已经创建了数据库并将数据插入到表中，接下来可以将注意力转移到设置Lambda函数上。

4.创建您的Lambda函数 (4. Create your Lambda function)

Now it’s time to set up your Lambda function. Open the Lambda service and ensure the Functions dashboard is selected. I recommend creating your Lambda function in the same region as the RDS instance you have already created.

现在是时候设置Lambda函数了。打开Lambda服务，并确保选择了功能仪表板。我建议在与已经创建的RDS实例相同的区域中创建Lambda函数。

Select “Create function” and give your function a name, I used ‘get-playlist’ in this tutorial. Use Python 3.8 as the Runtime and leave “Create a new role with basic Lambda permissions” as the Execution role. Create your function.

选择“创建函数”并给函数命名，在本教程中我使用了“ get-playlist”。使用Python 3.8作为运行时，并保留“使用Lambda基本权限创建新角色”作为执行角色。创建您的功能。

The pymysql library is not available to Lambda functions by default so we will need to import it so that it can be used to connect to RDS.

pymysql库默认情况下不可用于Lambda函数，因此我们将需要导入它，以便可以将其用于连接到RDS。

5.添加pymysql库作为Lambda层 (5. Add the pymysql library as Lambda layer)

Before we can add the pymysql library as a Lambda layer, we must first package the library as a zip file. In this tutorial I will explain how to do this on Windows, but a similar process can be followed on Mac/Linux.

在将pymysql库添加为Lambda层之前，我们必须首先将该库打包为zip文件。在本教程中，我将解释如何在Windows上执行此操作，但是在Mac / Linux上可以遵循类似的过程。

You will need python installed on your local Windows PC and the python version should match the Runtime version you selected for your Lambda function, in this case Python 3.8. You also need to install pip so that you can download the pymysql package. Here is a link to instructions on how to do this on Windows.

您将需要在本地Windows PC上安装python，并且python版本应与为Lambda函数选择的运行时版本匹配，在本例中为Python 3.8。您还需要安装pip，以便可以下载pymysql软件包。这是有关如何在Windows上执行此操作的说明的链接。

Once you have pip installed on your PC, open the command prompt and navigate to your desired directory and run the following command.

在PC上安装pip后，打开命令提示符并导航到所需目录并运行以下命令。

pip install --target . pymysql

This will create a folder called pymysql in the desired directory. You will then need to compress the pymysql folder as a zip file so that we can upload it to AWS.

这将在所需目录中创建一个名为pymysql的文件夹。然后，您需要将pymysql文件夹压缩为一个zip文件，以便我们可以将其上传到AWS。

In the Lambda console window, select the Layers option from the left side menu and click ‘Create layer’. Give your layer a name of ‘pymysql’ and upload the zip file that you just created. Select Python 3.8 as a Compatible runtime and click ‘Create’.

在Lambda控制台窗口中，从左侧菜单中选择“图层”选项，然后单击“创建图层”。为您的图层命名为“ pymysql”，然后上传刚刚创建的zip文件。选择Python 3.8作为兼容运行时，然后单击“创建”。

Go back to the ‘get-playlist’ function that you have already created and click on Layers in the Designer section. You can then add the Custom layer that you have just created. You are now ready to configure the rest of your Lambda function.

返回您已经创建的“获取播放列表”功能，然后在“设计器”部分中单击“图层”。然后，您可以添加刚刚创建的“自定义”层。现在，您可以配置其余的Lambda函数了。

Note: The alternative to adding a Lambda layer to your function is to directly upload the zip file to your Lambda function (under the Actions menu in the Function code section).

注意：向函数添加Lambda层的替代方法是直接将zip文件上传到Lambda函数(在“功能代码”部分的“动作”菜单下)。

6.从Lambda函数连接到数据库 (6. Connect to your database from your Lambda function)

Now that the pymysql library is available to you Lambda function code, you can add the following code to allow you to connect to your RDS instance. The code sets the database connection properties which are imported from rds_config, creates the connection to the database and executes a simple query to get all data from the ‘playlist’ table we created. The lambda function returns this data in the body of a HTTP response which could be invoked from API Gateway (I have discussed this in a previous tutorial).

现在，您可以使用Lambda函数代码使用pymysql库，您可以添加以下代码以允许您连接到RDS实例。该代码设置从rds_config导入的数据库连接属性，创建与数据库的连接，并执行简单的查询以从我们创建的“播放列表”表中获取所有数据。 lambda函数在HTTP响应的主体中返回此数据，该响应可以从API Gateway调用(我在上一教程中已经讨论过)。

import json
import pymysql
import rds_configrds_host = rds_config.db_endpoint
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name
port = 3306try:
    conn = pymysql.connect(host=rds_host,user=name,
                           passwd=password,db=db_name,
                           connect_timeout=5,
                           cursorclass=pymysql.cursors.DictCursor)except:
    sys.exit()def lambda_handler(event, context):    with conn.cursor() as cur:
        qry = "select * from playlist"
        cur.execute(qry)        body = cur.fetchall()    return {
        'statusCode': 200,
        'headers': {
            'Access-Control-Allow-Origin': '*',
            'Access-Control-Allow-Headers': 'Content-Type,X-Amz-Date,Authorization,X-Api-Key,X-Amz-Security-Token',
            'Access-Control-Allow-Credentials': 'true',
            'Content-Type': 'application/json'
        },
        'body': json.dumps(body)
    }

The next step is to add the rds_config file to your Lambda function to get the database properties referenced in the Lambda function code. In the Function code section, select File > New File and name your file rds_config.py. You will add the configuration properties for your RDS instance here. The structure of the file is shown below, replace the variables with the values for your RDS instance.

下一步是将rds_config文件添加到Lambda函数中，以获取Lambda函数代码中引用的数据库属性。在功能代码部分中，选择文件>新建文件，然后将文件命名为rds_config.py。您将在此处添加RDS实例的配置属性。文件的结构如下所示，将变量替换为RDS实例的值。

db_endpoint = 'your_rds_endpoint_address'db_username = 'your_master_username'db_password = 'your_master_password'db_name = 'your_database_name'

Before you can communicate with your RDS instance you will need to add your Lambda function to the same VPC as your RDS instance. In order to do this, you will need to update your Lambda execution role to attach the NetworkAdministrator policy. In the Permissions tab of your Lambda function, click on the Role name to open the IAM console where you can attach the policy.

与RDS实例进行通信之前，需要将Lambda函数添加到与RDS实例相同的VPC中。为此，您将需要更新Lambda执行角色以附加NetworkAdministrator策略。在Lambda函数的“权限”选项卡中，单击“角色名称”以打开IAM控制台，您可以在其中附加策略。

You can then go back to the Configuration tab and configure the VPC to match the RDS instance VPC configuration, which is shown in section 3 above.

然后，您可以返回到“配置”选项卡，并配置VPC以匹配RDS实例VPC配置，如上面的第3节所示。

You are now ready to test your Lambda function. In the top right of your Lambda function console window, select the ‘Select a test event’ drop down menu and click ‘Configure test events’. AWS comes with many test event templates already configured, you can use the ‘hello-world’ template. Give your test event a name and save. Click on the ‘Test’ button and verify that your function executes successfully.

现在您可以测试Lambda函数了。在Lambda功能控制台窗口的右上角，选择“选择测试事件”下拉菜单，然后单击“配置测试事件”。 AWS附带了许多已经配置的测试事件模板，您可以使用“ hello-world”模板。为您的测试事件命名并保存。单击“测试”按钮，并验证您的功能是否成功执行。

六，结论 (6. Conclusion)

You have now successfully created a simple RDS instance and set up a Lambda function to query the contents of the database by creating a pymysql layer.

现在，您已经成功创建了一个简单的RDS实例，并设置了Lambda函数以通过创建pymysql层来查询数据库的内容。