## 模块 2：通过 Python 访问 TiDB Cloud 集群

### 步骤 1. 导入示例数据

+ 创建示例数据库，并导入 **S&P 500** 示例数据。

> [!TIP]
> 在本步骤的导入脚本中，由 `LOCK TABLE` 命令引起的 `Access Denied` 信息可以安全忽略。在 TiDB 这样的分布式 SQL 数据库中，并不需要 `LOCK TABLE`。

  ```sql
  SOURCE sp500insight.sql
  ```

  ```output
  ...

  Query OK, 7947 rows affected (0.34 sec)
  Records: 7947  Duplicates: 0  Warnings: 0

  Query OK, 7882 rows affected (0.34 sec)
  Records: 7882  Duplicates: 0  Warnings: 0

  ...
  Query OK, 0 rows affected, 1 warning (0.01 sec)
  ```

{% if TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %}

- 返回 TiDB Cloud 控制台，如果 `Connect` 面板处于打开状态请关闭，在集群概览页面查看核心监控指标。
- 你会注意到，消耗的 RU 在不到 2 秒的时间里达到了 4K，你可以理解为数据导入性能曾短暂受到 RCU 上限的限制。虽然配置的 RU 是 2K，但你可以突发使用至 4K。由于超过 2K RU 的时间非常短暂，所以 Provisioned RCU 没有发生或无需发生自动扩容。例如：

