tidb_crud_operations_with_mysqlclient_driver.py

1import MySQLdb
2import os
3
4# Connect to the database
5# Set environment variables or replace with your connection details
6def get_connection():
7    return MySQLdb.connect(
8        host=os.getenv("TIDB_HOST", "127.0.0.1"),
9        port=int(os.getenv("TIDB_PORT", 4000)),
10        user=os.getenv("TIDB_USER", "root"),
11        password=os.getenv("TIDB_PASSWORD", ""),
12        database=os.getenv("TIDB_DB_NAME", "test"),
13        autocommit=True
14    )
15
16def main():
17    conn = get_connection()
18    with conn.cursor() as cursor:
19        # Create table
20        cursor.execute("DROP TABLE IF EXISTS player;")
21        cursor.execute("""
22            CREATE TABLE player (
23                id VARCHAR(36) PRIMARY KEY,
24                coins INTEGER,
25                goods INTEGER
26            );
27        """)
28
29        # Insert data
30        cursor.execute("INSERT INTO player (id, coins, goods) VALUES (%s, %s, %s);", ("1", 100, 1))
31        print("Inserted 1 row.")
32
33        # Query data
34        cursor.execute("SELECT id, coins, goods FROM player WHERE id = %s;", ("1",))
35        row = cursor.fetchone()
36        print(f"Query result: id={row[0]}, coins={row[1]}, goods={row[2]}")
37
38        # Update data
39        cursor.execute("UPDATE player SET coins = %s, goods = %s WHERE id = %s;", (200, 2, "1"))
40        print("Updated 1 row.")
41
42        # Delete data
43        cursor.execute("DELETE FROM player WHERE id = %s;", ("1",))
44        print("Deleted 1 row.")
45
46    conn.close()
47
48if __name__ == "__main__":
49    main()