--- title: "Working with TiDB Using mysql-connector-python" version: "v2" status: "GA" description: "In this lab, you will learn how to: Connect to, access and modify data in TiDB Cloud Serverless cluster by Python code; Use prepared statements and batch inserts to improve the performance of your application; Use AUTO_RANDOM instead of AUTO_INCREMENT to avoid hotspots; Use online DDL to perform online schema changes; Use and compare pessimistic and optimistic modes for transaction concurrency control." --- # Working with TiDB Using mysql-connector-python ## Overview TiDB is a distributed, cloud-native, and horizontally scalable database that supports hybrid transactional and analytical processing (HTAP). For application developers, TiDB is a database that is always available, reliable, and capable of scaling without limits. + In this lab, you will learn how to: + Connect to, access and modify data in TiDB Cloud Serverless cluster by Python code. + Use prepared statements and batch inserts to improve the performance of your application. + Use `AUTO_RANDOM` instead of `AUTO_INCREMENT` to avoid hotspots. + Use online DDL to add columns to a table without blocking or interrupting ongoing transactions. + Use and compare pessimistic and optimistic modes for concurrency control. ### Duration + This lab should take you approximately 90 minutes to complete. > [!NOTE] > In this lab, you will work with a web-based VS Code. Using a web-based IDE is NOT compulsory in TiDB. However, it is a common approach in cloud database collaborations. By deploying a developer client close to the cloud database, we can better simulate the application server's real-world behavior and network conditions. > [!TIP] > Once the lab environment is deployed, click **Next** on the bottom bar to begin **Module 1**. ## Module 1: Prepare to Run the Python Code ### Step 1. Open a terminal in IDE + Enter your own [VS Code](${LAB:SECURITY_ACCESS_PROXY_URL}?next=${LAB:"/vscode/?folder=/root/tidb-lab-python"|urlencode}) in the browser. + Click `Yes, I trust the authors` in the popup window. ![90%](https://lab-static.pingcap.com/quick-demo/vscode-trust.jpg) + Click on `≡`, `Terminal`, then `New Terminal` to open a new terminal in the VS Code. ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-open-terminal-0307.jpg) ### Step 2. Install Python dependencies + Run the following commands in the VS Code terminal to install the required dependencies. ```bash poetry install ``` ```output Creating virtualenv tidb-lab-python-CQ_Jo6Zv-py3.11 in /root/.cache/pypoetry/virtualenvs Installing dependencies from lock file Package operations: 4 installs, 0 updates, 0 removals - Installing python-dotenv (1.0.1) - Installing dotenv (0.9.9) - Installing mysql-connector-python (9.2.0) - Installing pymysql (1.1.1) ``` ### Step 3. Create the .env file + Create a `.env` file under folder `tidb-lab-python/`. ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-new-env-0307.jpg) + Add the following content to the `.env` file and save it. ```properties TIDB_HOST=${LAB:SERVERLESS_CLUSTER_HOST} TIDB_PORT=${LAB:SERVERLESS_CLUSTER_PORT} TIDB_USER='${LAB:SERVERLESS_CLUSTER_USERNAME}' TIDB_PASSWORD='${LAB:SERVERLESS_CLUSTER_PASSWORD}' TIDB_DB_NAME=${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} ``` ## Module 2: Run the Python Code Run the Python code to connect to TiDB Cloud Serverless cluster and do some basic operations. ### Step 1. Connect to TiDB Cloud Serverless cluster + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `demo_connection.py` file, you can briefly go through the source code. This Python script will connect to the lab TiDB Cloud Serverless cluster and create a new database named `${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}`. + Run `demo_connection.py` in the VS Code terminal. ```bash poetry run python mysql_connector_python_demo/demo_connection.py ``` ```output Connected to gateway01.us-west-2.prod.aws.tidbcloud.com:4000 as 42yK4xxxxxxxxxxxxsP (Connection ID: 1233194) The server is running 5.7.28-TiDB-Serverless ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-demo-connection-0307.jpg) > [!IMPORTANT] > The connection string is stored in the `.env` file. > We will use the `get_connection` function in `demo_connection.py` to get a connection to the TiDB Cloud Serverless Cluster for the following steps. ### Step 2. Insert data + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `positional_placeholders.py` file, you can briefly go through the source code. + Insert data by positional placeholders by executing `positional_placeholders.py`. ```bash poetry run python mysql_connector_python_demo/positional_placeholders.py ``` ```output 1 row inserted into table t1 by positional placeholders ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-positional-placeholders-0307.jpg) + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `named_placeholders.py` file, you can briefly go through the source code. + Insert data by named placeholders. ```bash poetry run python mysql_connector_python_demo/named_placeholders.py ``` ```output 1 row inserted into table t1 by named placeholders ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-named-placeholders-0307.jpg) ### Step 3. Query data + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `query_fetch_all.py` file, you can briefly go through the source code. + Query data by `fetchall()` function. ```bash poetry run python mysql_connector_python_demo/query_fetch_all.py ``` ```output PK of ABC is 1 PK of DEF is 2 ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-fetchall-0307.jpg) + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `query_fetch_one.py` file, you can briefly go through the source code. + Query data by `fetchone()` function. ```bash poetry run python mysql_connector_python_demo/query_fetch_one.py ``` ```output PK of ABC is 1 PK of DEF is 2 ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-fetchone-0307.jpg) ## Module 3: Using Prepared Statements and Batch Inserts Now, you can use Python code to read and write data to TiDB Cloud Serverless cluster. But if you want to improve the performance of your application, you can use prepared statements (for precompiled and repeated execution) and efficient batch inserts (for inserting multiple rows of data into a table in a single SQL statement). ### Step 1. Using prepared statements + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `prepared_statement.py` file, go through the sample code note line `connection.cursor(prepared=True)` and `print("Generated PK is", cursor.lastrowid)`. If you create cursor with parameter `prepared=False`, you are using the client side prepared statement only. The `cursor.lastrowid` to get the auto random ID of the recently inserted row. + Run `prepared_statement.py`. ```bash poetry run python mysql_connector_python_demo/prepared_statement.py ``` ```output The generated PK is 4035225266123964417 ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-prepared-statement-0307.jpg) ### Step 2. Using batch inserts + Then, we will compare the performance of batch inserts and single inserts. On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `batch_insert.py` file, + In this step, we will insert 1000 rows into the table `t1_batchtest`. In the batch insert function `_batch_style_insert`, we will use the `executemany` function to insert the data. On the other hand, we will insert the data row by row using the `execute` function in the single insert function `_single_style_insert`. ```bash poetry run python mysql_connector_python_demo/batch_insert.py ``` ```output Batch Inserting 1000 rows in 84.9921875 (ms). Total rows in t1_batchtest table: 1000. Non-Batch Inserting 1000 rows in 19608.541259765625 (ms). Total rows in t1_batchtest table: 1000. ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-batch-insert-0307.jpg) + As you can see, the batch insert is much faster than the single insert. ## Module 4: Use AUTO_RANDOM Instead of AUTO_INCREMENT To Avoid Hotspots + In a distributed database like TiDB, data is divided into regions and distributed across multiple storage nodes (TiKV nodes). + `AUTO_INCREMENT` Problem: + When using `AUTO_INCREMENT`, IDs are sequential (1, 2, 3, 4, ...) + New inserts all go to the same region where the latest IDs are stored + This creates a "hotspot" where one storage node handles most write traffic + The rest of the storage nodes are underutilized + This bottleneck can severely limit write throughput + `AUTO_RANDOM` Solution: + `AUTO_RANDOM` generates non-sequential, distributed IDs + These shard bits help distribute writes across different regions + The INSERT workload is balanced across all storage nodes + This improves write throughput and scalability for high-concurrency scenarios ```bash poetry run python mysql_connector_python_demo/auto_random.py ``` ```output ... AUTO_RANDOM IDs (distributed to avoid hotspots): 3 288230376151711748 1441151880758558729 1729382256910270465 3458764513820540934 3458764513820540935 4899916394579099650 5764607523034234888 6917529027641081866 7782220156096217093 AUTO_INCREMENT IDs (sequential, can cause hotspots): 1 2 3 4 5 6 7 8 9 10 ... ``` ## Module 5: Use Online DDL To Perform Online Schema Changes + This module illustrates how to perform **online schema changes (DDL operations)** in **TiDB** while ensuring that an ongoing **INSERT job** continues running without interruption. A key challenge in this process is handling **error code 8028**, which occurs when schema mutations temporarily block transactions. The demo demonstrates a robust approach to managing this scenario, allowing the **insert job** to recover and continue despite the schema change. + This module consists of two main operations: 1. **Insert Job (`--insert`)** + Continuously inserts new records into a specified table. + Handles potential schema mutation exception (error code **8028**) that may occur due to concurrent DDL operations. + Retries the insert operation in case of failure to maintain service availability. 2. **Schema Modification (`--alter`)** + Executes an `ALTER TABLE` command to modify the table structure while inserts are in progress. + Demonstrates that TiDB allows **online schema changes**, but transactions may momentarily fail due to schema mutations. + Ensures that the new schema is successfully applied while ongoing transactions adapt accordingly. > [!TIP] > **Key Implementation Details:** > > + **Handling TiDB Metadata Lock**: The script **disables metadata locking** (`SET GLOBAL tidb_enable_metadata_lock = OFF`) to allow DDL changes without blocking queries. > + **Automatic Error Handling**: The insert job **detects error code 8028** and **retries failed transactions** to prevent crashes. > + **Concurrent Execution**: The script enables running the **insert job** and **schema modification** in separate terminals, simulating a real-world scenario where database updates occur alongside ongoing transactions. + Run the insert job first. ```bash poetry run python mysql_connector_python_demo/online_ddl.py --insert ``` ```output Starting insert job on table online_ddl_test You can now run ALTER TABLE in another terminal with: python online_ddl.py --alter online_ddl_test Connected to TiDB: 2nR9s2Y6oh4rkp7.6z3VQEZ4@gateway01.us-west-2.prod.aws.tidbcloud.com:4000 using database test Disabled TiDB metadata lock for online DDL operations Current setting: tidb_enable_metadata_lock = OFF Created table online_ddl_test Inserting record 0 Inserting record 1 Inserting record 2 Inserting record 3 Inserting record 4 Inserting record 5 ... ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-online-ddl-0319.jpg) + Create a new terminal by clicking the `+` button in the VS Code. ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-add-terminal-0319.jpg) + Run the schema modification in the new terminal. ```bash poetry run python mysql_connector_python_demo/online_ddl.py --alter ``` ```output Executing ALTER TABLE on online_ddl_test Connected to TiDB: 2nR9s2Y6oh4rkp7.6z3VQEZ4@gateway01.us-west-2.prod.aws.tidbcloud.com:4000 using database test Table online_ddl_test exists, proceeding with ALTER TABLE Executing: ALTER TABLE online_ddl_test ADD COLUMN (ed VARCHAR(10) DEFAULT 'N/A') ALTER TABLE completed successfully in 0.39 seconds Updated table structure: id: int(11) k: int(11) c: char(120) pad: char(60) ed: varchar(10) ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-alter-table-0319.jpg) + On VS Code, switch back to the first terminal, observe the insert job will continue to run with a retry log. ```output ... Inserting record 105 Inserting record 106 Schema mutation encountered, retrying... Inserting record 107 Inserting record 108 ... ``` ![90%](https://public-pingcap-innovation.s3.us-west-2.amazonaws.com/demo-405/405-error-catch.jpg) + Neither the insert DML nor the DDL operation are blocked. ## Module 6: Concurrency Control - Pessimistic Mode Vs. Optimistic Mode In any database that supports concurrent access, there's a risk of conflicts, where multiple transactions may attempt to update the same data simultaneously. While a well-designed data model can minimize the chances of such conflicts, they remain a possibility and must be effectively managed. Databases offer mechanisms to handle concurrent data access, with the two primary approaches being pessimistic and optimistic concurrency control. TiDB supports both approaches. This module shows both pessimistic lock and optimistic lock behaviors in TiDB. ### Step 1. Read the pessimistic mode demo code + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_pessimistic.py` file. Let's go through it. + In the following code, two threads are started. Each thread will create a connection to TiDB. ```output ... t1 = threading.Thread(target=thread1_task) t2 = threading.Thread(target=thread2_task) t1.start() t2.start() ... ``` + In the following code, `threading.Event` is used to coordinate the actions of thread 1 and thread 2, so that the conflict is guaranteed to happen. Both thread 1 and 2 have auto commit disabled. ```output def thread1_task(): conn1 = get_connection(autocommit=False) cur1 = conn1.cursor(dictionary=True) cur1.execute("BEGIN PESSIMISTIC;") print("[Thread1] BEGIN PESSIMISTIC", flush=True) # notify thread2 to begin transaction thread1_begin.set() # update and hold lock cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;") print("[Thread1] UPDATED row → name='conn1'", flush=True) # notify thread2 to begin transaction thread1_updated.set() # wait thread2 to attempt update (blocked) thread2_ready.wait() cur1.execute("COMMIT;") print("[Thread1] COMMITTED", flush=True) ... ``` ```output def thread2_task(): conn2 = get_connection(autocommit=False) cur2 = conn2.cursor(dictionary=True) # wait thread1 to complete BEGIN thread1_begin.wait() cur2.execute("BEGIN PESSIMISTIC;") print("[Thread2] BEGIN PESSIMISTIC", flush=True) # wait thread1 to update and hold lock thread1_updated.wait() # notify thread1 that I will attempt UPDATE thread2_ready.set() print("[Thread2] attempting UPDATE (will block)...", flush=True) # this line will be blocked until thread1 COMMIT release row lock cur2.execute("UPDATE users SET name = 'conn2' WHERE id = 1;") print("[Thread2] UPDATED row → name='conn2'", flush=True) cur2.execute("COMMIT;") print("[Thread2] COMMITTED", flush=True) ... ``` ### Step 2. Run the demo in pessimistic mode + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_pessimistic.py` file. Let's go through it. + Run the demo code in pessimistic mode and observe the output. Note that thread two is not able to commit until thread 1 commits. ```bash poetry run python mysql_connector_python_demo/lock_pessimistic.py ``` ```output Create database lab_demo_405_3450001 [Thread1] BEGIN PESSIMISTIC [Thread2] BEGIN PESSIMISTIC [Thread1] UPDATED row → name='conn1' [Thread2] attempting UPDATE (will block)... [Thread1] COMMITTED [Thread2] UPDATED row → name='conn2' [Thread2] COMMITTED Update Result: name is changed to conn2 ``` ### Step 3. Read the optimistic mode demo code + On the left **EXPLORER** of the VS Code, under folder `mysql_connector_python_demo`, click `lock_optimistic.py` file. Let's go through it. + The demo code for optimistic lock is similar to the one for pessimistic lock, except that `try` and `except` logic is implemented for both threads with optimistic lock. Also, `threading.Event` is not used in the demo code for optimistic lock. + We take thread 1 as an example. ```output def thread1_task(): ... cur1.execute("BEGIN OPTIMISTIC;") print("[Thread1] BEGIN OPTIMISTIC", flush=True) # update cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;") print("[Thread1] UPDATED row → name='conn1'", flush=True) ... # thread1 try to commit. # error(s) will be raised if there is a conflict try: cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;") print("[Thread1] UPDATED row → name='conn1'", flush=True) print("[Thread1] Try to commit", flush=True) cur1.execute("COMMIT;") print("[Thread1] Commit Successful", flush=True) except mysql.connector.Error as err: print("[Thread1] Conflict occurs: {}".format(err), flush=True) print("[Thread1] Retry", flush=True) try: cur1.execute("BEGIN OPTIMISTIC;") print("[Thread1 retry] BEGIN OPTIMISTIC", flush=True) cur1.execute("UPDATE users SET name = 'conn1' WHERE id = 1;") print("[Thread1 retry] UPDATED row → name='conn1'", flush=True) cur1.execute("COMMIT;") print("[Thread1 retry] Commit successful", flush=True) except mysql.connector.Error as err: print("[Thread1 retry] Conflict occurs: {}".format(err), flush=True) ``` ### Step 4. Run the demo in optimistic mode + You can run the demo code for optimistic lock using the following command. Since we do not use `threading.Event` to control the execution sequence, there are many possible outputs. We list three typical outputs here. In the first sample output, `Thread 2` has a conflict and then retried. In the second sample output, `Thread 1` has a conflict. In the third sample output, there is no conflict. + However, no transaction lock waits will occur in this scenario. If a conflict arises, the resolution is left to your discretion. In our demo, a simple retry will be executed. ```bash poetry run python mysql_connector_python_demo/lock_optimistic.py ``` ```output [Thread1] BEGIN OPTIMISTIC [Thread1] UPDATED row → name='conn1' [Thread1] UPDATED row → name='conn1' [Thread1] Try to commit [Thread2] BEGIN OPTIMISTIC [Thread1] Commit Successful [Thread2] UPDATED row → name='conn2' [Thread2] Try to commit [Thread2] Conflict occurs: 9007 (HY000): Write conflict, txnStartTS=458156067333603352, conflictStartTS=458156067333603338, conflictCommitTS=458156067333603359, key=????, reason=Optimistic [try again later] [Thread2] Retry [Thread2 retry] BEGIN OPTIMISTIC [Thread2 retry] UPDATED row → name='conn2' [Thread2 retry] Commit successful Update Result: name is changed to conn2 ``` ```output [Thread2] BEGIN OPTIMISTIC [Thread2] UPDATED row → name='conn2' [Thread2] Try to commit [Thread1] BEGIN OPTIMISTIC [Thread2] Commit Successful [Thread1] UPDATED row → name='conn1' [Thread1] UPDATED row → name='conn1' [Thread1] Try to commit [Thread1] Conflict occurs: 9007 (HY000): Write conflict, txnStartTS=458156066350563402, conflictStartTS=458156066350563397, conflictCommitTS=458156066350563406, key=????, reason=Optimistic [try again later] [Thread1] Retry [Thread1 retry] BEGIN OPTIMISTIC [Thread1 retry] UPDATED row → name='conn1' [Thread1 retry] Commit successful Update Result: name is changed to conn1 ``` ```output [Thread2] BEGIN OPTIMISTIC [Thread2] UPDATED row → name='conn2' [Thread2] Try to commit [Thread2] Commit Successful [Thread1] BEGIN OPTIMISTIC [Thread1] UPDATED row → name='conn1' [Thread1] UPDATED row → name='conn1' [Thread1] Try to commit [Thread1] Commit Successful Update Result: name is changed to conn1 ``` ### Step 5. Go to the TiDB Cloud Console to start your own journey + The hands-on lab ends here. You can now start your TiDB Cloud journey [here](https://tidbcloud.com/clusters). If you had been using your own free-tier cluster for this lab, you'll find it listed on the same page, the cluster name is ${LAB:SERVERLESS_CLUSTER_NAME}. ## Conclusion In this lab, you have learned how to connect to TiDB Cluster by Python Code. You have also learned how to use the `mysql-connector-python` library to interact with TiDB. Include connect, insert, query one and query many. Then, we went through the prepared statements and batch inserts to improve the performance of your application. After that, we learned how to use `AUTO_RANDOM` to avoid hotspots and how to perform an online schema change without interrupting business. Finally, we showed you the pessimistic lock and optimistic lock for concurrency control in TiDB. ```hbsptform { "ui": { "titleLevel": 3 }, "portalId": "4466002", "formId": "89fa8368-eba1-4b84-b91e-eb9eb6613ddf", "region": "na1", "sfdcCampaignId": "701RC00000VqCODYA3" } ```