通过Python连接RDS for PostgreSQL实例

前提条件

用户需要具备以下技能：

• 熟悉计算机基础知识。
• 了解Python编程语言。
• 了解psycopg2库的基本使用。

安装依赖

安装psycopg2模块，它可以连接和查询PostgreSQL数据库。

pip install psycopg2

参数说明

使用SSL证书连接

下面的代码使用psycopg2.connect函数，基于SSL证书认证方式，连接到RDS for PostgreSQL数据库实例，并使用SQL INSERT、UPDATE操作数据，使用cursor.execute方法对数据库进行SQL操作：

在某些实例版本上，如果创建表报错“permission deny for schema public”，则手动执行grant create on SCHEMA public to root;后可以解决。

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()

输出结果如下：

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

无证书连接

无证书方式连接RDS for PostgreSQL数据库的Python代码，可参考以下示例：

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()