Python脚本访问Greenplum数据库安装指导

安装前准备

（1）操作系统(系统上面要安装一些必备的开发工具(比如gcc等))

 

linux-82:/home/PyODBC # cat /etc/SuSE-release

SUSE Linux EnterpriseServer 11 (x86_64)

VERSION = 11

PATCHLEVEL = 1

 

（2）安装所需的软件包

greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

--GP官网下载，GP的JDBC和ODBC驱动

pyodbc-3.0.6-1.x86_64.rpm                        

 --Python连接GP需要pyodbc驱动包

unixODBC-2.2.12-204.3.1.x86_64.rpm                

--unixODBC的驱动管理器

 

（3）将上面的包上传到搭建环境的服务器上面，比如/home/PyODBC

 

安装GP驱动包

（1）      解压greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

unzip greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.zip

 

（2）      执行解压后得到greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.bin可执行文件

linux-82:/home/PyODBC # ./greenplum-connectivity-4.3.0.0-build-2-SuSE10-x86_64.bin

 

********************************************************************

    You must read and accept the EMCConnectivity license agreement

                             before installing

********************************************************************

 

                 SOFTWARE LICENSE ANDMAINTENANCE AGREEMENT

 

           *** IMPORTANT INFORMATION - PLEASE READ CAREFULLY  ***

 

ThisSoftware contains computer programs and other proprietary material and

information,the use of which is subject to and expressly conditioned upon

acceptanceof this Software License and Maintenance Agreement (the "Agreement").

 

ThisAgreement is a legally binding document between you (meaning the individual

person orthe entity that the individual represents that has obtained the

Softwarefor its internal productive use and not for outright resale) (the

"Customer")and EMC (which means (i) EMC Corporation, if Customer is located in

theUnited States; (ii) the local EMC sales subsidiary, if Customer is located

in acountry in which EMC Corporation has a local sales subsidiary; and (iii)

EMCInformation Systems International ("EISI"), if Customer is locatedoutside

theUnited States and in a country in which EMC Corporation does not have a

localsales subsidiary). Unless EMC agrees otherwise in writing, this Agreement

governsCustomer's use of the Software except to the extent all or any portion

of theSoftware is: (a) the subject of a separate written agreement; or (b)

governedby a third party licensor's terms and conditions. Capitalized terms

havemeaning stated in the Agreement.

 

IfCustomer does not have a currently enforceable, written and separately signed

softwarelicense agreement directly with EMC or the Distributor from whom

Customerobtained this Software, then by clicking on the "Agree" or"Accept" or

similarbutton at the end of this Agreement, or proceeding with the

 

********************************************************************

    Do you accept the EMC Connectivity licenseagreement? [yes | no]

********************************************************************

 

yes     ---------同意许可

 

********************************************************************

Providethe installation path for Greenplum Connectivity or press ENTER to

acceptthe default installation path:/usr/local/greenplum-connectivity-4.3.0.0-build-2

********************************************************************

 

 

 

********************************************************************

InstallGreenplum Connectivity into</usr/local/greenplum-connectivity-4.3.0.0-build-2>? [yes | no]

********************************************************************

 

yes      ----------------保持默认的安装路径，你也可以自由指定安装路径

 

********************************************************************

/usr/local/greenplum-connectivity-4.3.0.0-build-2does not exist.

Create/usr/local/greenplum-connectivity-4.3.0.0-build-2 ? [ yes | no ]

(Selectingno will exit the installer)

********************************************************************

 

yes      ----------------创建安装目录           

 

Extractingproduct to /usr/local/greenplum-connectivity-4.3.0.0-build-2

 

 

********************************************************************

Installationcomplete.

GreenplumConnectivity is installed in /usr/local/greenplum-connectivity-4.3.0.0-build-2

 

Greenplumdocumentation is available for download at http://powerlink.emc.com.

************************************************************************

 

（3）      配置Greenplum DB数据库驱动

查看安装目录时，如下：

linux-82:/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc# ll

total 24

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:49 psqlodbc-08.02.0400

