Connecting to TiDB cluster with mysqlclient

English | 中文

This a sample project written by PingCAP for mysqlcient to connect to TiDB.

TiDB is a MySQL-compatible database. And mysqlclient is a popular open-source driver for Python.

Prerequisites

• Python 3.8 or higher
• Git
• A TiDB cluster. If you don't have a TiDB cluster, you can create one as follows:
  • (Recommended) Follow Creating a TiDB Serverless Cluster to create your own TiDB Cloud cluster.
  • Follow Deploy a Local Test TiDB Cluster or Deploy a Production TiDB Cluster to create a local cluster

Getting started

1. Clone the repository

git clone https://github.com/tidb-samples/tidb-python-mysqlclient-quickstart.git
cd tidb-python-mysqlclient-quickstart

2. Install dependencies (including mysqlclient)

pip install -r requirements.txt

If this is your first time installing mysqlclient, you may encounter some problems as it is not only a pure Python package, but also requires some C extensions. To resolve them, please refer to the mysqlclient official documentation

3. Configure connection information

(Option 1) TiDB Serverless

1. In the TiDB Cloud, navigate to the Clusters page, select your TiDB Serverless cluster. Go to the Overview page, and click the Connect button in the upper right corner.

2. Ensure the configurations in the confirmation window match your operating environment.

   • Endpoint Type is set to Public
   • Connect With is set to General
   • Operating System matches your environment

   If you are running in Windows Subsystem for Linux (WSL), switch to the corresponding Linux distribution.

3. Click Create password to create a password.

   If you have created a password before, you can either use the original password or click Reset Password to generate a new one.

4. Run the following command to copy .env.example and rename it to .env:

   cp .env.example .env

5. Copy and paste the corresponding connection string into the .env file. Example result is as follows:

    TIDB_HOST='{gateway-region}.aws.tidbcloud.com'
    TIDB_PORT='4000'
    TIDB_USER='{prefix}.root'
    TIDB_PASSWORD='{password}'
    TIDB_DB_NAME='test'
    CA_PATH=''

   Be sure to replace the placeholders {} with the values obtained from the connection dialog.

   TiDB Serverless requires a secure connection. Since the ssl_mode of mysqlclient defaults to PREFERRED, you don't need to manually specify CA_PATH. Just leave it empty. But if you have a special reason to specify CA_PATH manually, you can refer to the TLS Connections to TiDB Serverless to get the certificate paths for different operating systems.

6. Save the .env file.

(Option 2) TiDB Dedicated

1. In the TiDB Cloud, select your TiDB Dedicated cluster. Go to the Overview page, and click the Connect button in the upper right corner. Click Allow Access from Anywhere and then click Download TiDB cluster CA to download the certificate.

   For more configuration details, refer to TiDB Dedicated Standard Connection.

2. Run the following command to copy .env.example and rename it to .env:

   cp .env.example .env

3. Copy and paste the corresponding connection string into the .env file. Example result is as follows:

    TIDB_HOST='{host}.clusters.tidb-cloud.com'
    TIDB_PORT='4000'
    TIDB_USER='{username}'
    TIDB_PASSWORD='{password}'
    TIDB_DB_NAME='test'
    CA_PATH='{your-downloaded-ca-path}'

   Be sure to replace the placeholders {} with the values obtained from the Connect window, and configure CA_PATH with the certificate path downloaded in the previous step.

4. Save the .env file.

(Option 3) Self-Hosted TiDB

1. Run the following command to copy .env.example and rename it to .env:

   cp .env.example .env

2. Copy and paste the corresponding connection string into the .env file. Example result is as follows:

   TIDB_HOST='{tidb_server_host}'
   TIDB_PORT='4000'
   TIDB_USER='root'
   TIDB_PASSWORD='{password}'
   TIDB_DB_NAME='test'

   Be sure to replace the placeholders {} with the values, and remove the CA_PATH line. If you are running TiDB locally, the default host address is 127.0.0.1, and the password is empty.

3. Save the .env file.

4. Run

python mysqlclient_example.py

5. Expected output

Expected output

Useful Notes

• This Python driver is relatively low-level, so you will see a lot of SQL statements in the sample app. Unlike ORMs, there is no data object, and mysqlclient represents query objects with tuples. Although Python's driver is more convenient to use than those in other languages, due to its exposure to underlying implementations and the manual transaction management required, it is still recommended to use ORMs for programming unless there is a significant need for SQL. This can reduce the coupling of your app.
• For more on how to use mysqlclient, refer to the mysqlclient official documentation.

Next Steps

• You can continue reading the developer documentation to get more knowledge about TiDB development, such as: Insert Data, Update Data, Delete Data, Single Table Reading, Transactions, SQL Performance Optimization, etc.
• If you prefer to learn through courses, we also offer professional TiDB Developer Courses, and provide TiDB certifications after the exam.