Build a Python application

This topic describes how to build a Python application with OceanBase Database and the Python driver. Different drivers are required in different versions of Python environments. The PyMySQL driver is required in Python 3.x, and the MySQL-python driver is required in Python 2.x.

Prerequisites

You have deployed a Python runtime environment on your computer.

Create an application in Python 3.x

When you create an application in Python 3.x, you must connect to and use OceanBase Database by using the PyMySQL driver.

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 -uroot@test -p****** -P2881 -Doceanbase

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log in 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. The default tenant of a cluster is sys, and the default administrator of a tenant is root. 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.
• -D: the name of the database to be accessed.

Step 2: Install the PyMySQL driver

If you use Python 3.x, you can connect to a MySQL database server by using PyMySQL. PyMySQL implements Python Database API Specification 2.0 and contains a pure-Python MySQL client library.

For more information about PyMySQL, visit the official website of PyMySQL and see API Reference.

You can install PyMySQL by using one of the following two methods:

• Install PyMySQL through the CLI:

  python3 -m pip install PyMySQL
  

• Install PyMySQL through source code compilation:

  git clone https://github.com/PyMySQL/PyMySQL
  cd PyMySQL/
  python3 setup.py install
  

Step 3: Write an application

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

#!/usr/bin/python3

import pymysql

conn = pymysql.connect(host="localhost", port=2881,
                       user="root", passwd="", db="test")

cur = conn.cursor()

try:

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

finally:
    cur.close()
    conn.close()

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

• 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. The default tenant of a cluster is sys, and the default administrator of a tenant is root. 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.

• host: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an ODP. The value of this parameter is obtained from the -h parameter.

• port: the port for connecting to OceanBase Database, which is also the listening port of the ODP. The value of this parameter is obtained from the -P parameter.

• db: the name of the database to be accessed. The value of this parameter is obtained from the -D parameter.

Step 4: Run the application

After you edit the code, execute the test.py script.

python3 test.py
# If the following results are returned, you have connected to OceanBase Database, and the sample script is correctly executed.
((1, 'hangzhou'), (2, 'shanghai'))

Create an application in Python 2.x

When you create an application in Python 2.x, you must connect to and use OceanBase Database by using the MySQL-python driver. MySQL-python is a library for connecting to OceanBase Database in Python 2.x.

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 -uroot@test -p****** -P2881 -Doceanbase

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log in 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. The default tenant of a cluster is sys, and the default administrator of a tenant is root. 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.
• -D: the name of the database to be accessed.

Step 2: Install the MySQL-python driver

MySQL-python is an API that allows you to connect a Python application to a MySQL database. It implements the Python Database API Specification 2.0 and is built based on the MySQL C API.

For more information about MySQL-python, visit the official website of MySQL-python and GitHub.

You can run a yum command to install the MySQL-python driver.

yum install MySQL-python

Step 3: Write an application

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

#!/usr/bin/python2

import MySQLdb

conn= MySQLdb.connect(
    host='127.0.0.1',
    port = 2881,
    user='root',
    passwd='',
    db ='test'
)


cur = conn.cursor()

try:

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

finally:
    cur.close()
    conn.close()

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

• host: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an ODP. The value of this parameter is obtained from the -h parameter.

• 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. The default tenant of a cluster is sys, and the default administrator of a tenant is root. 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.

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

• port: the port for connecting to OceanBase Database, which is also the listening port of the ODP. The value of this parameter is obtained from the -P parameter.

• db: the name of the database to be accessed. The value of this parameter is obtained from the -D parameter.

Step 4: Run the application

After you edit the code, execute the test.py script.

python test.py
# If the following results are returned, you have connected to OceanBase Database, and the sample script is correctly executed.
((1L, 'hangzhou'), (2L, 'shanghai'))

More information

For more information about how to create a Python 3.x application, see the sample Python 3 application in the open-source community of OceanBase Database.