Connect to OceanBase Cloud using PyMySQL

This topic describes how to use the PyMySQL library to build an application that connects to OceanBase Cloud and perform basic database operations, such as creating tables, inserting data, querying data, and dropping tables.

Download the Python PyMySQL sample project Connect to OceanBase Cloud using PyMySQL (MySQL compatible mode)

Prerequisites

• You have installed Python 3.x and pip.
• You have registered an account for OceanBase Cloud and created an instance and a MySQL-compatible tenant. For more information, see Create an instance and Create a tenant.

Procedure

1. Check the versions of Python and pip.
2. Install the PyMySQL library.
3. Obtain the connection information of the cloud database from OceanBase Cloud.
4. Modify the database connection information in the config.py file.
5. 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 is compatible across platforms. It provides an interface for interacting with MySQL databases and supports Python 3.x versions.

Open the Command Prompt or PowerShell terminal, and run the following command to install the PyMySQL library.

1. Run the following command to navigate to the python-pymysql directory.

   Here is an example:

   cd python-pymysql
   

2. Run the following command to install the required Python libraries.

   Here is an example:

   pip install -r requirements.txt
   

Step 3: Obtain the connection information of the cloud database from OceanBase Cloud

1. Log in to the OceanBase Cloud console. On the instance list page, expand the information of the target instance, and in the target tenant, choose Connect > Get Connection String.

   For more information, see Obtain the connection string.

2. Fill in the following URL with the information of the created cloud database.

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

   Parameter description:

   • $host: the connection address of the cloud database, for example, t********.********.oceanbase.cloud.

   • $port: the connection port of the cloud database. The default value is 3306.

   • $database_name: the name of the database to be accessed.

     Notice

     The user of the tenant to be connected must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about account privileges, see Create and manage an account.

   • $user_name: the account for accessing the database.

   • $password: the password of the account.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud -P3306 -u mysql001 -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 connection information of the cloud database from OceanBase Cloud.

1. Navigate to the python-pymysql project folder.

2. 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': 't5******.********.oceanbase.cloud',
       'port': 3306,
       'user': 'mysql001',
       '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.

1. Navigate to the python-pymysql project directory.

   Here is an example:

   cd D:\demo\demo\python-pymysql
   

2. 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 introduction

Click python-pymysql to download the project code, which is a compressed package 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, used to perform basic database operations, including table creation, data insertion, data query, and table deletion.

• requirements.txt: stores the required Python packages and their versions for the project.

  Note

  The code obtained in this topic only lists the version requirements for the PyMySQL library. You can use the sudo pip install -r requirements.txt command to install the required libraries. After executing this command, the necessary libraries will be automatically installed.

config.py code

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 connection address of the cloud database on OceanBase Cloud.
• $port: the connection port of the cloud database on OceanBase Cloud.
• $user_name: the account for accessing the database.
• $password: the password of the account.
• $database_name: the name of the database to be connected.
• charset: the character set used for connecting to the database.

test_sql.py code

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 using PyMySQL to achieve the corresponding functions.

The code in this file mainly includes the following parts:

1. The SQL statement for creating a table.

   Define the SQL statement for creating a table named test_pymysql. 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)
   )
   '''
   

2. The SQL statement for inserting data.

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

   Sample code:

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

3. The SQL statement for querying data.

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

   Sample code:

   SELECT_DATA_SQL = '''
   SELECT * FROM test_pymysql
   '''
   

4. The SQL statement for deleting a table.

   Define the SQL statement for deleting the test_pymysql table.

   Sample code:

   DROP_TABLE_SQL = '''
   DROP TABLE test_pymysql
   '''
   

main.py code

The code in the main.py file obtained in this topic connects to a MySQL database by calling the pymysql module and outputs log information by calling the logging module. This code implements operations such as table creation, data insertion, data query, and table deletion.

The code in this file mainly includes the following parts:

1. 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
   

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

   Sample code:

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

3. Define the function for creating a table.

   Define a function named create_table() that outputs an INFO-level log message indicating that the table creation is about to start. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring their safe closure and preventing memory leaks. The SQL statement for creating the table is executed, and the transaction is committed with a log message, or rolled back with an error log message.

   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)
   

4. Define the function for inserting data.

   Define a function named insert_data() that outputs an INFO-level log message indicating that data insertion is about to start. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring their safe closure and preventing memory leaks. The SQL statement for inserting data is executed, and the transaction is committed with a log message, or rolled back with an error log message.

   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)
   

5. Define the function for querying data.

   Define a function named select_data() that is used to query data from the database. The with statement is used to manage the lifecycle of the database connection and cursor objects, ensuring their safe closure and preventing memory leaks. The execute() method is used to execute the SQL statement defined by SELECT_DATA_SQL to query data. The fetchall() method is used to obtain the query results, and the for loop is used 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)
   

6. Define the function for deleting a table.

   Define a function named drop_table() that uses the predefined database connection information (DB_CONFIG) and the SQL statement for deleting a table (DROP_TABLE_SQL). The function executes the table deletion operation and prints corresponding log messages indicating whether the operation was 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)
   

7. Define the entry point of the program, mainly for executing the database operation functions.

   First, determine whether the current module is running as the main program. If it is, perform the following operations:

   1. Call the create_table() function to create a 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()
   

8. Output an INFO-level log message indicating that the script execution is complete.

   Sample code:

   logging.info('Script execution completed')
   

Complete code

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

# 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
'''

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 OceanBase Cloud, you may encounter various errors. Below are some common errors and their handling methods:

1. 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.

2. Permission error: If you encounter a permission-related error, ensure that the user has sufficient permissions to perform the required operations.

3. SQL syntax error: If the SQL statement has a syntax error, check whether the syntax of the SQL statement is correct.

4. Data type error: If the data type of the inserted data does not match the table definition, ensure that the data type of the inserted data 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 instead of crashing directly. At the same time, we use the logging module to record error information, which facilitates debugging and troubleshooting.

References

• For more information about how to connect to OceanBase Cloud, see Overview of connection methods.