Help Center/ Relational Database Service/ User Guide/ Working with RDS for PostgreSQL/ Instance Connection/ Connecting to an RDS for PostgreSQL Instance Using Python
Updated on 2024-10-24 GMT+08:00
View PDF
Share

Connecting to an RDS for PostgreSQL Instance Using Python

Prerequisites

You are familiar with:

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

Installation Dependency

Install the psycopg2 driver 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 Overview 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

The port number shown on the Overview page of the DB instance. The default port number is 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.

Connection with an SSL Certificate

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)

Connection Without an SSL Certificate

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