CODEWITHSUNDEEP
pythoncx-oracle
thclpr
thclpr

Reputation: 5938

Insert List into oracle db with cx_python returns illegal variable name/number

Using the content of this xml example saved on a local file called test.xml , I'm trying to parse the contents and insert into my database using the following code:

import cx_Oracle
import re
import os
import xml.etree.ElementTree as ET
from ConfigParser import SafeConfigParser

def db_callmany(cfgFile, sql,params):

    parser = SafeConfigParser()
    parser.read(cfgFile)
    dsn = parser.get('odbc', 'dsn')
    uid = parser.get('odbc', 'user')
    pwd = parser.get('odbc', 'pass')
    try:
        con = None
        con = cx_Oracle.connect(uid , pwd, dsn)
        cur = con.cursor()
        cur.executemany(sql,params)
        con.commit()
    except cx_Oracle.DatabaseError, e:
            print 'Error %s' % e
            sys.exit(1)
    finally:
        if con:
            con.close()

if __name__ == '__main__':
    try:
        cfgFile='c:\\tests\\dbInfo.cfg'
        tree = ET.parse('test.xml')
        root = tree.getroot()
        mdfList = []
        for book in root.findall('book'):
            author = book.find('genre').text
            title = book.find('price').text
            the  = str((author,title))
            mdfList.append(the)

        sql = "INSERT INTO book_table ( GENRE, PRICE )"
        db_callmany(cfgFile,sql,mdfList)

    except KeyboardInterrupt:
        sys.stdout('\nInterrupted.\n')

But executing this code I receive the following error:

Error ORA-01036: illegal variable name/number

Exit code:  1

Not sure what I can be missing here in order to get this code working.

Upvotes: 0

Views: 5570

Answers (1)

George
George

Reputation: 1054

CX_Oracle requires placeholders and then a sequence of dictionaries for executemany rather than a sequence of sequences - so something like:

mdfList = list()

for book in root.findall('book'):
    Values = dict()
    Values['GENRE'] = book.find('genre').text
    Values['PRICE'] = book.find('price').text
    mdfList.append(Values)

sql = "INSERT INTO book_table (GENRE, PRICE) VALUES (:GENRE, :PRICE)"
db_callmany(cfgFile, sql, mdfList)

Upvotes: 2

Related Questions