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