八、Python 和ibm_db
为了从 Python 访问 Db2，您需要下载并设置ibm_db模块。目前，这个模块在任何 Linux、Unix 或 Windows 代码库中都不可用。但是，它可以通过 Python pip系统安装，或者作为源代码下载并在您的系统上编译。我们强烈建议您使用pip来安装ibm_db模块。但是在安装之前，需要满足一些先决条件，然后才能成功执行安装:
-
您的系统必须能够编译源代码。为了创建模块，必须安装 C 编译器和链接器。在 Linux 上，您可以通过运行以下命令来检查是否安装了编译器:
-
如果没有安装
pip，那么使用您的发行版的软件包安装程序来安装它。不要担心 pip 的 Python 版本，因为安装会为您的 Python 版本创建一个到正确版本pip的链接:sudo dnf install pip或
sudo yum install pip -
如果在 Linux 或 Unix 上运行，必须安装
python-devel包。使用您的发行版的安装包来安装python-devel (or python3-devel)包:sudo dnf install python-devel或
sudo yum install python-devel
gcc -v
一旦安装了额外的组件，就可以安装ibm_db包了:
sudo pip install ibm_db
或(适用于 RHEL 或 CentOS)
sudo python3 pip3 install ibm_db
(for RHEL or CentOS)
这将安装 Db2 包，以便 Python 脚本可以使用它。除非出于其他目的，否则不需要安装 Db2 客户机软件。ibm_db模块将允许 Python 脚本/程序在本地或远程访问 Db2，而无需任何其他软件包。
ibm_db包包含两个可以导入到脚本中的模块:ibm_db模块，这是从 Python 访问 Db2 的标准方式，以及ibm_db_dbi模块，这符合一般访问数据库系统的 Python DB-API 2.0 标准。在本书中，我们将集中讨论ibm_db模块，因为该模块是访问 Db2 的基础模块，并且实际上被ibm_db_dbi模块用来执行访问 Db2 的所有工作。
ibm_db模块是一个基于 C 源代码的模块。它是开源的，可以在 http://github.com/ibmdb/python-ibmdb/ 找到。此外，模块的 Python 测试和ibm_db_dbi模块的 Python 源代码都在同一个位置。
如果您在尝试安装ibm_db包时收到错误消息，那么一个或多个先决条件没有安装。如果消息声称找不到Python.h文件，那么您没有从 Linux/Unix 发行版的代码库中安装python-devel包，或者它没有正确安装。
一旦成功安装了 ibm_db 包，就可以编写第一个 Python 脚本来访问 Db2 了。
下面是一些使用ibm_db模块从sample数据库中检索数据的例子。所有这些例子都提示您输入用户名和密码。这些值总是 Db2 服务器上的值，而不是本地机器上的值(除非 Db2 系统驻留在本地机器上)。如果使用默认选项将sample数据库安装在服务器上，那么用户名将是db2inst1,，密码将是数据库管理员设置的任何值。
您的第一个 Python ibm_db 程序
本章中的示例 Python 代码访问 Db2 附带的sample数据库。您需要安装sample数据库，或者让您的 Db2 管理员为您安装。如果您自己在自己的系统上安装它，只需要运行一个命令就可以安装它。确保以db2inst1帐户运行该命令:
db2sampl -force -sql
这需要一点时间来安装，所以请耐心等待。安装后，您可以运行以下命令来测试数据库是否已成功安装:
db2 connect to sample
db2 "select * from department"
这将显示department表，它应该包含 14 条记录。
一旦成功执行，现在就可以编写访问 Db2 sample数据库的第一个 Python 程序了。
我们的第一个 Python 程序/脚本实际上非常简单。它模拟了我们之前用来测试数据库sample的 SQL select命令。我们希望表格内容有一个很好的格式显示，并且我们希望执行所有需要的错误检查，以确保我们可以跟踪程序可能遇到的任何错误。这将需要一个比第一个例子通常需要的更长的程序，但是它也给了我们一个机会来描述一些你将在你所有的 Python 程序中使用的ibm_dbAPI。
第一个 Python 示例程序如清单 8-1 所示。
#!/usr/bin/python
import sys, getpass
import ibm_db
def getColNamesWidths(results):
# get the width of each column
columns = list()
col = 0
numColumns = 0
try:
numColumns = ibm_db.num_fields(results)
except Exception:
pass
# If information about the number columns returned could not be obtained,
# display an error message and exit .
if numColumns is False:
print("\nERROR: Unable to obtain information about the result set produced.")
conn.closeConnection()
exit(-1)
while col < numColumns:
col_name = ibm_db.field_name(results, col)
col_width = ibm_db.field_width(results, col)
# the field name can be bigger than the display width
col_width = max(len(col_name), col_width)
columns.append((col_name, col_width))
col += 1
return columns # return a list of tuples (name, size)
def populateColTitleLines(columns):
# populate the two title lines for the results
col = 0
line = ''
lines = []
# do the title line
while col < len(columns):
(col_name, col_width) = columns[col]
title = col_name + ((col_width - len(col_name)) * ' ')
line += ' ' + title
col += 1
lines.append(line)
# do the underlines
col = 0
line = ''
while col < len(columns):
(col_name, col_width) = columns[col]
line += ' ' + (col_width * '-')
col += 1
lines.append(line)
return lines # return the two title lines
def populateLines(results, headerLines):
# print the data records
lines = []
record = ibm_db.fetch_tuple(results)
while record is not False:
line = ''
col = 0
numColumns = 0
try:
numColumns = ibm_db.num_fields(results)
except Exception:
pass
# If information about the number columns returned could not be obtained,
# display an error message and exit .
if numColumns is False:
print("\nERROR: Unable to obtain information about the result set produced.")
conn.closeConnection()
exit(-1)
while col < numColumns:
colstr = record[col]
(name, col_width) = headerLines[col]
coltype = ibm_db.field_type(results, col)
if record[col] is None:
line += ' -' + ((col_width - 1) * ' ')
elif coltype in ("clob", "dbclob", "blob", "xml", "string"):
# these are the string types
line += ' ' + str(colstr) + ((col_width - len(colstr)) * ' ')
else:
# these are the numeric types, or at least close enough
colstr = str(colstr)
line += ' ' + ((col_width - len(colstr)) * ' ') + colstr
col += 1
lines.append(line)
record = ibm_db.fetch_tuple(results)
return lines
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# get the records from the database
sqlstmt = 'select * from department'
try:
results = ibm_db.exec_immediate(connID, sqlstmt)
except Exception:
pass
# If the sql statement could not be executed, display an error message and exit
if results is False:
print("\nERROR: Unable to execute the SQL statement specified.")
ibm_db.close(connID)
exit(-1)
# fetch SQL results and format lines
headerLines = getColNamesWidths(results)
titleLines = populateColTitleLines(headerLines)
dataLines = populateLines(results, headerLines)
selrecords = len(dataLines)
#print the result lines
for line in titleLines:
print(line)
for line in dataLines:
print(line)
# print the number of records returned
print('\n ' + str(selrecords) + ' record(s) selected.')
ibm_db.close(connID)
exit(0)
Listing 8-1Display the department Table
Python 脚本从通常的第一行开始，通知系统这是一个 Python 脚本，然后指定要执行的 Python 解释器。
接下来的两行是脚本所需的 Python 导入语句:
import sys, getpass
import ibm_db
这里唯一不同寻常的是ibm_db模块的导入。
第一个函数getColNamesWidths()从结果表中获得每列所需的列名和显示宽度。对ibm_db.num_fields()函数的调用获得结果集包含的列数。一旦我们有了这个数字，我们就循环遍历每一列并调用ibm_db.field_name()和ibm_db.field_width()来获得列名和列显示宽度。这些将用于为每列添加标题，并设置列显示宽度。这些值随后被返回给调用者。
下一个函数populateColTitleLines()创建两行，它们最终将被打印到屏幕上。这些行是列标题行和分隔符虚线。它利用上一个函数getColNamesWidths()的值来计算如何格式化这些行。一旦行被创建，它们将作为 Python list返回给调用者。
下一个函数populateLines()创建从department表中获取的行，并使用传递给该函数的信息将每个记录格式化为一个显示行。创建的每一行都被添加到 Python list中，然后返回给调用者。每个结果集记录都通过ibm_db.fetch_tuple()函数获取，然后根据传递给该函数的信息进行格式化。
主程序代码紧跟在populateLines()函数之后。主程序代码的第一部分设置了一些值，这些值将在程序连接到 Db2 数据库时使用。在所有使用ibm_db模块的 Python 脚本中，driver变量总是相同的。host变量是保存sample数据库的服务器的 IP 地址或 DNS 名称。如果数据库在本地，那么它也可以是 IP 地址127.0.0.1。db变量具有您想要连接的数据库的名称；在我们的例子中，这是sample。我们没有初始化uid和pw变量，所以我们没有将用户 id 和密码信息编码到程序中。autocommit和connecttype变量是我们在这个脚本中没有使用的选项。
接下来的几行从用户那里获得了uid和pw变量信息。然后，我们调用 ibm_db.connect()函数实际连接到 Db2 和前面变量指定的数据库。如果此函数因任何原因失败，Python 脚本将退出并显示错误消息。这是一个深思熟虑的决定，这样我们就不会在程序中嵌入大量代码来纠正用户提供的信息或错误输入到脚本中的信息。
接下来的两段代码是执行实际工作的地方。第一部分设置要执行的 SQL 语句。在这种情况下，我们希望获取关于 department 表的所有信息。然后我们调用ibm_db.exec_immediate()来执行 SQL 语句。如果ibm_db.exec_immediate()失败，将显示错误信息，程序将退出并显示错误信息。如果成功了，我们继续下一段代码。
下一部分代码调用程序中定义的三个函数来获取并显示 SQL 语句的结果。这些功能已在前面描述过。
最后一个主要部分实际上将列标题和获取的数据打印到标准输出中。
最后一部分调用ibm_db.close()来关闭我们与 Db2 数据库的会话并退出脚本。
咻！这是很多代码，但重要的是要注意，我们包括了所有必要的错误检查，并生成了一个格式良好的报告返回给我们的程序用户。此外，这是可以在你编写的几乎任何使用select语句的程序中反复使用的代码，或者作为一个更复杂更大的程序的基础。这正是我们在后面的例子中要做的。程序的大部分将被重用，无需任何更改，只有 SQL 语句将被更改或代码将被添加到该部分以支持扩展功能。
清单 8-1 的输出在清单 8-2 中提供。
$ ./example8-1.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
------ ------------------------------------ ------ -------- ---------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
B01 PLANNING 000020 A00 -
C01 INFORMATION CENTER 000030 A00 -
D01 DEVELOPMENT CENTER - A00 -
D11 MANUFACTURING SYSTEMS 000060 D01 -
D21 ADMINISTRATION SYSTEMS 000070 D01 -
E01 SUPPORT SERVICES 000050 A00 -
E11 OPERATIONS 000090 E01 -
E21 SOFTWARE SUPPORT 000100 E01 -
F22 BRANCH OFFICE F2 - E01 -
G22 BRANCH OFFICE G2 - E01 -
H22 BRANCH OFFICE H2 - E01 -
I22 BRANCH OFFICE I2 - E01 -
J22 BRANCH OFFICE J2 - E01 -
14 record(s) selected.
Listing 8-2Output of Listing 8-1
尽管我们的输出和本章开始时测试 Db2 命令的显示之间有一些小的差异，但本质上是相同的。我们特意用两个空格隔开这些列，以便在视觉上更好地分隔这些列。包含NULL数据的记录有一个破折号来表示列开始字符中的NULL值。
在这一点上，我们应该指出这本书有一个附录专门用来记录ibm_dbAPI。尽管这些信息取自于ibm_db文档，我们还是添加了一些注释，希望能使文档更加清晰，并增强文档，使您的程序更具可读性。
使用参数标记
我们的下一个例子稍微复杂一些。它使用所谓的参数标记来允许将 Python 变量信息动态替换到 SQL 语句中。这意味着参数标记的值来自 SQL 语句之外。为了实现这一点，我们必须使用一些不同的 ibm_db API 调用来准备 SQL 语句，替换变量，然后执行语句。
我们将基本上使用与上一个例子相同的代码，但是代码的一个部分会有所不同。修改后的代码如清单 8-3 所示。
.
.
.
# get the records from the database
sqlstmt = 'SELECT projname, deptno FROM project WHERE deptno = ?'
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the SQL statement.")
exit(-1)
deptid = 'B01'
retcode = ibm_db.bind_param(prepstmt, 1, deptid, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
try:
results = ibm_db.execute(prepstmt)
except Exception:
pass
# If the sql statement could not be executed, display an error message and exit
if results is False:
print("\nERROR: Unable to execute the SQL statement specified.")
ibm_db.close(connID)
exit(-1)
.
.
.
Listing 8-3The Modified Code
请记住，除了两个 Python 语句之外，这一部分之前和之后的代码与清单 8-1 中的代码是相同的，因此，如果您没有使用来自 press 的打包代码，那么在重新创建这些代码时要小心。
除了语句中的最后一个字符，SQL 语句非常正常。问号(？)标识参数标记。这是我们将在代码中替换 Python 变量的地方。一条 SQL 语句可以有任意多个参数标记，并且可以包含数据甚至 SQL 关键字。在我们的例子中，它将包含我们正在寻找的特定部门号。
下一行是对ibm_db.prepare()的调用。这将解析 SQL 语句并记下任何参数标记。这必须在将数据代入 SQL 语句之前完成。然后，我们检查来自ibm_db.prepare()的返回代码，以确保在我们继续之前成功。
下一条语句设置我们将代入 SQL 语句的 Python 变量。这个价值从何而来，完全取决于你。它可能是 Python 程序的输入参数，也可能来自文件，或者您甚至可以提示用户输入值。只要确保在使用它之前对值进行适当的错误检查。
下一条语句调用ibm_db.bind_param()将 Python 变量绑定到 SQL 语句。该函数的第一个参数标识前一次调用ibm_db.prepare()时输出的准备好的语句。第二个参数是要在 SQL 语句中替换(或绑定)的 Python 变量。在这种情况下，使用的是部门编号。如果您在 SQL 语句中编码了多个参数标记，我们就需要这样做。对于每个参数标记，您需要单独调用 ibm_db.bind_param()。第三个参数指定标记是输入、输出还是输入/输出变量。第四个参数指定正在传递的变量的 SQL 类型。
下一组语句围绕着我们对ibm_db.execute()的调用，用于错误检查。这个函数实际上执行准备好的 SQL 语句。下面的代码检查以确保执行成功。
在执行 SQL 语句后，之后的代码是我们对三个函数的调用，然后打印 SQL 语句的结果。这段代码与我们之前的例子没有变化。
强烈建议您阅读ibm_db.prepare()功能的文档。有大量的参数类型来标识您应该熟悉的 SQL 数据类型。
带有参数标记的 SQL 语句可能是程序中最常用的语句类型。它们非常灵活，只需要多一点代码就可以容纳在您的 Python 代码中。
清单 8-4 提供了这个例子的输出。
$ ./example8-2.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
PROJNAME DEPTNO
------------------------ ------
WELD LINE PLANNING B01
Listing 8-4Output from Listing 8-3
我们的 SQL 语句只指定了两列的检索，并且只有一个部门被指定为B01。该报告很小，因为只有一个指定了编号的部门，但是它指出了参数标记非常有用的地方。
关于参数标记的更多信息
下一个示例使用多个参数标记来创建用于查询project表的值的选择。基本上，我们想列出所有指定了department号码的project名字。
同样，我们将使用本项目第一个例子中的基本代码来生成报告，同时只显示修改后的代码(清单 8-5 )。
.
.
.
# get the records from the database
sqlstmt = 'SELECT projname, deptno FROM project WHERE deptno = ? OR deptno = ?'
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the statement.")
exit(-1)
deptid1 = 'B01'
deptid2 = 'D01'
retcode = ibm_db.bind_param(prepstmt, 1, deptid1, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
retcode = ibm_db.bind_param(prepstmt, 2, deptid2, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
results = ibm_db.execute(prepstmt)
.
.
.
Listing 8-5Using Multiple Parameter Values
请记住，除了两个 Python 语句之外，这一部分之前和之后的代码与清单 8-1 中的代码是相同的，因此，如果您没有使用来自 press 的打包代码，那么在重新创建这些代码时要小心。
该查询的select语句包含两个参数值。第一个标记将标记为1;，第二个标记为2。在 SQL 语句中，参数标记总是从左向右标记。
接下来，我们准备调用ibm_db.prepare()的 SQL 语句，就像所有带参数标记的 SQL 语句一样。如果调用返回False,，那么我们产生一个错误消息并退出 Python 程序。
清单中接下来是将被替换到参数标记中的 Python 变量的赋值。同样，这些可以来自任何地方。我们在这里给它们赋值只是为了尽可能清楚地说明发生了什么。
现在我们调用ibm_db.bind_param()两次，每个参数标记一次。一旦值被绑定到 SQL 预准备语句，它将保持绑定，直到再次调用ibm_cb.bind_param()。
最后，我们调用ibm_db.execute()来获得查询结果。
清单 8-6 显示了这个查询的输出。
$ ../examples/example8-3.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
PROJNAME DEPTNO
------------------------ ------
ADMIN SERVICES D01
WELD LINE AUTOMATION D01
WELD LINE PLANNING B01
3 record(s) selected.
Listing 8-6Output from Listing 8-5
该报告显示，我们有三个项目将指定的部门编号用作参数标记，两个项目用于department 'D01'，一个项目用于部门“B01”。这是参数标记在程序中非常有用的另一个好例子。您可以将所需的数据存储在文件中，或者将其作为参数输入到 Python 程序中，并在不修改 Python 脚本的情况下更改报告。
使用参数标记生成多个报告
清单 8-7 展示了如何用一个带参数标记的 SQL 语句创建多个报告。正如您将看到的，这实际上比听起来容易。
同样，我们将使用本项目第一个示例中的基本代码来生成报告，同时只显示修改后的代码。
.
.
.
# get the records from the database
sqlstmt = """SELECT empno, firstnme, midinit, lastname, salary
FROM employee WHERE salary < ? and salary > ?
ORDER BY salary"""
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the statement.")
exit(-1)
salaries = (('35000.00', '0.00'), ('50000.00', '40000.00'))
for i in (salaries):
(salary1, salary2) = i
retcode = ibm_db.bind_param(prepstmt, 1, salary1, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
retcode = ibm_db.bind_param(prepstmt, 2, salary2, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
results = ibm_db.execute(prepstmt)
headerLines = getColNamesWidths(prepstmt)
titleLines = populateColTitleLines(headerLines)
dataLines = populateLines(prepstmt, headerLines)
selrecords = len(dataLines)
#print the result lines
for line in titleLines:
print(line)
for line in dataLines:
print(line)
# print the number of records returned
print('\n ' + str(selrecords) + ' record(s) selected.\n')
.
.
.
Listing 8-7Producing Multiple Reports with One SQL Statement
乍一看，这个清单与我们的其他例子有些不同。主要原因是我们将报告打印代码移到了循环中。我们这样做是因为我们需要为每次调用ibm_db.execute()函数打印一份报告。
我们的 SQL 语句稍微复杂一些，因为我们在末尾添加了一个ORDER BY子句，以便输出是有序的。它仍然有两个参数标记来指定薪水的范围。
接下来，我们准备 SQL 语句并测试结果中的错误。
下一条语句建立了一个 Python 列表数组，指定了我们想要的工资范围。指定了两个范围，每个范围将在单独调用ibm_db.execute()函数时使用。
剩下的代码是一个循环，它采用一组salary值来查询employee表。代码的工作方式与前面的例子一样，只是报告打印代码被移到了循环内部，这样两个查询都能够打印它们的报告。
本例中需要注意的一点是，我们不必多次准备 SQL 语句。这是特意设计的，以便准备好的 SQL 语句可以多次使用，就像我们的示例一样。要生成可能不同的报告，只需将新值重新绑定到参数标记，然后执行语句。
清单 8-8 提供了这个例子的输出。
$ ../examples/example8-4.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
EMPNO FIRSTNME MIDINIT LASTNAME SALARY
------ ------------ ------- --------------- -----------
200340 ROY R ALONZO 31840.00
1 record(s) selected.
EMPNO FIRSTNME MIDINIT LASTNAME SALARY
------ ------------ ------- --------------- -----------
000230 JAMES J JEFFERSON 42180.00
000340 JASON R GOUNOT 43840.00
000170 MASATOSHI J YOSHIMURA 44680.00
000330 WING LEE 45370.00
200280 EILEEN R SCHWARTZ 46250.00
200010 DIAN J HEMMINGER 46500.00
000260 SYBIL P JOHNSON 47250.00
000240 SALVATORE M MARINO 48760.00
000250 DANIEL S SMITH 49180.00
000120 SEAN O'CONNELL 49250.00
000220 JENNIFER K LUTZ 49840.00
11 record(s) selected.
Listing 8-8Multiple Reports from a Single SQL Statement
我们已经成功地在两个不同的salary范围内生成了两个报告，并按salary字段对记录进行了排序。我们对报告打印代码所做的惟一更改是在报告的末尾添加了一个换行符，这样两个报告之间就有了一些间隔。
使用没有绑定变量的参数标记
当有大量数据要加载或更新到表中时，最好使用这个例子。ibm_db.execute()函数有一个额外的可选参数，可以用来传递参数标记值，而不是为每个参数标记调用ibm_db.bind_param()函数。
当要插入/更新的行来自一个文件或另一个外部资源时，使用ibm_db.execute()效果最好。在我们的例子中，我们使用一个文件，该文件包含要插入到employee表中的行。该文件被构造成一个逗号分隔值(CSV)文件，每行数据代表一个要添加到employee表中的新行。
本例中要解决的额外问题是将输入文件中的每一行数据从单个字符串转换为 Python tuple的一组值，这是ibm_db.execute()函数所需要的。在清单 8-9 显示后，我们将对此进行更详细的讨论。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# Add new designer employees to the employee table
sql = """INSERT INTO employee (empno, firstnme, midinit, lastname,
workdept, phoneno, hiredate, job, edlevel, sex, birthdate,
salary, bonus, comm) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
stmt = ibm_db.prepare(connID, sql)
if stmt:
inserts = 0
with open('./example8-5.csv') as f:
line = f.readline()
while len(line) > 0:
emp_list = line.split(',')
for i in range(0, len(emp_list)):
emp_list[i] = emp_list[i].rstrip("' \n")
emp_list[i] = emp_list[i].lstrip("' ")
emp = tuple(emp_list)
result = ibm_db.execute(stmt, emp)
if result is False:
print("Unable to execute the SQL statement.")
exit(-1)
inserts += 1
line = f.readline()
print(str(inserts) + ' employees inserted successfully.')
# Now delete those new employees
ibm_db.exec_immediate(connID, "delete from employee where empno = '000350'")
ibm_db.exec_immediate(connID, "delete from employee where empno = '000360'")
ibm_db.exec_immediate(connID, "delete from employee where empno = '000370'")
ibm_db.exec_immediate(connID, "delete from employee where empno = '000380'")
print('4 employees deleted successfully.')
ibm_db.close(connID)
exit(0)
Listing 8-9Parameter Markers Without Binding Variables
第一个语句是我们程序所需的标准 Python import语句。
下一部分代码是我们访问 Db2 数据库的标准设置，提示用户输入用户 id 和密码，并连接到 Db2 数据库。
下一段代码不同于我们前面的例子。我们为将要插入的每一列数据设置了一个带有参数标记的 SQL INSERT语句。在本例中，这恰好是employee表中的所有列。接下来，我们准备最终执行的语句。
下一个代码块是所有工作发生的地方。我们首先打开包含要插入到表中的信息的文件。然后我们一次读一行。接下来，由于该文件是一个 CSV 结构，我们在每个逗号处拆分该行。分割的片段在每个片段中都有我们不需要的数据，所以我们删除了前导和尾随空格以及单引号。最后，我们将列值的 Python list改为 Python tuple。
现在我们可以用列值的tuple作为函数调用中的第二个参数来调用ibm_db.execute()。然后，我们检查以确保insert正常工作，然后读取下一行。
我们做的最后一件事是删除我们添加到employee表中的行。这将使数据库保持其原始状态。
最后，我们关闭数据库连接。
清单 8-10 显示了程序的所有输出。这里没什么可看的，但这是程序成功执行的结果。
$ ./example8-5.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
4 employees inserted successfully.
4 employees deleted successfully.
Listing 8-10Output from Listing 8-9
这个例子表明，只需在一个循环中做一点工作，就可以消除对ibm_db.bind_param()函数的 14 次调用。它还消除了使用 14 个不同的变量来保存信息，并用一个简单的 Python list和一个单独的tuple来代替它们。
我们应该在这里添加另一个注释，以便可能清理一个项目。Python 中的元组是不可变的。这意味着一旦创建了它们，就不能用其他成员来修改或扩展它们。这就是为什么我们必须在一次调用中将 Python list转换成tuple。
连接表格
Db2 支持查询中的表连接。有两种类型的联接–内部联接和外部联接。自从 Db2 出现以来就支持内部连接，但是外部连接是一个相对较新的概念。我们将在本节讨论这两种类型。
内部联接
在 SQL 中，内部联接有两种形式。内部连接的第一种形式可以追溯到 Db2 的初期，并且编码起来相当简单。第二种形式的内部联接同样易于编码，并且更容易准确地确定要联接什么以及如何联接。
下面的清单 8-11 显示了旧形式的内部连接。这只是整个程序的摘录，只是为了展示 SQL 语句及其处理方式。
# get the records from the database
sqlstmt = """SELECT e.empno, e.lastname, d.deptname FROM emp e, dept d
WHERE e.workdept = d.deptno AND d.deptname = ?"""
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the statement.")
exit(-1)
deptnme = 'SOFTWARE SUPPORT'
retcode = ibm_db.bind_param(prepstmt, 1, deptnme, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
results = ibm_db.execute(prepstmt)
headerLines = getColNamesWidths(prepstmt)
titleLines = populateColTitleLines(headerLines)
dataLines = populateLines(prepstmt, headerLines)
selrecords = len(dataLines)
Listing 8-11The Old Form of an Inner Join
这个程序的输出将只显示在被称为“软件支持”的部门工作的雇员那个部门总共应该只有六名员工。这两个表在employee表中的workdept列和department表中的deptno列连接在一起。一个额外的约束要求只查看deptname等于“软件支持”的行
如您所见，这个例子非常简单。当 SQL 语句有许多约束，并且 SQL 语句中的连接列不容易被发现时，就会出现问题。为了解决这个问题并方便地支持外部联接，在 SQL 语句中添加了一个新的子句来显式指定联接的类型。
下面的清单 8-12 展示了一个内部连接的新语法，它获得了与前一个例子相同的数据。
# get the records from the database
sqlstmt = """SELECT e.empno, e.lastname, d.deptname FROM emp e
INNER JOIN dept d ON e.workdept = d.deptno AND d.deptname = ?"""
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the statement.")
exit(-1)
deptnme = 'SOFTWARE SUPPORT'
retcode = ibm_db.bind_param(prepstmt, 1, deptnme, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
results = ibm_db.execute(prepstmt)
headerLines = getColNamesWidths(prepstmt)
titleLines = populateColTitleLines(headerLines)
dataLines = populateLines(prepstmt, headerLines)
selrecords = len(dataLines)
Listing 8-12The New Form of an Inner Join
本例中唯一修改的是 SQL 语句。否则，程序不变。SQL 语句有一个新子句 INNER JOIN 子句和。这些显式声明了正在对指定的两个表进行内部连接。在这种情况下，没有 WHERE 子句，因为 ON 子句替换了它。
这个程序的输出和前面的程序完全一样。这里给出这两个例子的原因是仍然有大量的程序仍然使用旧形式的内部连接，根据需要识别和处理这些程序是很重要的。
外部连接
外部联接与内部联接的不同之处在于，它不仅能够显示与内部联接相似的结果行，还能够显示两个表的联接列都设置为 NULL 的行。因此，它会显示由于其中一列的值无效而无法连接在一起的行。
这实际上是 Db2 的一个很少使用的特性，因此我们不打算在这里展示一个例子。有关更多信息，请参考 Web 上的 IBM Db2 知识中心。
插入、更新和删除
在这一节中，我们将看看 SQL insert、update和delete语句，以及它们与 Python 和参数标记的关系。实际上，我们在前一节的示例中看到了delete语句，但是这里我们将更详细地讨论所有这些语句。
清单 8-13 是使用所有四个 SQL 数据操作语句的例子。它将一个新条目插入到employee表中，更新它，获取它进行显示，然后删除该条目。这将使employee表保持其原始状态。
#!/usr/bin/python
import sys, getpass
from decimal import *
import ibm_db
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# Add new designer employees to the employee table
sql = """INSERT INTO employee (empno, firstnme, midinit, lastname,
workdept, phoneno, hiredate, job, edlevel, sex, birthdate,
salary, bonus, comm) VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
stmt = ibm_db.prepare(connID, sql)
if stmt is False:
print("Unable to prepare the SQL statement.")
exit(-1)
emp = ('000350', 'DAVID', 'W', 'ANDREWS', 'D11', '3634', '1969-03-20', \
'DESIGNER', 20, 'M', '1951-06-14', 40160.00, 500, 2220)
result = ibm_db.execute(stmt, emp)
if result is False:
print("Unable to execute the SQL statement.")
ibm_db.close(connID)
exit(-1)
# Now update the salary
sql = "UPDATE employee SET salary = ? where empno = '000350'"
stmt = ibm_db.prepare(connID, sql)
if stmt is False:
print("Unable to prepare the SQL statement.")
exit(-1)
salary = str(Decimal('40160.00') * Decimal('1.1'))
retcode = ibm_db.bind_param(stmt, 1, salary, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
result = ibm_db.execute(stmt)
if result is False:
print("Unable to execute the SQL statement.")
ibm_db.close(connID)
exit(-1)
# Ensure the salary is updated
sql = "select empno, salary from employee where empno = '000350'"
results = ibm_db.exec_immediate(connID, sql)
if results is False:
print("\nERROR: Unable to execute the SQL statement specified.")
ibm_db.close(connID)
exit(-1)
(empno, salary) = ibm_db.fetch_tuple(results)
print('empno: ', str(empno), ' old salary: 40160.00 new salary: ', str(salary))
# Now delete the employee we added
ibm_db.exec_immediate(connID, "delete from employee where empno = '000350'")
ibm_db.close(connID)
exit(0)
Listing 8-13Insert, Update, and Delete Example
示例清单与我们所有的示例一样，只有一个例外。为了更新salary，它在内部是一个decimal字段，我们需要导入decimal模块。这样，我们可以保持调整与 SQL 字段一致。
第一个任务是向employee表中插入一条新记录。我们在 INSERT 语句中使用参数字段。这就是 SQL 语句中所有的问号。然后我们调用ibm_db.prepare()来准备语句。
接下来，我们为要插入的数据创建tuple。然后，我们调用ibm_db.execute()将新行插入数据库。
下一个任务是update我们刚刚插入的记录。我们为新的salary创建带有单个参数标记的 SQL UPDATE 语句，然后准备该语句。下一条语句将当前薪金转换为十进制字段，并将其增加 10%。这样做的结果是一个新字段，也是一个十进制字段。然后，我们将新的薪水绑定到 UPDATE SQL 语句和execute语句。
为了确保update成功，并向我们自己证明它是有效的，我们用 SELECT 语句从数据库中获取数据并显示结果。结果显示员工编号、旧工资和新工资。
最后，我们delete新的行，让数据库回到它最初的开始状态。
这个例子有点复杂，但是它是所有 SQL 数据操作语句的一个例子。这个例子也可以作为你几乎每天都会遇到的许多活动的起点。
清单 8-14 具有清单 8-13 的输出。它显示了新员工在更新前后的工资。
$ ./example8-6.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
empno: 000350 old salary: 40160.00 new salary: 44176.00
Listing 8-14Output from Listing 8-13
值得注意的是，10%增长的计算是完全正确的，不会受到浮点计算的不准确性的影响。通过使用decimal包来执行计算，我们已经确保应用了正确的增量。
其他一些 ibm _ db APIs
在这一节中，我们将看看您可能会用到的其他一些ibm_db包 API。这些不是数据操作语句，而是可以提供关于数据库和 Python 程序处理环境各个方面的信息。
需要注意的是，这并没有涵盖ibm_db包中所有剩余的 API。其余的 API 很少使用，并且有充分的文档记录，因此它们可以很容易地集成到您的程序中。
清单 8-15 显示了这个例子的代码。我们将在上市后对其进行审查。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# Test if the connection is active
active = ibm_db.active(connID)
if active:
print('The currect connection is active.')
else:
print('*The current connection is not active.')
# Test autocommit
commit = ibm_db.autocommit(connID)
if active:
print('Autocommit is active.')
else:
print('*Autocommit is not active.')
# Get the client info
clientinfo = ibm_db.client_info(connID)
if clientinfo:
print('Client info:')
print(' APPL_CODEPAGE: ', clientinfo.APPL_CODEPAGE)
print(' CONN_CODEPAGE: ', clientinfo.CONN_CODEPAGE)
print(' DATA_SOURCE_NAME: ', clientinfo.DATA_SOURCE_NAME)
print(' DRIVER_NAME: ', clientinfo.DRIVER_NAME)
print(' DRIVER_ODBC_VER: ', clientinfo.DRIVER_ODBC_VER)
print(' DRIVER_VER: ', clientinfo.DRIVER_VER)
print(' ODBC_SQL_CONFORMANCE: ', clientinfo.ODBC_SQL_CONFORMANCE)
print(' ODBC_VER: ', clientinfo.ODBC_VER)
else:
print('Could not obtain client info.')
# Get column priviliges, if they exist
priv = ibm_db.column_privileges(connID, None, uid.upper(), 'employee', 'workdept')
row = ibm_db.fetch_assoc(priv)
if row:
print('Sample database, table department, column priviliges:')
print(" Schema name : {}" .format(row['TABLE_SCHEM']))
print(" Table name : {}" .format(row['TABLE_NAME']))
print(" Column name : {}" .format(row['COLUMN_NAME']))
print(" Privilege grantor : {}" .format(row['GRANTOR']))
print(" Privilege recipient : {}" .format(row['GRANTEE']))
print(" Privilege : {}" .format(row['PRIVILEGE']))
print(" Privilege is grantable : {}" .format(row['IS_GRANTABLE']))
else:
print('No column privileges to retrieve.')
# Get column metadata, if it exists
coldata = ibm_db.columns(connID, None, None, 'employee', 'empno')
row = ibm_db.fetch_assoc(coldata)
if row:
print('Sample database, table department, columns metadata:')
table_name = row['TABLE_NAME']
column_name = row['COLUMN_NAME']
print(" Table name : {}" .format(table_name))
print(" Column name : {}" .format(column_name))
else:
print('No column metadata to retrieve.')
# Test SQL commit
rc = ibm_db.commit(connID)
if rc:
print('Commit succeeded.')
else:
print('*Commit did not succeed.')
ibm_db.close(connID)
exit(0)
Listing 8-15Some Miscellaneous ibm_db APIs
与往常一样，该程序的起始代码与我们的其他示例相同。第一个测试是ibm_db.active() API。这个 API 确定作为参数传递的连接是否仍然处于活动状态。虽然在大多数程序中并不重要，但在使用ibm_db.pconnect() API 的程序中会用到它。
下一个使用的 API 是ibm_db.autocommit()。这个 API 可以获取和设置指定连接的自动提交标志。
下一个 API 是ibm_db.client_info()。这个 API 返回关于连接的客户端的信息。它列出了使用的代码页、驱动程序名称和版本以及 SQL 标准符合性。
接下来，ibm_db.column_priviliges()查询表中分配给特定列的特权。这些特权可能存在，也可能不存在。示例数据库中的表通常没有被分配特殊权限，因此 API 不会从这些表返回任何数据。
ibm_db.columns() API 用于查询分配给表中某一列的元数据。示例数据库没有为我们能够确定的列分配元数据。
我们测试的最后一个 API 是ibm_db.commit() API。这个 API 在调用时强制提交数据库。无论自动提交标志是否打开，这都应该有效。
清单 8-16 显示了清单 8-15 在示例客户机上的输出。
$ ./example8-7.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
The currect connection is active.
Autocommit is active.
Client info:
APPL_CODEPAGE: 1208
CONN_CODEPAGE: 1208
DATA_SOURCE_NAME: SAMPLE
DRIVER_NAME: libdb2.a
DRIVER_ODBC_VER: 03.51
DRIVER_VER: 11.01.0405
ODBC_SQL_CONFORMANCE: EXTENDED
ODBC_VER: 03.01.0000
No column privileges to retrieve.
No column metadata to retrieve.
Commit succeeded.
Listing 8-16Output from the Listing 8-15 Program
的输出非常简单。大多数情况下，只有一行结果来自被测试的 API。客户机信息 API 有更多的数据，所有这些都是需要这些信息的程序员感兴趣的。
创建数据库对象
ibm_db库还可以帮助您创建数据库对象，比如表、索引和表空间。必要时，它还可以删除现有的数据库对象。这些操作是通过 CREATE 和 DROP SQL 语句完成的。
此外，本节还将介绍另外两个ibm_dbAPI，即stmt_error和stmt_errormsg。这些 API 用于报告错误情况和解释。它们可以在prepare()、exec_immediate()和callproc()API 之后使用，包含有助于您诊断 SQL 语句问题的信息。
清单 8-17 展示了如何使用 Python 模块创建一个表。需要注意的一点是:下面的例子没有真正的意义，只是严格地用于展示表列定义的例子。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# create the sample table
sqlstmt = """CREATE TABLE myexampletable (
C01 INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1),
C02 CHAR(50),
C03 VARCHAR(70),
C04 DEC(15,2),
C05 FLOAT(21),
C06 CLOB(1K),
C07 VARGRAPHIC(2000),
C08 BLOB(1M),
C09 DATE,
C10 TIME,
C11 TIMESTAMP,
C12 XML,
C13 BOOLEAN,
PRIMARY KEY(C01))"""
try:
rc = ibm_db.exec_immediate(connID, sqlstmt)
except:
print("Create ' {} ' failed with ".format(sqlstmt))
print("Error : {}".format(ibm_db.stmt_errormsg()))
exit(-1)
print('\n The CREATE statement executed successfully.')
ibm_db.close(connID)
exit(0)
Listing 8-17Creating a Sample Table
关于这个例子，有一些事情需要注意。首先，除了作为例子，表格没有任何意义。它不依赖于示例数据库中的任何其他表。这只是 Db2 提供给用户的一些列数据类型的一个例子。
其次，try / except块增加了一些不错的异常处理。它从exec_immidiate() API 中寻找非零返回代码，如果没有找到，它就执行except代码块。
第三，except 块调用了stmt_errormsg() API，它将打印出与 SQL 语句中的错误相关的错误消息。
第四，这个例子中没有格式化代码，因为它不返回结果表，只是一个简单的返回代码。
C01 列是递增列的一个例子。每次插入或更改新行时，数据库都会生成一个值放在该列中。按照 SQL 语句中最后一个子句的定义，该列也是表的主键。
C02 是一个字符串，总是有 50 个字符与之相关联。当您将少于 50 个字符的字符串分配给此列时，系统会用空格填充右侧的字符串，直到其长度达到 50 个字符。如果您尝试添加超过 50 个字符的字符串，它将被截断为 50 个字符。
C03 列是可变字符串。这种类型的字符串不会在右边填充空格。相反，会记录字符串的实际长度。但是如果字符串长度超过 70 个字符，它将被截断为 70 个字符。
C04 列是一个固定的十进制数，长度为 15 个数值，有 2 个小数位。
C05 列是一个浮点字段，总共可以容纳 21 个数字字符。
C06 列是一个可变图形字段，最大值为 1，000，000 字节。
C07 列是可变图形字段，最大 200 字节。
C08 列是长度为 1，000，000 字节的二进制 LOB。
C09 列是包含数据值的字段。
C10 列是包含时间值的字段。
C11 列是包含时间戳值的字段。该字段包含日期和时间值，它们连接在一起形成时间和日期的实例。
C12 列是一个 XML 字段。这个字段实际上是指向包含 XML 数据的文件系统的指针。
C13 列是一个布尔型字段，用于简单的是/否值。
现在我们已经查看了该表，您可能希望将其从数据库中删除。清单 8-18 会帮你做到。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, autocommit, connecttype)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# create the sample table
sqlstmt = """DROP TABLE myexampletable"""
try:
rc = ibm_db.exec_immediate(connID, sqlstmt)
except:
print("Drop ' {} ' failed with ".format(sqlstmt))
print("Error : {}".format(ibm_db.stmt_errormsg()))
exit(-1)
print('\n The DROP statement executed successfully.')
ibm_db.close(connID)
exit(0)
Listing 8-18Removing a Table
这个程序与前一个程序非常相似，只是它使用 DROP SQL 语句来删除我们在本节开始时创建的表。
获取现有表的属性
有时，您可能需要知道现有 Db2 表中每一列的属性。有了ibm_db库，你可以很容易地获得这些属性。清单 8-19 展示了如何实现这一点。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
resultSet = False
dataRecord = False
tableName = "EMP"
sqlDataTypes = {0 : "SQL_UNKNOWN_TYPE", 1 : "SQL_CHAR", 2 : "SQL_NUMERIC", 3 : "SQL_DECIMAL",
4 : "SQL_INTEGER", 5 : "SQL_SMALLINT", 6 : "SQL_FLOAT", 7 : "SQL_REAL", 8 : "SQL_DOUBLE",
9 : "SQL_DATETIME", 12 : "SQL_VARCHAR", 16 : "SQL_BOOLEAN", 19 : "SQL_ROW",
91 : "SQL_TYPE_DATE", 92 : "SQL_TYPE_TIME", 93 : "SQL_TYPE_TIMESTAMP",
95 : "SQL_TYPE_TIMESTAMP_WITH_TIMEZONE", -8 : "SQL_WCHAR", -9 : "SQL_WVARCHAR",
-10 : "SQL_WLONGVARCHAR", -95 : "SQL_GRAPHIC", -96 : "SQL_VARGRAPHIC",
-97 : "SQL_LONGVARGRAPHIC", -98 : "SQL_BLOB", -99 : "SQL_CLOB", -350 : "SQL_DBCLOB",
-360 : "SQL_DECFLOAT", -370 : "SQL_XML", -380 : "SQL_CURSORHANDLE", -400 : "SQL_DATALINK",
-450 : "SQL_USER_DEFINED_TYPE"}
sqlDateTimeSubtypes = {1 : "SQL_CODE_DATE", 2 : "SQL_CODE_TIME", 3 : "SQL_CODE_TIMESTAMP",
4 : "SQL_CODE_TIMESTAMP_WITH_TIMEZONE"}
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, '', '')
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# Attempt to retrieve information about all columns of a table
resultSet = ibm_db.columns(connID, None, None, tableName, None)
# If The Information Desired Could Not Be Retrieved, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to obtain the information desired\n.")
conn.closeConnection()
exit(-1)
noData = False
loopCounter = 1
while noData is False:
dataRecord = ibm_db.fetch_assoc(resultSet)
if dataRecord is False:
noData = True
else:
# Display Record Header Information
print("Column " + str(loopCounter) + " details:")
print("_________________________________________")
# Display The Information Stored In The Data Record Retrieved
print("Table schema : {}" .format(dataRecord['TABLE_SCHEM']))
print("Table name : {}" .format(dataRecord['TABLE_NAME']))
print("Column name : {}" .format(dataRecord['COLUMN_NAME']))
print("Data type : {}" .format(dataRecord['TYPE_NAME']))
print("Size : {}" .format(dataRecord['COLUMN_SIZE']))
print("Buffer size : {}" .format(dataRecord['BUFFER_LENGTH']))
print("Scale (decimal digits) : ", end="")
if dataRecord['DECIMAL_DIGITS'] == None:
print("Not applicable")
else:
print("{}" .format(dataRecord['DECIMAL_DIGITS']))
print("Precision radix : ", end="")
if dataRecord['NUM_PREC_RADIX'] == 10:
print("Exact numeric data type")
elif dataRecord['NUM_PREC_RADIX'] == 2:
print("Approximate numeric data type")
elif dataRecord['NUM_PREC_RADIX'] == None:
print("Not applicable")
print("Can accept NULL values : ", end="")
if dataRecord['NULLABLE'] == ibm_db.SQL_FALSE:
print("NO")
elif dataRecord['NULLABLE'] == ibm_db.SQL_TRUE:
print("YES")
print("Remarks : {}" .format(dataRecord['REMARKS']))
print("Default value : {}" .format(dataRecord['COLUMN_DEF']))
print("SQL data type : ", end="")
print(sqlDataTypes.get(dataRecord['SQL_DATA_TYPE']))
print("SQL data/time subtype : ", end="")
print(sqlDateTimeSubtypes.get(dataRecord['SQL_DATETIME_SUB']))
print("Data type : {}" .format(dataRecord['DATA_TYPE']))
print("Length in octets : ", end="")
if dataRecord['CHAR_OCTET_LENGTH'] == None
:
print("Not applicable")
else:
print("{}" .format(dataRecord['CHAR_OCTET_LENGTH']))
print("Ordinal position : {}" .format(dataRecord['ORDINAL_POSITION']))
print("Can contain NULL values : {}" .format(dataRecord['IS_NULLABLE']))
# Increment The loopCounter Variable And Print A Blank Line To Separate The
# Records From Each Other
loopCounter += 1
print()
ibm_db.close(connID)
exit(0)
Listing 8-19Obtaining the Column Attributes of an Existing Table
列有许多可用的属性。根据列上定义的数据类型或未定义的数据类型，其中一些将不适用。您感兴趣的主要属性是数据类型或 SQL 数据类型，它决定了存储在列中的数据类型。有些特定的属性仅适用于某些类型的数据，例如，SCALE 属性仅适用于 DECIMAL 数据类型。
清单 8-20 列出了表格中的所有列。在下面的输出中，为了节省空间，我们删除了一些列。
$ ./example8-14.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
Column 1 details:
_________________________________________
Table schema : DB2INST1
Table name : EMP
Column name : EMPNO
Data type : CHAR
Size : 6
Buffer size : 6
Scale (decimal digits) : Not applicable
Precision radix : Not applicable
Can accept NULL values : NO
Remarks : None
Default value : None
SQL data type : SQL_CHAR
SQL data/time subtype : None
Data type : 1
Length in octets : 6
Ordinal position : 1
Can contain NULL values : NO
Column 2 details:
_________________________________________
Table schema : DB2INST1
Table name : EMP
Column name : FIRSTNME
Data type : VARCHAR
Size : 12
Buffer size : 12
Scale (decimal digits) : Not applicable
Precision radix : Not applicable
Can accept NULL values : NO
Remarks : None
Default value : None
SQL data type : SQL_VARCHAR
SQL data/time subtype : None
Data type : 12
Length in octets : 12
Ordinal position : 2
Can contain NULL values : NO
.
.
.
Column 14 details:
_________________________________________
Table schema : DB2INST1
Table name : EMP
Column name : COMM
Data type : DECIMAL
Size : 9
Buffer size : 11
Scale (decimal digits) : 2
Precision radix : Exact numeric data type
Can accept NULL values : YES
Remarks : None
Default value : None
SQL data type : SQL_DECIMAL
SQL data/time subtype : None
Data type : 3
Length in octets : Not applicable
Ordinal position : 14
Can contain NULL values : YES
Listing 8-20Output of Listing 8-19
每个列以及所有属性都显示在完整的输出文件中。清单 8-20 只是完整清单的一小部分摘录。
清单 8-14 Python 程序在许多情况下非常有用，比如发现列中是否允许空值，是否为列指定了默认值，可以提供列的最大大小线索的 SIZE 属性，以及许多其他属性。
获取结果集的属性
与获取现有 Db2 表的属性不同，结果集是保存查询结果的临时表。我们使用这个 API 的原因是，一个结果集可能是两个或多个表的连接，一些列可能在连接中被修改，其属性被连接过程修改。因此，除非通过反复试验，否则很难确定连接列的实际属性。
清单 8-21 展示了如何发现结果集的属性，以便它们可以用来帮助您确定一个列应该如何显示。
#!/usr/bin/python
import sys, getpass
import ibm_db
# main program
resultSet = False
dataRecord = False
tableName = "EMP"
sqlDataTypes = {0 : "SQL_UNKNOWN_TYPE", 1 : "SQL_CHAR", 2 : "SQL_NUMERIC", 3 : "SQL_DECIMAL",
4 : "SQL_INTEGER", 5 : "SQL_SMALLINT", 6 : "SQL_FLOAT", 7 : "SQL_REAL", 8 : "SQL_DOUBLE",
9 : "SQL_DATETIME", 12 : "SQL_VARCHAR", 16 : "SQL_BOOLEAN", 19 : "SQL_ROW",
91 : "SQL_TYPE_DATE", 92 : "SQL_TYPE_TIME", 93 : "SQL_TYPE_TIMESTAMP",
95 : "SQL_TYPE_TIMESTAMP_WITH_TIMEZONE", -8 : "SQL_WCHAR", -9 : "SQL_WVARCHAR",
-10 : "SQL_WLONGVARCHAR", -95 : "SQL_GRAPHIC", -96 : "SQL_VARGRAPHIC",
-97 : "SQL_LONGVARGRAPHIC", -98 : "SQL_BLOB", -99 : "SQL_CLOB", -350 : "SQL_DBCLOB",
-360 : "SQL_DECFLOAT", -370 : "SQL_XML", -380 : "SQL_CURSORHANDLE", -400 : "SQL_DATALINK",
-450 : "SQL_USER_DEFINED_TYPE"}
sqlDateTimeSubtypes = {1 : "SQL_CODE_DATE", 2 : "SQL_CODE_TIME", 3 : "SQL_CODE_TIMESTAMP",
4 : "SQL_CODE_TIMESTAMP_WITH_TIMEZONE"}
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db.connect(conn_str, '', '')
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# Attempt to retrieve information about all columns of a table
sqlstmt = """SELECT e.empno, e.lastname, d.deptname FROM emp e, dept d
WHERE e.workdept = d.deptno AND d.deptname = ?"""
prepstmt = ibm_db.prepare(connID, sqlstmt)
if prepstmt is False:
print("Unable to prepare the statement.")
exit(-1)
deptnme = 'SOFTWARE SUPPORT'
retcode = ibm_db.bind_param(prepstmt, 1, deptnme, ibm_db.SQL_PARAM_INPUT, \
ibm_db.SQL_CHAR)
results = ibm_db.execute(prepstmt)
# If The Information Desired Could Not Be Retrieved, Display An Error Message And Exit
if results is False:
print("\nERROR: Unable to obtain the information desired\n.")
ibm_db.close(connID)
exit(-1)
loopCounter = 1
cols = ibm_db.num_fields(prepstmt)
while loopCounter <= cols:
# Display Record Header Information
print("Column " + str(loopCounter) + " details:")
print("_________________________________________")
# Display The Information Stored In The Data Record Retrieved
print("Column name : {}" .format(ibm_db.field_name(prepstmt, loopCounter)))
print("Data type : {}" .format(ibm_db.field_type(prepstmt, loopCounter)))
print("Size : {}" .format(ibm_db.field_display_size(prepstmt, loopCounter)))
print("Scale (decimal digits) : ", end="")
if ibm_db.field_scale(prepstmt, loopCounter) == None:
print("Not applicable")
else
:
print("{}" .format(ibm_db.field_scale(prepstmt, loopCounter)))
print("Precision : {}" .format(ibm_db.field_precision(prepstmt, loopCounter)))
print("Display size : ", end="")
if ibm_db.field_display_size(prepstmt,loopCounter) == None:
print("Not applicable")
else:
print("{}" .format(ibm_db.field_display_size(prepstmt,loopCounter)))
# Increment The loopCounter Variable And Print A Blank Line To Separate The
# Records From Each Other
loopCounter += 1
print()
ibm_db.close(connID)
exit(0)
Listing 8-21Obtaining the Attributes of a Result Set
这个例子有点类似于清单 8-19 程序，除了可用的属性更少。这主要是由于结果集的性质，它在 Python 程序中主要用于显示目的。
清单 8-22 是清单 8-21 程序的输出。
$ ./example8-15.py
Enter the Db2 userid to be used: db2inst1
Password for db2inst1:
Column 1 details:
_________________________________________
Column name : LASTNAME
Data type : string
Size : 15
Scale (decimal digits) : 0
Precision : 15
Display size : 15
Column 2 details:
_________________________________________
Column name : DEPTNAME
Data type : string
Size : 36
Scale (decimal digits) : 0
Precision : 36
Display size : 36
Column 3 details:
_________________________________________
Column name : False
Data type : False
Size : False
Scale (decimal digits) : False
Precision : False
Display size : False
Listing 8-22Output from Listing 8-21
这里的输出类似于清单 8-14 。添加显示大小属性非常有价值，因为这是正确显示列数据所需的字符数。
ibm_db_dbi 和 Python
ibm_db_dbi模块实际上是一个 Python 脚本，但是它可以像 Python 模块一样被导入。该模块遵循 PEP 249 Python 数据库 API 规范 v2.0，这使得您的程序可以跨不同的数据库移植——至少这是总体思路。这个模块的规范有些松散，因此留下了很大的添加空间，这可能无法移植到其他数据库。ibm_db_dbi就是这种情况。
清单 8-23 是一个使用ibm_db_dbi模块的 Python 程序的例子。
#!/usr/bin/python
import sys, getpass
import ibm_db_dbi
# main program
driver = "{IBM DB2 ODBC DRIVER}" # Db2 driver information
host = '192.168.1.201' # database host IP or dns address
port = "50000" # host port
db = "sample" # database registered name
uid = None # userid on the database host
pw = None # password of the uid
autocommit = '' # autocommit default override
connecttype = '' # connect type default override
uid = input("Enter the Db2 userid to be used: ")
pw = getpass.getpass(prompt = "Password for %s: " % uid)
if pw == None or pw == '':
print("The password you entered is incorrect.")
exit(-1)
#host = host + ':' + port
conn_str = 'DRIVER=' + driver + ';HOSTNAME=' + host + \
';PORT=' + port + ';DATABASE=' + db + \
';UID=' + uid + ';PWD=' + pw
connID = ibm_db_dbi.connect(dsn=conn_str, conn_options=None)
# If the connection fails for any reason an uncaught exception is thrown
# and the program will exit with an error.
# get a cursor
cursor = connID.cursor()
sqlstmt = 'select * from department'
cursor.execute(sqlstmt)
# build/print header lines and fetch/print all rows
row = cursor.fetchone()
if row:
rows = 0
cols = len(cursor.description)
col = 0
typecode = []
collen = []
tline1 = ''
tline2 = ''
i = 0
# print the report header lines
while i < cols:
(name,type_code,display_size,internal_size,precision,scale,null_ok) = \
cursor.description[i]
typecode.append(type_code)
collen.append(max(display_size, len(name)))
tline1 = tline1 + ' ' + name + (collen[i]-len(name))*' '
tline2 = tline2 + ' ' + (collen[i]*'-')
i += 1
print(tline1 + '\n' + tline2)
# print each fetched row
while row:
rows += 1
colvals = list(row)
i = 0
line = ''
while i < cols:
(name,type_code,display_size,internal_size,precision,scale,null_ok) = \
cursor.description[i]
if colvals[i] is None:
line = line + ' -' + (collen[i]-1)*' '
elif typecode[i] in ibm_db_dbi.DECIMAL:
line = line + ' ' + (collen[i]-len(colvals[i]))*' ' + colvals[i]
else:
line = line + ' ' + colvals[i] + (collen[i]-len(colvals[i]))*' '
i += 1
print(line)
row = cursor.fetchone()
# print summary
print('\n ' + str(rows) + ' record(s) selected.')
connID.close()
exit(0)
Listing 8-23An ibm_db_dbi Example Python Program
这个例子中的代码很容易理解。真正值得注意的是，生成与图 1-1 相同的输出所需的 Python 代码数量减少了。这有点误导，因为同样是 Python 代码的ibm_db_dbi模块正在做我们在清单 8-1 中为自己做的大量工作。因此，当模块和我们的程序代码放在一起时，大约有相同数量的 Python 代码被执行。
清单 8-24 显示您仍然可以在我们的 SQL 语句中使用参数标记。
# get a cursor
cursor = connID.cursor()
sqlstmt = 'SELECT projname, deptno FROM project WHERE deptno = ? OR deptno = ?'
cursor.execute(sqlstmt, ('B01', 'D01'))
# build/print header lines and fetch/print all rows
Listing 8-24Using Parameter Markers with ibm_db_dbi
我们只是展示了清单 8-9 中使用参数标记的修改后的语句。这将改变程序以匹配清单 8-3 的输出。注意，参数是作为元组在execute函数上传递的。
同样，大部分代码与上一个示例相同。这再次表明，使用ibm_db_dbi模块，我们可以利用它来减少 Python 程序中的代码。请注意，您应该尽可能不使用无法移植的代码。
ibm_db 模块何去何从？
在这一章中，我们已经深入讨论了ibm_db模块，但是它将走向何方？对模块源代码的仔细分析揭示了与 Db2 APIs 的一些不兼容性。开发人员承认这些问题应该得到解决，并且正在想办法解决。他们也承认，对于许多 Python 开发者来说，该模块的文档缺乏足够的例子。
这些和其他模块问题肯定会在未来版本中得到解决，但目前它们不会造成太多问题。正如我们在本书中展示的那样，该模块当然是可用的。但是，为了让 Db2 拥有更多基于 Windows、Linux 和 Unix 的用户，可以做些什么呢？目前，IBM 开发人员正在创建 Python 模块，通过创建从程序的通用接口到ibm_db模块的接口，允许使用通用数据库 API 的程序使用 Db2 作为主数据库。这将允许 Db2 存储程序所需的对象。
到目前为止，开发人员已经开发了四个这样的接口，我们将在下面讨论。
ibm_db_dbi 模块
在上一节中已经讨论了ibm_db_dbi接口，但是在这里值得一提。安装ibm_db模块时会包含该接口。它基于 PEP 248 规范。PEP 249 描述了一个通用接口，它应该支持几乎所有的本地数据库查询接口。ibm_db_dbi接口完全支持这个规范。
上一节有一个如何使用ibm_db_dbi接口的例子。这个接口调用ibm_db接口中的 API 来访问 Db2 数据库。但是需要注意的是，如果您的数据库在未来发生变化，您的 Python 程序需要的唯一变化是修改import语句，使其指向新数据库使用的接口文件。
通过使用一个通用的数据库接口，你的程序变得更容易移植，也更容易维护。使用通用数据库接口的缺点是程序员会失去一些本地数据库接口的高级特性。因此，在选择程序的数据库接口时，要仔细权衡这些选择。
Django 数据库接口
Django 系统是构建基于 web 的页面的一个非常强大的机制。它有许多有用的特性，并且被世界上大多数的网络程序员所使用。Django 还有一个通用的数据库接口，数据库供应商可以用它来翻译数据库支持的 API。这与 PEP 248 接口的工作方式非常相似。Db2 提供了ibm_db_django模块，该模块执行从 Django 访问 Db2 所必需的翻译。
要安装ibm_db_django模块，执行以下命令:
sudo pip install ibm_db
sudo pip install ibm_db_django
这将在您的机器上安装最新版本的ibm_db_django和ibm_db模块。剩下要做的就是在 Django 配置文件中配置模块，这样就可以使用 Db2 作为 Django 应用的存储设备了。清单 8-25 显示了需要添加到 Django settings.py文件中的内容。
DATABASES = {
'default': {
'ENGINE' : 'ibm_db_django',
'NAME' : 'mydjangodb',
'USER' : 'db2inst1',
'PASSWORD' : 'xxxxxx2',
'HOST' : 'localhost',
'PORT' : '50000',
'PCONNECT' : True, #Optional property, default is false
}
}
Listing 8-25Django settings.py File Extract
下面几行还需要添加到settings.py文件中的 tuple INSTALLED_APPS 中。
'django.contrib.flatpages',
'django.contrib.redirects',
'django.contrib.comments',
'django.contrib.admin',
通用的 Django 数据库 API 完全受ibm_db_django模块支持，所以在 Python 应用中不需要特殊的编码。但是，如果您没有权限，可能需要 Db2 管理员在 Db2 服务器上建立数据库。
Django 默认执行时没有事务，即处于自动提交模式。这种默认设置通常不是您在 web 应用中想要的。您应该记得在 Django 中打开事务支持。
要了解更多关于 Django 的信息，请访问 www.djangoproject.com/ 网站。
SQLAlchemy 适配器
SQLAlchemy 是一个对象关系适配器。它转换 Python 程序中的对象信息，并将其映射到关系数据库，以允许对象从程序的一次执行保存到下一次执行。转换是通过一组众所周知的模式执行的，因此存储对象的可靠性是持久的。
SQLAlchemy 工具包在 Python 领域得到了广泛使用，因为它在转换过程中使用了成熟的规则。这些工具很容易理解，在其他面向对象语言中也有很长的使用历史。
要安装the ibm_db_sa模块，执行以下命令:
sudo pip install ibm_db
sudo pip install ibm_db_sa
这将在您的机器上安装最新版本的 ibm_db_sa 和ibm_db模块。剩下要做的就是在 CLI 配置文件中配置该模块，这样就可以使用 Db2 作为 SQLAlchemy 应用的存储设备了。清单 8-26 显示了需要添加到 CLI 配置文件中的内容。
[pydb]
Database=pydev
Protocol=tcpip
Hostname=host.name.com
Servicename=50000
uid=db2inst1
pwd=secret
Listing 8-26CLI Configuration File Extract
一旦 CLI 配置文件有了正确的设置，就可以开始在程序中使用 SQLAlchemy 模块来创建 Python 程序对象的持久副本，并存储在 Db2 中。
要了解更多关于 SQLAlchemy 的信息，请访问 www.sqlalchemy.org/ 网站。
Alembic 适配器
Alembic 项目是 SQLAlchemy 项目的子项目。它是一个迁移工具，允许将存储在 SQLAlchemy 关系数据库中的对象迁移到另一个关系数据库中。无论使用不同于项目当前使用的关系数据库的原因是什么，Alembic 将允许您将所有数据迁移到新的关系数据库，也就是说，将 SQLite SQLAlchemy 数据库迁移到 Db2。
虽然这个工具在大多数环境中并不是日常使用的，但是在您需要的时候它是可用的。
要安装the ibm_db_alembic模块，使用以下命令:
sudo pip install ibm_db
sudo pip install ibm_db_alembic
这将在您的机器上安装最新版本的ibm_db_alembic和ibm_db模块。
要了解更多关于 Alembic 的信息，请访问 https://alembic.sqlalchemy.org/ 网站。
未来
ibm_db模块及其相关的子项目在允许 Python 访问 Db2 环境方面有了一个很好的开端。增加这个覆盖率有很多可能性，但是在写这本书的时候，有一件事阻碍了新项目的发展——缺乏开发人员。这也是阻碍许多开源项目发挥其全部潜能的问题。然而，ibm_db项目有一个小优势，那就是有一小部分来自 IBM 的开发人员正在努力推进这个项目。但是他们需要你的帮助和支持。所以，如果你有时间和兴趣，请自愿参加这个项目。
摘要
在本章中，我们已经介绍了ibm_db包和 API。我们已经展示了所有的数据操作 SQL 语句以及许多杂项 API。我们还介绍了如何使用参数标记，以及将 Python 数据绑定到使用它的语句的不同方式。
希望本章包含的信息和示例能够让您牢牢掌握如何使用和操作 Db2 数据库。您还应该能够更好地利用附录信息。
扫一扫
2714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
