21、Python开发DB2应用程序全攻略

Python开发DB2应用程序全攻略

在Python开发中，与DB2数据库进行交互是一项常见的任务。为了实现高效、便捷的交互，我们可以使用 ibm_db 驱动，它能提供出色的性能和丰富的功能支持。下面将详细介绍如何使用 ibm_db 驱动进行DB2数据库的连接、数据操作等。

1. 环境准备

在开始之前，需要确保已经安装了 ibm_db 驱动。同时，所有相关Python DB2驱动的开源项目主页可以在 http://code.google.com/p/ibm-db/ 找到。

2. 连接到DB2数据库

要连接到DB2数据库，首先需要在Python脚本中导入 ibm_db 驱动模块，然后使用 ibm_db.connect 函数进行连接。

import ibm_db

ibm_db.connect 函数的语法为：

IBM_DBConnection ibm_db.connect (string dsn, string user, string password[, array options])

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| dsn | 数据库连接字符串，格式为 "DATABASE=database; HOSTNAME=hostname; PORT=port; PROTOCOL=TCPIP; UID=username; PWD=password;" |
| user | 用于连接数据库的用户名；如果 dsn 字符串中已指定用户名，则指定空字符串 "" |
| password | 连接数据库的用户名对应的密码；如果 dsn 字符串中已指定密码，则指定空字符串 "" |
| options | 可选参数：一个包含连接选项的字典，会影响连接的行为 |

以下是连接本地和远程数据库的示例：
- 连接本地编目的SAMPLE数据库

import ibm_db
conn = ibm_db.connect("SAMPLE","db2admin","password")

• 直接通过TCP/IP连接远程SAMPLE数据库

import ibm_db
conn = ibm_db.connect("DATABASE=SAMPLE; HOSTNAME=host2; PORT=50000; PROTOCOL=TCPIP; UID=db2admin; PWD=password;", "", "")

如果连接成功， ibm_db.connect 函数将返回一个 ibm_db.IBM_DBConnection 对象，后续可以使用该对象进行数据库操作。

此外， ibm_db 驱动还支持创建持久连接，持久连接在关闭后会保留在连接池中，后续具有相同凭证的连接请求可以重用现有连接。详细信息可查看 http://code.google.com/p/ibm-db/wiki/APIs 中关于 ibm_db.pconnect 函数的API规范。

3. 数据检索

如果在编写应用程序时已知SQL语句，可以按照以下步骤选择和获取数据：
1. 使用 ibm_db.connect 函数连接到数据库，连接成功后返回数据库连接对象。
2. 直接调用 ibm_db.exec_immediate 函数执行SQL SELECT语句，若执行成功，该函数将返回一个与结果集关联的语句对象。

ibm_db.exec_immediate 函数的语法为：

IBM_DBStatement ibm_db.exec_immediate( IBM_DBConnection connection, string statement [, array options] )

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| connection | 由 ibm_db.connect() 返回的有效数据库连接对象 |
| statement | 字符串格式的SQL语句，该语句不能包含任何参数标记，即在编写时必须已知该语句 |
| options | 可选参数：一个包含语句选项的字典，可以使用该参数为支持可滚动游标类型的数据库服务器请求可滚动游标。默认返回向前只读游标 |

示例代码如下：

stmt = ibm_db.exec_immediate(conn, 'SELECT firstnme, lastname FROM employee fetch first 2 rows only')

3. 调用 ibm_db.fetch_tuple() 函数从结果集中获取行，该函数返回一个元组，表示结果集中的下一行或请求的行。元组中的每个列值通过0索引的列位置进行索引。

ibm_db.fetch_tuple 函数的语法为：

array ibm_db.fetch_tuple(IBM_DBStatement stmt, [, int row_number])

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| stmt | 包含结果集的有效语句对象 |
| row_number | 可选参数：要从结果集中获取的特定行的1索引行号。如果结果集使用向前只读游标，传递此参数会产生警告 |

如果结果集中没有更多行，或者 row_number 请求的行不存在于结果集中，该函数将返回 False 。

以下是获取并打印结果集中所有行的示例代码：

mytuple = ibm_db.fetch_tuple(stmt)
while mytuple != False:
    print "First Name: %s, Last Name: %s" % (mytuple[0], mytuple[1])
    mytuple = ibm_db.fetch_tuple(stmt)

