Use AI to quickly cross the novice zone: use DevChat to write Python programs-CSV import TDengine

Are you still using Baidu to search for programming? Go directly to AI to help novices quickly cross the novice zone.
The following uses one of the most common scenarios of the Internet of Things as an example to demonstrate how to use AI to quickly program.

ChatGPT4 is currently the most popular AI, but it is not available in China. But now the new DevChat allows everyone to try it out.

The following introduction comes from the introduction of Bing AI chat:

DevChat is an open source platform designed to help developers integrate artificial intelligence into code generation and documentation more effectively. It provides a very practical and efficient way for developers to interact and collaborate with large language models (LLM). DevChat aims to go beyond simple code auto-completion and limited manipulation of code snippets to make your coding life easier.

It’s not that I’m lazy. Since I’m using AI, I’ll use it to the end.

0. Environment preparation

Let’s prepare the environment first. This time, we are going to write a program to import CSV files into the TDengine database. In the early stage, you need to build a TDengine database and construct a sample CSV file.

0.1. Create database

For the installation and configuration of TDengine data, please refer to my previous articles TDengine3.0 Basic Operation and TDengine Study Notes-Cluster Installation

After the software is installed, create the database and tables for testing.

create database test vgroup 1 stt_trigger 1;
use test;
create table t1(ts timestamp,v1 int,v2 int,v3 int);

For an introduction to database parameters, see my previous article or the TDengine official website. If it is used for testing, the default parameters are sufficient and directly execute create database test;.

0.2. Constructing test data

Use a script to construct 100 rows of data. According to the best practice of TDengine, the time is directly in EPOCH format.

st=1696118400000
for i in {<!-- -->1..100}
do
    echo "${st},$i,$i,$i" >>test.csv
    st=$(($st + 1000))
done

The default time precision of the database is ms, so millisecond precision is used here, and the data time is incremented by seconds.

1. Install DevChat

Everyone is familiar with VSCode. DevChat provides a VSCode plug-in for easy integration. The method is also very simple. Please see the official blog for the specific installation method:

DevChat VSCode plug-in installation and configuration guide

The official documentation is very detailed, so I won’t take screenshots here.

2. Programming using DevChat

Programming ideas:

Read data from csv to array
Splice array data into sql
Write data to database

You still have to do this part of the work yourself, and AI cannot replace it.

2.1. Reading data from CSV to array

Close DevChat

import csv

# Function is used to read data from csv file and store in array
def read_csv(file_path):
    data = []
    with open(file_path, 'r') as file:
        reader = csv.reader(file)
        for row in reader:
            data.append(row)
    return data

#Replace with actual csv file path
csv_data = read_csv("/path/to/your/file.csv")

Click Insert Code to add code directly to the file, eliminating the need for copying and pasting.

2.2. Splicing array data into SQL

This part is a little more complicated because the maximum SQL length of TDengine is 1MB, so when we splice SQL, we need to be able to limit the length of SQL.

def create_sql_query(arr, chunk_size):
    sql_queries = []
    for i in range(0, len(arr), chunk_size):
        chunk = arr[i:i + chunk_size]
        values = ', '.join(f"'{<!-- -->item}'" for item in chunk)
        sql_query = f"INSERT INTO table (values) VALUES ({<!-- -->values})"
        sql_queries.append(sql_query)
    return sql_queries

At the same time, I also compared it with CSDN’s writing assistant.

code show as below:

sql = ""
for i in range(0, len(data), length):
    sub_data = data[i:i + length]
    sub_sql = "UPDATE table SET field IN ({})".format(",".join(sub_data))
    sql + = sub_sql

print(sql)

The implementation logic is the same, but I personally think that DevChat’s implementation of storing SQL in an array is more convenient.

2.3. Write data to the database

In order to make the code simpler and reduce the workload of DevChat, the Restful method is used to write to the database (the native method is too complicated and DevChat may not be able to handle it).

import requests
import json

# First, we need to log in to the database through RESTful method
login_url = "http://localhost:6041/rest/login/root/taosdata" # Use your username and password
login_res = requests.get(login_url)
if login_res.status_code != 200:
    print("Failed to log in!")
else:
    print("Login to database successfully!")

# Next, we insert some data
insert_url = "http://localhost:6041/rest/sql"
headers = {<!-- -->'Authorization': 'Basic cm9vdDp0YW9zZGF0YQ=='}
data = {<!-- -->
    "sql": "INSERT INTO weather.city VALUES(now, 'Beijing', 20.5, 'sunny')"
}

