Help Center> FunctionGraph> FAQ> External Resource Access FAQs> How Does a Function Access the MySQL Database?
View PDF

How Does a Function Access the MySQL Database?

Perform the following operations:

  1. Check whether the MySQL database is deployed in a VPC.
    • If the MySQL database is deployed in a VPC, configure the same VPC and subnet as the MySQL database for the function by referring to Configuring VPC Access.
    • If the MySQL database is built on a public network, obtain its public IP address.
  2. Compile code for connecting a function to the MySQL database.
    1. Create a function, and add the pymysql dependency to the function on the Code tab page, as shown in Figure 1.
      For details about how to upload the pymysql dependency, see Creating a Dependency.
      Figure 1 Adding a dependency

      FunctionGraph provides public dependency pymysql. Therefore, you can use it directly.

    2. Edit the following code to connect the function to the MySQL database: 
      # -*- coding:utf-8 -*-
import pymysql.cursors

def handler (event, context):
    # Connect to the database
    connection = pymysql.connect(host='host_ip',
                                 user='user',
                                 password='passwd',
                                 db='db',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    try:
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
            cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()

        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
            cursor.execute(sql, ('webmaster@python.org',))
            result = cursor.fetchone()
            print(result)
    finally:
        connection.close()
        output = '^_^'
    return output

      If the function needs to access RDS APIs, create an agency and grant required permissions.