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.
- 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 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:
- Load the Psycopg 2 package. To use Psycopg 2, run the
import psycopg2command to load the Psycopg 2 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>", 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.
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.
- 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
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 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.
- 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.