除了 ibm_db.fetch_tuple ， ibm_db 驱动还提供了其他3种数据获取函数，分别是 ibm_db.fetch_assoc 、 ibm_db.fetch_both 和 ibm_db.fetch_row ，更多信息请查看 http://code.google.com/p/ibm-db/wiki/APIs 。

如果要测试上述代码片段，可以编辑相关Python脚本，将用户ID和密码替换为自己的，然后运行脚本。例如，运行 select.py 脚本的命令为：

python select.py

4. 数据插入、更新和删除

与SELECT语句类似，如果在编写应用程序时已知INSERT、UPDATE或DELETE SQL语句，可以直接调用 ibm_db.exec_immediate 函数执行这些语句。对于包含变量输入或参数标记的SQL语句，请参考后续部分。

执行SQL语句成功后，可以使用 ibm_db.num_rows 函数返回受SQL语句影响的行数。

以下是一个示例代码：

stmt = ibm_db.exec_immediate(conn, "CREATE TABLE PRODUCT(product_id char(6), name char(30))")
stmt = ibm_db.exec_immediate(conn, "Insert into PRODUCT values ('000001','computer'), ('000002','TV')")
print "Returns for insert: ", ibm_db.num_rows(stmt)
stmt = ibm_db.exec_immediate (conn, "update PRODUCT set name = 'notebook' where product_id='000001'")
print "Returns for update: ", ibm_db.num_rows(stmt)
stmt = ibm_db.exec_immediate (conn, "delete from PRODUCT where product_id='000003'")
print "Returns for delete: ", ibm_db.num_rows(stmt)
stmt = ibm_db.exec_immediate (conn, "drop table PRODUCT")

上述代码片段是 ins_upd_del.py 脚本的一部分。如果要测试该代码，可以修改脚本中的用户ID和密码，然后运行脚本：

python ins_upd_del.py

5. 执行带参数标记的SQL语句

当SQL语句可能包含在运行时才知道值的变量输入时，可以使用问号（?）作为参数标记。准备和执行此类SQL语句的步骤如下：
1. 使用 ibm_db.connect 函数连接到数据库，连接成功后返回数据库连接对象。
2. 使用 ibm_db.prepare 函数准备带有参数标记的SQL字符串，若准备成功，该函数将返回一个 IBM_DBStatement 语句对象。

ibm_db.prepare 函数的语法为：

IBM_DBStatement ibm_db.prepare( IBM_DBConnection connection, string statement [, array options] )

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| connection | 由 ibm_db.connect() 返回的有效数据库连接对象 |
| statement | 字符串格式的SQL语句，该语句可以包含输入/输出参数标记，其值在SQL运行时才知道 |
| options | 可选参数：一个包含语句选项的字典，可以使用该参数为支持可滚动游标类型的数据库服务器请求可滚动游标。默认返回向前只读游标 |

示例代码如下：

stmt = ibm_db.prepare(conn, "SELECT empno, lastname, job, salary FROM employee WHERE workdept = ?")

3. 对于每个参数标记，调用 ibm_db.bind_param 函数将输入值绑定到SQL中的参数标记。

ibm_db.bind_param 函数的语法为：

Py_True/Py_None ibm_db.bind_param(IBM_DBStatement stmt, int parameter-number, string variable [, int parameter-type [, int data-type [, int precision [, int scale [, int size[]]]]]] )

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| stmt | 由 ibm_db.prepare() 返回的已准备好的语句 |
| parameter-number | 指定已准备好的语句中参数的1索引位置 |
| variable | 要绑定到 parameter-number 指定的参数的Python变量 |
| parameter-type | 可选参数：参数标记的类型。可以是输入参数（ SQL_PARAM_INPUT ）、输出参数（ SQL_PARAM_OUTPUT ）或接受输入并返回输出的参数（ SQL_PARAM_INPUT_OUTPUT ）。为避免内存开销，还可以指定 PARAM_FILE 将Python变量绑定到包含大对象（BLOB、CLOB或DBCLOB）数据的文件的名称。默认值为 SQL_PARAM_INPUT |
| data-type | 可选参数：一个常量，指定Python变量应绑定的SQL数据类型，如 SQL_BINARY 、 DB2_CHAR 、 DB2_DOUBLE 或 DB2_LONG |
| precision | 可选参数：指定变量应绑定到数据库的精度 |
| scale | 可选参数：指定变量应绑定到数据库的小数位数 |
| size | 可选参数：指定应从INOUT/OUT参数中检索的大小 |

示例代码如下：

ibm_db.bind_param(stmt, 1, "A00")

4. 调用 ibm_db.execute() 函数执行SQL语句。

ibm_db.execute 函数的语法为：

Py_True/Py_False ibm_db.execute(IBM_DBStatement stmt [, tuple parameters])

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| stmt | 由 ibm_db.prepare() 返回的已准备好的语句 |
| parameters | 可选参数：一个包含与已准备好的语句中任何参数标记匹配的输入参数的元组。如果已经调用了 bind_param 函数绑定参数，则该参数可选 |

如果SQL执行成功，该函数返回 True ，否则抛出异常。如果要运行的SQL是SELECT语句或调用返回一个或多个结果集的存储过程，结果集将与语句对象关联，可以使用各种获取函数（如 ibm_db.fetch_tuple ）从结果集中检索行。

示例代码如下：

ibm_db.execute(stmt)

上述代码片段是 param.py 脚本的一部分。如果要测试该代码，可以修改脚本中的用户ID和密码，然后运行脚本：

python param.py

6. 调用存储过程

要从Python应用程序中调用存储过程，可以按照以下步骤操作：
1. 使用 ibm_db.connect 函数连接到数据库，创建数据库连接对象。
2. 调用 ibm_db.callproc 函数调用存储过程，该函数返回一个元组，元组的第一个元素是语句对象，其余元素是参数的修改副本。如果存储过程返回结果集，语句对象将包含这些结果集，同时OUT和INOUT参数可能会被替换为新值，输入参数保持不变。如果有多个结果集，可以通过传递原始语句资源作为第一个参数调用 ibm_db.next_result 方法，例如 stmt1 = ibm_db.next_result(stmt) ，以移动到下一个结果集。

ibm_db.callproc 函数的语法为：

tuple ibm_db.callproc ( IBM_DBConnection connection, string procname, [,tuple parameters] )

该函数的参数说明如下：
| 参数名 | 描述 |
| ---- | ---- |
| connection | 由 ibm_db.connect() 返回的有效数据库连接对象 |
| procname | 要调用的存储过程的名称 |
| parameters | 可选参数：一个元组，必须为存储过程期望的每个参数包含一个条目。如果存储过程没有参数，则该参数可选 |

以下是一个调用存储过程的示例：
假设存在一个SQL过程 sp_get_employees ，定义如下：

CREATE PROCEDURE sp_get_employees (IN dept_no CHAR(3), OUT dept_name VARCHAR(36))
DYNAMIC RESULT SETS 1
BEGIN
    DECLARE emp_cursor CURSOR WITH RETURN TO CLIENT FOR SELECT firstnme, lastname FROM employee WHERE workdept=dept_no;
    OPEN emp_cursor;
    SELECT deptname INTO dept_name FROM department WHERE deptno=dept_no;
END  @

可以使用以下Python代码调用该存储过程：

deptno='A00'
deptname=''
stmt, deptno,deptname=ibm_db.callproc( conn, "sp_get_employees", (deptno, deptname))
mytuple = ibm_db.fetch_tuple(stmt)
while mytuple != False:
    print "First Name is : %s, Last Name is : %s" % (mytuple[0], mytuple[1])
    mytuple = ibm_db.fetch_tuple(stmt)

上述代码片段是 sp.py 脚本的一部分。如果要测试该代码，需要先运行以下命令创建SQL过程：

db2 connect to sample
db2 -td@ -f sp_get_employees.db2
db2 terminate

然后修改 sp.py 脚本中的用户ID和密码，运行脚本：

python sp.py

通过以上步骤，你可以在Python应用程序中使用 ibm_db 驱动与DB2数据库进行各种交互操作，包括连接数据库、数据检索、插入、更新、删除以及调用存储过程等。希望这些内容对你有所帮助！

Python开发DB2应用程序全攻略

7. 操作流程总结与流程图

为了更清晰地展示使用 ibm_db 驱动进行DB2数据库操作的整体流程，下面将各个操作步骤进行总结，并给出对应的mermaid流程图。

7.1 整体操作流程总结

• 连接数据库 ：导入 ibm_db 模块，使用 ibm_db.connect 函数连接到DB2数据库，根据数据库位置（本地或远程）设置不同的连接参数。
• 数据检索 ：连接成功后，使用 ibm_db.exec_immediate 函数执行SELECT语句获取结果集，再用 ibm_db.fetch_tuple 等函数从结果集中提取数据。
• 数据插入、更新和删除 ：使用 ibm_db.exec_immediate 函数执行INSERT、UPDATE和DELETE语句，通过 ibm_db.num_rows 函数获取受影响的行数。
• 执行带参数标记的SQL语句 ：使用 ibm_db.prepare 函数准备SQL语句， ibm_db.bind_param 函数绑定参数， ibm_db.execute 函数执行语句。
• 调用存储过程 ：使用 ibm_db.callproc 函数调用存储过程，处理返回的结果集和参数。

7.2 操作流程图

graph TD;
    A[导入ibm_db模块] --> B[连接数据库];
    B --> C{操作类型};
    C -->|数据检索| D[执行SELECT语句];
    D --> E[获取结果集数据];
    C -->|数据插入、更新、删除| F[执行相应SQL语句];
    F --> G[获取受影响行数];
    C -->|带参数SQL语句| H[准备SQL语句];
    H --> I[绑定参数];
    I --> J[执行SQL语句];
    C -->|调用存储过程| K[调用存储过程];
    K --> L[处理结果集和参数];

8. 常见问题与解决方案

在使用 ibm_db 驱动进行DB2数据库开发过程中，可能会遇到一些常见问题，以下是一些问题及对应的解决方案。

问题描述	可能原因	解决方案
连接数据库失败	用户名、密码错误；数据库服务未启动；网络问题	检查用户名和密码是否正确；确保数据库服务已启动；检查网络连接是否正常
执行SQL语句报错	SQL语法错误；数据库表不存在；权限不足	检查SQL语句的语法；确认数据库中是否存在相应的表；检查用户是否具有执行该操作的权限
结果集获取不到数据	SQL查询条件不匹配；表中无数据	检查SQL查询条件是否正确；确认表中是否有符合条件的数据
绑定参数失败	参数类型不匹配；参数位置错误	确保绑定的参数类型与SQL语句中要求的类型一致；检查参数位置是否正确

9. 性能优化建议

为了提高Python应用程序与DB2数据库交互的性能，可以考虑以下几点优化建议。

9.1 合理使用持久连接

如前面提到， ibm_db 驱动支持持久连接，持久连接在关闭后会保留在连接池中，后续具有相同凭证的连接请求可以重用现有连接。这样可以减少频繁建立连接的开销，提高性能。

import ibm_db
# 使用pconnect创建持久连接
conn = ibm_db.pconnect("SAMPLE","db2admin","password")

9.2 优化SQL语句

• 避免使用SELECT *，只选择需要的列，减少数据传输量。
• 合理使用索引，提高查询效率。例如，如果经常根据 workdept 列进行查询，可以在该列上创建索引。

CREATE INDEX idx_workdept ON employee (workdept);

9.3 批量操作

对于插入、更新和删除操作，尽量使用批量操作，减少与数据库的交互次数。例如，使用批量插入语句：

data = [('000001','computer'), ('000002','TV')]
stmt = ibm_db.prepare(conn, "INSERT INTO PRODUCT VALUES(?,?)")
for row in data:
    ibm_db.execute(stmt, row)

10. 总结与展望

通过使用 ibm_db 驱动，我们可以方便地在Python应用程序中与DB2数据库进行交互，实现数据的检索、插入、更新、删除以及调用存储过程等操作。在实际开发过程中，我们需要根据具体需求选择合适的操作方法，并注意性能优化和常见问题的处理。

未来，随着Python和DB2技术的不断发展，可能会有更多的新特性和优化方法出现。我们可以持续关注这些技术的发展动态，不断提升应用程序的性能和稳定性，为用户提供更好的服务体验。同时，也可以探索更多与数据库交互的场景和方法，进一步拓展Python在数据库应用开发领域的应用范围。

希望本文能够帮助你更好地掌握使用Python开发DB2应用程序的方法和技巧，祝你在开发过程中取得成功！