AnalyticDB:Python

Prerequisites

Before you begin, ensure that you have:

• The internal or public endpoint of the instance

  • Internal endpoint: available when your client runs on an Elastic Compute Service (ECS) instance in the same region and network type as the AnalyticDB for PostgreSQL instance. Find it in the AnalyticDB for PostgreSQL console under Basic Information > Database Connection Information.

  • Public endpoint: required when your client is outside Alibaba Cloud, or on an ECS instance in a different region or network type. See Manage public endpoints to apply for one.

• The client IP address added to an IP address whitelist of the instance. See Configure an IP address whitelist.

Install psycopg2

Install psycopg2 using pip:

pip install psycopg2

If you prefer to install from the system package manager (CentOS) or from source, use one of the following methods instead:

• System package: yum -y install python-psycopg2

• Source code:

  yum install -y postgresql-devel*
  wget http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.tar.gz
  tar xf psycopg2-2.6.tar.gz
  cd psycopg2-2.6
  python setup.py build
  sudo python setup.py install

Connect and run a query

1. Store your connection credentials as environment variables:

   export DB_HOST=<your-instance-endpoint>
   export DB_NAME=<your-database-name>
   export DB_USER=<your-username>
   export DB_PASSWORD=<your-password>

   Replace the placeholders with your actual values:

   Placeholder	Description	Example
   <your-instance-endpoint>	Internal or public endpoint of the instance	mygpdbpub.gpdb.rds.aliyuncs.com
   <your-database-name>	Name of the database to connect to	gpdb
   <your-username>	Database username	mygpdb
   <your-password>	Database password	—

2. Create a Python script (for example, connect.py) with the following code:

   import os
   import psycopg2
   
   # Read connection parameters from environment variables
   host = os.environ.get("DB_HOST")
   database = os.environ.get("DB_NAME")
   user = os.environ.get("DB_USER")
   password = os.environ.get("DB_PASSWORD")
   
   # Connect to the instance
   conn = psycopg2.connect(
       host=host,
       port=5432,
       database=database,
       user=user,
       password=password,
   )
   conn.autocommit = True
   
   # Run a query and print the results
   sql = "SELECT * FROM gp_segment_configuration;"
   cursor = conn.cursor()
   cursor.execute(sql)
   rows = cursor.fetchall()
   for row in rows:
       print(row)
   
   conn.close()

3. Run the script:

   python connect.py

   If the connection succeeds, the output lists the segment configuration of the instance. The output looks similar to:

   (1, -1, 'p', 'p', 's', 'u', 3022, '192.168.2.158', '192.168.2.158', None, None)
   (6, -1, 'm', 'm', 's', 'u', 3019, '192.168.2.47', '192.168.2.47', None, None)
   (2, 0, 'p', 'p', 's', 'u', 3025, '192.168.2.148', '192.168.2.148', 3525, None)
   (4, 0, 'm', 'm', 's', 'u', 3024, '192.168.2.158', '192.168.2.158', 3524, None)
   (3, 1, 'p', 'p', 's', 'u', 3023, '192.168.2.158', '192.168.2.158', 3523, None)
   (5, 1, 'm', 'm', 's', 'u', 3026, '192.168.2.148', '192.168.2.148', 3526, None)

   Each row represents one segment in the cluster. The columns correspond to fields in the gp_segment_configuration system table.

psycopg2 disables autocommit by default. Setting conn.autocommit = True lets each statement take effect immediately without requiring an explicit conn.commit() call. If you omit this setting, wrap your SQL statements in explicit conn.commit() and conn.rollback() calls to control transaction boundaries.

What's next

• Configure an IP address whitelist

• Manage public endpoints