Connecting to TiDB cluster with SQLAlchemy

English | 中文

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

TiDB is a MySQL-compatible database. And SQLAlchemy is the Python SQL toolkit and ORM(Object Relational Mapper).

Prerequisites

• Python 3.10 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-sqlalchemy-quickstart.git
cd tidb-python-sqlalchemy-quickstart

2. Install dependencies (including SQLAlchemy and PyMySQL)

pip install -r requirements.txt

Why do we need PyMySQL?

SQLAlchemy is an ORM library that supports multiple databases. It is a high-level abstraction of the database, which can help us write SQL statements in a more object-oriented way. However, it does not provide a database driver. We need to install a database driver to connect to the database. In this sample project, we use PyMySQL as the database driver, which is a pure Python MySQL client library that is compatible with MySQL and TiDB, can be easily installed in all platforms.

You can also use other database drivers, such as mysqlclient and mysql-connector-python, but they are not pure Python libraries, so you need to install the corresponding C/C++ compiler and MySQL client library to compile them. For more information, refer to SQLAlchemy's 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='{ca_path}'

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

   TiDB Serverless requires a secure connection, 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 sqlalchemy_example.py

5. Expected output

Expected output

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.