Basic CRUD Operations

This example demonstrates basic CRUD (Create, Read, Update, Delete) operations using pytidb (the official Python SDK for TiDB).

1. Connect to TiDB using the pytidb client.
2. Create a table with text, vector, and JSON columns.
3. Run basic CRUD operations on the data.

Prerequisites

Before you begin, ensure you have the following:

• Python (>=3.10): Install Python 3.10 or a later version.
• A TiDB Cloud Starter cluster: You can create a free TiDB cluster on TiDB Cloud.

How to run

Step 1. Clone the pytidb repository

git clone https://github.com/pingcap/pytidb.git
cd pytidb/examples/basic/

Step 2. Install the required packages

python -m venv .venv
source .venv/bin/activate
pip install -r reqs.txt

Step 3. Set environment variables

1. In the TiDB Cloud console, navigate to the Clusters page, and then click the name of your target cluster to go to its overview page.
2. Click Connect in the upper-right corner. A connection dialog is displayed, with connection parameters listed.
3. Set environment variables according to the connection parameters as follows:

cat > .env <<EOF
TIDB_HOST={gateway-region}.prod.aws.tidbcloud.com
TIDB_PORT=4000
TIDB_USERNAME={prefix}.root
TIDB_PASSWORD={password}
TIDB_DATABASE=test
EOF

Step 4. Run the demo

python main.py

Expected output:

=== CREATE TABLE ===
Table created

=== TRUNCATE TABLE ===
Table truncated

=== CREATE ===
Created 3 items

=== READ ===
ID: 1, Content: TiDB is a distributed SQL database, Metadata: {'category': 'database'}
ID: 2, Content: GPT-4 is a large language model, Metadata: {'category': 'llm'}
ID: 3, Content: LlamaIndex is a Python library for building AI-powered applications, Metadata: {'category': 'rag'}

=== UPDATE ===
Updated item #1
After update - ID: 1, Content: TiDB Cloud Starter is a fully-managed, auto-scaling cloud database service, Metadata: {'category': 'dbass'}

=== DELETE ===
Deleted item #2

=== FINAL STATE ===
ID: 1, Content: TiDB Cloud Starter is a fully-managed, auto-scaling cloud database service, Metadata: {'category': 'dbass'}
ID: 3, Content: LlamaIndex is a Python library for building AI-powered applications, Metadata: {'category': 'rag'}

=== COUNT ROWS ===
Number of rows: 2

=== DROP TABLE ===
Table dropped

Basic CRUD operations completed!

Related Resources

• Source Code: View on GitHub