官方文档：https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.5.0/com.ibm.db2.luw.apdv.python.doc/doc/t0054682.html
import ibm_db conn = ibm_db.connect("database=MICRO_11; " "hostname=localhost; " "port=50000; " "protocol=tcpip; " "uid=administrator; " "pwd=wyz", "", "") stmt = ibm_db.exec_immediate(conn,"SELECT SYS_ORG_TYPE_CODE,SYS_ORG_TYPE_NAME " "FROM SYS_ORG_TYPE_TB " "WHERE SYS_ORG_TYPE_UPID IS NOT NULL") # if结果集条数为0：result==False # if结果集条数>0：结果为一个tuple result = ibm_db.fetch_tuple(stmt) while result: print(result[0], result[1]) # 顺序和select字段顺序一样 result = ibm_db.fetch_tuple(stmt)
一、建连接：
''' ibm_db.connect() #非持久性连接 ibm_db.pconnect() #持久性连接，提升性能，连接不关闭 ''' import ibm_db conn = ibm_db.connect("dsn=name","username","password") #连接cataloged或非cataloged数据库 ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username; PWD=password;", "", "")
二、执行SQL：
不带参数的
''' string可以为XQuery表达式，用XMLQuery包装的 如果将用户输入作为SQL变量，可能受到SQL注入攻击 返回的是cursor类型，调用 ibm_db.num_rows()可以得到影响的数据行数 如果执行错误，可通过ibm_db.stmt_error() 或 ibm_db.stmt_errormsg()得到错误信息 ''' import ibm_db conn = ibm_db.connect("dsn=name","username","password") stmt = ibm_db.exec_immediate(conn, "UPDATE employee SET bonus = '1000' WHERE job = 'MANAGER'") print "Number of affected rows: ", ibm_db.num_rows(stmt) # XQuery if conn: sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = "Olathe" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as "t") AS CLOB(32k)) FROM xml_test c WHERE id = 1" stmt = ibm_db.exec_immediate(conn, sql) result = ibm_db.fetch_both(stmt) while( result ): print "Result from XMLSerialize and XMLQuery:", result[0] result = ibm_db.fetch_both(stmt)
带参数的：
''' ibm_db.prepare(), ibm_db.bind_param()，ibm_db.execute()三个方法 准备语句可以提高性能，因为数据库服务器为数据检索创建了优化的访问计划，如果再次执行该语句，则可以重用它 ''' import ibm_db conn = ibm_db.connect("dsn=name","username","password") sql = "SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE EMPNO > ? AND EMPNO < ?" stmt = ibm_db.prepare(conn, sql) max = 50 min = 0 # 绑定参数 ibm_db.bind_param(stmt, 1, min) ibm_db.bind_param(stmt, 2, max) ibm_db.execute(stmt) # Process results # Invoke prepared statement again using dynamically bound parameters param = max, min, ibm_db.execute(stmt, param)
三、检索结果集：ibm_db.fetch_both(stmt,num) 如果指定游标为scrollable类型，【在调用ibm_db.exec_immediate()或ibm_db.prepare()的时候】，num为检索的行数
import ibm_db # 第一种方式： conn = ibm_db.connect( "dsn=name", "username", "password" ) sql = "SELECT * FROM EMPLOYEE" stmt = ibm_db.exec_immediate(conn, sql) dictionary = ibm_db.fetch_both(stmt) while dictionary != False: print "The ID is : ", dictionary["EMPNO"] print "The Name is : ", dictionary[1] dictionary = ibm_db.fetch_both(stmt) # 第二种方式： tuple = ibm_db.fetch_tuple(stmt) while tuple != False: print "The ID is : ", tuple[0] print "The name is : ", tuple[1] tuple = ibm_db.fetch_tuple(stmt) # 第三种方式： dictionary = ibm_db.fetch_assoc(stmt) while dictionary != False: print "The ID is : ", dictionary["EMPNO"] print "The name is : ", dictionary["FIRSTNME"] dictionary = ibm_db.fetch_assoc(stmt) # 第四种方式： while ibm_db.fetch_row(stmt) != False: print "The Employee number is : ", ibm_db.result(stmt, 0) print "The Name is : ", ibm_db.result(stmt, "NAME")
四、调用存储过程
import ibm_db conn = ibm_db.connect("dsn=sample","username","password") if conn: sql = 'CALL match_animal(?, ?, ?)' stmt = ibm_db.prepare(conn, sql) name = "Peaches" second_name = "Rickety Ride" weight = 0 ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT) ibm_db.bind_param(stmt, 2, second_name, ibm_db.SQL_PARAM_INPUT_OUTPUT) ibm_db.bind_param(stmt, 3, weight, ibm_db.SQL_PARAM_OUTPUT) print "Values of bound parameters _before_ CALL:" print " 1: %s 2: %s 3: %d " % (name, second_name, weight) if ibm_db.execute(stmt): print "Values of bound parameters _after_ CALL:" print " 1: %s 2: %s 3: %d " % (name, second_name, weight)
五、开启事务：适合大批量插入数据提升性能
import ibm_db array = { ibm_db.SQL_ATTR_AUTOCOMMIT : ibm_db.SQL_AUTOCOMMIT_OFF } conn = ibm_db.pconnect("dsn=SAMPLE", "user", "password", array) sql = "DELETE FROM EMPLOYEE" try: stmt = ibm_db.exec_immediate(conn, sql) except: print "Transaction couldn't be completed." ibm_db.rollback(conn) else: ibm_db.commit(conn) print "Transaction complete."
六、错误处理：
第一种：
import ibm_db try: conn = ibm_db.connect("dsn=sample","user","password") except: print "no connection:", ibm_db.conn_errormsg() else: print "The connection was successful"
第二种：
import ibm_db conn = ibm_db.connect( "dsn=sample", "user", "password") sql = "DELETE FROM EMPLOYEE" try: stmt = ibm_db.exec_immediate(conn, sql) except: print "Transaction couldn't be completed:" , ibm_db.stmt_errormsg() else: print "Transaction complete."
七、查看元数据
注意：调用元数据函数消耗大量的空间。如果可能的话，考虑缓存调用的结果以便在后续调用中使用。
| ibm_db.client_info() | 返回包含数据库客户端信息的只读对象. |
| ibm_db.column_privileges() | 返回一个结果集，列出表的column和其关联特权 |
| ibm_db.columns() | 返回一个结果集，列出表的column和其关联元数据 |
| ibm_db.foreign_keys() | 返回一个结果集，列出表的column和其关联元数据 |
| ibm_db.primary_keys() | 返回一个结果集，列出表的外键 |
| ibm_db.procedure_columns() | 返回一个结果集，列出一个或多个存储过程的参数 |
| ibm_db.procedures() | 返回一个结果集，列出数据库里注册了的的存储过程 |
| ibm_db.server_info() | 返回包含数据库服务器信息的只读对象. |
| ibm_db.special_columns() | 返回一个结果集，列出 表的唯一行标识符列 |
| ibm_db.statistics() | 返回一个结果集，列出表的索引和统计信息 |
| ibm_db.table_privileges() | 返回一个结果集，列出数据库中的表和相关权限。 |
import ibm_db conn = ibm_db.connect("dsn=sample", "user", "password") client = ibm_db.client_info(conn) # DB客户端信息 if client: print "DRIVER_NAME: string(%d) "%s"" % (len(client.DRIVER_NAME), client.DRIVER_NAME) print "DRIVER_VER: string(%d) "%s"" % (len(client.DRIVER_VER), client.DRIVER_VER) print "DATA_SOURCE_NAME: string(%d) "%s"" % (len(client.DATA_SOURCE_NAME), client.DATA_SOURCE_NAME) print "DRIVER_ODBC_VER: string(%d) "%s"" % (len(client.DRIVER_ODBC_VER), client.DRIVER_ODBC_VER) print "ODBC_VER: string(%d) "%s"" % (len(client.ODBC_VER), client.ODBC_VER) print "ODBC_SQL_CONFORMANCE: string(%d) "%s"" % (len(client.ODBC_SQL_CONFORMANCE), client.ODBC_SQL_CONFORMANCE) print "APPL_CODEPAGE: int(%s)" % client.APPL_CODEPAGE print "CONN_CODEPAGE: int(%s)" % client.CONN_CODEPAGE ibm_db.close(conn) else: print "Error." # DB服务器信息 server = ibm_db.server_info(conn) if server: print "DBMS_NAME: string(%d) "%s"" % (len(server.DBMS_NAME), server.DBMS_NAME) print "DBMS_VER: string(%d) "%s"" % (len(server.DBMS_VER), server.DBMS_VER) print "DB_NAME: string(%d) "%s"" % (len(server.DB_NAME), server.DB_NAME) ibm_db.close(conn) else: print "Error."