Psycopg is a PostgreSQL database adapter for the Python programming language. Hologres is compatible with PostgreSQL 11, which allows you to use Psycopg to connect to Hologres. This topic describes how to use Python 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.
- 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
- Install the psycopg2 package.Run the following command to install the psycopg2 package:
# pip install psycopg2-binary
Connect to Hologres
After you install Python 3.8 and Psycopg, perform the following steps to connect to Hologres:
- Load the psycopg2 package.To use psycopg2, run the
import psycopg2command to load the psycopg2 package that you installed.
- 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>")
Parameter Description host The endpoint of your Hologres instance.
You can view the endpoint of the Hologres instance on the Configurations tab 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 on the Configurations tab 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.
After you connect to the Hologres database, you can use psycopg2 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:
- Create a cursor.Before you develop data, run the
cur = conn.cursor()command to create a cursor for the connection.
- Develop data.
- Create a table.
The following sample command creates a table named
holo_testand 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);")
- Insert data.
The following sample command inserts integers from 1 to 1,000 to the table named
cur.execute("INSERT INTO holo_test SELECT generate_series(%s, %s)", (1, 1000))
- Query the data.
cur.execute("SELECT sum(num) FROM holo_test;") cur.fetchone()
- Create a table.
- Commit the transaction.After you write the command for data query, add the
conn.commit()command to ensure that the operation is committed as a transaction.
- 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.