Function Compute:Accessing ApsaraDB RDS for MySQL

Step 2: Access RDS from your function

1. Log on to the Function Compute console. In the function list, find the target function. On its details page, click the Code tab and enter the following sample code in the editor.

   from flask import Flask, jsonify
   import pymysql
   import os
   from datetime import datetime
   import logging
   app = Flask(__name__)
   # Global variable to store the singleton MySQL connection
   _mysql_connection = None
   # Create a database connection (singleton pattern)
   def getConnection():
       global _mysql_connection
       try:
           # If the connection exists and is active, return it.
           if _mysql_connection is not None:
               try:
                   # Test if the connection is valid with a simple query.
                   with _mysql_connection.cursor() as cursor:
                       cursor.execute("SELECT 1")  # A simple query to test the connection status
                       result = cursor.fetchone()
                       if result and result[0] == 1:
                           return _mysql_connection
               except pymysql.OperationalError:
                   # If the connection is broken, reset it.
                   _mysql_connection = None
           # If the connection does not exist or is broken, create a new one.
           _mysql_connection = pymysql.connect(
               host=os.environ['MYSQL_HOST'],
               port=int(os.environ['MYSQL_PORT']),
               user=os.environ['MYSQL_USER'],
               password=os.environ['MYSQL_PASSWORD'],
               db=os.environ['MYSQL_DBNAME']
           )
           return _mysql_connection
       except Exception as e:
           logging.error(f"Error occurred during database connection: {e}")
           raise
   @app.route('/', defaults={'path': ''})
   @app.route('/<path:path>', methods=['GET', 'POST', 'PUT', 'DELETE'])
   def hello_world(path):
       conn = getConnection()
       try:
           with conn.cursor() as cursor:
               # Query all records from the users table. You must change 'users' to your actual table name.
               sql = "SELECT * FROM users"
               cursor.execute(sql)
               result = cursor.fetchall()
               columns = [desc[0] for desc in cursor.description]  # Get the list of column names
               # Convert the query result to a list of dictionaries
               users = []
               for row in result:
                   user = {}
                   for idx, column_name in enumerate(columns):
                       value = row[idx]
                       if isinstance(value, datetime):  # Handle datetime fields
                           user[column_name] = value.strftime('%Y-%m-%d %H:%M:%S')
                       else:
                           user[column_name] = value
                   users.append(user)
               if users:
                   # Return the JSON response with all users
                   return jsonify(users), 200
               else:
                   # If no users are found, return a 404 error
                   return jsonify({'error': 'No users found'}), 404
       except Exception as e:
           logging.error(f"Error occurred during database operation: {e}")
           return jsonify({'error': 'Database error'}), 500
   if __name__ == '__main__':
       app.run(host='0.0.0.0', port=9000)
   

2. On the Function Details page, select the Configuration tab, find Advanced Settings, and click Modify on its right. In the Advanced Settings panel, find the Environment Variables section, configure the following environment variables, and then click Deploy.

   Parameter	Value	Description
   MYSQL_HOST	rm-bp19u8e76ae****.mysql.rds.aliyuncs.com	The endpoint of the RDS instance. If you are accessing an RDS database in the same VPC, set this environment variable to the internal endpoint of the database. If you are accessing an RDS database across different VPCs or regions, set this environment variable to the public endpoint of the database. In the RDS instance list, click the ID of the target RDS instance. In the navigation pane on the left, click Database Connection. On the Database Connection page, you can obtain the internal or public endpoint of the database.
   MYSQL_DBNAME	db_test	The name of the database created in the RDS instance.
   MYSQL_PASSWORD	*****	The database password.
   MYSQL_PORT	3306	The private port of the database instance.
   MYSQL_USER	dms_user_****	The name of the account created in the RDS instance.

3. On the function details page, click the Code tab, and then click Test Function. After the function executes successfully, check the returned result to confirm that the query completed.

   The returned JSON data contains user records with fields such as id, username, email, password_hash, created_at, and updated_at.