在 Python 中将 SQL 与 IBM_DB 连接器一起使用

2022-01-14 00:00:00 python db2 ibm-midrange db2-400

问题描述

是否有人使用 IBM 的 Python for PASE 的 ibm_db 包来更新 IBM i(以前称为 AS/400)上的 Db2 文件?

Has anyone used the ibm_db package with IBM's Python for PASE to update Db2 files on IBM i (formerly AS/400)?

我想使用 Python 脚本(来自 QSH)来更新 Db2 数据库.我的目的是在运行时填充值并更新 Db2 文件的字段.它适用于静态(硬编码)值，但不适用于动态值.

I want to use Python scripts (from QSH) to update the Db2 database. My purpose is to populate values at runtime and update the fields of Db2 files. It works with static (hardcoded) values, but not dynamic ones.

这是我正在尝试的，但它不起作用:

Here is what I am trying, but it is not working:

import ibm_db

c1 = ibm_db.connect('*LOCAL','userid','password') 
sql = """INSERT INTO TEMPLIB.TEMPPF (TYPE, DRPARTY, CRPARTY, 
                                     AMOUNT,ACNUM, DESCRIPT) 
          VALUES('%s', '%s', '%s', '%s', '%s', '%s'), 
          %(self.type, self.debitparty, self.creditparty, self.amount, 
            self.craccountnumber, self.description) with NC
      """

stmt = ibm_db.exec_immediate(c1, sql )

  • self.type、self.debitparty等都是Python实例变量，有值.
  • TYPE、DRPARTY、CRPARTY等是TEMPPF的字段.

    • self.type, self.debitparty, etc. are Python instance variables and have values.
    • TYPE, DRPARTY, CRPARTY, etc. are fields of TEMPPF.

      • 像下面这样填充sql"变量这样更简单的方法可以工作:

      Something simpler like populating the 'sql' variable as below works:

      sql = "select * from TEMPLIB.TEMPPF"

      所以在某处我没有正确地制作 INSERT 格式.请问有人知道格式吗?我尝试了几种在 Internet 上可用的格式，但它们与 Python 不兼容，或者它们不是很好的示例.

      So somewhere I am not making the INSERT format correctly. Does anyone know the format please? I tried a couple of formats available on the Internet, but they are not compatible with Python, or they are not good examples.

      解决方案

      首先，您使用模数运算符连接字符串不正确，因为 %(vars) 需要驻留在预期的字符串之外格式化.

      First, your concatenation of strings with the modulus operator is not correct as %(vars) needs to reside outside the string intended to be formatted.

      其次，您应该使用 SQL 参数化(any 数据库中的行业标准，而不仅仅是 DB2)，而不是数据和查询语句的字符串插值.您可以使用 ibm_db_dbi 模块在游标 execute 调用中传递参数:

      Second, you should be using SQL parameterization (an industry standard in any database, not just DB2) and not string interpolation of data and query statement. You can do so using the ibm_db_dbi module to pass parameters in the cursor execute call:

      import ibm_db
import ibm_db_dbi   # ADD DBI LAYER

db = ibm_db.connect('*LOCAL','userid','password') 

# ADD FOR PYTHON STANDARD DB-API PROPERTIES (I.E., CURSOR)
conn = ibm_db_dbi.Connection(db)   
cur = conn.cursor()

# PREPARED STATEMENT (WITH PLACEHOLDERS)
sql = """INSERT INTO TEMPLIB.TEMPPF (TYPE, DRPARTY, CRPARTY, 
                                     AMOUNT, ACNUM, DESCRIPT) 
          VALUES(?, ?, ?, ?, ?, ?)
          with NC
      """

# EXECUTE ACTION QUERY BINDING PARAMS
cur.execute(sql, (self.type, self.debitparty, self.creditparty, self.amount, 
                  self.craccountnumber, self.description)) 

cur.close()
conn.close()

相关文章