0

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.

Lukas Graf
  • 30,317
  • 8
  • 77
  • 92
Arvind Kandaswamy
  • 1,821
  • 3
  • 21
  • 30
  • 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`](https://www.python.org/dev/peps/pep-0249/#paramstyle) docs for your Oracle DBAPI adapter for the supported parameter styles. – Lukas Graf Sep 23 '15 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 Sep 24 '15 at 00:54

1 Answers1

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.

Arvind Kandaswamy
  • 1,821
  • 3
  • 21
  • 30