用户在创建好数据仓库集群后使用PyGreSQL第三方库连接到集群，则可以使用Python访问DWS ，并进行数据表的各类操作。

连接集群前的准备

• DWS 集群已绑定弹性IP。
• 已获取DWS 集群的数据库管理员用户名和密码。

数据库中是不会存储您的密码原文的，而是存储的密码的HASH摘要，在密码校验时与客户端发来的密码摘要进行比对（中间会有加盐操作）。故当您改变了密码算法策略时，数据库也是无法还原您的密码，再生成新的HASH算法的摘要值的。必须您手动修改一次密码或者创建一个新用户，这时新的密码将会采用您设置的HASH算法进行摘要存储，用于下次连接认证。

• 已获取DWS 集群的公网访问地址，含IP地址和端口。具体请参见 获取集群连接地址。
• 已安装PyGreSQL第三方库。

下载地址：http://www.pygresql.org/download/index.html。

• 安装部署操作请参见：http://www.pygresql.org/contents/install.html。

PyGreSQL的使用依赖于PostgreSQL的libpq动态库（32位的PyGreSQL对应32位的libpq，64位的PyGreSQL对应64位的libpq），Linux中可以依赖yum命令解决。在Windows系统使用PyGreSQL需要先安装libpq，主要方式有两种：

1.安装PostgreSQL，并配置libpq、ssl、crypto动态库位置到环境变量PATH中。

2.安装psqlodbc，使用PostgreSQL ODBC驱动携带的libpq、ssl、crypto动态库。

使用约束

由于PyGreSQL是基于PostgreSQL的客户端接口，它的功能DWS并不能完全支持。具体支持情况请见下表。

DWS对PyGreSQL主要接口支持情况

在Linux环境使用PyGreSQL第三方库连接集群

1.以root用户登录Linux环境。

2.执行以下命令创建python_dws.py文件。

vi python_dws.py复制

请复制粘贴以下内容放入python_dws.py文件中：

#!/usr/bin/env python3 
# _*_ encoding:utf-8 _*_ 
  
from __future__ import print_function 
  
import pg 
  
  
def create_table(connection): 
    print("Begin to create table") 
    try: 
        connection.query("drop table if exists test;" 
                         "create table test(id int, name text);") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Table created successfully") 
  
  
def insert_data(connection): 
    print("Begin to insert data") 
    try: 
        connection.query("insert into test values(1,'number1');") 
        connection.query("insert into test values(2,'number2');") 
        connection.query("insert into test values(3,'number3');") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Insert data successfully") 
  
  
def update_data(connection): 
    print("Begin to update data") 
    try: 
        result = connection.query("update test set name = 'numberupdated' where id=1;") 
        print("Total number of rows updated :", result) 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Update, Operation done successfully") 
  
  
def delete_data(connection): 
    print("Begin to delete data") 
    try: 
        result = connection.query("delete from test where id=3;") 
        print("Total number of rows deleted :", result) 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Delete,Operation done successfully") 
  
  
def select_data(connection): 
    print("Begin to select data") 
    try: 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1]) 
    except pg.InternalError as e: 
        print(e) 
        print("select failed") 
    else: 
        print("Operation done successfully") 
  
  
if __name__ == '__main__': 
    try: 
        conn = pg.DB(host='10.154.70.231', 
                     port=8000, 
                     dbname='gaussdb', # 需要连接的database 
                     user='dbadmin', 
                     passwd='password')  # 数据库用户密码 
    except pg.InternalError as ex: 
        print(ex) 
        print("Connect database failed") 
    else: 
        print("Opened database successfully") 
        create_table(conn) 
        insert_data(conn) 
        select_data(conn) 
        update_data(conn) 
        delete_data(conn) 
        conn.close()复制

或使用dbapi接口实现：

#!/usr/bin/python 
# -*- coding: UTF-8 -*- 
  
from __future__ import print_function 
  
import pg 
import pgdb 
  
  
def create_table(connection): 
    print("Begin to create table") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("drop table if exists test;" 
                       "create table test(id int, name text);") 
        connection.commit() 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Table created successfully") 
        cursor.close() 
  
  
