python oracle insert datatype mismatch

Question

I am trying to insert a dictionary into an Oracle table and its failing with data mismatch error. I want to know if the syntax I am using is a correct one.

In [19]:selrow
Out[19]:[{'CUSTOMER_NM': 'ABC INC',
  'CUSTOMER_NO': 'A0050129',
  'CUSTOMER_SK': 926,
  'EFFECTIVE_DT': datetime.datetime(2015, 10, 2, 0, 0)}]

Here is my insert statement:

In [30]:cur=db.cursor()
cur.execute('INSERT INTO DIM_CUST (%s) VALUES (%s)', (selrow[0].keys(),               selrow[0].values()))
db.commit()

and I am getting the following error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-30-eefac6fb0aa7> in <module>()
      2 #query = 'INSERT INTO DIM_CUST (%s) VALUES (%s)', (selrow[0].keys(), selrow[0].values())
      3 #rint query
----> 4 cur.execute('INSERT INTO DIM_CUST (%s) VALUES (%s)', (selrow[0].keys(), selrow[0].values()))
      5 db.commit()

TypeError: expecting numeric data

Is my insert syntax correct? I am using dictionary keys as column names and dictionary values as the values to be entered into the table.

pl. ask any details i may have omitted. Thank you for any help that you can provide.

Can't really test this right now, so just a comment instead of an answer: Something along the lines of cur.execute('INSERT INTO DIM_CUST (CUSTOMER_NO, CUSTOMER_NM, CUSTOMER_SK, EFFECTIVE_DT) VALUES (:CUSTOMER_NO, :CUSTOMER_NM, :CUSTOMER_SK, :EFFECTIVE_DT)', selrow[0]) should work. See the paramstyle docs for your Oracle DBAPI adapter for the supported parameter styles. — Lukas Graf
– Lukas Graf, Commented Sep 23, 2015 at 23:55
Thanks Lukas, Worked like charm. I rewrote it to make it somewhat generic. See below for my answer. Thanks again. — Arvind Kandaswamy
– Arvind Kandaswamy, Commented Sep 24, 2015 at 0:54

Arvind Kandaswamy · Accepted Answer · 2015-09-24 00:59:18Z

1

Thank you Lucas & haraprasadj. Based on your pointers, I came up with this:

cols = ', '.join(selrow[0].keys())
vals = ':'+', :'.join(selrow[0].keys())
sql='INSERT INTO DIM_CUST (%s) VALUES (%s)' % (cols, vals)
cur.execute(sql, selrow[0])
db.commit()

It works! appeciate your help.

answered Sep 24, 2015 at 0:59

Arvind Kandaswamy

2,3114 gold badges23 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

python oracle insert datatype mismatch

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related