Using JayDeBeApi to Connect to a Database

JayDeBeApi is a Python module that provides a convenient, efficient way for Python developers to use the Java JDBC driver to connect to and perform operations on databases.

This section describes how to use JayDeBeApi to connect to the GaussDB database.

Environment Configuration

Configure the GaussDB development environment.
Prepare the basic development environment of GaussDB and obtain the database connection parameters. For example:
```
gsql -h ***.***.***.*** -p 20000 -U *** -W ****** -d test
```
Parameter description:

-h: IP address of the server hosting the GaussDB instance

-p: connection port of the GaussDB instance

-U: username for the connection

-W: user password

-d: name of the database you want to connect.
Install the JayDeBeApi driver.
1. Install Java JDK 8 and Python 3 on the local PC. To check the software versions, run the following commands:
```
java -version
python --version
pip --version
```
2. If the server can connect to the Python Package Index (PyPI), run the pip command to install JayDeBeApi:
```
pip install jaydebeapi
```
  If the server cannot connect to PyPI, download an offline installation package of JayDeBeApi and install it on the local PC.

Obtain the GaussDB driver package.

Download particular packages listed in Table 1 based on the version of your instance.

**Table 1** Driver package download list
Version	Download Address
3.x	Driver package Verification package for the driver package
2.x	Driver package Verification package for the driver package

To prevent a software package from being tampered with during transmission or storage, download the corresponding verification package and perform the following steps to verify the software package:

Upload the software package and verification package to the same directory on a Linux VM.
Run the following command to verify the integrity of the software package:
cat GaussDB_driver.zip.sha256 | sha256sum --check

If OK is displayed in the command output, the verification is successful.
```
GaussDB_driver.zip: OK
```

Example

Create a script file.

Create a test_jaydebeapi.py file and write the following code into the file:

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

def test_jaydebeapi():
    #Set required parameters.
    url = 'jdbc:opengauss://***.***.***.***:20000/test'
    user = '***'
    password = '******'
    driver = 'com.huawei.opengauss.jdbc.Driver'
    jarFile = './opengaussjdbc.jar'

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

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

    #Insert three groups of data into the students table.
    sql = "insert into students values(1,'xiaoming'),(2,'xiaohong'),(3,'xiaolan')"
    cur.execute(sql)

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

    #Update data in the students table.
    sql = 'update students set name = \'xiaolv\' where id = 1'
    cur.execute(sql)

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

    #Delete the students table.
    sql = 'drop table students'
    cur.execute(sql)

    cur.close()
    conn.close()

test_jaydebeapi()

Configure required parameters in the code.

#Set the connection URL, which requires the IP address, port number, and database name of the database server you want to connect.
url = 'jdbc:opengauss://***.***.***.***:20000/test'
#Enter the username.
user = '***'
#Enter the password.
password = '******'
#Specify the path to the JDBC driver class.
driver = 'com.huawei.opengauss.jdbc.Driver'
#Specify the path to the JAR package of the JDBC driver. By default, it is stored in the same directory as the test_jaydebeapi.py file.
jarFile = './opengaussjdbc.jar'

Execute the program.
Run the following command to execute the test_jaydebeapi.py file:
```
python ./test_jaydebeapi.py
```
Check the result.
The GaussDB database is successfully connected, and two query results are returned, as shown in the following figure.

Figure 1 Query result