Connect to seekdb using PyMySQL

This topic describes how to use the PyMySQL library and seekdb to build an application that performs basic database operations, such as creating tables, inserting data, querying data, and deleting tables.

Download the python-pymysql 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 PyMySQL library.
Obtain the seekdb connection information.
Modify the database connection information in the config.py file.
Run the main.py file.

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.7.0
PS C:\Windows\system32> pip --version
pip 22.3.1 from d:\python\python37\lib\site-packages\pip (python 3.7)

Step 2: Install the PyMySQL library

PyMySQL is a pure Python implementation of a MySQL client library. It is easy to install, does not require compilation, and has good cross-platform compatibility. It provides an interface for interacting with a MySQL database and supports Python 3.x.

Open the command prompt or PowerShell terminal, and run the following commands to install the PyMySQL library.

Run the following command to go to the python-pymysql directory.

Here is an example:
```
cd python-pymysql
```
Run the following command to install the required Python libraries for the project.

Here is an example:
```
pip install -r requirements.txt
```

info

You can also directly open the command prompt or PowerShell terminal and run the pip install pymysql command to install the PyMySQL library. PyMySQL is a pure Python library, which is easy to install and does not require compilation, making it suitable for use in development and testing environments.

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 during seekdb deployment.
$database_name: the name of the database to access.
$user_name: the connection account. The format is username.
$password: the password for 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-pymysql/config.py file based on the information obtained in Step 3: Obtain the seekdb connection information.

