Help Center/ Relational Database Service_RDS for PostgreSQL/ User Guide/ Instance Connection/ Connecting to an RDS for PostgreSQL Instance Using Python
Updated on 2026-07-06 GMT+08:00
View PDF
Share

Connecting to an RDS for PostgreSQL Instance Using Python

Scenarios

If you are connecting to an instance using Python, an SSL certificate is optional, but using an SSL certificate can improve the security of your data.

Prerequisites

You should be familiar with:

  • Computer basics.
  • Python programming language.
  • How to use psycopg2.

Installation Dependency

Install psycopg2 to connect to PostgreSQL databases.

pip install psycopg2

Parameters

Table 1 Parameter description

Parameter

Description

dbname

The name of the database to be connected. The default database name is postgres.

user

The username used for connecting to the database.

password

The password of the username.

host
  • If you access the database from an ECS, it is the floating IP address shown on the Summary page of the DB instance.
  • If you access the database over the Internet, it is the EIP that has been bound to the DB instance.

port

Enter the port number displayed under the Database Port field on the Summary page. Default value: 5432

sslmode

The SSL connection mode.

  • disable: No certificate is used for connection and security is not concerned.
  • verify-ca: CA authentication is used.
  • For details about other options, see the community documentation.

sslrootcert

The path of the server certificate.

Connecting to an Instance

The following code is an example of how to use the psycopg2.connect function to connect to an RDS for PostgreSQL instance based on SSL certificate authentication and how to use the cursor.execute method to run INSERT and UPDATE statements on the instance.

An error "permission deny for schema public" may be reported when you create tables on certain instance versions. To resolve this problem, use the grant create on SCHEMA public to root; command.

import psycopg2
db_params ={'database':'postgres','user':'root','password':'****','host':'xxx.xxx.xxx.xxx','port':'5432','sslmode':'verify-ca','sslrootcert':'/path/to/CA/ca.pem',}
conn=psycopg2.connect(**db_params)
print("Connection established")
cursor = conn.cursor()
# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print("Finished dropping table (if existed)")
# Create a table
cursor.execute("grant create on SCHEMA public to root;")
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table")
# Insert some data into the table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("banana",150))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("orange",154))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("apple",100))
print("Inserted 3 rows of data")
cursor.execute("SELECT * FROM inventory;")
result = cursor.fetchall()
for row in result:
    print(row)
# Clean up
conn.commit()
cursor.close()
conn.close()

The command output is as follows:

Connection established
Finished dropping table(if existed)
Finished creating table
Inserted 3 rows of data
(1,'banana',150)
(2,'orange',154)
(3,'apple',100)

The following code is an example of how to connect to an RDS for PostgreSQL instance without using an SSL certificate.

import psycopg2
db_params ={'database':'postgres','user':'root','password':'****','host':'xxx.xxx.xxx.xxx','port':'5432','sslmode':'disable'}
conn=psycopg2.connect(**db_params)
print("Connection established")
cursor = conn.cursor()
# Drop previous table of same name if one exists
cursor.execute("DROP TABLE IF EXISTS inventory;")
print("Finished dropping table (if existed)")
# Create a table
cursor.execute("grant create on SCHEMA public to root;")
cursor.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
print("Finished creating table")
# Insert some data into the table
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("banana",150))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("orange",154))
cursor.execute("INSERT INTO inventory (name, quantity) VALUES (%s, %s);",("apple",100))
print("Inserted 3 rows of data")
cursor.execute("SELECT * FROM inventory;")
result = cursor.fetchall()
for row in result:
    print(row)
# Clean up
conn.commit()
cursor.close()
conn.close()
Parent Topic: Instance Connection