Connect to OceanBase Database by using PyMySQL

This topic describes how to build an application by using PyMySQL, OceanBase Database, and Python. It will cover basic operations such as creating tables, inserting data, and querying data.

To download the project code, click python-pymysql.

Prerequisites

• Python 3.x and pip were installed.
• OceanBase Database was installed.

Procedure

1. Obtain the connection information for OceanBase Database.
2. Get familiar with the python-mysqlclient project code.
3. Check Python and pip.
4. Install PyMySQL.
5. Create a database in OceanBase Database.
6. Modify the database connection information in the config.py file.
7. Run the insert_data.py file.
8. Run the main.py file.

Step 1: Obtain the connection information for OceanBase Database

Contact the OceanBase deployment team or administrator to get the required database connection details.

For example:

obclient -hxxx.xxx.xxx.xxx -P2883 -uroot@mysql001#cluster_name -p****** -Dtest

The parameters are described as follows:

• -h: the IP address for connecting to OceanBase Database. If you connect OceanBase Database by using an OceanBase Database Proxy (ODP), this parameter specifies the ODP address. If you connect to OceanBase Database directly, this parameter specifies the IP address of an OBServer node.
• -P: the port for connecting to OceanBase Database. If you connect to OceanBase Database by using an ODP, the default value is 2883. (You can customize the value when you deploy the ODP.) If you connect to OceanBase Database directly, the default value is 2881. (You can customize the value when you deploy OceanBase Database.)
• -u: the connection account for the tenant. If you connect to OceanBase Database by using an ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. If you connect to OceanBase Database directly, the format is username@tenant name.
• -p: the account password.
• -D: the name of the database to be accessed. You can change it to the business database.

For more information about connection strings, see Connect to an OceanBase Database tenant by using OBClient.

Step 2: Get familiar with the python-pymysql project code

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

After decompressing it, you will get a folder named python-pymysql with the following directory structure:

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

Files in this directory are described as follows:

• config.py: This file is used to store database connection information and SQL statements.

• insert_data.py: This file is used to insert data into a database.

• main.py: This file is the entrance to the main program. You can use it to perform query operations.

• requirements.txt: This file lists the Python libraries required for the project and their version requirements.

  Note

  The requirements.txt file in the package obtained in this topic lists only the version requirements of the PyMySQL library. You can run the sudo pip install -r requirements.txt command to automatically install the required library.

Introduction to the config.py file

The code in the config.py file obtained in this topic defines the database connection information and SQL statements for creating tables, inserting data, and querying data. After you connect to a database by using PyMySQL, you can execute the SQL statements to implement corresponding features.

The code in this file contains the following parts:

1. Configure database connection information.

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

   The code is as follows:

   DB_CONFIG = {
       'host': 'localhost',
       'port': port,
       'user': 'user_name',
       'password': '',
       'database': 'db_name',
       'charset': 'utf8mb4'
   }
   

   The parameters are described as follows:

   • host: the IP address for connecting to OceanBase Database. If you connect to OceanBase Database directly, this parameter specifies the IP address of an OBServer node. If you connect to OceanBase Database by using an ODP, this parameter specifies the ODP address.
   • port: the port for connecting to OceanBase Database. The default value is 2881 for direct connection and 2883 for connection through ODP.
   • the port for connecting to OceanBase Database. If you connect to OceanBase Database directly, the default value is 2881. If you connect to OceanBase Database by using an ODP, the default value is 2883.
   • user: the username for connecting to OceanBase Database. If you connect to OceanBase Database directly, the format is {username}@{tenant name}. If you connect to OceanBase Database by using an ODP, the format is {username}@{tenant name}#{cluster name}. For more information about how to connect to OceanBase Database, see Overview.
   • password: the password for connecting to OceanBase Database.
   • database: the name of the database to be connected to.
   • charset: the character set for connecting to OceanBase Database.

   Notice

   You need to modify the parameter values in the sample code based on the actual environment and database settings.

2. Define an SQL statement for creating a table.

   Define an SQL statement to create a table named test_tbl1. If the table already exists, no operation is performed. Define three fields for the table: id, name, and age. Here, id is an auto-increment primary key.

   The code is as follows:

   CREATE_TABLE_SQL = '''
   CREATE TABLE IF NOT EXISTS test_tbl1 (
   id INT(11) NOT NULL AUTO_INCREMENT,
   name VARCHAR(128) NOT NULL,
   age INT(11) NOT NULL,
   PRIMARY KEY (id))
   '''
   

3. Define an SQL statement for inserting data.

   Define an SQL statement to insert three data records into the test_tbl1 table. Each data record contains the name and age fields. The value of each field is passed in as a placeholder %s when the SQL statement is executed.

   The code is as follows:

   INSERT_DATA_SQL = '''
   INSERT INTO test_tbl1 (name, age) VALUES 
   ('John', 20),
   ('Lucy', 25),
   ('Tom', 30)
   '''
   

4. Define an SQL statement for querying data.

   Define an SQL statement to query all data from the test_tbl1 table.

   The code is as follows:

   SELECT_DATA_SQL = '''
   SELECT * FROM test_tbl1
   '''
   

Introduction to the insert_data.py file

The code in the insert_data.py file obtained in this topic calls the pymysql module to connect to a MySQL database, calls the logging module to output logs, and implements database operations such as creating tables and inserting data. The code defines functions to make operations modular and reusable. When the insert_data module is directly run as the main program, the specified operations are performed.

The code in this file contains the following parts:

1. Import the required modules and configurations.

   Import the logging, pymysql, and config modules. The config module defines the database connection information and SQL statements.

   The code is as follows:

   import logging
   import pymysql
   from config import DB_CONFIG, CREATE_TABLE_SQL, INSERT_DATA_SQL
   

