Chapter 8 Python Language Package (psycopg)

This chapter describes how to use Python language package (psycopg).

Install Fujitsu Enterprise Postgres Client Package for the architecture to be developed and executed.

This section describes the Python language package (psycopg), configure the environment, and encrypt communication data when using the package.

To run an application that uses the Python language package (psycopg), set the environment variables as follows.

When using on a machine on which a package with the same name of OSS is installed, make sure that Python language packages (psycopg) group (under the directory added to PYTHONPATH) and packages with the same name of OSS are not mixed in the module search path (sys.path).

• Required for execution of the application

  • PYTHONPATH
    fujitsuEnterprisePostgresClientInstallDir/psycopg/python<PythonVersionToUse>/site-packages

  Example

  Note that "<x>" indicates the product version, "<y>" is the version of Python you want to use.

  PYTHONPATH=/opt/fsepv<x>client64/psycopg/python<y>/site-packages:$PYTHONPATH; export PYTHONPATH

• Required for execution of the application

  • PATH
    fujitsuEnterprisePostgresClientInstallDir\lib

  • PYTHONPATH
    fujitsuEnterprisePostgresClientInstallDir \psycopg\python<PythonVersionToUse>\site-packages

  Example

  When the 32-bit version client package is installed on a 64-bit operating system. "<x>" indicates the product version, "<y>" is the version of Python you want to use.

  SET PATH=%ProgramFiles(x86)%\Fujitsu\fsepv<x>client32\lib;%PATH%
  SET PYTHONPATH=%ProgramFiles(x86)%\Fujitsu\fsepv<x>client32\psycopg\python<y>\site-packages;%PYTHONPATH%

Setting the message language and encoding used by the application requires the same environment setup as using the C language library.

However, the Python language package (psycopg) cannot use the PQsetClientEncoding function to set the encoding. Use the SET command with the execute function to specify the encoding for client_encoding.

For information about settings in the C language library, refer to "5.2.2 Message Language and Encoding System Used by Applications Settings" in the "C Library (libpq)".

Encrypting the communication data requires the same setup as using the C library (libpq).

For information about setting up the environment in the C library, refer to "5.2.3 Settings for Encrypting Communication Data" in the "C Library (libpq)".

Use the connection service file to specify the connection destination. In the connection service file, a name (service name) is defined as a set, comprising information such as connection destination information and various types of tuning information set for connections. By using the service name defined in the connection service file when connecting to databases, it is no longer necessary to modify applications when the connection information changes.

Refer to "Client Interfaces", "The Connection Service File" in the PostgreSQL Documentation for details.

In addition, refer to "6.3 Connecting with the Database" in "Embedded SQL in C" for information on connection string.

PEP 249 - Enables development with an interface that conforms to the Python Database API Specification v2.0. Refer to the OSS Psycopg documentation (https://www.psycopg.org/psycopg3/docs/) for details.

Here is an example of an application using a package for the Python language (psycopg).

import psycopg

# Connect to an existing database
with psycopg.connect("host=localhost port=27500 dbname=test user=postgres") as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        # Execute a command: this creates a new table
        cur.execute("""
            CREATE TABLE IF NOT EXISTS test (
                id serial PRIMARY KEY,
                num integer,
                data text)
            """)

        # Pass data to fill a query placeholders and let Psycopg perform
        # the correct conversion (no SQL injections!)
        cur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))

        # Query the database and obtain data as Python objects.
        cur.execute("SELECT * FROM test")

        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
        # of several records, or even iterate on the cursor
        for record in cur:
            print(record)

        # Make the changes to the database persistent
        conn.commit()

However, if you are using the Python language package (psycopg), there are the following differences to the OSS Psycopg.

This section explains points to consider when creating applications while in database multiplexing mode.

If an application connection switch occurs while in database multiplexing mode, explicitly close the connection and then reestablish the connection or reexecute the application.

The table below shows errors that may occur during a switch, and the corresponding action to take.

*1: Return value of psycopg.pq.ExecStatus().

*2: Return value of SQLSTATE from psycopg.pq.DiagnosticField().

*3: Return value of psycopg.pq.ConnStatus().

The Python client (psycopg) of Fujitsu Enterprise Postgres supports database data types.