Go to the python-pymysql project folder.
Modify the database connection information in the config.py file.
- In a Windows environment, use a text editor to open the config.py file 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:
```
DB_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 main.py file to query data and output the results.

Go to the python-pymysql project directory.

Here is an example:
```
cd D:\demo\demo\python-pymysql
```

Run the main.py file.

Here is an example:

python main.py

The returned result is as follows:

2023-11-10 16:56:48,021 - INFO - Start executing the script
2023-11-10 16:56:48,021 - INFO - Start creating the table
2023-11-10 16:56:48,281 - INFO - Table creation successful
2023-11-10 16:56:48,281 - INFO - Start inserting data
2023-11-10 16:56:48,540 - INFO - Data insertion successful
(1, 'John', 20)
(2, 'Lucy', 25)
(3, 'Tom', 30)
2023-11-10 16:56:48,737 - INFO - Start dropping the table
2023-11-10 16:56:48,999 - INFO - Table dropped successfully
2023-11-10 16:56:48,999 - INFO - Script execution completed

Project code

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

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

python-pymysql
├── config.py
├── test_sql.py
├── main.py
└── requirements.txt

File description:

config.py: stores database connection information.
test_sql.py: stores SQL statements.
main.py: the main program entry, which executes basic database operations, including table creation, data insertion, data query, and table deletion.
requirements.txt: stores the required Python packages and their versions.

info
The code obtained in this topic only lists the version requirements for the PyMySQL library. You can run the sudo pip install -r requirements.txt command to install the required libraries.

Introduction to config.py

The code in the config.py file obtained in this topic defines the database connection information. The database connection information mainly includes the following parts:

Specify the IP address, port number, username, password, database name, and character set for connecting to the database.

Sample code:

DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

Parameter description:

$host: the IP address for connecting to seekdb. Replace it with the actual IP address. You can also use the local IP address or 127.0.0.1.
$port: the port number for connecting to seekdb. Replace it with the actual port number. The default port number is 2881. You can customize the port number when you deploy seekdb.
$user_name: the username for connecting to the database.
$password: the password for connecting to the database.
$database_name: the name of the database to be connected.
charset: the character set used for connecting to the database.

tip
The parameter values are determined based on the specific environment and database settings. You need to modify them as required.

Introduction to test_sql.py

The code in the test_sql.py file obtained in this topic defines the SQL statements for database operations, including table creation, data insertion, data query, and table deletion. These SQL statements can be executed after connecting to the database by using PyMySQL to implement the corresponding features.

The code in this file mainly includes the following parts:

SQL statements for creating tables.

Define the SQL statement for creating the test_pymysql table. The table has three fields: id, name, and age. The id field is an auto-incrementing primary key.

Sample code:
```
CREATE_TABLE_SQL = '''
CREATE TABLE test_pymysql (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(128) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY (id)
)
'''
```
SQL statements for inserting data.

Define an SQL statement for inserting data into the test_pymysql table. The inserted data will contain three records, each with two fields: name and age. The values of each field will be passed into the SQL statement through placeholders %s.

Sample code:
```
INSERT_DATA_SQL = '''
INSERT INTO test_pymysql (name, age) VALUES 
(%s, %s),
(%s, %s),
(%s, %s)
'''
```
SQL statements for querying data.

Define an SQL statement for querying data from the test_pymysql table.

Sample code:
```
SELECT_DATA_SQL = '''
SELECT * FROM test_pymysql
'''
```
SQL statements for deleting tables.

Define an SQL statement for deleting the test_pymysql table.

Sample code:
```
DROP_TABLE_SQL = '''
DROP TABLE test_pymysql
'''
```

Introduction to main.py

The code in the main.py file obtained in this topic calls the pymysql module to connect to the MySQL database and calls the logging module to output log information. This way, the operations of creating tables, inserting data, querying data, and deleting tables can be implemented.

The code in this file mainly includes the following parts:

Import the required modules.
1. Import the logging module.
2. Import the pymysql module.
3. Import the config.py module, which defines the database connection information.
4. Import the test_sql.py module, which defines the SQL statements for database operations.
Sample code:
```
import logging
import pymysql
from config import DB_CONFIG
from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL
```

Set the logging level and format, and output an INFO-level log message indicating that the script is about to be executed.

Sample code:

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logging.info('Start executing the script')

Define the function for creating tables.

Define a function named create_table(). Output an INFO-level log message indicating that the table is about to be created. Use the with statement to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects to avoid memory leaks. Execute the SQL statement for creating tables, commit the transaction, and output log messages, or rollback the transaction and output error log messages.

Sample code:

def create_table():
    logging.info('Start creating the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(CREATE_TABLE_SQL)
                conn.commit()
                logging.info('Table creation successful')
            except Exception as e:
                conn.rollback()
                logging.error('Table creation failed, Reason：%s' % e)

Define the function for inserting data.

Define a function named insert_data(). Output an INFO-level log message indicating that data is about to be inserted. Use the with statement to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects to avoid memory leaks. Execute the SQL statement for inserting data, commit the transaction, and output log messages, or rollback the transaction and output error log messages.

Sample code:

def insert_data():
    logging.info('Start inserting data')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                flattened_data = [d for item in data for d in item]
                cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                conn.commit()
                logging.info('Data insertion successful')
            except Exception as e:
                conn.rollback()
                logging.error('Data insertion failed, Reason：%s' % e)

Define the function for querying data.

Define a function named select_data() for querying data from the database. Use the with statement to manage the lifecycle of the database connection and cursor objects, ensuring the safe closure of the database connection and cursor objects to avoid memory leaks. Use the execute() method to execute the SQL statement defined by SELECT_DATA_SQL to query data. Use the fetchall() method to obtain the query results and use the for loop to output the results line by line.

Sample code:
```
def select_data():
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            cursor.execute(SELECT_DATA_SQL)
            result = cursor.fetchall()
            for row in result:
                print(row)
```

Define the function for deleting tables.

Define a function named drop_table(). This function uses the predefined database connection information (DB_CONFIG) and the SQL statement for deleting tables (DROP_TABLE_SQL). The function executes the table deletion operation and prints corresponding log messages indicating whether the operation is successful or failed. If the table deletion operation fails, an error message is printed.

Sample code:

def drop_table():
    logging.info('Start dropping the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(DROP_TABLE_SQL)
                conn.commit()
                logging.info('Table dropped successfully')
            except Exception as e:
                conn.rollback()
                logging.error('Table drop failed, Reason：%s' % e)

Define the program entry point, which is mainly used to execute the database operation functions.

First, determine whether the current module is the main program. If it is, perform the following operations:
1. Call the create_table() function to create the database table.
2. Call the insert_data() function to insert data into the table.
3. Call the select_data() function to query data from the table.
4. Call the drop_table() function to delete the database table.
Sample code:
```
if __name__ == '__main__':
    create_table()
    insert_data()
    select_data()
    drop_table()
