在Python中将SQL与IBM_DB连接器一起使用 [英] Using SQL with IBM_DB connector in Python

问题描述

是否有人将 ibm_db 包与IBM的Python for PASE一起使用来更新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参数化（任何数据库中的行业标准，而不仅仅是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()

这篇关于在Python中将SQL与IBM_DB连接器一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！