ApsaraDB for OceanBase:Connect to an OceanBase database by using a Python driver

Prerequisites

You have deployed a Python runtime environment on your computer.

Connect to an OceanBase database in Python 3.x

In Python 3.x, the PyMySQL driver is required for connecting to and using an OceanBase database.

Step 1: Obtain the database connection parameters

For more information, see Obtain the connection parameters. Example:

$ obclient -hxxx.xxx.xxx.xxx -P3306 -u a**** -p****** -Dtest

The database connection parameters specify the information required to access the database. You can verify the database connection parameters by logging on to the database before using them in the sample code.

Options:

• -h: the domain name of the OceanBase database to be connected.

• -P: the port for connecting to the OceanBase database. By default, the port is 3306 in MySQL mode.

• -u: the tenant account.

• -p: the account password.

• -D: the database name.

Step 2: Install PyMySQL

PyMySQL is a library in Python 3.x for connecting to MySQL database servers. 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:

• Installation by running a command on the CLI

  $ python3 -m pip install PyMySQL

• Build obcdc by compiling the source code

  1. Clone the source code.

     $ git clone https://github.com/PyMySQL/PyMySQL

  2. Switch to the PyMySQL directory.

     $ cd PyMySQL/

  3. Compile the code for installation.

     $ python3 setup.py install

Step 3: Write the sample code

Edit the sample code for test.py:

#!/usr/bin/python3

import pymysql

conn = pymysql.connect(host="xxx.xxx.xxx.xxx", port=3306,
 user="a****", 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. Refer to the following parameters. The parameter values are obtained in Step 1.

• user: the tenant account, which corresponds to the -u option.

• password: the tenant account password, which corresponds to the -p option.

• host: the domain name of the OceanBase database to be connected, which corresponds to the -h option.

• port: the port for connecting to the OceanBase database, which corresponds to the -P option. By default, the port is 3306 in MySQL mode.

• db: the database name, which corresponds to the -D option.

Step 4: Run the sample code

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

$ python3 test.py

If the following result is returned, the database is connected and the sample statement is executed correctly.

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

Connect to an OceanBase database from Python 2.x

In Python 2.x, the MySQL-python driver is required for connecting to and using an OceanBase database. MySQL-python is a library in Python 2.x for connecting to database servers.

Step 1: Obtain the database connection parameters

For more information, see Obtain the connection parameters. Example:

$ obclient -hxxx.xxx.xxx.xxx -P3306 -u a**** -p****** -Dtest

The database connection parameters specify the information required to access the database. You can verify the database connection parameters by logging on to the database before using them in the sample code.

Options:

• -h: the domain name of the OceanBase database to be connected.

• -P: the port for connecting to the OceanBase database. By default, the port is 3306 in MySQL mode.

• -u: the tenant account.

• -p: the account password.

• -D: the database name.

Step 2: Install MySQL-python

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 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 the sample code

Edit the sample code for test2.py:

#!/usr/bin/python2

import MySQLdb

conn= MySQLdb.connect(
 host='xxx.xxx.xxx.xxx',
 port = 3306,
 user='a****',
 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. Refer to the following parameters. The parameter values are obtained in Step 1.

• host: the domain name of the OceanBase database to be connected, which corresponds to the -h option.

• user: the tenant account, which corresponds to the -u option.

• passwd: the tenant account password, which corresponds to the -p option.

• port: the port for connecting to the OceanBase database, which corresponds to the -P option. By default, the port is 3306 in MySQL mode.

• db: the database name, which corresponds to the -D option.

Step 4: Run the sample code

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

$ python test.py

If the following result is returned, the database is connected and the sample statement is executed correctly.

((1L, 'hangzhou'), (2L, 'shanghai'))