def insert_data(connection): 
    print("Begin to insert data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("insert into test values(1,'number1');") 
        cursor.execute("insert into test values(2,'number2');") 
        cursor.execute("insert into test values(3,'number3');") 
        connection.commit() 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Insert data successfully") 
        cursor.close() 
  
  
def update_data(connection): 
    print("Begin to update data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("update test set name = 'numberupdated' where id=1;") 
        connection.commit() 
        print("Total number of rows updated :", cursor.rowcount) 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Update, Operation done successfully") 
  
  
def delete_data(connection): 
    print("Begin to delete data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("delete from test where id=3;") 
        connection.commit() 
        print("Total number of rows deleted :", cursor.rowcount) 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Delete,Operation done successfully") 
  
  
def select_data(connection): 
    print("Begin to select data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
        print("select failed") 
    else: 
        print("Operation done successfully") 
        cursor.close() 
  
  
if __name__ == '__main__': 
    try: 
        conn = pgdb.connect(host='10.154.70.231', 
                                      port='8000', 
                                      database='gaussdb', # 需要连接的database 
                                      user='dbadmin', 
                                      password='password') # 数据库用户密码 
    except pg.InternalError as ex: 
        print(ex) 
        print("Connect database failed") 
    else: 
        print("Opened database successfully") 
        create_table(conn) 
        insert_data(conn) 
        select_data(conn) 
        update_data(conn) 
        delete_data(conn) 
        conn.close()复制

3.按照实际集群信息，修改python_dws.py文件中的集群公网访问地址、集群端口号、数据库名称、数据库用户名、数据库密码。

说明

PyGreSQL接口不提供重试连接的能力，您需要在业务代码中实现重试处理。

conn = pgdb.connect(host='10.154.70.231', 
                                      port='8000', 
                                      database='gaussdb', # 需要连接的database 
                                      user='dbadmin', 
                                      password='password') # 数据库用户密码复制

4.执行以下命令，使用PyGreSQL第三方库连接集群。

python python_dws.py复制

在Windows环境使用PyGreSQL第三方库连接集群

1.在Windows系统中，单击“开始”按钮 ，在搜索框中，键入 cmd ，然后在结果列表中单击“cmd.exe”打开命令提示符窗口。

2.在命令提示符窗口中，执行以下命令创建python_dws.py文件。

type nul> python_dws.py复制

请复制粘贴以下内容放入python_dws.py文件中：

#!/usr/bin/env python3 
# _*_ encoding:utf-8 _*_ 
  
from __future__ import print_function 
  
import pg 
  
  
def create_table(connection): 
    print("Begin to create table") 
    try: 
        connection.query("drop table if exists test;" 
                         "create table test(id int, name text);") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Table created successfully") 
  
  
def insert_data(connection): 
    print("Begin to insert data") 
    try: 
        connection.query("insert into test values(1,'number1');") 
        connection.query("insert into test values(2,'number2');") 
        connection.query("insert into test values(3,'number3');") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Insert data successfully") 
  
  
def update_data(connection): 
    print("Begin to update data") 
    try: 
        result = connection.query("update test set name = 'numberupdated' where id=1;") 
        print("Total number of rows updated :", result) 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Update, Operation done successfully") 
  
  
def delete_data(connection): 
    print("Begin to delete data") 
    try: 
        result = connection.query("delete from test where id=3;") 
        print("Total number of rows deleted :", result) 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Delete,Operation done successfully") 
  
  
def select_data(connection): 
    print("Begin to select data") 
    try: 
        result = connection.query("select * from test order by 1;") 
        rows = result.getresult() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1]) 
    except pg.InternalError as e: 
        print(e) 
        print("select failed") 
    else: 
        print("Operation done successfully") 
  
  
if __name__ == '__main__': 
    try: 
        conn = pg.DB(host='10.154.70.231', 
                     port=8000, 
                     dbname='gaussdb', # 需要连接的database 
                     user='dbadmin', 
                     passwd='password')  # 数据库用户密码 
    except pg.InternalError as ex: 
        print(ex) 
        print("Connect database failed") 
    else: 
        print("Opened database successfully") 
        create_table(conn) 
        insert_data(conn) 
        select_data(conn) 
        update_data(conn) 
        delete_data(conn) 
        conn.close()复制

或使用dbapi接口实现：：

#!/usr/bin/python 
# -*- coding: UTF-8 -*- 
  
from __future__ import print_function 
  
import pg 
import pgdb 
  
  
def create_table(connection): 
    print("Begin to create table") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("drop table if exists test;" 
                       "create table test(id int, name text);") 
        connection.commit() 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Table created successfully") 
        cursor.close() 
  
  
def insert_data(connection): 
    print("Begin to insert data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("insert into test values(1,'number1');") 
        cursor.execute("insert into test values(2,'number2');") 
        cursor.execute("insert into test values(3,'number3');") 
        connection.commit() 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("Insert data successfully") 
        cursor.close() 
  
  
def update_data(connection): 
    print("Begin to update data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("update test set name = 'numberupdated' where id=1;") 
        connection.commit() 
        print("Total number of rows updated :", cursor.rowcount) 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Update, Operation done successfully") 
  
  
def delete_data(connection): 
    print("Begin to delete data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("delete from test where id=3;") 
        connection.commit() 
        print("Total number of rows deleted :", cursor.rowcount) 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
    else: 
        print("After Delete,Operation done successfully") 
  
  
def select_data(connection): 
    print("Begin to select data") 
    try: 
        cursor = connection.cursor() 
        cursor.execute("select * from test;") 
        rows = cursor.fetchall() 
        for row in rows: 
            print("id = ", row[0]) 
            print("name = ", row[1], "\n") 
    except pg.InternalError as e: 
        print(e) 
        print("select failed") 
    else: 
        print("Operation done successfully") 
        cursor.close() 
  
  
if __name__ == '__main__': 
    try: 
        conn = pgdb.connect(host='10.154.70.231', 
                                      port='8000', 
                                      database='gaussdb', # 需要连接的database 
                                      user='dbadmin', 
                                      password='password') # 数据库用户密码 
    except pg.InternalError as ex: 
        print(ex) 
        print("Connect database failed") 
    else: 
        print("Opened database successfully") 
        create_table(conn) 
        insert_data(conn) 
        select_data(conn) 
        update_data(conn) 
        delete_data(conn) 
        conn.close()复制

3.按照实际集群信息，修改python_dws.py文件中的集群公网访问地址、集群端口号、数据库名称、数据库用户名、数据库密码。

PyGreSQL接口不提供重试连接的能力，您需要在业务代码中实现重试处理。

conn = pgdb.connect(host='10.154.70.231', 
                                      port='8000', 
                                      database='gaussdb', # 需要连接的database 
                                      user='dbadmin', 
                                      password='password') # 数据库用户密码复制

4.执行以下命令，使用PyGreSQL第三方库连接集群。

python python_dws.py