res = requests.post(insert_url, headers=headers, data=json.dumps(data))
if res.status_code != 200:
    print("Failed to insert data! Please check your SQL.")
else:
    print("Data inserted successfully!")

This part needs to be praised by DevChat. The code uses the Authorization parameter to connect to the TDengine database, which is much better than using plain text account passwords. It would be perfect if you could write a custom Token code.

2.4. Program modification

The program generated by AI must deviate from our actual scenario. After obtaining the code of each function block, simple modifications need to be made.

import csv
import requests
import json

# Function is used to read data from csv file and store in array
def read_csv(file_path):
    data = []
    with open(file_path, 'r') as file:
        reader = csv.reader(file)
        for row in reader:
            data.append(row)
    return data

def create_sql_query(arr, chunk_size):
    sql_queries = []
    for i in range(0, len(arr), chunk_size):
        chunk = arr[i:i + chunk_size]
        values = ' '.join(f"({<!-- -->','.join(item)})" for item in chunk)
        sql_query = f"INSERT INTO test.t1 VALUES {<!-- -->values};"
        sql_queries.append(sql_query)
    return sql_queries

csv_data = read_csv("test.csv")

# First, we need to log in to the database through RESTful method
login_url = "http://10.7.7.14:6041/rest/login/root/taosdata" # Use your username and password
login_res = requests.get(login_url)
if login_res.status_code != 200:
    print("Failed to log in!")
else:
    print("Login to database successfully!")

insert_url = "http://10.7.7.14:6041/rest/sql"
headers = {<!-- -->'Authorization': 'Basic cm9vdDp0YW9zZGF0YQ=='}

sql_list = create_sql_query(csv_data,10)

for sql in sql_list:
    print(sql)
    res = requests.post(insert_url, headers=headers, data=sql)
    if res.status_code != 200:
        print("Failed to insert data! Please check your SQL.")
    else:
        print("Data inserted successfully!")

In addition to the file name and database address, I mainly made two modifications to the above code:

Added an array traversal. This part can also be completed using DevChat, but the code is too simple to waste computing resources.
Modified the splicing method of sql. The code splicing method given by DevChat is problematic, as shown below. This kind of SQL cannot be used in any database, so this part needs to be criticized.

2.5 Running code

Finally it’s time to get to the most important part, checking out the AI.

The program runs successfully, let’s check the database

All the data is also written in.

3. Program optimization

In addition to helping write programs, DevChat can also debug and optimize programs. Select Add to DevChat to send the program as an attachment to DevChat and let it optimize

Optimized code:

import csv
import requests
import json

def read_csv(file_path):
    with open(file_path, 'r') as file:
        return list(csv.reader(file))

def create_sql_query(arr, chunk_size):
    sql_queries = []
    for i in range(0, len(arr), chunk_size):
        chunk = arr[i:i + chunk_size]
        values = ', '.join(f"({<!-- -->','.join(item)})" for item in chunk)
        sql_query = f"INSERT INTO test.t1 VALUES {<!-- -->values}"
        sql_queries.append(sql_query)
    return sql_queries

def execute_sql_query(url, headers, sql_query):
    res = requests.post(url, headers=headers, data=sql_query)
    if res.status_code != 200:
        print("Failed to insert data! Please check your SQL.")
    else:
        print("Data inserted successfully!")

def login_to_db(url):
    res = requests.get(url)
    if res.status_code != 200:
        print("Failed to log in!")
    else:
        print("Login to database successfully!")
        return True

def main():
    csv_data = read_csv("test.csv")

    login_url = "http://10.7.7.14:6041/rest/login/root/taosdata"
    if not login_to_db(login_url):
        return

    insert_url = "http://10.7.7.14:6041/rest/sql"
    headers = {<!-- -->'Authorization': 'Basic cm9vdDp0YW9zZGF0YQ=='}

    sql_queries = create_sql_query(csv_data,10)

    for sql in sql_queries:
        execute_sql_query(insert_url, headers, sql)

if __name__ == "__main__":
    main()

4. Benefits

DevChat is currently available for free trial, so don’t miss it if you pass by.

KOLs who successfully participate will have an exclusive red envelope redemption code. Before November 31, fans can use the invitation code to redeem a balance of 10 yuan on Devchat.

Red envelope code: CyqJYpDkAW2KMBGLiXywJW

DevChat official website
GitHub

Directory