Python封装DM达梦数据库操作

一、测试环境

1.操作系统版本

[root@dm8 dmPython]# cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 7.5 (Maipo)[root@dm8 dmPython]#

2、达梦数据库版本：​​​​​​​

[root@dm8 dmPython]# disql SYSDBA/SYSDBA服务器[LOCALHOST:5236]:处于普通打开状态登录使用时间: 7.944(毫秒)disql V8SQL> select * from v$version; 行号     BANNER                   ---------- -------------------------1          DM Database Server 64 V82          DB Version: 0x7000a 已用时间: 1.862(毫秒). 执行号:3186.SQL>

注意这里的DM8 是测试版，所以版本和官方下载的不一样。

 二、安装dmPython 

Python 有现成的mysql，oracle 等数据库包，可以直接import 使用，但对于达梦数据库，必须首先安装dmPython，才可以使用。

在$DM_HOME/drivers 目录下有多种类型的驱动：​​​​​​​

[dmdba@dm8 ~]$ cd /dm/dmdbms/drivers/[dmdba@dm8 drivers]$ lsdci  dpi  jdbc  node.js  oci  odbc  php_pdo  python[dmdba@dm8 drivers]$[dmdba@dm8 drivers]$ cd python/[dmdba@dm8 python]$ lsdmPython  third[dmdba@dm8 python]$ cd dmPython/[dmdba@dm8 dmPython]$ lsBuffer.c      dmPython.vcxproj          Error.c    exObject.c   row.c     tObject.c  var_pub.h    vInterval.c  vObject.cBuffer.h      dmPython.vcxproj.filters  Error.h    py_Dameng.c  row.h     trc.c      vBfile.c     vLob.c       vString.cConnection.c  dmPython.vcxproj.user     exBfile.c  py_Dameng.h  setup.py  trc.h      vCursor.c    vlong.cCursor.c      Environment.c             exLob.c    README.txt   strct.h   var.c      vDateTime.c  vNumber.c[dmdba@dm8 dmPython]$ 

这里我们需要手工安装dmPython 包。进入dmPython目录后执行命令：

python setup.py install

命令虽简单，但过程插曲比较多。

1、插曲一：权限问题Permission denied

开始使用dmdba 用户执行，报如下错误：​​​​​​​

[dmdba@dm8 dmPython]$ python setup.py installrunning installerror: can't create or remove files in install directory The following error occurred while trying to add or remove files in theinstallation directory:     [Errno 13] Permission denied: '/usr/lib64/python2.7/site-packages/test-easy-install-3032.write-test' The installation directory you specified (via --install-dir, --prefix, orthe distutils default setting) was:     /usr/lib64/python2.7/site-packages/ Perhaps your account does not have write access to this directory?  If theinstallation directory is a system-owned directory, you may need to sign inas the administrator or "root" account.  If you do not have administrativeaccess to this machine, you may wish to choose a different installationdirectory, preferably one that is listed in your PYTHONPATH environmentvariable. For information on other options, you may wish to consult thedocumentation at:   https://pythonhosted.org/setuptools/easy_install.html Please make the appropriate changes for your system and try again. [dmdba@dm8 dmPython]$

根据提示，需要使用root用户来安装dmPython。

2、 插曲二: cannot locate an Dameng software installation

切换成root用户执行，又报如下错误：​​​​​​​

[root@dm8 ~]# cd /dm/dmdbms/drivers/python/dmPython[root@dm8 dmPython]# python setup.py installTraceback (most recent call last):  File "setup.py", line 103, in <module>    raise DistutilsSetupError("cannot locate an Dameng software " /distutils.errors.DistutilsSetupError: cannot locate an Dameng software installation[root@dm8 dmPython]#

因为安装依赖DM的环境，这里在root用户下配置dm的环境变量。​​​​​​​

[root@dm8 dmPython]# cat ~/.bash_profile# .bash_profile # Get the aliases and functionsif [ -f ~/.bashrc ]; then    . ~/.bashrcfi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH export PATH="/dm/dmdbms/bin:$PATH" export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/dm/dmdbms/bin"export DM_HOME="/dm/dmdbms"[root@dm8 dmPython]#

3 、插曲三：unable to execute gcc: No such file or directory

解决上面的问题后继续安装：​​​​​​​