drwxr-xr-x 6 hadoop users 4096 2013-11-15 09:52 psqlodbc-08.02.0500

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:53 psqlodbc-08.03.0400

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:54 psqlodbc-08.04.0200

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:55 psqlodbc-09.00.0200

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:56psqlodbc-09.02.0100

我们会看到有好几个版本的驱动，我们可以选择psqlodbc-08.02.0500版本的，再查看如下目录：

linux-82:/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc/psqlodbc-08.02.0500# ll

total 48

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:51 datadirect-51sp2_64

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:51 datadirect-52_64

drwxr-xr-x 3 hadoop users 4096 2013-11-15 09:52 datadirect-53sp2_64

-r--r--r-- 1 hadoop users 25746 2013-11-15 09:50 license.txt

-r--r--r-- 1 hadoop users 1383 2013-11-15 09:50 readme.txt

drwxr-xr-x 4hadoop users  4096 2013-11-15 09:50unixodbc-2.2.12

同样我们可以看到驱动管理器。

鉴于GP基于Postgresql8.2版本，我们这里面选择驱动为psqlodbc-08.02.0500，驱动管理器选择为datadirect-52_64。

所以，我们修改greenplum_connectivity_path.sh文件中的内容：

GP_ODBC_DRIVER=psqlodbc-08.02.0500          --值与实际目录名称相同

GP_ODBC_DRIVER_MANAGER=datadirect-52_64    --值与实际目录名称相同

 

注：该文件默认权限位444，是不允许编辑的，你可以手动修改文件的权限，也可以修改整个安装目录的权限位755，如下：

chmod -R 755greenplum-connectivity-4.3.0.0-build-2

 

保存greenplum_connectivity_path.sh后，要记得source，使环境变量生效，如下：

source greenplum_connectivity_path.sh

 

安装unixODBC驱动

（1）      直接安装RPM包

rpm -ivh unixODBC-2.2.12-204.3.1.x86_64.rpm

 

（2）      查看RPM包安装路径

linux-82:/home/PyODBC# rpm -ql unixODBC-2.2.12-204.3.1

/etc/unixODBC

/etc/unixODBC/ODBCDataSources

/etc/unixODBC/odbc.ini

/etc/unixODBC/odbcinst.ini

/usr/bin/dltest

/usr/bin/isql

/usr/bin/iusql

/usr/bin/odbc_config

/usr/bin/odbcinst

/usr/lib64/libboundparam.so.1

/usr/lib64/libboundparam.so.1.0.0

/usr/lib64/libgtrtst.so.1

/usr/lib64/libgtrtst.so.1.0.0

/usr/lib64/libodbc.so

/usr/lib64/libodbc.so.1

/usr/lib64/libodbc.so.1.0.0

/usr/lib64/libodbccr.so.1

/usr/lib64/libodbccr.so.1.0.0

/usr/lib64/libodbcinst.so

/usr/lib64/libodbcinst.so.1

/usr/lib64/libodbcinst.so.1.0.0

/usr/lib64/unixODBC

/usr/lib64/unixODBC/libesoobS.so

/usr/lib64/unixODBC/libesoobS.so.1

/usr/lib64/unixODBC/libesoobS.so.1.0.0

/usr/lib64/unixODBC/libmimerS.so

/usr/lib64/unixODBC/libmimerS.so.1

/usr/lib64/unixODBC/libmimerS.so.1.0.0

/usr/lib64/unixODBC/libnn.so

/usr/lib64/unixODBC/libnn.so.1

/usr/lib64/unixODBC/libnn.so.1.0.0

/usr/lib64/unixODBC/libodbcdrvcfg1S.so

/usr/lib64/unixODBC/libodbcdrvcfg1S.so.1

/usr/lib64/unixODBC/libodbcdrvcfg1S.so.1.0.0

/usr/lib64/unixODBC/libodbcdrvcfg2S.so

/usr/lib64/unixODBC/libodbcdrvcfg2S.so.1