2. Set the level and format of logging, and generate an INFO log to indicate that script execution starts.

   The code is as follows:

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

3. Define a function for creating a table.

   Define a function named create_table(), and generate an INFO log to indicate that table creation starts. Use the with statement to manage the life cycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks. Execute the SQL statement for creating a table, commit the transaction, and generate a success log or roll back the transaction and generate an error log.

   The code is as follows:

   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 a function for inserting data.

   Define a function named insert_data(), and generate an INFO log to indicate that data insertion starts. Use the with statement to manage the life cycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without memory leaks. Execute the SQL statement for inserting data, commit the transaction, and generate a success log or roll back the transaction and generate an error log.

   The code is as follows:

   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. Determine whether the current module is directly run as the main program. If yes, create a table and insert data.

   The code is as follows:

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

6. Generate an INFO log to indicate that script execution is complete.

   The code is as follows:

   logging.info('Script execution completed')
   

Introduction to the main.py file

The code in the main.py file obtained in this topic defines a function named select_data() to query data from the test_tbl1 table and output the results.

The code in this file contains the following parts:

1. Import the PyMySQL library and config module. The config module defines the database connection information and SQL statements.

   The code is as follows:

   import pymysql
   from config import DB_CONFIG, SELECT_DATA_SQL
   

2. Define a function for querying data.

   Define a function named select_data() to query data from a database. Use the with statement to manage the life cycle of database connections and cursor objects. This ensures that database connections and cursor objects can be securely closed without 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 output the results row by row through the for loop.

   The code is as follows:

   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)
   

3. Determine whether the current module is directly run as the main program. If yes, execute the select_data() function to query data and output the results.

   The code is as follows:

   if __name__ == '__main__':
       select_data()
   

   Note

   This statement specifies to call the select_data() function only when the current module is directly run as the main program. If the current module is just imported into another module, the function will not be called. This can avoid unnecessary operations when the module is imported and improve the reusability and readability of the program.

Complete code examples

DB_CONFIG = {
    'host': 'localhost',
    'port': port,
    'user': 'user_name',
    'password': '',
    'database': 'db_name',
    'charset': 'utf8mb4'
}

CREATE_TABLE_SQL = '''
CREATE TABLE IF NOT EXISTS test_tbl1 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
  )
'''

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

SELECT_DATA_SQL = '''
SELECT * FROM test_tbl1
'''

import logging
import pymysql
from config import DB_CONFIG, CREATE_TABLE_SQL, INSERT_DATA_SQL

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

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)

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)

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

logging.info('Script execution completed')

import pymysql
from config import DB_CONFIG, SELECT_DATA_SQL

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)

if __name__ == '__main__':
    select_data()

Step 3: Check Python and pip

Open a terminal in the command prompt or PowerShell and run the python --version and pip --version commands to check whether Python and pip are properly installed.

For example:

[admin@xxx /home/admin]
$python --version
Python 3.6.8

[admin@xxx /home/admin]
$pip --version
pip 21.3.1 from /usr/local/lib/python3.6/site-packages/pip (python 3.6)

Step 4: Install PyMySQL

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

1. Run the following command to go to the python-pymysql project directory:

   For example:

   cd python-pymysql
   

2. Run the following command to install the Python library required by the project:

   • Linux:

     For example:

     sudo pip install -r requirements.txt
     

   • Windows:

     For example:

     pip install -r requirements.txt
     

Step 5: Create a database in OceanBase Database

1. Connect to OceanBase Database.

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A
   

2. Execute the following SQL statement to create a database named test_db:

   CREATE DATABASE test_db;
   

   For more information about the syntax for creating a database, see CREATE DATABASE.

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

1. Go to the python-pymysql project directory.

2. Modify the database connection information in the config.py file.

   • In Windows, use the text editor to open the config.py file and modify the database connection information based on the actual situation.
   • In Linux, use the vi config.py or vim config.py command to open the config.py file and modify the database connection information based on the actual situation.

   The following example shows the database connection information in the config.py file:

   DB_CONFIG = {
       'host': '10.10.10.1',
       'port': 2881,
       'user': 'root@mysql001',
       'password': '******',
       'database': 'test_db',
       'charset': 'utf8mb4'
   }
   

Step 7: Run the insert_data.py file

Open a terminal in the command prompt or PowerShell and run the insert_data.py file to create a table in the database and insert data.

1. Go to the python-pymysql project directory.

   For example:

   cd /home/admin/python-pymysql
   

2. Run the insert_data.py file.

   For example:

   python insert_data.py
   

   The response is as follows:

   2023-07-20 17:22:19,565 - INFO - Start executing the script
   2023-07-20 17:22:19,565 - INFO - Start creating the table
   2023-07-20 17:22:20,999 - INFO - Table creation successful
   2023-07-20 17:22:20,999 - INFO - Start inserting data
   2023-07-20 17:22:21,139 - INFO - Data insertion successful
   2023-07-20 17:22:21,140 - INFO - Script execution completed
   

Step 8: Run the main.py file

Open a terminal in the command prompt or PowerShell and run the main.py file to query data and output the results.

1. Go to the python-pymysql project directory.

   For example:

   cd /home/admin/python-pymysql
   

2. Run the main.py file.

   For example:

   python main.py
   

   The response is as follows:

   (1, 'John', 20)
   (2, 'Lucy', 25)
   (3, 'Tom', 30)
   

References

• For more information about how to connect to OceanBase Database, see Overview.

• For more information about how to create a database, see CREATE DATABASE.