文档首页 > > 管理指南> 连接集群> 使用Python第三方库psycopg2连接集群

使用Python第三方库psycopg2连接集群

分享
更新时间: 2019/08/09 GMT+08:00

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

连接集群前的准备

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

  1. root用户登录Linux环境。
  2. 执行以下命令创建python_dws.py文件,并把复制粘贴以下内容放进入python_dws.py文件。

    vi python_dws.py

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    #!/usr/bin/python
    # -*- coding: UTF-8 -*- 
    
    import psycopg2
    def CreateTable(connection):
        print "Begin to create table"
        try:
            cursor = connection.cursor()
            cursor.execute('''drop table if exists test;create table test(id int, name text);''')
            #print "Table created successfully"
            connection.commit()
        except psycopg2.ProgrammingError,e:
            print e
        else:
            print "Table created successfully"
            cursor.close()
    
    
    def InsertData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "Insert data successfully"
            cursor.close()
    
    
    def UpdateData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "After Update, Operation done successfully";
    
    
    def DeleteData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "After Delete,Operation done successfully";
    
    
    def SelectData(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 psycopg2.ProgrammingError,e:
            print e
            print "select failed"
        else:
            print "Operation done successfully";
            cursor.close()
    
    if __name__ == '__main__':
        try:
            connection = psycopg2.connect(host='10.154.70.231', port='8000', database='postgres', user='dbadmin', password='Bigdata_2013')
        except psycopg2.DatabaseError, e:
            print e
            print "Connect database failed"
        else:
            print "Opened database successfully"
            CreateTable(connection)
            InsertData(connection)
            SelectData(connection)
            UpdateData(connection)
            DeleteData(connection)
            connection.close()
    

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

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

    1
    connection = psycopg2.connect(host='10.154.70.231', port='8000', database='postgres', user='dbadmin', password='Bigdata_2013')
    

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

    python python_dws.py

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

  1. 在Windows系统中,单击“开始”按钮 ,在搜索框中,键入cmd,然后在结果列表中单击“cmd.exe”打开命令提示符窗口。
  2. 在命令提示符窗口中,执行以下命令创建python_dws.py文件,并把复制粘贴以下内容放进入python_dws.py文件。

    type nul> python_dws.py
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    #!/usr/bin/python
    # -*- coding: UTF-8 -*-
    
    import psycopg2
    def CreateTable(connection):
        print "Begin to create table"
        try:
            cursor = connection.cursor()
            cursor.execute('''drop table if exists test;create table test(id int, name text);''')
            #print "Table created successfully"
            connection.commit()
        except psycopg2.ProgrammingError,e:
            print e
        else:
            print "Table created successfully"
            cursor.close()
    
    
    def InsertData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "Insert data successfully"
            cursor.close()
    
    
    def UpdateData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "After Update, Operation done successfully";
    
    
    def DeleteData(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 psycopg2.ProgrammingError,e:
            print e
        else:
            print "After Delete,Operation done successfully";
    
    
    def SelectData(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 psycopg2.ProgrammingError,e:
            print e
            print "select failed"
        else:
            print "Operation done successfully";
            cursor.close()
    
    if __name__ == '__main__':
        try:
            connection = psycopg2.connect(host='10.154.70.231', port='8000', database='postgres', user='dbadmin', password='Bigdata_2013')
        except psycopg2.DatabaseError, e:
            print e
            print "Connect database failed"
        else:
            print "Opened database successfully"
            CreateTable(connection)
            InsertData(connection)
            SelectData(connection)
            UpdateData(connection)
            DeleteData(connection)
            connection.close()
    

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

    1
    connection = psycopg2.connect(host='10.154.70.231', port='8000', database='postgres', user='dbadmin', password='Bigdata_2013')
    

  4. 在命令提示符窗口中,执行以下命令,使用psycopg第三方库连接集群。

    python python_dws.py

分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区