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

Question

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.

George · Accepted Answer · 2012-12-10 14:31:15Z

2

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)

answered Dec 10, 2012 at 14:31

George

1,0542 gold badges11 silver badges23 bronze badges

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

1 Comment

kavehmb Over a year ago

cx_Oracle : expecting a list of dictionaries or sequences

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related