## モジュール 2: Python から TiDB Cloud クラスタへのアクセス

### 手順 1. サンプルデータのインポート

+ サンプルデータベースを作成し、サンプル**S&P 500**データをインポートします。

> [!TIP]
> この手順でインポートスクリプトから発生する`Access Denied`メッセージは安全に無視できます。`LOCK TABLE`はTiDBのような分散SQLデータベースでは必要ありません。

  ```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` パネルが開いている場合は閉じて、クラスタの概要ページで Core Metrics（主要指標）を確認してください。消費されたRUが2秒未満の間だけ4Kに到達したことに気付くでしょう。これはデータインポート中、一時的に最大値で性能が制限されたことを意味します。プロビジョニング済みRU（RCU）は2Kですが、バーストで最大4Kまで利用可能です。2K RUを超えた時間が非常に短かったため、自動スケールは発動しませんでした。例えば以下のように確認できます。

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

- ラボの手順を非常に素早く実行した場合、次のような状況が発生することもあります。クラスタ作成直後、2Kの最小RCUが完全に有効になるまでに約1分かかります。バーストリソースがまだ利用可能な間に2K RUを超える負荷が発生すると、クラスタは即座に追加のプロビジョニングRCU（ただし最大4Kまで）を付与する必要があると判断します。しかし4Kからほぼ0までワークロードが急激に減少すると、Provisioned RCUも2Kに戻ります。

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

- [Terminal 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 rows in set, 1 warning (0.19 sec)
  ```

### 手順 3. 分析クエリを実行するスクリプトの作成

+ OSプロンプトに戻ります。

  ```sql
  EXIT;
  ```

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

+ Python 仮想環境を作成します。

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

+ 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 connection is closed")
  EOF
  ls -l test01.py
  ```

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

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

+ Create a Python script.

  ```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"))
    # Executing the query
    cursor.execute(query)
    print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f"))

    # Fetching all rows from the last executed query
    results = cursor.fetchall()

    # Creating a PrettyTable object
    my_table = PrettyTable()

    # Adding field names (column names) to the table
    field_names = [i[0] for i in cursor.description]
    my_table.field_names = field_names

    # Adding rows to the table
    for row in results:
        my_table.add_row(row)

    # Printing the table
    print(my_table)
    print("==========================")

  try:
    # Establishing the connection
    conn = mysql.connector.connect(**config)

    # Creating a cursor object
    cursor = conn.cursor()

    # Your SQL query
    queryName= "■■■Query 1: Market capitalization by industry■■■"
    query = """
    SELECT
        `sector`,
        SUM(`market_cap`) AS `total_market_cap`
    FROM
        `companies`
    GROUP BY
        `sector`;
    """
    execQuery(query, queryName)

    queryName= "■■■Query 2: Market capitalization ranking in the energy industry■■■"
    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:
    # Closing the connection
    if conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection is closed")
  EOF
  ls -l test01.py
  ```

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

{% endif %}

### 手順 4. スクリプトを実行して分析クエリを実行する

+ Pythonスクリプトを実行して、サンプルデータから2つのレポートを取得します。経過時間に注目してください。

{% 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 connection is closed
  ```