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 Python2.7 or 3.x and pip.
• You have installed OceanBase Database and created a MySQL tenant.

Procedure

The main steps are as follows:

1. Obtain the OceanBase Database connection string.
2. Install the MySQL Connector/Python driver.
3. Edit the database connection information in the test.py file.
4. Run the test.py file.

Step 1: Obtain a database connection string

Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string.

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

where:

• $host indicates the IP address for connecting to OceanBase Database, which is the IP address of an OceanBase Database Proxy (ODP) for connection through ODP, or the IP address of an OBServer node for direct connection.

• $port indicates 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 indicates the name of the database to be accessed.

  Notice

  The user for connecting to a tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name indicates the tenant account. For connection through ODP, the tenant account can be in the Username@Tenant name#Cluster name or Cluster name:Tenant name:Username format. For direct connection, the tenant account is in the Username@Tenant name format.

• $password indicates the password of the account.

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.

For more information about MySQL Connector/Python, visit Introduction to MySQL Connector/Python.

The installation code for Python 3 is as follows:

pip3 install mysql-connector-python

Check whether MySQL Connector/Python has been installed, and view the details.

pip3 show mysql-connector-python

Step 3: Write an application

Open your text editor, write a sample test.py file, and save it. The sample code is as follows:

import mysql.connector

# Create a connection.
cnx = mysql.connector.connect(
    host="xxxx",  
    port=xxxx,
    user="xxx",
    password="",
    charset=""utf8mb4"
)

# Create a database.
def create_database(cursor):
    cursor.execute("CREATE DATABASE IF NOT EXISTS testdb")
    cursor.execute("USE testdb")

# Create a 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)

# Modify 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)

# Obtain a cursor object.
cursor = cnx.cursor()

# Create a database and use it.
create_database(cursor)
create_table(cursor)

# Insert data.
insert_data(cursor)

# Modify data.
update_data(cursor)

# Query data.
select_data(cursor)

# Close the cursor and connection.
cursor.close()
cnx.close()

Modify the database connection information in the test.py file based on the connection string obtained in Step 1.

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

Here is an example of the database connection information in the test.py file:

# Modify the connection information based on the obtained connection string.
cnx = mysql.connector.connect(
    host="10.10.10.1",  
    port=2881,
    user="test_user001@mysql001",
    password="test",
    charset="utf8"
)

Step 4: Run the application

Start a command-line tool or a PowerShell client and run the test.py file to query data and output the query 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.

   python3 test.py
   

   The return result is as follows:

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

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.