mysqlclient connection example with seekdb

This topic describes how to use mysqlclient and seekdb to build an application that can perform basic operations such as creating tables, inserting data, and querying data.

Click here to download the python-mysqlclient sample project

Prerequisites

You have installed Python 3.x and pip.
You have installed seekdb.

Procedure

Check the versions of Python and pip.
Install the mysqlclient library.
Obtain the seekdb connection information.
Modify the database connection information in the config.py file.
Run the main.py file.
Perform the corresponding operations in the interactive command-line interface.

Step 1: Check the versions of Python and pip

Open the command prompt or PowerShell terminal, and run the python --version and pip --version commands to ensure that Python and pip are properly installed.

Here is an example:

PS C:\Windows\system32> python --version
Python 3.11.2
PS C:\Windows\system32> pip --version
pip 23.3.1 from C:\Users\xxx\AppData\Local\Programs\Python\Python311\Lib\site-packages\pip (python 3.11)

Step 2: Install the mysqlclient library

The mysqlclient library is a high-performance MySQL client library implemented in C, suitable for scenarios with high performance requirements. When installing the mysqlclient library, you need to compile and link the MySQL C API to connect to seekdb.

Windows environment

Method 1: Install the precompiled binary file (recommended)

Download the whl file that is compatible with your Python version and operating system platform from Download files.
Open the command prompt or PowerShell terminal, go to the directory where the whl file is stored, and run the following command to install it:
```
pip install mysqlclient-2.2.0-cp311-cp311-win_amd64.whl
```

Method 2: Install directly

Install MySQL or MySQL Connector/C and ensure that the development components are installed.
Install a C compiler, such as Visual Studio or MinGW.
Run the following command to install the mysqlclient library:
```
pip install mysqlclient
```

Linux environment

Install the Python development package:
```
sudo yum install python3-devel
```
Install the MySQL development library:
```
sudo yum install mysql-devel
```
Run the following command to install the mysqlclient library:
```
sudo pip install mysqlclient
```

info

The mysqlclient library is a high-performance MySQL client library, suitable for production environments with high performance requirements.

Step 3: Obtain the seekdb connection information

Contact the seekdb deployment personnel or administrator to obtain the database connection string.

mysql -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

$host: the IP address for connecting to seekdb. Replace this with the actual IP address. You can also use the local IP address or 127.0.0.1.
$port: the port for connecting to seekdb. Replace this with the actual port. The default port is 2881, which can be customized when deploying seekdb.
$database_name: the name of the database to be accessed.
$user_name: the connection account. Format: username.
$password: the password of the account.

For more information about the connection string, see Connect to seekdb by using a MySQL client.

Here is an example:

mysql -hxxx.xxx.xxx.xxx -P2881 -uroot -p****** -Dtest

Step 4: Modify the database connection information in the config.py file

Modify the database connection information in the python-mysqlclient/config.py file based on the information obtained in Step 3: Obtain the seekdb connection information.

Go to the python-mysqlclient project folder.
Modify the database connection information in the config.py file.
- In a Windows environment, open the config.py file in a text editor and modify the database connection information to match the actual situation.
- In a Linux environment, use the vi config.py or vim config.py command to edit the config.py file and modify the database connection information to match the actual situation.
Here is an example of the database connection information in the config.py file:
```
OCEANBASE_CONFIG = {
    'host': '10.10.10.1',
    'port': 2881,
    'user': 'root',
    'password': '******',
    'database': 'test',
    'charset': 'utf8mb4'
}
```

Step 5: Run the main.py file

Open the command prompt or PowerShell terminal and run the python main.py command to start the program.

Go to the python-mysqlclient project directory.

Here is an example:
```
cd /home/admin/python-mysqlclient
```

Run the following command to start the main.py program.

python main.py

The returned result is as follows:

Table created successfully
Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
Enter the command [1/2/3]>

Step 6: Perform the corresponding operations in the interactive command-line interface

Example of successful data insertion.
1. In the interactive command-line interface, enter 1 and press Enter.
  
  Here is an example:
```
Enter the command [1/2/3]> 1
```
2. After the Enter name: prompt, enter your name and press Enter.
  
  Here is an example:
```
Enter name：A1
```
3. After the Enter age: prompt, enter your age and press Enter.
  
  Here is an example:
```
Enter age：18
```
4. The system returns a message indicating that the data was inserted successfully: Record inserted successfully. Then, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operation.
  
  Here is an example:
```
Record inserted successfully
Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
Enter the command [1/2/3]>
```
Example of failed data insertion.
1. In the interactive command-line interface, enter 1 and press Enter.
  
  Here is an example:
```
Enter the command [1/2/3]> 1
```
2. After the Enter name: prompt, enter your name and press Enter.
  
  Here is an example:
```
Enter name：A2
```
3. After the Enter age: prompt, enter your age and press Enter.
  
  info
  The age field is of the integer type.
  
  Here is an example:
```
Enter age: eighteen
```
4. The system returns an error message: (1366, 'Incorrect integer value'). Then, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operation.
  
  Here is an example:
