ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using SQL with IBM_DB connector in Python

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using SQL with IBM_DB connector in Python

    Hello

    Did anyone use 'ibm_db' package of i-series/python to update DB2 files in AS400 ??

    I want to use Python scripts(from QSH) to update 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 class variables and have got 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 couple of formats available in the internet, either they are not compatible with Python, or they are not good examples.

    Pls help.

    Regards, Dipayan


  • #2
    I'm not a python programmer, but I have a guess.
    Since you are building the SQL statement as a string, rather than trying to use substitution codes, why not insert them directly into the string?
    (I don't know how to do string concatenation in Python, I am guessing it's +)

    sql = "INSERT INTO TEMPLIB.TEMPPF (TYPE, DRPARTY, CRPARTY, AMOUNT,ACNUM, DESCRIPT) VALUES("+self.type+", "+self.debitparty+", "+self.creditparty+", "+self.amount+", "+self.craccountnumber+", "+self.description) with NC"

    Comment


    • #3
      The first thing that jumped out at me was "QSH". For this kind of thing, I'd say the shell priorities should be something like this:
      1. SSH client
      2. SSH client
      3. SSH client
      4. PASE shell (QP2TERM)
      5. QSH

      Comment

      Working...
      X