python连接Dm数据库

1.1Python安装

在linux环境中，一般都是默认安装python的，可以先在终端进行确定

1.2编译dmPthon

1.2.1安装DM8数据库进行编译

此方式需要DM8和python安装在一个服务器上

1.2.1.1设置环境变量

[root@localhost ~]# vim /root/.bash_profile
DM_HOME=/opt/dmdbms
export DM_HOME
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/drivers/dpi
[root@localhost ~]# source /root/.bash_profile

复制

1.2.1.2编译安装dmPython

进入到$DM_HOME/drivers/python/dmPython目录下，执行命令：

[root@localhost ~]# cd /opt/dmdbms/drivers/python/dmPython
[root@localhost dmPython]# python3 setup.py install

复制

1.2.2不安装DM8进行编译

如果在安装了python环境后，只想连接其他服务器上的DM8数据库，而不想在本机上安装数据库，可以采用以下方法

1.2.2.1复制目录

将DM8数据库的服务端的$DM_HOME/drivers/dpi、$DM_HOME/drivers/python/dmPython这两个目录scp一份到安装了python连接服务端，再将dpi改名为bin，并将dpi目录下的include目录mv到$DM_HOME目录下：

[root@localhost ~]# mkdir /dm8 #python连接服务端创建存放目录
[root@localhost drivers]# scp -r dpi/ python/dmPython/ root@192.168.1.201:/dm8 #数据库的服务端文件传到python连接服务端
[root@localhost ~]# mv /dm8/dpi /dm8/bin #python连接服务端进行更名
[root@localhost ~]# mv /dm8/bin/include /dm8 #python连接服务端进行移动

复制

1.2.2.2添加环境变量

[root@localhost ~]# vim /root/.bash_profile
DM_HOME=/dm8
export DM_HOME
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DM_HOME/bin
[root@localhost ~]# source /root/.bash_profile

复制

1.2.2.3编译安装dmPython

[root@localhost ~]# cd /dm8/dmPython
[root@localhost dmPython]# python3 setup.py install

复制

1.3Python连接DM8数据库

1.3.1Python连接本地DM8

[root@localhost ~]# mkdir /opt/python3_test #创立程序存放文件夹
[root@localhost ~]# cd /opt/python3_test
[root@localhost python3_test]# vim py_conn.py
#!/usr/bin/python
#coding:utf-8
import dmPython
try:
    conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='localhost',  port=5236)
    cursor  = conn.cursor()
    print('python: conn success!')
    conn.close()
except (dmPython.Error, Exception) as err:
    print(err)

复制

执行以下命令，可以看到是否连接数据库

[root@localhost python3_test]# python3 py_conn.py

复制

注意：如果报以下错误，注意python语句的缩进（按Tab键）

1.3.2Python连接非本地DM8

与连接本地区别为“server=” 后加IP（注意安装DM8服务器关闭防火墙）

[root@localhost ~]# mkdir /opt/python3_test #创立程序存放文件夹
[root@localhost ~]# cd /opt/python3_test
[root@localhost python3_test]# vim py_conn.py
#!/usr/bin/python
#coding:utf-8
import dmPython
try:
    conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='192.168.1.205',  port=5236)
    cursor  = conn.cursor()
    print('python: conn success!')
    conn.close()
except (dmPython.Error, Exception) as err:
    print(err)

复制

1.4开发示例

1.4.1Python连接本地DM8

1.4.1.1创建表空间、用户和赋权

[root@localhost ~]# cd /opt/python3_test
[root@localhost python3_test]# vim TEST.py
#!/usr/bin/python
#coding:utf-8
import dmPython
try:
    conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='localhost',  port=5236)
    cursor  = conn.cursor()
    try:
         cursor.execute ("CREATE TABLESPACE TEST DATAFILE 'TEST.DBF' SIZE 128 AUTOEXTEND ON NEXT 512")
         print('python: create tablespace success!')
         cursor.execute ("CREATE USER TEST IDENTIFIED BY Dameng1234 DEFAULT TABLESPACE TEST DEFAULT INDEX TABLESPACE TEST")
         print('python: create usre success!')
         cursor.execute ('GRANT PUBLIC,RESOURCE,VTI,SOI TO TEST')
         print('python: grant success!')
    except (dmPython.Error, Exception) as err:
        print(err)
    conn.close()
except (dmPython.Error, Exception) as err:
    print(err)
[root@localhost python3_test]# python3 TEST.py

复制

注意：创建时注意'TEST.DBF'的引号问题，在python中双引号与单引号作用一致，最好使用双引号，这样可以执行包含单引号的语句，其中Python最注重格式，格式不对还是会报错。

1.4.1.2创建数据表

[root@localhost ~]# cd /opt/python3_test
[root@localhost python3_test]# vim fruit.py
#!/usr/bin/python
#coding:utf-8
import dmPython
try:
    conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='localhost',  port=5236)
    cursor  = conn.cursor()
    try:
        cursor.execute ("create table fruit(name varchar(15),color varchar(15),price float )")
        print('python: create table success!')
    except (dmPython.Error, Exception) as err:
        print(err)
    conn.close()
except (dmPython.Error, Exception) as err:
    print(err)

复制

1.4.1.3增、删、改、查

[root@localhost ~]# cd /opt/python3_test
[root@localhost python3_test]# vim dml.py
#!/usr/bin/python
#coding:utf-8
import dmPython
try:
    conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='localhost',  port=5236)
    cursor  = conn.cursor()
    try:
        cursor.execute ("insert into fruit (name,color,price) values ('苹果','绿色',8.8)")
        cursor.execute ("insert into fruit (name,color,price) values ('苹果','红色',8.5)")
        print('python: insert  success!') #插入数据
        cursor.execute ("update fruit set price='9.9' where price = '8.8'")
        print('python: update success!')#修改数据
        cursor.execute ("select * from fruit")
        res = cursor.fetchall()
        for tmp in res:
            for c1 in tmp:
                print (c1)
        cursor.execute ("delete from fruit where price = '8.8'")
        print('python: delete success!')#删除数据
    except (dmPython.Error, Exception) as err:
        print(err)
    conn.close()
except (dmPython.Error, Exception) as err: 
print(err

复制