Using Psycopg to Connect to a Database

Psycopg is a Python API used to execute SQL statements and provides a unified access API for GaussDB. Applications can perform data operations based on psycopg. Psycopg2 is the encapsulation of libpq and is implemented using the C language, which is efficient and secure. It provides cursors on both clients and servers, asynchronous communication and notification, and the COPY TO and COPY FROM functions. It supports multiple types of Python out-of-the-box and adapts to GaussDB data types. Through the flexible object adaptation system, you can extend and customize the adaptation. Psycopg2 is compatible with Unicode.

GaussDB supports the psycopg2 feature and allows psycopg2 to be connected in SSL mode.

**Table 1** Platforms supported by psycopg
OS	Platform	Python Version
EulerOS V2.0SP5	Arm64 x86_64	3.8.5
EulerOS V2.0SP9	Arm64 x86_64	3.7.4
EulerOS V2.0SP10, Kylin V10, and UnionTech20	Arm64 x86_64	3.7.9
EulerOS V2.0SP11 and SUSE 12.5	Arm64 x86_64	3.9.11
Huawei Cloud EulerOS 2.0	Arm64 x86_64	3.9.9

During psycopg2 compilation, OpenSSL of GaussDB is linked. OpenSSL of GaussDB may be incompatible with OpenSSL of the OS. If incompatibility occurs, for example, "version 'OPENSSL_1_1_1f' not found" is displayed, use the environment variable LD_LIBRARY_PATH to isolate the OpenSSL provided by the OS and the OpenSSL on which GaussDB depends.

For example, when the application software client.py that invokes psycopg2 is executed, the environment variable is explicitly assigned to the application software.

export LD_LIBRARY_PATH=/path/to/gaussdb/libs:$LD_LIBRARY_PATH python client.py

In the preceding command, /path/to/pyscopg2/lib indicates the directory where the OpenSSL library on which the GaussDB depends is located. Change it as required.

Prerequisites

A Python development environment has been installed on the local PC.

Connecting to a Database

Prepare related drivers and dependent libraries. Obtain the package GaussDB-Kernel_Database version number_OS version number_64bit_Python.tar.gz from the release package.
After the decompression, the following folders are generated:
- psycopg2: psycopg2 library file
- lib: lib library file
Load the driver.
- Before using the driver, perform the following operations:
  1. Decompress the driver package of the corresponding version.
```
tar zxvf xxxx-Python.tar.gz
```
  2. Copy psycopg2 to the site-packages folder in the Python installation directory as the root user.
```
su root
cp psycopg2 $(python3 -c 'import site; print(site.getsitepackages()[0])') -r
```
  3. Change the psycopg2 directory permission to 755.
```
chmod 755 $(python3 -c 'import site; print(site.getsitepackages()[0])')/psycopg2 -R
```
  4. Add the psycopg2 directory to the environment variable $PYTHONPATH and validate it.
```
export PYTHONPATH=$(python3 -c 'import site; print(site.getsitepackages()[0])'):$PYTHONPATH
```
  5. For non-database users, configure the lib directory in LD_LIBRARY_PATH after decompression.
```
export LD_LIBRARY_PATH=path/to/lib:$LD_LIBRARY_PATH
```
- Load a database driver before creating a database connection.
  1
  
  import psycopg2

Connect to a database.

Connect to the database in non-SSL mode.

Use the psycopg2.connect function to obtain the connection object.
Use the connection object to create a cursor object.

Connect to the database in SSL mode.

When you use psycopy2 to connect to the GaussDB server, you can enable SSL to encrypt the communication between the client and server. To enable SSL, you must have the server certificate, client certificate, and private key files. For details on how to obtain these files, see related documents and commands of OpenSSL.

Use the .ini file (the configparser package of Python can parse this type of configuration file) to save the configuration information about the database connection.
Add SSL connection parameters sslmode, sslcert, sslkey, and sslrootcert to the connection options.
1. sslmode: For details about the options, see Table 2.
2. sslcert: client certificate path.
3. sslkey: client key path.
4. sslrootcert: root certificate path.
Use the psycopg2.connect function to obtain the connection object.
Use the connection object to create a cursor object.
To use SSL to connect to the database, ensure that the Python interpreter is compiled in the mode of generating a dynamic link library (.so) file. You can perform the following steps to check the connection mode of the Python interpreter:
1. Run the import ssl command in the Python interpreter to import SSL.
2. Run the ps ux command to query the PID of the Python interpreter. Assume that the PID is ******.
3. In the shell CLI, run the pmap -p ****** | grep ssl command and check whether the command output contains the path related to libssl.so. If yes, the Python interpreter is compiled in dynamic link mode.

**Table 2** sslmode options
sslmode	Enable SSL Encryption	Description
disable	No	SSL connection is not enabled.
allow	Possible	If the database server requires SSL connection, SSL connection can be enabled. However, authenticity of the database server will not be verified.
prefer	Possible	If the database supports SSL connection, SSL connection is preferred. However, authenticity of the database server will not be verified.
require	Yes	SSL connection is required, but only data is encrypted. However, authenticity of the database server will not be verified.
verify-ca	Yes	SSL connection is required, and the validity of the server CA must be verified.
verify-full	Yes	The SSL connection must be enabled, which is not supported by GaussDB currently.