```
Output an INFO-level log message indicating that the script is completed.

Sample code:
```
logging.info('Script execution completed')
```

Complete code

config.py

# Database Connection
DB_CONFIG = {
    'host': '$host',
    'port': $port,
    'user': '$user_name',
    'password': '$password',
    'database': '$database_name',
    'charset': 'utf8mb4'
}

test_sql.py

# Create table
CREATE_TABLE_SQL = '''
CREATE TABLE test_pymysql (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
  )
'''

# Insert data
INSERT_DATA_SQL = '''
INSERT INTO test_pymysql (name, age) VALUES 
(%s, %s),
(%s, %s),
(%s, %s)
'''

# Query data
SELECT_DATA_SQL = '''
SELECT * FROM test_pymysql
'''

# Delete table
DROP_TABLE_SQL = '''
DROP TABLE test_pymysql
'''

main.py

import logging
import pymysql
from config import DB_CONFIG
from test_sql import CREATE_TABLE_SQL, INSERT_DATA_SQL, SELECT_DATA_SQL, DROP_TABLE_SQL

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logging.info('Start executing the script')

# Create table 
def create_table():
    logging.info('Start creating the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(CREATE_TABLE_SQL)
                conn.commit()
                logging.info('Table creation successful')
            except Exception as e:
                conn.rollback()
                logging.error('Table creation failed, Reason：%s' % e)

# Insert data
def insert_data():
    logging.info('Start inserting data')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                data = [('John', 20), ('Lucy', 25), ('Tom', 30)]
                flattened_data = [d for item in data for d in item]
                cursor.executemany(INSERT_DATA_SQL, [flattened_data])
                conn.commit()
                logging.info('Data insertion successful')
            except Exception as e:
                conn.rollback()
                logging.error('Data insertion failed, Reason：%s' % e)

# Query data
def select_data():
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(SELECT_DATA_SQL)
                result = cursor.fetchall()
                for row in result:
                    print(row)
            except Exception as e:
                logging.error('Data query failed, Reason：%s' % e)

# Delete table
def drop_table():
    logging.info('Start dropping the table')
    with pymysql.connect(**DB_CONFIG) as conn:
        with conn.cursor() as cursor:
            try:
                cursor.execute(DROP_TABLE_SQL)
                conn.commit()
                logging.info('Table dropped successfully')
            except Exception as e:
                conn.rollback()
                logging.error('Table drop failed, Reason：%s' % e)

if __name__ == '__main__':
    create_table()
    insert_data()
    select_data()
    drop_table()

logging.info('Script execution completed')

Error handling

When using PyMySQL 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 whether the connection parameters are correct, including the host name, port, username, password, and database name.
Permission error: If you encounter a permission-related error, make sure that the user has sufficient permissions to perform the required operations.
SQL syntax error: If the SQL statement has a syntax error, check whether the SQL statement is correctly formatted.
Data type error: If the data type of the inserted data does not match the table definition, make sure that the data type of the inserted data is correct.

In the code, we use the try-except statement to catch and handle these errors, ensuring that the program can handle errors gracefully without crashing. We also use the logging module to record error information, which facilitates debugging and troubleshooting.

References

For more information about how to connect to seekdb, see Connection methods overview.

Prerequisites​

Procedure​

Step 1: Check the versions of Python and pip​

Step 2: Install the PyMySQL library​

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​

Project code​

Introduction to config.py​

Introduction to test_sql.py​

Introduction to main.py​

Complete code​

config.py​

test_sql.py​

main.py​

Error handling​

References​

Contents

Prerequisites

Procedure

Step 1: Check the versions of Python and pip

Step 2: Install the PyMySQL library

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

Project code

Introduction to config.py

Introduction to test_sql.py

Introduction to main.py

Complete code

config.py

test_sql.py

main.py

Error handling

References