[root@dm8 dmPython]# source ~/.bash_profile[root@dm8 dmPython]# python setup.py  installrunning installrunning bdist_eggrunning egg_infocreating dmPython.egg-infowriting dmPython.egg-info/PKG-INFOwriting top-level names to dmPython.egg-info/top_level.txtwriting dependency_links to dmPython.egg-info/dependency_links.txtwriting manifest file 'dmPython.egg-info/SOURCES.txt'reading manifest file 'dmPython.egg-info/SOURCES.txt'writing manifest file 'dmPython.egg-info/SOURCES.txt'installing library code to build/bdist.linux-x86_64/eggrunning install_librunning build_extbuilding 'dmPython' extensioncreating buildcreating build/temp.linux-x86_64-2.7gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DDM64 -I/dm/dmdbms/include -I/usr/include/python2.7 -c py_Dameng.c -o build/temp.linux-x86_64-2.7/py_Dameng.o -DBUILD_VERSION=2.3unable to execute gcc: No such file or directoryerror: command 'gcc' failed with exit status 1[root@dm8 dmPython]#

提示缺少gcc 包，直接yum 安装：

[root@dm8 dmPython]# yum install gcc* -y

4、插曲四：py_Dameng.h:8:20: fatal error: Python.h: No such file or directory

解决gcc 包之后继续安装：​​​​​​​

[root@dm8 dmPython]# python setup.py  installrunning installrunning bdist_eggrunning egg_infowriting dmPython.egg-info/PKG-INFOwriting top-level names to dmPython.egg-info/top_level.txtwriting dependency_links to dmPython.egg-info/dependency_links.txtreading manifest file 'dmPython.egg-info/SOURCES.txt'writing manifest file 'dmPython.egg-info/SOURCES.txt'installing library code to build/bdist.linux-x86_64/eggrunning install_librunning build_extbuilding 'dmPython' extensiongcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -DDM64 -I/dm/dmdbms/include -I/usr/include/python2.7 -c py_Dameng.c -o build/temp.linux-x86_64-2.7/py_Dameng.o -DBUILD_VERSION=2.3In file included from py_Dameng.c:3:0:py_Dameng.h:8:20: fatal error: Python.h: No such file or directory #include <Python.h>                    ^compilation terminated.error: command 'gcc' failed with exit status 1[root@dm8 dmPython]#

这里提示：

py_Dameng.h:8:20: fatal error: Python.h: No such file or directory

这里是缺少python-devel 包，老方法，直接yum 解决：

[root@dm8 dmPython]# yum install python-devel

最后终于安装成功：​​​​​​​

[root@dm8 dmPython]# python setup.py install……creating distcreating 'dist/dmPython-2.3-py2.7-linux-x86_64.egg' and adding 'build/bdist.linux-x86_64/egg' to itremoving 'build/bdist.linux-x86_64/egg' (and everything under it)Processing dmPython-2.3-py2.7-linux-x86_64.eggCopying dmPython-2.3-py2.7-linux-x86_64.egg to /usr/lib64/python2.7/site-packagesAdding dmPython 2.3 to easy-install.pth file Installed /usr/lib64/python2.7/site-packages/dmPython-2.3-py2.7-linux-x86_64.eggProcessing dependencies for dmPython==2.3Finished processing dependencies for dmPython==2.3[root@dm8 dmPython]#

 三、测试dmPython ​​​​​​​​​​​​​​

[root@dm8 dmPython]# pythonPython 2.7.5 (default, Feb 20 2018, 09:19:12) [GCC 4.8.5 20150623 (Red Hat 4.8.5-28)] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import dmPython/usr/lib64/python2.7/site-packages/dmPython-2.3-py2.7-linux-x86_64.egg/dmPython.py:3: UserWarning: Module dmPython was already imported from /usr/lib64/python2.7/site-packages/dmPython-2.3-py2.7-linux-x86_64.egg/dmPython.pyc, but /dm/dmdbms/drivers/python/dmPython is being added to sys.path>>> conn=dmPython.connect(user='SYSDBA',password='SYSDBA',server='192.168.20.171',port=5236)>>> cursor=conn.cursor()>>> cursor.execute("select 'https://www.cndba.cn' from dual")<__builtin__.DmdbCursor on <dmPython.Connection to SYSDBA@192.168.20.171:5236>>>>> rets=cursor.fetchall()>>> rets[('https://www.cndba.cn',)]>>> cursor.close()>>> conn.close()