## Module 2: Accessing TiDB Cloud Cluster from Python ### Step 1. Import the sample data + Create the sample database, and import the sample **S&P 500** data. > [!TIP] > You can safely ignore the `Access Denied` message caused by the `LOCK TABLE` command from the import script in this step. `LOCK TABLE` is not necessary in a distributed SQL database like TiDB. ```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' %} - Back to your TiDB Cloud console, close the `Connect` panel if it's open, and review the Core Metrics in the cluster Overview. - You'll notice that the consumed RU reached 4K for less than 2 seconds; you can imagine that the performance of the data import was briefly limited by this upper RCU setting. Although the provisioned RU is 2K, you are allowed to burst up to 4K. Because you stayed above the 2K RU level for only a very short time, the Provisioned RCU did not or didn't need to auto-scale. For example: ![tidblabs-essential-rcu-monitor.jpg 80%](https://lab-static.pingcap.com/images/2025/11/13/3327f748fa256d2e8239b7dbc5066bbea0addf9b.jpg) - If you follow the lab steps very quickly, you may observe the following situation: Right after your cluster is created, it takes about 1 minute for the 2K minimum RCU to fully establish. While burst resources are still readily available, your workload that exceeds 2K RU starts running. At this point, the cluster determines it is necessary to promptly add more Provisioned RCU for you (no more than 4K of course). However, when it detects that your workload quickly drops from 4K back to near 0, the Provisioned RCU reverts to 2K. ![essential-autoscale.jpeg 75%](https://lab-static.pingcap.com/images/2025/11/14/5fae6a98cebe5982ac9f053dda729e40d0a4697e.jpeg) - Return to [Terminal 1](ssh://monitor1-ssh#1). {% endif %} ### Step 2. Access the sample data ```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) ``` ### Step 3. Create a script for executing an analytical query + Exit to OS prompt. ```sql EXIT; ``` + Create a Python virtual environment. ```bash python3 -m venv ~/.venv ~/.venv/bin/pip install mysql-connector-python prettytable ``` {% if TIDB_ENVIRONMENT == 'TiDB Cloud Starter' %} + Create a Python script. ```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")) # 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 ``` {% 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 %} ### Step 4. Run the script to execute the analytical queries + Run the Python script to get two report from the sample data. Pay attention to the elapsed time. {% 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 ■■■Query 1: Market capitalization by industry■■■ 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 | +------------------------+------------------+ ========================== ■■■Query 2: Market capitalization ranking in the energy industry■■■ 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 ```