Psycopg Package

After the decompression, the following folders are generated:

• psycopg2: psycopg2 library file
• lib: lib library file

• 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 the database in non-SSL mode.

1. Use the psycopg2.connect function to obtain the connection object.
2. 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.

1. 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.
2. Add SSL connection parameters sslmode, sslcert, sslkey, and sslrootcert to the connection options.
   1. sslmode: For details about the options, see Table 1.
   2. sslcert: client certificate path.
   3. sslkey: client key path.
   4. sslrootcert: root certificate path.
3. Use the psycopg2.connect function to obtain the connection object.
4. 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 the command output contains the path, the Python interpreter is compiled in dynamic link mode.

1. Construct an operation statement and use %s as a placeholder. During execution, psycopg2 will replace the placeholder with the parameter value. You can add the RETURNING clause to obtain the automatically generated column values.
2. Use the cursor.execute method to execute one row of SQL statement, and use the cursor.executemany method to execute multiple rows of SQL statements.

1. cursor.fetchone(): fetches the next row in a query result set and returns a sequence. If no data is available, null is returned.
2. cursor.fetchall(): fetches all remaining rows in a query result and returns a list. An empty list is returned when no rows are available.

For database-specific data types, such as tinyint, the corresponding columns in the query result are character strings.

After you complete required data operations in a database, close the database connection. Call the close method such as connection.close() to close the connection.

This method closes the database connection and does not automatically call commit(). If you just close the database connection without calling commit() first, changes will be lost.