```
(1366, 'Incorrect integer value')
Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
Enter the command [1/2/3]>
```
Example of data query.
1. In the interactive command-line interface, enter 2 and press Enter.
  
  Here is an example:
```
Enter the command [1/2/3]> 2
```
2. The system displays the data in the table. Then, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operation.
  
  Here is an example:
```
(1, 'A1', 18)
Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
Enter the command [1/2/3]>
```
Example of entering an invalid command.
1. In the interactive command-line interface, enter a command other than 1/2/3 and press Enter.
  
  Here is an example:
```
Enter the command [1/2/3]> A
```
2. The system returns an error message: Invalid command, please enter command again [1/2/3]. Then, it displays a message indicating that you can enter 1, 2, or 3 and press Enter to perform the corresponding operation.
  
  Here is an example:
```
Invalid command, please enter command again [1/2/3]
Instruction: 1.Insert Data; 2.Query Data; 3.Exit.
Enter the command [1/2/3]>
```
Example of exiting the program.

In the interactive command-line interface, enter 3 and press Enter to exit the program.

Here is an example:
```
Enter the command [1/2/3]> 3
```

Project code introduction

Click python-mysqlclient to download the project code, which is a compressed file named python-mysqlclient.zip.

After decompressing it, you will find a folder named python-mysqlclient. The directory structure is as follows:

python-mysqlclient
├── config.py
├── db.py
├── main.py
└── requirements.txt

File description:

config.py: Manages database connection configuration information.
db.py: Performs database operations, including table creation, data insertion, and data queries.
main.py: The entry point of the application, featuring a simple user interface for command-based interactions.
requirements.txt: Lists the required Python libraries for the project.

info
The code obtained in this article only lists the version requirements for the mysqlclient library. You can install the required libraries by running the pip install -r requirements.txt command.

config.py code introduction

When you use Python to connect to a database, you need to specify the database connection parameters. You can store these parameters in a separate configuration file, such as config.py. By encapsulating these parameters in a dictionary, you can avoid repeatedly writing these parameters in each file. You can directly reference the dictionary in other Python files to connect to the database.

The config.py file obtained in this topic defines a dictionary variable named OCEANBASE_CONFIG for managing seekdb connection parameters.

Sample code:

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': 2881,  # Default port. You can change the port as needed.
    'user': 'root',
    'password': '',
    'database': 'db_name',
    'charset': 'utf8mb4'
}

Parameter description:

host: the IP address for connecting to seekdb. You must replace this parameter with an actual IP address. You can also use the local IP address or 127.0.0.1.
port: the port for connecting to seekdb. You must replace this parameter with an actual port. The default value is 2881. You can customize the port when you deploy seekdb.
user: the username for connecting to the database.
password: the password for connecting to the database.
database: the name of the database to connect to.
charset: the character set to use when connecting to the database.

info

The specific parameters depend on the project requirements and the characteristics of the database. We recommend that you adjust and configure the parameters as needed.

db.py code introduction

The db.py file is a Python module that encapsulates database operations. It is mainly used to implement database operations such as adding, deleting, modifying, and querying data.

The file includes the following parts:

Import the MySQLdb module and the database connection parameters.

Sample code:
```
import MySQLdb
from config import OCEANBASE_CONFIG
```

Define a function for creating a table.

Define the create_table function. This function creates a table named test_tbl1 in seekdb. The with statement manages the lifecycle of the database connection and cursor objects. This ensures the safe closure of the database connection and cursor objects, preventing memory leaks. The function defines an SQL statement, executes the SQL statement, and prints the execution result or exception information.

Sample code:

def create_table():
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                create_table_sql = """
                    CREATE TABLE test_tbl1 (
                        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                        name VARCHAR(50) NOT NULL,
                        age INT UNSIGNED NOT NULL,
                        PRIMARY KEY (id)
                    ) ENGINE=OCEANBASE AUTO_INCREMENT=1
                """
                cursor.execute(create_table_sql)

                print("Table created successfully")
            except MySQLdb.Error as err:
                print(err)

Define a function for inserting data.

Define the insert_record function. This function inserts a record containing the name and age fields into the specified table. The with statement manages the lifecycle of the database connection and cursor objects. This ensures the safe closure of the database connection and cursor objects, preventing memory leaks. The function defines an SQL statement, executes the insert operation, commits the transaction, and prints the execution result or exception information.

Sample code:

def insert_record(table_name, name, age):
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                insert_sql = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"
                cursor.execute(insert_sql, (name, age))
                conn.commit()

                print("Record inserted successfully")
            except MySQLdb.Error as err:
                print(err)

Define a function for querying table data.

Define the select_all function. This function queries all records in the specified table. The with statement manages the lifecycle of the database connection and cursor objects. This ensures the safe closure of the database connection and cursor objects, preventing memory leaks. The function defines an SQL statement, executes the query, and traverses the query results to print all records. If an exception occurs, the exception is caught and printed.

Sample code:

def select_all(table_name):
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                select_sql = f"SELECT * FROM {table_name}"
                cursor.execute(select_sql)
                result = cursor.fetchall()
                for row in result:
                    print(row)

            except MySQLdb.Error as err:
                print(err)

