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
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 |
|
port |
The port number shown on the Overview page of the DB instance. The default port number is 5432. |
sslmode |
The SSL connection mode.
|
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()
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot