dmPython是用于Python连接达梦数据库的接口，遵循PythonDBAPIversion2.0规范。用户需要先安装DM数据库软件并设置DM_HOME环境变量，然后使用编译工具如MicrosoftVisualC++BuildTools来编译源码进行安装。文章提供了详细的编译和安装步骤，以及代码示例，展示如何使用dmPython进行数据查询、插入、删除等操作。
dmPython 简介
dmPython 是 DM 提供的依据 Python DB API version 2.0 中 API 使用规定而开发的数据库访问接口。
使用 Python 连接达梦数据库时需要安装 dmPython。安装完 DM 数据库软件后，在安装路径下的 drivers 目录下，可以找到 dmPython 的驱动源码，由于提供的是源码，需要自己编译安装。
Windows 环境编译安装 dmPython
安装 DM 数据库软件并设置 DM_HOME 环境变量
dmPython 源码依赖 DM 安装目录中提供的 include 头文件，编译安装前需要检查是否安装 DM 数据库软件，并设置 DM_HOME 环境变量。
设置 DM_HOME 环境变量
安装编译工具 Microsoft Visual C++ Build Tools
编译安装 dmPython
安装完 DM 数据库软件后，在安装路径下的 drivers 目录下，找到 dmPython 的驱动源码。
进入到 dmPython 驱动源码目录
cd D:\dmdbms\drivers\python\dmPython
编译安装 dmPython
python setup.py install
编译安装结束后使用 pip list 命令查看是否安装成功
查看搜索路径并将 dpi 目录文件拷贝到搜索路径下
dmPython 通过调用 DM DPI 接口完成 Python 模块扩展。在其使用过程中，除 Python标准库以外，还需要 DPI 的运行环境。
进入 python 解释器查看搜索路径
python
import sys
sys.path
C:\Users\Admin\AppData\Local\Programs\Python\Python39\lib\site-packages\dmpython-2.3-py3.9-win-amd64.egg
代码示例
import dmPython
r"""
默认开启自动提交(autoCommit)模式
"""
class DmDb:
def __init__(self, user='SYSDBA', password='SYSDBA', server='localhost', port=5236):
"""
初始化方法,创建连接对象,游标对象
:param user: 账号(对应模式名)
:param password:密码
:param server: 数据库ip
:param port: 端口
"""
try:
self.conn = dmPython.connect(user=user, password=password, server=server, port=port)
self.cursor = self.conn.cursor()
if self.cursor: print('>>>数据库连接成功<<<')
except Exception as err:
print(err)
def __del__(self):
# 对象被销毁的时候执行 关闭连接对象
self.conn.close()
def select_data(self, sql):
"""
查询数据
:param sql:select语句
:return: 查询结果
"""
try:
self.cursor.execute(sql)
return self.cursor.fetchall()
except Exception as err:
print(err)
def execute_one(self, sql):
"""
插入一条数据
:param sql:一条sql语句
:return: 受影响的条数
"""
try:
self.cursor.execute(sql)
except Exception as err:
print(err)
return self.cursor.rowcount
def execute_many_for(self, sql_list: list):
"""
批量插入数据,多条insert语句装载在列表中
:param sql_list: 多条insert语句组成的列表
:return: 每条sql插入成功受影响的条数组成的列表, 执行失败的sql列表
"""
failed_sql = []
effect_rows = []
for sql in sql_list:
try:
self.cursor.execute(sql)
# 受影响的条数计数
effect_row = self.cursor.rowcount
effect_rows.append(effect_row)
except Exception as err:
print(err, f'执行异常的sql为: {sql}')
failed_sql.append(sql)
return effect_rows, failed_sql
def del_data(self, sql):
"""
删除数据
:param sql:删除sql语句
:return: 受影响的条数
"""
try:
self.cursor.execute(sql)
except Exception as err:
print(err)
return self.cursor.rowcount
def execute_many(self, sql, params: list):
"""
批量执行,用于批量执行insert语句
:param sql: sql语句, eg: 'insert into "TEST"."stu" values(?, ?, ?, ?)'
:param params: 对应sql语句中的参数,按位置顺序传参;参数类型为[(),()]或[[],[]]
:return: 受影响的记录条数
"""
try:
self.cursor.executemany(sql, params)
return self.cursor.rowcount
except Exception as err:
print(f'!!!批量插入失败: {err}, 全部回滚<<<')
def create_user_schema(self, user_schema, password):
"""
创建用户和模式,并对用户授权
:param user_schema: 用户名(模式名)
:param password: 密码
:return:
"""
sql = [
rf'CREATE USER "{user_schema}" IDENTIFIED BY "{password}" HASH WITH SHA512 NO SALT PASSWORD_POLICY 2 ENCRYPT BY "{password}" LIMIT FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "MAIN";',
rf'grant "PUBLIC","VTI","SOI" to "{user_schema}";',
rf'grant CREATE SESSION to "{user_schema}";']
sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
expected_results = (f'{user_schema}', f'{user_schema}')
try:
for i in sql:
self.cursor.execute(i)
select_results = self.select_data(sql2)
if expected_results in select_results:
return True
except Exception as err:
print(err)
def del_user_schema(self, user_and_schema):
"""
删除用户(模式)
:param user_and_schema: 用户名(模式名)
:return:
"""
sql = f'drop user {user_and_schema} cascade;'
sql2 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null'
expected_results = (f'{user_and_schema}', f'{user_and_schema}')
try:
if expected_results in self.select_data(sql2):
self.cursor.execute(sql)
select_results = self.select_data(sql2)
if expected_results not in select_results:
return True
else:
print(f'要删除的目标对象不存在: {user_and_schema}')
except Exception as err:
print(err)
if __name__ == '__main__':
# dm = DmDb()
sql1 = 'select * from "TEST"."stu";'
sql2 = """insert into "TEST"."stu" values(1,'dmpython','男',20);"""
sql3 = ["""insert into "TEST"."stu" values(2,'dmpython','女',21);""",
"""insert into "TEST"."stu" values(3,'dmpython','男',22);""",
"""insert into "TEST"."stu" values(3,'dmpython','男',22);"""]
sql4 = """delete from "TEST"."stu" where "name" = 'dmpython'"""
sql5 = 'delete from "TEST"."stu"'
# Seq_params = [(1, 'dmpython', '男', 20), (2, 'dmpython', '女', 21), (3, 'dmpython', '男', 22)]
Seq_params = [[1, 'dmpython', '男', 20], [2, 'dmpython', '女', 21], [3, 'dmpython', '男', 22]]
# Seq_params = [[1, 'dmpython', '男', 20], [2, 'dmpython', '女', 21], [2, 'dmpython', '男', 22]]
sql_many = """insert into "TEST"."stu" values(?, ?, ?, ?)"""
table = '"TEST"."stu"'
sql6 = """update "TEST"."stu" set "name" = 'dm_manager_modified_dmPython' where "id" = 100;"""
sql7 = ["""update "TEST"."stu" set "name" = 'dm_manager_modified_dmPython' where "id" = 100;""",
"""update "TEST"."stu" set "name" = 'dm_manager_modified_dmPython' where "id" = 99;"""]
sql8 = ["""insert into "TEST"."stu" values(1,'dmpython','男',20);""",
"""update "TEST"."stu" set "name" = 'dm_manager_modified_dmPython' where "id" = 1;""",
"""update "TEST"."stu" set "name" = 'dm_manager_modified_dmPython' where "id" = 100;"""]
dm_admin = DmDb(user='SYSDBA', password='SYSDBA')
sql9 = r'select a.name as username, b.name as schenma from sysobjects a inner join sysobjects b on a.id = b.pid where b.subtype$ is null order by username desc;'
print(dm_admin.create_user_schema('LSZ_INSIDE', 'Gwxa123456'))
# print(dm_admin.del_user_schema('LSZ_INSIDE'))
pass
扫一扫
1784
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
