Function Compute:Best practices for accessing ApsaraDB RDS for SQL Server

Step 1: Configure the database whitelist

Step 2: Access the SQL Server database

1. Log on to the Function Compute console. In the function list, find the target function. On the function details page, click the Code tab. In the code editor, write the following sample code and adapt it for your database table.

   from flask import Flask, jsonify
   import os
   import pymssql  # The pymssql library must be installed.
   from datetime import datetime
   
   
   app = Flask(__name__)
   
   # A global variable to store the singleton database connection.
   _mssql_connection = None
   
   # Create a database connection (singleton pattern).
   def getConnection():
       global _mssql_connection
       try:
           # If the connection exists and is active, return it.
           if _mssql_connection is not None:
               try:
                   # Test if the connection is valid with a simple command.
                   with _mssql_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 _mssql_connection
               except pymssql.OperationalError:
                   # If the connection is broken, reset it.
                   _mssql_connection = None
   
           # If the connection does not exist or is broken, create a new one.
           _mssql_connection = pymssql.connect(
               host=os.environ['MSSQL_SERVER'],
               port=int(os.environ['MSSQL_PORT']),
               user=os.environ['MSSQL_USER'],
               password=os.environ['MSSQL_PASSWORD'],
               database=os.environ['MSSQL_DATABASE'],
               charset='utf8'
           )
           return _mssql_connection
       except Exception as e:
           print(f"ERROR: Unexpected error: Could not connect to SQL Server instance: {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 can change the table name as needed.
               cursor.execute("SELECT * FROM users")
               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 a 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
       finally:
           # No need to close the connection because a singleton pattern is used.
           pass
   
   
   if __name__ == '__main__':
       app.run(host='0.0.0.0', port=9000)
   

2. Install the required dependency for the function: pip3 install -t . pymssql. Click Deploy to apply the changes. For detailed steps, see Install dependencies by using the Web IDE terminal in the console.

3. On the Function Details tab, find Advanced Settings and click Modify to its right. In the Advanced Settings panel, find the Environment Variables section, configure the following environment variables, and then click Deploy.

   Parameter	Value	Description
   MSSQL_SERVER	rm-bp*****wn.sqlserver.rds.aliyuncs.com	The endpoint of the database instance. For the Access from the same VPC scenario, use the database's internal endpoint. For the Access from a different VPC or region scenario, use its public endpoint. Navigate to the instance list, select the appropriate region, and click the target instance ID. In the left-side navigation pane, click Database Connection. On the Database Connection page, view the endpoint information.
   MSSQL_PORT	1433	The database port number.
   MSSQL_USER	*****	The username for the database account.
   MSSQL_PASSWORD	*****	The password for the database account.
   MSSQL_DATABASE	*****	The database name.

4. On the function details page, select the Code tab and click Test Function. After the function is executed, view the returned result.