/usr/lib64/unixODBC/libodbcdrvcfg2S.so.1.0.0

/usr/lib64/unixODBC/libodbcminiS.so

/usr/lib64/unixODBC/libodbcminiS.so.1

/usr/lib64/unixODBC/libodbcminiS.so.1.0.0

/usr/lib64/unixODBC/libodbcmyS.so

/usr/lib64/unixODBC/libodbcmyS.so.1

/usr/lib64/unixODBC/libodbcmyS.so.1.0.0

/usr/lib64/unixODBC/libodbcnnS.so

/usr/lib64/unixODBC/libodbcnnS.so.1

/usr/lib64/unixODBC/libodbcnnS.so.1.0.0

/usr/lib64/unixODBC/libodbcpsql.so

/usr/lib64/unixODBC/libodbcpsql.so.1

/usr/lib64/unixODBC/libodbcpsql.so.1.0.0

/usr/lib64/unixODBC/libodbcpsql.so.2

/usr/lib64/unixODBC/libodbcpsql.so.2.0.0

/usr/lib64/unixODBC/libodbcpsqlS.so

/usr/lib64/unixODBC/libodbcpsqlS.so.1

/usr/lib64/unixODBC/libodbcpsqlS.so.1.0.0

/usr/lib64/unixODBC/libodbctxtS.so

/usr/lib64/unixODBC/libodbctxtS.so.1

/usr/lib64/unixODBC/libodbctxtS.so.1.0.0

/usr/lib64/unixODBC/liboplodbcS.so

/usr/lib64/unixODBC/liboplodbcS.so.1

/usr/lib64/unixODBC/liboplodbcS.so.1.0.0

/usr/lib64/unixODBC/liboraodbcS.so

/usr/lib64/unixODBC/liboraodbcS.so.1

/usr/lib64/unixODBC/liboraodbcS.so.1.0.0

/usr/lib64/unixODBC/libsapdbS.so

/usr/lib64/unixODBC/libsapdbS.so.1

/usr/lib64/unixODBC/libsapdbS.so.1.0.0

/usr/lib64/unixODBC/libtdsS.so

/usr/lib64/unixODBC/libtdsS.so.1

/usr/lib64/unixODBC/libtdsS.so.1.0.0

/usr/lib64/unixODBC/libtemplate.so

/usr/lib64/unixODBC/libtemplate.so.1

/usr/lib64/unixODBC/libtemplate.so.1.0.0

/usr/share/doc/packages/unixODBC

/usr/share/doc/packages/unixODBC/AUTHORS

/usr/share/doc/packages/unixODBC/COPYING

/usr/share/doc/packages/unixODBC/ChangeLog

/usr/share/doc/packages/unixODBC/NEWS

/usr/share/doc/packages/unixODBC/README

/usr/share/doc/packages/unixODBC/README.GTK

/usr/share/doc/packages/unixODBC/README.SuSE

/usr/share/doc/packages/unixODBC/index.html

/usr/share/doc/packages/unixODBC/smallbook.gif

/usr/share/doc/packages/unixODBC/unixODBC.gif

可以可看到unixODBC配置文件位于/etc/unixODBC下面：

linux-82:/etc/unixODBC # ll /etc/unixODBC

total 4

drwxr-xr-x 2 root root 4096 2011-06-15 00:32 ODBCDataSources

-rw-r--r-- 1 root root    02011-06-15 00:32 odbc.ini

-rw-r--r-- 1 root root    02011-06-15 00:32 odbcinst.ini

 

（3）      编辑unixODBC的两个配置文件，如下：

linux-82:/etc/unixODBC # cat /etc/unixODBC/odbc.ini

[GreenplumDSN]

Driver =Greenplum          ----值要和/etc/unixODBC/odbcinst.ini中名字一致

Trace = 1

Debug=1

Database = noas            ----GP数据库名

Servername = 10.41.24.102   ----GP的IP地址

UserName = noas           ----GP用户名

Password = noas            ----GP用户密码

Port = 5432                ----GP访问端口号

ReadOnly = No

