Psycopg is a PostgreSQL database adapter for the Python programming language. Hologres is compatible with PostgreSQL 11. You can use Psycopg to connect to Hologres. This topic describes how to use Psycopg 2 to connect to Hologres. In this example, Python 3.8 and CentOS 7 are used.

Install Python 3.8

You can install Python 3.8 in a Miniconda or Anaconda environment. In this example, Python 3.8 is installed on CentOS 7.

  1. Install Python 3.8.
    Download a version of Python based on actual needs and run the following commands to install Python: 
    # yum install centos-release-scl
# yum install rh-python38
# scl enable rh-python38 bash
# python --version
Python 3.8.6
  2. Install the Psycopg 2 package.
    Run the following command to install the Psycopg 2 package: 
     # pip install psycopg2-binary

Connect to Hologres

After you install Python 3.8 and Psycopg 2, perform the following steps to connect to Hologres:

  1. Load the Psycopg 2 package.
    To use Psycopg 2, run the import psycopg2 command to load the Psycopg 2 package that you installed.
  2. Connect to your Hologres database.
    You can use the psycopg2.connect() function to connect to your Hologres database. The following sample code provides an example on how to use this function. The following table describes the parameters of this function. 
    conn = psycopg2.connect(host="<Endpoint>", port=<Port>, dbname="<databases>", user="<Access ID>", password="<Access Key>", application_name="<Application Name>")
    Parameter Description
    host The endpoint of your Hologres instance.

    You can view the endpoint of the Hologres instance in the Network Information section of the instance details page in the Hologres console.

    port The port number of your Hologres instance.

    You can view the port number of the Hologres instance in the Network Information section of the instance details page in the Hologres console.

    dbname The name of the Hologres database to which you want to connect.
    user The AccessKey ID of your Alibaba Cloud account.

    You can obtain the AccessKey ID from the Security Management page.

    password The AccessKey secret of your Alibaba Cloud account.

    You can obtain the AccessKey secret from the Security Management page.

    application_name Optional. The name of your application.

    The parameter is used to specify the application on which the SQL statement is executed, which helps you understand the usage of the statement when you query logs.

Use Hologres

After you connect to the Hologres database, you can use Psycopg 2 to develop data in the database. You can perform the following steps to create a table, insert data, query the data, and then release resources. If you need to use Fixed Plan to perform read and write operations that require better performance, set the related Grand Unified Configuration (GUC) parameters. For more information, see Accelerate the execution of SQL statements by using fixed plans.

  1. Create a cursor.
    Before you develop data, run the cur = conn.cursor() command to create a cursor for the connection.
  2. Develop data.
    1. Create a table
      The following sample command creates a table named holo_test and specifies that the type of data in the table is INTEGER. You can set a custom table name and specify a custom data type based on your business requirements. 
      cur.execute("CREATE TABLE holo_test (num integer);")
    2. Insert data.
      The following sample command inserts integers from 1 to 1,000 to the holo_test table. 
      cur.execute("INSERT INTO holo_test SELECT generate_series(%s, %s)", (1, 1000))
    3. Query the data.
      cur.execute("SELECT sum(num) FROM holo_test;")
cur.fetchone()
  3. Commit the transaction.
    After you run the command for data query, run the conn.commit() command. This ensures that the operation is committed as a transaction. You can also set the autocommit parameter to true to automatically commit the operation.
  4. Release resources.
    After the preceding commands are run, run the following commands to close the cursor and disconnect from your Hologres database. This ensures that subsequent operations are not affected. 
    cur.close()
conn.close()