Connect to OceanBase Database by using MySQL-connector-python

This topic describes how to connect to and use OceanBase Database by using the Python driver MySQL-connector-python.

Prerequisites

• You have installed Python 2.7 or 3.x and pip.
• You have installed OceanBase Database and created a MySQL tenant.

Procedure

1. Obtain the connection string of OceanBase Database.
2. Install the MySQL-connector-python driver.
3. Write a test.py file and enter the database connection information.
4. Run the test.py file.

Step 1: Obtain the connection string of OceanBase Database

Obtain the database connection string from the deployment personnel or administrator of OceanBase Database.

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

Parameters:

• $host: the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), use the IP address of an ODP. For direct connection, use the IP address of an OBServer node.

• $port: the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.

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

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, UPDATE, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the tenant connection account. For connection through ODP, the format is username@tenant name#cluster name or cluster name:tenant name:username. For direct connection, the format is username@tenant name.

• $password: the account password.

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

Here is an example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install the MySQL-connector-python driver

MySQL Connector/Python is an official driver provided by MySQL for connecting to MySQL databases. It is written in Python and used to connect to and operate MySQL databases in Python. MySQL Connector/Python supports Python 2.7 and Python 3.x.

Open the command prompt or PowerShell terminal, and run the following command to install the MySQL-connector-python driver.

pip3 install mysql-connector-python

After the installation is completed, run the following command to check whether the installation was successful:

pip3 list | grep mysql-connector-python

Step 3: Write an application

Write the test.py file based on the information obtained in Step 1: Obtain the connection string of OceanBase Database and enter the database connection information.

1. Create a file named test.py.

2. Enter the following content into the test.py file and modify the database connection information as needed.

   Here is an example of the test.py file:

   import mysql.connector
   
   # Database connection information
   config = {
       'host': 'xxx.xxx.xxx.xxx',
       'port': 2881,
       'user': 'test_user001@mysql001',
       'password': '******',
       'database': 'test'
   }
   
   # Create a database connection
   conn = mysql.connector.connect(**config)
   cursor = conn.cursor()
   
   # Create a database
   cursor.execute("CREATE DATABASE IF NOT EXISTS test")
   
   # Use the database
   cursor.execute("USE test")
   
   # Create a table
   cursor.execute("""
   CREATE TABLE IF NOT EXISTS test_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       age INT
   )
   """)
   
   # Insert data
   cursor.execute("""
   INSERT INTO test_table (name, age) VALUES
   ('John', 20),
   ('Lucy', 25),
   ('Tom', 30)
   """)
   
   # Update data
   cursor.execute("""
   UPDATE test_table SET age = 26 WHERE name = 'Lucy'
   """)
   
   # Query data
   cursor.execute("SELECT * FROM test_table")
   rows = cursor.fetchall()
   for row in rows:
       print(row)
   
   # Close the connection
   cursor.close()
   conn.close()
   

Step 4: Run the test.py file

Open the command prompt or PowerShell terminal, run the test.py file, query data, and output the result.

1. Go to the directory where the test.py file is located.

   Here is an example:

   cd D:\demo\demo
   

2. Run the test.py file.

   Here is an example:

   python test.py
   

   The return result is as follows:

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

Error handling

When you use MySQL Connector/Python to connect to OceanBase Database, various errors may occur. The following table describes some common errors and their solutions.

1. Connection errors: If you cannot connect to the database, check whether the connection parameters, including the host name, port, username, password, and database name, are correct.

2. Privilege errors: If you encounter a privilege error, make sure that the user has sufficient privileges to perform the required operation.

3. SQL syntax errors: If there is a syntax error in your SQL statement, check whether the syntax is correct.

4. Data type errors: If the data type of the inserted data does not match the table definition, make sure that the data type is correct.

In your code, you can use the try-except statement to capture and handle these errors, ensuring that the program can handle errors gracefully instead of crashing. You can also use the logging module to record error information for debugging and troubleshooting.

References

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

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