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.插曲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-label --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-label --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-label --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-label --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()