Connect to OceanBase Cloud using MySQL Connector/Python

This topic explains how to use the MySQL Connector/Python driver to connect to and use OceanBase Cloud databases.

Background information

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

For more detailed information about MySQL Connector/Python, refer to the MySQL Documentation.

Prerequisites

• Python 2.7/Python 3.x and pip are installed.
• You have registered an OceanBase Cloud account, and have created a cluster instance and a MySQL-compatible tenant. For details, refer to Create a cluster instance and Create a tenant.

Procedure

Step 1: Obtain an OceanBase Cloud connection string

1. Log in to the OceanBase Cloud console. On the Instances page, expand the target instance and click Create under the target tenant.

   

2. In the pop-up window, click Connect with Public IP.

3. In the Connect with Public IP window, complete the following settings to generate the connection string:

   1. Under 1. Add an IP address to the allowlist, click Add to add your exit IP address(es) used for the connection to the allowlist.
   2. (Optional) Under 2. Download the CA certificate to connect securely to the tenant, download the CA certificate and complete the verification.
   3. Under 3. Connect to your instance, click the drop-down list for Database and Account to create a database and an account for the connection. Select MySQL CLI as the connection method.

   Notice

   Please keep your password in a secure place after creating your account.

   

Step 2: Install the MySQL Connector/Python driver

1. Execute the following command to install the driver. Here, Python3 is used as an example.

   pip3 install mysql-connector-python
   

2. Execute the following command to check if the installation is successful.

   pip3 show mysql-connector-python
   

Step 3: Write the application

1. Create the test.py file using the sample code below in a text editor, and save it.

   import mysql.connector
   
   # Create connection
   cnx = mysql.connector.connect(
       host="xxxx",  
       port=xxxx,
       user="xxx",
       password="",
       charset="utf8"
   )
   
   # Create database
   def create_database(cursor):
       cursor.execute("CREATE DATABASE IF NOT EXISTS testdb")
       cursor.execute("USE testdb")
   
   # Create table
   def create_table(cursor):
       cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))")
   
   # Insert data
   def insert_data(cursor):
       sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
       val = ("111", "111@example.com")
       cursor.execute(sql, val)
       cnx.commit()
       print("Data inserted, ID:", cursor.lastrowid)
   
   # Update data
   def update_data(cursor):
       sql = "UPDATE users SET email = %s WHERE name = %s"
       val = ("222@example.com", "111")
       cursor.execute(sql, val)
       cnx.commit()
       print(cursor.rowcount, "record(s) affected")
   
   # Query data
   def select_data(cursor):
       cursor.execute("SELECT * FROM users")
       result = cursor.fetchall()
       for row in result:
           print(row)
   
   # Get cursor object
   cursor = cnx.cursor()
   
   # Create and use database
   create_database(cursor)
   create_table(cursor)
   
   # Insert data
   insert_data(cursor)
   
   # Update data
   update_data(cursor)
   
   # Query data
   select_data(cursor)
   
   # Close cursor and connection
   cursor.close()
   cnx.close()
   

2. According to Step 1: Obtain an OceanBase Cloud connection string, modify the content under Create connection in the file test.py and save it. For example:

   # Create connection
   cnx = mysql.connector.connect(
       host="t5*******.aws-ap-southeast-1.oceanbase.cloud",  
       port=3306,
       user="test",
       password="12*****!",
       charset="utf8"
   )
   

   • host: Taken from the -h parameter in the connection string, which is the hostname of OceanBase Cloud database.
   • port: Taken from the -P parameter in the connection string, which is the OceanBase Cloud database connection port.
   • user: Taken from the -u parameter in the connection string, which is the account name.
   • password: Taken from the -p parameter in the connection string, which is the account password.

Step 4: Run the application

1. Open a command line tool and navigate to the directory where test.py is located.

2. Execute the following command to run the test.py file.

   python3 test.py
   

   The result is as follows:

   Data inserted, ID: 1
   1 record(s) affected
   (1, '111', '222@example.com')