Build a Python application

This topic describes how to build a Python application with OceanBase Database and the JayDeBeApi driver.

Prerequisites

• A basic database development environment is deployed.

• The Java environment on your computer is Java JDK 8 or later.

• The Python environment on your computer is version 3.6.8.

• You have obtained the installation package of OceanBase Connector/J.

Procedure

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. For example:

obclient  -h100.88.xx.xx -usys@oracle -p****** -P2881

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log on to OceanBase Database by using the database connection string, to verify that the parameters are correct.

The parameters are described as follows:

• -h: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an OceanBase Database Proxy (ODP).

• -u: the username for connecting to a tenant, in the format of username@tenant name#cluster name. In Oracle mode, the default username of the administrator is sys. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.

• -p: the user password.

• -P: the port for connecting to OceanBase Database, which is also the listening port of the ODP.

Step 2: Install the JayDeBeApi driver

Install JayDeBeApi. We recommend that you use the PIP installation method. For more information, see Install JayDeBeApi with PIP.

// Install JayDeBeApi for Python 3.
pip3 install JayDeBeApi

Step 3: Write an application

The following sample code of test.py is for your reference:

#!/usr/bin/env python3.6
# -*- coding: UTF-8 -*-
encoding = "utf8"
import jaydebeapi

def ob_test():   
    url = 'jdbc:oceanbase://host:port'
    user = 's**@oracle'
    password = '******'
    driver = 'com.alipay.oceanbase.jdbc.Driver'
    jarFile = './oceanbase-client-2.4.0.jar'

    conn = jaydebeapi.connect(driver, url, [user, password], jarFile)
    cur = conn.cursor()

    # Create a table named cities.
    sql = 'create table cities (id int, name varchar(24))'
    cur.execute(sql)

    # Insert two sets of data into the cities table.
    sql = "insert into cities values(1,'hangzhou'),(2,'shanghai')"
    cur.execute(sql)

    # Query all data in the cities table.
    sql = 'select * from cities'
    cur.execute(sql)
    ans = cur.fetchall()
    print(ans)

    # Drop the cities table.
    sql = 'drop table cities'
    cur.execute(sql)

    cur.close()
    conn.close()

ob_test()

Modify the database connection parameters in the code. Values of the parameters are from the database connection string obtained in Step 1.

• url: The value of this parameter is obtained from the -h and -P parameters, in the format of jdbc:oceanbase://IP:port/?pool=false. This parameter specifies the IP address and port for connecting to OceanBase Database, which are usually the IP address of an ODP and the port for database access.

• user: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The value of this parameter is obtained from the -u parameter. In Oracle mode, the default username of the administrator is sys. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.

• password: the user password. The value of this parameter is obtained from the -p parameter.

• driver: the class path, which does not need to be modified.

• jarFile: the path of the JAR installation package of OceanBase Connector/J.

• sqlStr: the SQL statement, which can be changed as needed.

Step 4: Run the application

After you write the script, execute it and check whether the query data is returned properly.

# Place the JAR installation package of OceanBase Connection/J in the current directory.
python3 test.py

[(1, 'hangzhou'), (2, 'shanghai')]

More information

For more information about how to use OceanBase Connector/J, see the OceanBase Connector/J documentation.