AnalyticDB:Python

Prerequisites

• The internal or public endpoint of an AnalyticDB for PostgreSQL instance is obtained.

  • If your client is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and uses the same network type as the AnalyticDB for PostgreSQL instance, you can use the internal endpoint to connect to the AnalyticDB for PostgreSQL instance. Log on to the AnalyticDB for PostgreSQL console. In the Database Connection Information section of the Basic Information page, view the internal endpoint of the instance.

  • If your client is deployed on an ECS instance that resides in a different region or uses a different network type from your AnalyticDB for PostgreSQL instance or a system outside Alibaba Cloud, you must apply for a public endpoint and then use the public endpoint to connect to the AnalyticDB for PostgreSQL instance. For more information about how to apply for a public endpoint, see Manage public endpoints.

• The IP address of your client is added to an IP address whitelist of the AnalyticDB for PostgreSQL instance. For more information, see Configure an IP address whitelist.

Procedure

You can use psycopg2 to connect to Greenplum or PostgreSQL in Python.

1. Install psycopg2. If you use CentOS, you can use one of the following installation methods:

   • Method 1: Run the yum -y install python-psycopg2 command.

   • Method 2: Run the pip install psycopg2 command.

   • Method 3: Use the 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

2. Set the PYTHONPATH environment variable for subsequent variable reference. Sample code:

    import psycopg2
    // The SQL statement. 
    sql = '<SELECT * FROM gp_segment_configuration;>'
    // The database name, username, and password that are used to connect to the instance. 
    conn = psycopg2.connect(database='<gpdb>', user='<mygpdb>', password='<mygpdb>', host='mygpdbpub.gpdb.rds.aliyuncs.com', port=5432)
    conn.autocommit = True
    cursor = conn.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        print row
    conn.commit()
    conn.close()

   Sample output:

   (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)