![tidblabs-essential-rcu-monitor.jpg 80%](https://lab-static.pingcap.com/images/2025/11/13/3327f748fa256d2e8239b7dbc5066bbea0addf9b.jpg)

- 如果你非常快地完成实验步骤，可能会观察到以下现象：在集群刚创建完成后，2K 的最小 RCU 需要约 1 分钟才能完全生效。在此期间，仍可使用突发资源，在超出 2K RU 时负载开始运行。这时，集群会判断你需要及时增加 Provisioned RCU（但不会超过 4K）。不过，一旦集群检测到你的负载很快从 4K 降到了接近 0，Provisioned RCU 就会恢复为 2K。

![essential-autoscale.jpeg 75%](https://lab-static.pingcap.com/images/2025/11/14/5fae6a98cebe5982ac9f053dda729e40d0a4697e.jpeg)

- 回到 [终端 1](ssh://monitor1-ssh#1)。

{% endif %}

### 步骤 2. 查询示例数据

  ```sql
  SELECT sector, industry, COUNT(*) AS companies
  FROM companies c
  WHERE c.stock_symbol IN (SELECT stock_symbol FROM index_compositions WHERE index_symbol = 'SP500')
  GROUP BY sector, industry
  ORDER BY sector, companies DESC;
  ```

  ```output
  +------------------------+------------------------------------------+-----------+
  | sector                 | industry                                 | companies |
  +------------------------+------------------------------------------+-----------+
  | Basic Materials        | Specialty Chemicals                      |        10 |
  | Basic Materials        | Agricultural Inputs                      |         4 |
  | Basic Materials        | Building Materials                       |         2 |
  | Basic Materials        | Chemicals                                |         2 |
  | Basic Materials        | Steel                                    |         2 |
  | Basic Materials        | Gold                                     |         1 |
  | Basic Materials        | Copper                                   |         1 |
  | Communication Services | Entertainment                            |         7 |
  ...
  | Utilities              | Utilities—Regulated Electric             |        23 |
  | Utilities              | Utilities—Diversified                    |         2 |
  | Utilities              | Utilities—Regulated Gas                  |         2 |
  | Utilities              | Utilities—Regulated Water                |         1 |
  | Utilities              | Utilities—Independent Power Producers    |         1 |
  +------------------------+------------------------------------------+-----------+
  共 113 行，1 个警告 (0.19 sec)
  ```

### 步骤 3. 创建分析型查询脚本

+ 退出到操作系统命令行。

  ```sql
  EXIT;
  ```

+ 创建 Python 虚拟环境。

  ```bash
  python3 -m venv ~/.venv
  ~/.venv/bin/pip install mysql-connector-python prettytable
  ```

{% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %}

+ 创建 Python 脚本。

  ```bash
  cat > test01.py <<'EOF'
  import mysql.connector
  import datetime
  from prettytable import PrettyTable

  config = {
    'user': '${LAB:SERVERLESS_CLUSTER_USERNAME}',
    'password': '${LAB:SERVERLESS_CLUSTER_PASSWORD}',
    'host': '${LAB:SERVERLESS_CLUSTER_HOST}',
    'database': '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}',
    'raise_on_warnings': False,
    'port': ${LAB:SERVERLESS_CLUSTER_PORT},
  }

  def execQuery(query, queryName):
    print(queryName)
    print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))
    # 执行查询
    cursor.execute(query)
    print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))

    # 获取所有行
    results = cursor.fetchall()

    # 创建 PrettyTable 对象
    my_table = PrettyTable()

    # 添加字段名
    field_names = [i[0] for i in cursor.description]
    my_table.field_names = field_names

    # 添加表格内容
    for row in results:
        my_table.add_row(row)

    # 打印表格
    print(my_table)
    print("==========================")

  try:
    # 建立连接
    conn = mysql.connector.connect(**config)

    # 创建游标对象
    cursor = conn.cursor()

    # SQL 查询
    queryName= "■■■查询 1：各行业总市值■■■"
    query = """
    SELECT
        `sector`,
        SUM(`market_cap`) AS `total_market_cap`
    FROM
        `companies`
    GROUP BY
        `sector`;
    """
    execQuery(query, queryName)

    queryName= "■■■查询 2：能源行业成分股市值排名■■■"
    query = """
    WITH index_companies AS (
        SELECT
            c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth
        FROM companies c
        JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol
        WHERE index_symbol = 'SP500' AND sector = 'Energy'
    ), companies_latest_price AS (
        SELECT
            stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price,
                 last_close_price - last_2nd_close_price                          AS last_change,
      (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage
        FROM (
          SELECT
            LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol,
            LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day,
            NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price,
            NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price,
            ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num
          FROM stock_price_history sph
          WHERE
            record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY)
            AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic)
        ) sub
        WHERE
            row_num = 2
    )
    SELECT
        ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth,
      clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage
    FROM index_companies ic
    LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol
    ORDER BY ic.weight DESC;
    """

    execQuery(query, queryName)

  except mysql.connector.Error as err:
    print(f"Error: {err}")

  finally:
    # 关闭连接
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL 连接已关闭")
  EOF
  ls -l test01.py
  ```

  ```output
  -rw-r--r--. 1 ubuntu ubuntu 3636 4月 28 02:22 test01.py
  ```

{% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %}

+ 创建 Python 脚本。

  ```bash
  cat > test01.py <<'EOF'
  import mysql.connector
  import datetime, sys
  from prettytable import PrettyTable

  db_user = sys.argv[1]
  db_password = sys.argv[2]
  db_host = sys.argv[3]
  db_database = sys.argv[4]
  db_port = sys.argv[5]

  config = {
    'user': db_user,
    'password': db_password,
    'host': db_host,
    'database': db_database,
    'raise_on_warnings': False,
    'port': db_port,
  }

  def execQuery(query, queryName):
    print(queryName)
    print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))
    # 执行查询
    cursor.execute(query)
    print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))

    # 获取所有行
    results = cursor.fetchall()

    # 创建 PrettyTable 对象
    my_table = PrettyTable()

    # 添加字段名
    field_names = [i[0] for i in cursor.description]
    my_table.field_names = field_names

    # 添加表格内容
    for row in results:
        my_table.add_row(row)

    # 打印表格
    print(my_table)
    print("==========================")

  try:
    # 建立连接
    conn = mysql.connector.connect(**config)

    # 创建游标对象
    cursor = conn.cursor()

    # SQL 查询
    queryName= "■■■查询 1：各行业总市值■■■"
    query = """
    SELECT
        `sector`,
        SUM(`market_cap`) AS `total_market_cap`
    FROM
        `companies`
    GROUP BY
        `sector`;
    """
    execQuery(query, queryName)

    queryName= "■■■查询 2：能源行业成分股市值排名■■■"
    query = """
    WITH index_companies AS (
        SELECT
            c.stock_symbol, c.short_name, c.exchange_symbol, ic.weight, c.market_cap AS market_cap, c.revenue_growth AS revenue_growth
        FROM companies c
        JOIN index_compositions ic ON c.stock_symbol = ic.stock_symbol
        WHERE index_symbol = 'SP500' AND sector = 'Energy'
    ), companies_latest_price AS (
        SELECT
            stock_symbol, DATE(last_day) AS last_change_day, last_close_price, last_2nd_close_price,
                 last_close_price - last_2nd_close_price                          AS last_change,
      (last_close_price - last_2nd_close_price) / last_2nd_close_price AS last_change_percentage
        FROM (
          SELECT
            LAST_VALUE(stock_symbol) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS stock_symbol,
            LAST_VALUE(record_date) OVER (PARTITION BY stock_symbol ORDER BY record_date) AS last_day,
            NTH_VALUE(close, 1) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_close_price,
            NTH_VALUE(close, 2) OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS last_2nd_close_price,
            ROW_NUMBER() OVER (PARTITION BY stock_symbol ORDER BY record_date DESC) AS row_num
          FROM stock_price_history sph
          WHERE
            record_date > DATE_SUB((SELECT MAX(record_date) FROM stock_price_history), INTERVAL 2 DAY)
            AND sph.stock_symbol IN (SELECT stock_symbol FROM index_companies ic)
        ) sub
        WHERE
            row_num = 2
    )
    SELECT
        ic.stock_symbol, ic.exchange_symbol, ic.short_name, ic.weight, ic.market_cap, ic.revenue_growth,
      clp.last_change, clp.last_change_day, clp.last_close_price, clp.last_2nd_close_price, clp.last_change_percentage
    FROM index_companies ic
    LEFT JOIN companies_latest_price clp ON ic.stock_symbol = clp.stock_symbol
    ORDER BY ic.weight DESC;
    """

    execQuery(query, queryName)

  except mysql.connector.Error as err:
    print(f"Error: {err}")

  finally:
    # 关闭连接
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL 连接已关闭")
  EOF
  ls -l test01.py
  ```

  ```output
  -rw-r--r--. 1 ubuntu ubuntu 3636 4月 28 02:22 test01.py
  ```

{% endif %}

### 步骤 4. 运行脚本执行分析查询

+ 运行 Python 脚本，基于示例数据获取两份报表。请注意脚本的执行用时。

{% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %}

  ```bash
  ~/.venv/bin/python test01.py
  ```

{% elif TIDB_ENVIRONMENT == 'TiDB Cloud Essential' %}

  ```bash
  ~/.venv/bin/python test01.py \
  $SERVERLESS_CLUSTER_USERNAME \
  $SERVERLESS_CLUSTER_PASSWORD \
  $SERVERLESS_CLUSTER_HOST \
  lab_demo_${LAB:LAB_INSTANCE_ID} \
  $SERVERLESS_CLUSTER_PORT
  ```

{% endif %}

  ```output
  ■■■查询 1：各行业总市值■■■
  Query execution start time:  02:22:15.881417
  Query execution end time:  02:22:15.902559
  +------------------------+------------------+
  |         sector         | total_market_cap |
  +------------------------+------------------+
  |      Real Estate       |   927820086272   |
  |       Utilities        |  1023797270016   |
  |       Healthcare       |  5122903213056   |
  |       Technology       |  7906855563776   |
  |   Financial Services   |  4661122020864   |
  |      Industrials       |  3199253085184   |
  |   Consumer Cyclical    |  3238477340160   |
  |   Consumer Defensive   |  2872367655424   |
  |         Energy         |  1788549832704   |
  |    Basic Materials     |   889871172608   |
  | Communication Services |  3669841019392   |
  +------------------------+------------------+
  ==========================
  ■■■查询 2：能源行业成分股市值排名■■■
  Query execution start time:  02:22:15.904362
  Query execution end time:  02:22:18.595003
  +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+
  | stock_symbol | exchange_symbol |            short_name           |         weight         |  market_cap  | revenue_growth |     last_change     | last_change_day |   last_close_price   | last_2nd_close_price |  last_change_percentage |
  +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+
  |     XOM      |       NYQ       |     Exxon Mobil Corporation     |  0.013441937585551282  | 474511933440 |     0.689      |  1.2800064086914100 |    2023-01-10   | 111.3700027465820300 | 110.0899963378906200 |  0.01162690935843785832 |
  |     CVX      |       NYQ       |       Chevron Corporation       |  0.009567226003564158  | 337731289088 |      0.81      | -0.8399963378906000 |    2023-01-10   | 175.1999969482422000 | 176.0399932861328000 | -0.00477162218772232275 |
  |     COP      |       NYQ       |          ConocoPhillips         |  0.004417814517419704  | 155952644096 |     1.237      |  0.1599960327148400 |    2023-01-10   | 118.2399978637695300 | 118.0800018310546900 |  0.00135497993084178221 |
  |     SLB      |       NYQ       |        Schlumberger N.V.        | 0.0022377960040345308  | 78996119552  |     0.279      |         None        |       None      |         None         |         None         |           None          |
  |     EOG      |       NYQ       |       EOG Resources, Inc.       | 0.0021024604924476343  | 74218659840  |     0.924      | -0.7600021362304600 |    2023-01-10   | 126.8499984741211000 | 127.6100006103515600 | -0.00595566282105957140 |
  |     MPC      |       NYQ       |  Marathon Petroleum Corporation |  0.002050747487098508  | 72393146368  |     0.818      |  1.0999984741211000 |    2023-01-10   | 117.5999984741211000 | 116.5000000000000000 |  0.00944204698816394850 |
  |     OXY      |       NYQ       | Occidental Petroleum Corporatio |  0.001695676548826279  | 59858837504  |     0.792      |         None        |       None      |         None         |         None         |           None          |
  |     PXD      |       NYQ       | Pioneer Natural Resources Compa | 0.0016017013932190817  | 56541433856  |     0.641      |         None        |       None      |         None         |         None         |           None          |
  |     VLO      |       NYQ       |    Valero Energy Corporation    | 0.0015472850935959574  | 54620491776  |     0.593      |         None        |       None      |         None         |         None         |           None          |
  |     HES      |       NYQ       |         Hess Corporation        | 0.0012744122628788822  | 44987846656  |     0.776      |  1.6300048828125000 |    2023-01-10   | 145.2500000000000000 | 143.6199951171875000 |  0.01134942861878312162 |
  |     PSX      |       NYQ       |           Phillips 66           | 0.0012589621320069535  | 44442443776  |     0.799      |         None        |       None      |         None         |         None         |           None          |
  |     KMI      |       NYQ       |       Kinder Morgan, Inc.       | 0.0012062691273686955  | 42582335488  |     0.354      |  0.0400009155273440 |    2023-01-10   | 18.7800006866455080  | 18.7399997711181640  |  0.00213452059850037319 |
  |     DVN      |       NYQ       |     Devon Energy Corporation    | 0.0012053375130788024  | 42549448704  |     0.789      | -0.2800025939941400 |    2023-01-10   | 62.8499984741210940  | 63.1300010681152340  | -0.00443533326875800678 |
  |     WMB      |       NYQ       |  Williams Companies, Inc. (The) | 0.0011162172177908748  | 39403425792  |     0.091      |         None        |       None      |         None         |         None         |           None          |
  |     HAL      |       NYQ       |       Halliburton Company       | 0.0010393744130051477  | 36690808832  |     0.388      |  0.0500030517578160 |    2023-01-10   | 40.9900016784668000  | 40.9399986267089840  |  0.00122137404580161222 |
  |     OKE      |       NYQ       |           ONEOK, Inc.           | 0.0008854096904464799  | 31255721984  |     0.769      |         None        |       None      |         None         |         None         |           None          |
  |     BKR      |       NMS       |       Baker Hughes Company      | 0.0008807471517968449  | 31091130368  |     0.054      | -0.0400009155273450 |    2023-01-10   | 30.8299999237060550  | 30.8700008392334000  | -0.00129578602008027511 |
  |     FANG     |       NMS       |     Diamondback Energy, Inc.    |  0.000730020047064916  | 25770334208  |     0.565      |  0.2900085449218600 |    2023-01-10   | 142.2400054931640600 | 141.9499969482422000 |  0.00204303311839874783 |
  |     MRO      |       NYQ       |     Marathon Oil Corporation    | 0.0005965698099807201  | 21059426304  |     0.731      |  0.0399990081787110 |    2023-01-10   | 27.0499992370605470  | 27.0100002288818360  |  0.00148089625471161593 |
  |     CTRA     |       NYQ       |       Coterra Energy Inc.       | 0.0005690298110137097  | 20087240704  |     5.204      | -0.3899993896484380 |    2023-01-10   | 24.6900005340576170  | 25.0799999237060550  | -0.01555021494556719473 |
  |     TRGP     |       NYQ       |      Targa Resources, Inc.      | 0.0004842024080652878  | 17092760576  |     0.773      |         None        |       None      |         None         |         None         |           None          |
  |     APA      |       NMS       |         APA Corporation         | 0.00039910236086756015 | 14088655872  |     0.374      |  0.5799980163574200 |    2023-01-10   | 43.8199996948242200  | 43.2400016784668000  |  0.01341345961709929139 |
  |     EQT      |       NYQ       |         EQT Corporation         | 0.0003576031445812937  | 12623697920  |     1.092      | -0.6399993896484380 |    2023-01-10   | 33.4000015258789060  | 34.0400009155273440  | -0.01880139166966068805 |
  +--------------+-----------------+---------------------------------+------------------------+--------------+----------------+---------------------+-----------------+----------------------+----------------------+-------------------------+
  ==========================
  MySQL 连接已关闭
  ```