云函数接入数据库

Python 可使用云函数环境已经内置的 pymysql 依赖包进行数据库连接。示例代码如下：

# -*- coding: utf8 -*-
from os import getenv
import pymysql
from pymysql.err import OperationalError
mysql_conn = None
def __get_cursor():
    try:
        return mysql_conn.cursor()
    except OperationalError:
        mysql_conn.ping(reconnect=True)
        return mysql_conn.cursor()
def main_handler(event, context):
    global mysql_conn
    if not mysql_conn:
        mysql_conn = pymysql.connect(
        host        = getenv('DB_HOST', '<YOUR DB HOST>'),
        user        = getenv('DB_USER','<YOUR DB USER>'),
        password    = getenv('DB_PASSWORD','<YOUR DB PASSWORD>'),
        db          = getenv('DB_DATABASE','<YOUR DB DATABASE>'),
        port        = int(getenv('DB_PORT','<YOUR DB PORT>')),
        charset     = 'utf8mb4',
        autocommit  = True
        )
    
    with __get_cursor() as cursor:
        cursor.execute('select * from employee')
        myresult = cursor.fetchall()
        print(myresult)
        for x in myresult:
            print(x)

Node.js 支持使用连接池进行连接，连接池具备自动重连功能，可有效避免因云函数底层或者数据库释放连接造成的连接不可用情况。示例代码如下：

说明

使用连接池前需先安装 mysql2 依赖包，详情请参见 依赖安装。

'use strict';
const DB_HOST       = process.env[`DB_HOST`]
const DB_PORT       = process.env[`DB_PORT`]
const DB_DATABASE   = process.env[`DB_DATABASE`]
const DB_USER       = process.env[`DB_USER`]
const DB_PASSWORD   = process.env[`DB_PASSWORD`]
const promisePool = require('mysql2').createPool({
  host              : DB_HOST,
  user              : DB_USER,
  port              : DB_PORT,
  password          : DB_PASSWORD,
  database          : DB_DATABASE,
  connectionLimit   : 1
}).promise();

exports.main_handler = async (event, context, callback) => {
  let result = await promisePool.query('select * from employee');
  console.log(result);
}

PHP 可使用 pdo_mysql 或 mysqli 依赖包进行数据连接。示例代码如下：

<?php
function handler($event, $context) {
try{
    $pdo = new PDO('mysql:host= getenv("DB_HOST");dbname= getenv("DB_DATABASE"),getenv("DB_USER"),getenv("DB_PASSWORD")');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }catch(PDOException $e){
    echo '数据库连接失败: '.$e->getMessage();
    exit;
  }
}

<?php
function main_handler($event, $context) {
    $host = "";
    $username = "";
    $password = "";
 
    // 创建连接
    $conn = mysqli_connect($servername, $username, $password);

    // 检测连接
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
        }
    echo "连接成功"; 

    mysqli_close($conn);
    echo "断开连接"; 
}
?>

1. 请参考 依赖安装，安装以下依赖：
   
   <dependencies>
       <dependency>
           <groupId>com.tencentcloudapi</groupId>
           <artifactId>scf-java-events</artifactId>
           <version>0.0.2</version>
       </dependency>
       <dependency>
           <groupId>com.zaxxer</groupId>
           <artifactId>HikariCP</artifactId>
           <version>3.2.0</version>
       </dependency>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>8.0.11</version>
       </dependency>
   </dependencies>

2. 使用 Hikari 连接池进行连接，示例代码如下：
   
   package example;
   
   import com.qcloud.scf.runtime.Context;
   import com.qcloud.services.scf.runtime.events.APIGatewayProxyRequestEvent;
   import com.qcloud.services.scf.runtime.events.APIGatewayProxyResponseEvent;
   import com.zaxxer.hikari.HikariConfig;
   import com.zaxxer.hikari.HikariDataSource;
   import javax.sql.DataSource;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.util.HashMap;
   import java.util.Map;
   public class Http {
       private DataSource dataSource;
       public Http() {
           HikariConfig config = new HikariConfig();
           config.setJdbcUrl("jdbc:mysql://" + System.getenv("DB_HOST") + ":"+ System.getenv("DB_PORT") + "/" + System.getenv("DB_DATABASE"));
           config.setUsername(System.getenv("DB_USER"));
           config.setPassword(System.getenv("DB_PASSWORD"));
           config.setDriverClassName("com.mysql.jdbc.Driver");
           config.setMaximumPoolSize(1);
           dataSource = new HikariDataSource(config);
       }
   
       public String mainHandler(APIGatewayProxyRequestEvent requestEvent, Context context) {
           System.out.println("start main handler");
           System.out.println("requestEvent: " + requestEvent);
           System.out.println("context: " + context);
   
           try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement("SELECT * FROM employee")) {
               ResultSet rs = ps.executeQuery();
               while (rs.next()) {
                   System.out.println(rs.getInt("id"));
                   System.out.println(rs.getString("first_name"));
                   System.out.println(rs.getString("last_name"));
                   System.out.println(rs.getString("address"));
                   System.out.println(rs.getString("city"));
               }
           } catch (SQLException e) {
               e.printStackTrace();
           }
   
           APIGatewayProxyResponseEvent apiGatewayProxyResponseEvent = new APIGatewayProxyResponseEvent();
           apiGatewayProxyResponseEvent.setBody("API GW Test Success");
           apiGatewayProxyResponseEvent.setIsBase64Encoded(false);
           apiGatewayProxyResponseEvent.setStatusCode(200);
   
           Map<String, String> headers = new HashMap<>();
           headers.put("Content-Type", "text");
           headers.put("Access-Control-Allow-Origin", "*");
           apiGatewayProxyResponseEvent.setHeaders(headers);
   
           return apiGatewayProxyResponseEvent.toString();
       }
   }