RowVersioning = No

DisallowPremature = No

ShowSystemTables = Yes

ShowOidColumn = No

FakeOidIndex = No

useDeclareFetch = 1

Fetch = 4096

UpdatableCursors = Yes

Protocol = 7.4-1

 

linux-82:/etc/unixODBC # cat /etc/unixODBC/odbcinst.ini

[Greenplum]

Description = PostgreSQL driver forGreenplum

Driver=/usr/local/greenplum-connectivity-4.3.0.0-build-2/drivers/odbc/psqlodbc-08.02.0500/unixodbc-2.2.12/psqlodbcw.so                   ------GP的ODBC驱动

UsageCount=1

FileUsage= 1

 

 

（4）      使用isql测试

linux-82:/etc/unixODBC # isql GreenplumDSN

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                      |

+---------------------------------------+

SQL> select user;

+-----------------------------------------------------------------+

| current_user                                                   |

+-----------------------------------------------------------------+

| noas                                                           |

+-----------------------------------------------------------------+

SQLRowCount returns -1

1 rows fetched

SQL>

 

注：如果你在执行isql时，出现如下情况：

linux-82:/etc/unixODBC # isql GreenplumDSN

[ISQL]ERROR: Could not SQLConnect

 

这个问题很大情况下是你没有source文件greenplum_connectivity_path.sh导致的，执行source greenplum_connectivity_path.sh文件后，再执行就OK了，最好的办法是将source该文件加入到系统环境变量中。

 

安装pyodbc驱动

（1）      直接安装RPM包

linux-82:/home/PyODBC # rpm -ivh pyodbc-3.0.6-1.x86_64.rpm

 

（2）      查看pyodbc安装目录

linux-82:/home/PyODBC # rpm -ql pyodbc-3.0.6-1

/usr/local/lib64/python2.6/site-packages/pyodbc-3.0.6-py2.6.egg-info

/usr/local/lib64/python2.6/site-packages/pyodbc.so

测试python脚本

（1）      准备python测试脚本，如下：

linux-82:/etc/unixODBC # cat hellokitty.py

#!/usr/bin/python

#-*- encoding: utf-8 -*-

####################################################################

# name: hellokitty.py   

# describe: 测试python访问Greenplum数据库

########################################################################

import pyodbc

import sys

reload(sys)

sys.setdefaultencoding('utf8')

 

class GreenplumTest:

    debug = 1

    def __init__(self,dbinfo):

        self.UID = dbinfo[1]

        self.PWD = dbinfo[2]

        odbcinfo    ='DSN=%s;UID=%s;PWD=%s'%(dbinfo[0],dbinfo[1],dbinfo[2])

        self.cnxn   =pyodbc.connect(odbcinfo,autocommit=True,ansi=True)

        self.cursor =self.cnxn.cursor()

 

    def __del__(self):

        if self.cursor:

            self.cursor.close()

        if self.cnxn:

            self.cnxn.close()

 

    def _printinfo(self,msg):

        print"%s"%(msg)

        print "\n"

 

    def testsql(self):

 

        # 获取表的所有字段    

    sql = '''

          select * from hello1 h1 join hello2h2 on  h1.id = h2.id;

          '''

       

       

    self.cursor.execute(sql.strip())             

    row = self.cursor.fetchall()

    return row

  

#Main

defmain():

    # 检查传入参数个数    

    if len(sys.argv) < 4 :

        print 'usage: python SybaseDSN usernamepassword\n'

        sys.exit(1)

   

    # 定义连接GP的信息     

    dbinfo = []

    dbinfo.append(sys.argv[1])

    dbinfo.append(sys.argv[2])

    dbinfo.append(sys.argv[3])

 

    GPT= GreenplumTest(dbinfo)

    ret = GPT.testsql()

    return ret

if__name__ == '__main__':

    sys.exit(main())

（2）      测试过程：

linux-82:/etc/unixODBC # python hellokitty.py GreenplumDSN noas noas

[(3, 'hello3', 3, 'hello3')]