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
unzipgreenplum-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 -ivhunixODBC-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 -qlpyodbc-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‘)]
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
