CNDBA社区
前面的几篇博客我们看了SQLite、Mysql、Oracle数据库的封装，本篇看下达梦数据库的封装.
Python 封装 SQLite 数据库操作（使用类封装基本的增删改查）
http://www.cndba.cn/dave/article/2154
Python 封装 MySQL 数据库操作（使用类封装基本的增删改查）
http://www.cndba.cn/dave/article/2171
Python 封装 Oracle 数据库操作（使用类封装基本的增删改查）
https://www.cndba.cn/dave/article/2172
1 测试环境
操作系统本：
[root@dm8 dmPython]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.5 (Maipo)
[root@dm8 dmPython]#
达梦数据库版本：
[root@dm8 dmPython]# disql SYSDBA/SYSDBA
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间: 7.944(毫秒)
disql V8
SQL> select * from v$version;
行号 BANNER
---------- -------------------------
1 DM Database Server 64 V8
2 DB Version: 0x7000a
已用时间: 1.862(毫秒). 执行号:3186.
SQL>
注意我这里的DM8 是测试版，所以版本和官方下载的不一样。
2 安装dmPython
Python 有现成的mysql，oracle 等数据库包，可以直接import 使用，但对于达梦数据库，必须首先安装dmPython，才可以使用。
在$DM_HOME/drivers 目录下有多种类型的驱动：
[dmdba@dm8 ~]$ cd /dm/dmdbms/drivers/
[dmdba@dm8 drivers]$ ls
dci dpi jdbc node.js oci odbc php_pdo python
[dmdba@dm8 drivers]$
[dmdba@dm8 drivers]$ cd python/
[dmdba@dm8 python]$ ls
dmPython third
[dmdba@dm8 python]$ cd dmPython/
[dmdba@dm8 dmPython]$ ls
Buffer.c dmPython.vcxproj Error.c exObject.c row.c tObject.c var_pub.h vInterval.c vObject.c
Buffer.h dmPython.vcxproj.filters Error.h py_Dameng.c row.h trc.c vBfile.c vLob.c vString.c
Connection.c dmPython.vcxproj.user exBfile.c py_Dameng.h setup.py trc.h vCursor.c vlong.c
Cursor.c Environment.c exLob.c README.txt strct.h var.c vDateTime.c vNumber.c
[dmdba@dm8 dmPython]$
这里我们需要手工安装dmPython 包。 进入dmPython目录后执行命令：python setup.py install
命令虽简单，但过程插曲比较多。
2.1 插曲一：
开始使用dmdba 用户执行，报如下错误：
[dmdba@dm8 dmPython]$ python setup.py install
running install
error: can't create or remove files in install directory
The following error occurred while trying to add or remove files in the
installation 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, or
the distutils default setting) was:
/usr/lib64/python2.7/site-packages/
Perhaps your account does not have write access to this directory? If the
installation directory is a system-owned directory, you may need to sign in
as the administrator or "root" account. If you do not have administrative
access to this machine, you may wish to choose a different installation
directory, preferably one that is listed in your PYTHONPATH environment
variable.
For information on other options, you may wish to consult the
documentation 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.2 插曲二:
切换成root用户执行，又报如下错误：
[root@dm8 ~]# cd /dm/dmdbms/drivers/python/dmPython
[root@dm8 dmPython]# python setup.py install
Traceback (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 functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# 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]#
2.3 插曲三：
解决上面的问题后继续安装：
[root@dm8 dmPython]# source ~/.bash_profile
[root@dm8 dmPython]# python setup.py install
running install
running bdist_egg
running egg_info
creating dmPython.egg-info
writing dmPython.egg-info/PKG-INFO
writing top-level names to dmPython.egg-info/top_level.txt
writing dependency_links to dmPython.egg-info/dependency_links.txt
writing 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/egg
running install_lib
running build_ext
building 'dmPython' extension
creating build
creating build/temp.linux-x86_64-2.7
gcc -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.3
unable to execute gcc: No such file or directory
error: command 'gcc' failed with exit status 1
[root@dm8 dmPython]#
提示缺少gcc 包，直接yum 安装：
[root@dm8 dmPython]# yum install gcc* -y
关于yum 的配置，参考我的博客：
Linux 平台下 yum 源 配置 手册
https://www.cndba.cn/dave/article/154
2.4 插曲四：
解决gcc 包之后继续安装：
[root@dm8 dmPython]# python setup.py install
running install
running bdist_egg
running egg_info
writing dmPython.egg-info/PKG-INFO
writing top-level names to dmPython.egg-info/top_level.txt
writing dependency_links to dmPython.egg-info/dependency_links.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/egg
running install_lib
running build_ext
building 'dmPython' extension
gcc -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.3
In 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 dist
creating 'dist/dmPython-2.3-py2.7-linux-x86_64.egg' and adding 'build/bdist.linux-x86_64/egg' to it
removing 'build/bdist.linux-x86_64/egg' (and everything under it)
Processing dmPython-2.3-py2.7-linux-x86_64.egg
Copying dmPython-2.3-py2.7-linux-x86_64.egg to /usr/lib64/python2.7/site-packages
Adding dmPython 2.3 to easy-install.pth file
Installed /usr/lib64/python2.7/site-packages/dmPython-2.3-py2.7-linux-x86_64.egg
Processing dependencies for dmPython==2.3
Finished processing dependencies for dmPython==2.3
[root@dm8 dmPython]#
2.5 测试dmPython
[root@dm8 dmPython]# python
Python 2.7.5 (default, Feb 20 2018, 09:19:12)
[GCC 4.8.5 20150623 (Red Hat 4.8.5-28)] on linux2
Type "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()
通过dmPython 访问DM 数据库没有问题，dmPython 接口更多的详细说明可以直接参考官方文档《DM8_dmPython 使用手册》，我们这里不在进行测试。
3 封装类
3.1 Python 代码
# -*- coding: utf-8 -*-
# @Time : 2019-11-27 22:52
# @Author : cndba
# @FileName: MyDM.py
# @Software: PyCharm
# @Blog ：https://www.cndba.cn/dave
import dmPython
class MyDM:
SHOW_SQL = True
def __init__(self, host='127.0.0.1', port=5236, user='SYSDBA', password='SYSDBA'):
self.host = host
self.port = port
self.user = user
self.password = password
def get_con(self):
try:
conn = dmPython.connect(user=self.user, password=self.password, server=self.host, port=self.port, autoCommit=True)
return conn
except dmPython.Error, e:
print "dmPython Error:%s" % e
def select_all(self, sql):
try:
con = self.get_con()
# print con
cur = con.cursor()
cur.execute(sql)
fc = cur.fetchall()
return fc
except dmPython.Error, e:
print "dmPython Error:%s" % e
finally:
cur.close()
con.close()
def select_by_where(self, sql, data):
try:
con = self.get_con()
# print con
d = (data,)
cur = con.cursor()
cur.execute(sql, d)
fc = cur.fetchall()
# if len(fc) > 0:
# for e in range(len(fc)):
# print(fc[e])
return fc
except dmPython.Error, e:
print "dmPython Error:%s" % e
finally:
cur.close()
con.close()
def dml_by_where(self, sql, params):
try:
con = self.get_con()
cur = con.cursor()
for d in params:
if self.SHOW_SQL:
print('执行sql:[{}],参数:[{}]'.format(sql, d))
cur.execute(sql, d)
con.commit()
except dmPython.Error, e:
con.rollback()
print "dmPython Error:%s" % e
finally:
cur.close()
con.close()
# 不带参数的更新方法
def dml_nowhere(self, sql):
try:
con = self.get_con()
cur = con.cursor()
count = cur.execute(sql)
con.commit()
return count
except dmPython.Error, e:
con.rollback()
print "dmPython Error:%s" % e
finally:
cur.close()
con.close()
# 开始测试函数
def select_all():
sql = "select * from dave"
fc = db.select_all(sql)
for row in fc:
print row
def select_by_where():
sql = "select * from dave where USER_ID=:1"
data = '0551'
fc = db.select_by_where(sql, data)
for row in fc:
print row
def ins_by_param():
sql = "insert into dave(USERNAME,USER_ID) values(:1,:2)"
data = [('https://www.cndba.cn', '0551'), ('https://www.cndba.cn/dave', '0556')]
db.dml_by_where(sql, data)
def del_by_where():
sql = "delete from dave where USERNAME = :1 or USER_ID=:2"
data = [('huaining', '0556')]
db.dml_by_where(sql, data)
def update_by_where():
sql = "update dave set USER_ID=:1 where USER_ID=:2"
data = [('0556', '0551')]
db.dml_by_where(sql, data)
def del_nowhere():
sql = "delete from dave"
print db.dml_nowhere(sql)
if __name__ == "__main__":
db = MyDM( '192.168.20.171', port=5236, user='SYSDBA', password='SYSDBA')
# ins_by_param()
# select_by_where()
# del_by_where()
# select_all()
# update_by_where()
del_nowhere()
select_all()
这里根据对应的测试注释或启用相关的函数。
3.2 执行过程如下
注意我这里测试之前先创建好了测试表dave：
SQL> create table dave(username varchar(50),user_id varchar(10));
测试过程如下：
[root@dm8 Code]# python MyDM.py
执行sql:[insert into dave(USERNAME,USER_ID) values(:1,:2)],参数:[('https://www.cndba.cn', '0551')]
执行sql:[insert into dave(USERNAME,USER_ID) values(:1,:2)],参数:[('https://www.cndba.cn/dave', '0556')]
('https://www.cndba.cn', '0551')
('https://www.cndba.cn/dave', '0556')
[root@dm8 Code]# python MyDM.py
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[delete from dave where USERNAME = :1 or USER_ID=:2],参数:[('huaining', '0556')]
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[update dave set USER_ID=:1 where USER_ID=:2],参数:[(361, 366)]
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[update dave set USER_ID=:1 where USER_ID=:2],参数:[('0551', '0556')]
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[update dave set USER_ID=:1 where USER_ID=:2],参数:[('0551', '0556')]
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[update dave set USER_ID=:1 where USER_ID=:2],参数:[('0551', '0556')]
('https://www.cndba.cn', '0551')
[root@dm8 Code]# python MyDM.py
执行sql:[update dave set USER_ID=:1 where USER_ID=:2],参数:[('0556', '0551')]
('https://www.cndba.cn', '0556')
[root@dm8 Code]# python MyDM.py
None
[root@dm8 Code]#
基本测试完成，更多操作可以参考dmPython 文档。
版权声明：本文为博主原创文章，未经博主允许不得转载。