main.py code introduction

The main.py file demonstrates how to use Python and the MySQLdb module to operate a database. It also provides an interactive command-line interface for you to perform the corresponding operations. You can use this program to create a table, insert a record, and query all records, thereby implementing basic database operations.

The file includes the following parts:

Import the functions defined in the db.py file.

Import the create_table, insert_record, and select_all functions from the db module.

Sample code:
```
from db import create_table, insert_record, select_all
```
Define a function for operating the database.

Define the main function. This function implements a simple command-line interactive program for operating the database. It first calls the create_table function to create a table named test_tbl1. Then, it enters a while loop to wait for user commands. Based on the user's selection, the program calls different functions to perform insert or query operations until the user enters the 3 command to exit the program. If the user enters an invalid command, the program prompts the user to re-enter the command.

Based on the different commands entered, the program performs the following operations: insert data (1), query data (2), and exit the program (3).

Sample code:
```
def main():
    create_table()
    while True:
        print("Instruction: 1.Insert Data; 2.Query Data; 3.Exit.")

        command = input("Enter the command [1/2/3]> ")
        if command == "1":
            name = input("Enter name：")
            age = input("Enter age：")
            insert_record("test_tbl1", name, age)
        elif command == "2":
            select_all("test_tbl1")
        elif command == "3":
            break
        else:
            print("Invalid command, please enter command again [1/2/3]")
```
Set the usage scenario of the main function.

Set the main function to be called only when main.py is directly run. If the program is imported into another module, the main function is not executed.

Sample code:
```
if __name__ == "__main__":
    main()
```
Note
This approach prevents the main function from being automatically executed when the module is imported, ensuring the reusability and scalability of the program.

Complete code

tab config.py

OCEANBASE_CONFIG = {
    'host': 'localhost',
    'port': 2881,  # Default port. You can change the port as needed.
    'user': 'user_name',
    'password': '',
    'database': 'db_name',
    'charset': 'utf8mb4'
}

tab db.py

import MySQLdb
from config import OCEANBASE_CONFIG


def create_table():
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                create_table_sql = """
                    CREATE TABLE test_tbl1 (
                        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                        name VARCHAR(50) NOT NULL,
                        age INT UNSIGNED NOT NULL,
                        PRIMARY KEY (id)
                    ) ENGINE=OCEANBASE AUTO_INCREMENT=1
                """
                cursor.execute(create_table_sql)

                print("Table created successfully")
            except MySQLdb.Error as err:
                print(err)


def insert_record(table_name, name, age):
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                insert_sql = f"INSERT INTO {table_name} (name, age) VALUES (%s, %s)"
                cursor.execute(insert_sql, (name, age))
                conn.commit()

                print("Record inserted successfully")
            except MySQLdb.Error as err:
                print(err)


def select_all(table_name):
    with MySQLdb.connect(**OCEANBASE_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                select_sql = f"SELECT * FROM {table_name}"
                cursor.execute(select_sql)
                result = cursor.fetchall()
                for row in result:
                    print(row)

            except MySQLdb.Error as err:
                print(err)

tab main.py

from db import create_table, insert_record, select_all

def main():
    create_table()
    while True:
        print("Instruction: 1.Insert Data; 2.Query Data; 3.Exit.")

        command = input("Enter the command [1/2/3]> ")
        if command == "1":
            name = input("Enter name：")
            age = input("Enter age：")
            insert_record("test_tbl1", name, age)
        elif command == "2":
            select_all("test_tbl1")
        elif command == "3":
            break
        else:
            print("Invalid command, please enter command again [1/2/3]")

if __name__ == "__main__":
    main()

Error Handling

When using mysqlclient to connect to seekdb, you may encounter various errors. Here are some common errors and their solutions:

Connection Error: If you cannot connect to the database, check if the connection parameters are correct, including the hostname, port, username, password, and database name.
Permission Error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.
SQL Syntax Error: If there is a syntax error in the SQL statement, check if the SQL statement is correctly formatted.
Data Type Error: If the data type of the inserted data does not match the table definition, ensure that the data type is correct.

In the code, we use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully without crashing.

References

For more information about how to connect to seekdb, see Overview of connection methods.

Prerequisites​

Procedure​

Step 1: Check the versions of Python and pip​

Step 2: Install the mysqlclient library​

Windows environment​

Linux environment​

Step 3: Obtain the seekdb connection information​

Step 4: Modify the database connection information in the config.py file​

Step 5: Run the main.py file​

Step 6: Perform the corresponding operations in the interactive command-line interface​

Project code introduction​

config.py code introduction​

db.py code introduction​

main.py code introduction​

Note

Complete code​

Error Handling​

References​

Contents

Prerequisites

Procedure

Step 1: Check the versions of Python and pip

Step 2: Install the mysqlclient library

Windows environment

Linux environment

Step 3: Obtain the seekdb connection information

Step 4: Modify the database connection information in the config.py file

Step 5: Run the main.py file

Step 6: Perform the corresponding operations in the interactive command-line interface

Project code introduction

config.py code introduction

db.py code introduction

main.py code introduction

Complete code

Error Handling

References