Python中TDengine stmt API的使用方法及向TDengine表批量插入多行数据的推荐方式

Hey there! Let's walk through how to use TDengine's stmt API in Python and the best ways to batch insert data—these are common tasks when working with TDengine, so I'll keep it practical with code examples.

The stmt API shines for parameterized queries (to avoid SQL injection) and efficient repeated execution of the same SQL statement. Here's a step-by-step guide with code:

Prerequisite #

First, make sure you have the official Python client installed:

pip install taospy

Example Workflow #

import taos

# Step 1: Establish a connection to your TDengine instance
conn = taos.connect(
    host="localhost",  # Replace with your TDengine host
    user="root",
    password="taosdata",
    database="your_target_db"  # Optional: specify default database
)

try:
    # Step 2: Create a statement object
    stmt = conn.statement()

    # Step 3: Define your parameterized SQL query
    # Use ? as placeholders for parameters
    sql_query = "SELECT ts, temperature, humidity FROM weather WHERE location = ? AND ts >= ?"

    # Step 4: Bind parameters (match the order of placeholders)
    # Parameters can be a tuple or list
    stmt.bind_param(("Beijing", 1620000000000))

    # Step 5: Execute the statement
    stmt.execute()

    # Step 6: Fetch and process the results
    result_set = stmt.use_result()
    print("Query Results:")
    for row in result_set:
        print(f"Timestamp: {row[0]}, Temp: {row[1]}°C, Humidity: {row[2]}%")

finally:
    # Step 7: Clean up resources (always close stmt and connection)
    stmt.close()
    conn.close()

Key Notes #

• Parameter Binding: Using bind_param ensures your inputs are safely escaped, preventing SQL injection.
• Result Handling: use_result() retrieves the full result set; for large datasets, consider fetch_block() to get results in chunks.
• Resource Management: Wrap operations in a try-finally block (or use context managers) to ensure connections/stmts are closed properly.

For inserting multiple rows, batch operations are always better than single-row inserts—they reduce network round-trips and improve performance. Here are the two most reliable approaches:

Option 1: Use executemany (Simple & Straightforward) #

This is the easiest method for small to medium-sized datasets:

import taos

conn = taos.connect(host="localhost", user="root", password="taosdata", database="your_db")

try:
    # First, create a sample table if it doesn't exist
    with conn.cursor() as cursor:
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS weather (
                ts TIMESTAMP,
                temperature FLOAT,
                humidity INT,
                location NCHAR(20)
            )
        """)

    # Prepare your batch data (list of tuples, matching table schema)
    batch_data = [
        (1620000000000, 25.5, 60, "Beijing"),
        (1620000300000, 26.1, 58, "Beijing"),
        (1620000600000, 25.8, 59, "Shanghai"),
        (1620000900000, 28.3, 65, "Shanghai")
    ]

    # Execute batch insert with executemany
    with conn.cursor() as cursor:
        cursor.executemany(
            "INSERT INTO weather VALUES (?, ?, ?, ?)",
            batch_data
        )
    
    # Don't forget to commit the transaction!
    conn.commit()
    print(f"Successfully inserted {len(batch_data)} rows")

finally:
    conn.close()

Option 2: Use stmt API's bind_param_batch (High Performance for Large Datasets) #

If you're inserting thousands or millions of rows, the stmt API's batch binding is more efficient—it sends all data in fewer network calls:

import taos

conn = taos.connect(host="localhost", user="root", password="taosdata", database="your_db")

try:
    stmt = conn.statement()
    insert_sql = "INSERT INTO weather VALUES (?, ?, ?, ?)"

    # Prepare large batch data (could be generated or loaded from a file)
    large_batch_data = [
        (1620001200000, 27.2, 62, "Guangzhou"),
        (1620001500000, 27.8, 64, "Guangzhou"),
        # Add hundreds/thousands more rows here
    ]

    # Bind all parameters at once
    stmt.bind_param_batch(large_batch_data)
    stmt.execute()
    conn.commit()

    print(f"Successfully inserted {len(large_batch_data)} rows via stmt batch")

finally:
    stmt.close()
    conn.close()

Recommendation #

• Use executemany for small to medium batches (hundreds of rows) where simplicity matters.
• Use bind_param_batch for large datasets (thousands+ rows) to maximize performance.
• Always remember to call conn.commit() after inserts—TDengine uses transactional commits by default for batch operations.

内容的提问来源于stack exchange，提问作者zitsen