Using SQL with IBM_DB connector in Python

Question

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

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, etc. are Python instance variables and have values.
TYPE, DRPARTY, CRPARTY, etc. are fields of TEMPPF.

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

sql = "select * from TEMPLIB.TEMPPF"

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.

Apologies. This was my first post and I think the font I used to copy paste from my word doc created this problem. Will take care. Thanks for showing interest. :) — deep
– deep, Commented May 23, 2018 at 4:14
Don't use ibm_db at all, just ibm_db_dbi. Then more-or-less just follow PEP 249. — John Y
– John Y, Commented May 23, 2018 at 13:19

Parfait · Accepted Answer · 2018-05-23 17:41:21Z

3

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

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()

edited May 23, 2018 at 17:41

answered May 23, 2018 at 1:39

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

deep Over a year ago

Hey @Parfait. That worked. Thank you so very much. tc :)

John Y Over a year ago

You have a typo (exeucte). Also, people normally don't import both ibm_db and ibm_db_dbi. Just use the latter, which has its own connect() function. It's typical not to give user and password, just '*LOCAL' by itself.

Parfait Over a year ago

@JohnY ... Indeed thanks for that and I did not know ibm_db_dbi has its own .connect(). Let me test that and revise accordingly. IBM docs are limiting in this respect.

John Y Over a year ago

The official documentation for the whole ibm_db project is pretty bad, unfortunately. On top of everything else, OP is specifically using the ibm_db package bundled with iSeriesPython. So you can't fully rely on what you know about Python and ibm_db on mainstream platforms. Some things you just really have to test with iSeriesPython.

John Y Over a year ago

I should clarify that my previous comment incorrectly mentions iSeriesPython. OP was using the term "iSeriesPython" without realizing that it means something other than IBM's Python for PASE. The rest of my comment still stands.

Collectives™ on Stack Overflow

Using SQL with IBM_DB connector in Python

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related