在Python中遍历游标

Question

我刚开始用Python编写代码，我有一个问题。我正在使用ibm_dbi驱动程序

下面是我的sql示例：

    FOR v AS cur CURSOR FOR
       SELECT S.NAME FROM SYSIBM.SYSTABLES AS S
       JOIN (SELECT DISTINCT TBNAME FROM SYSIBM.SYSCOLUMNS WHERE CREATOR = 'SOME_SCHEMA_DB') AS C.TBNAME = S.NAME WHERE S.CREATOR = 'SOME_SCHEMA' AND S.TYPE = 'T'
       DO
         SET TABLE = SCHEMA1 ||'.'||v.Name
         ...
         SELECT 1 INTO TEMP_TABLE FROM SYSIBM.SYSTABLES WHERE TYPE='T' AND CREATOR = SCHEMA1 AND NAME = v.NAME
    END FOR;

到目前为止，我在Python中有：

#make a connection to db
conn = ibm_db_dbi.connect("DATABASE=%s;HOSTNAME=%s,....)
#define a cursor
cur = conn.cursor()
sql="""SELECT S.NAME FROM SYSIBM.SYSTABLES AS S
           JOIN (SELECT DISTINCT TBNAME FROM SYSIBM.SYSCOLUMNS WHERE CREATOR = 'SOME_SCHEMA_DB') AS C.TBNAME = S.NAME WHERE S.CREATOR = 'SOME_SCHEMA' AND S.TYPE = 'T'"""

resultSet = cur.execute(sql)

我不知道如何迭代查询结果并从游标设置值。如何为这段代码设置值

SET TABLE = SCHEMA1 ||'.'||v.Name
             ...
SELECT 1 INTO TEMP_TABLE FROM SYSIBM.SYSTABLES WHERE TYPE='T' AND CREATOR = SCHEMA1 AND NAME = v.NAME

Answer 1

提示：首先让SQL在python之外工作。您的问题显示该查询存在语法错误或拼写错误

要迭代查询的结果集，请使用DBI接口的一种有文档记录的获取方法（例如，fetchmany()或fetchall()或其他方法）

下面是一个片段，它使用fetchmany（）

try:
   cur = conn.cursor()
   sql="""SELECT S.NAME FROM SYSIBM.SYSTABLES AS S
          inner JOIN (SELECT DISTINCT TBNAME 
                      FROM SYSIBM.SYSCOLUMNS 
                      WHERE TBCREATOR = 'SOME_SCHEMA') AS C
          ON C.TBNAME = S.NAME 
          WHERE S.CREATOR = 'SOME_SCHEMA' 
          AND S.TYPE = 'T';"""
   cur.execute(sql)
   result = cur.fetchmany()
   while result:
       for i in result:
           print(i)
       result = cur.fetchmany()


except Exception as e:
    print(e)
    raise