Using a Python dict for a SQL INSERT statement for CX_ORACLE

Question

convert a dictionary to SQL insert for the cx_Oracle driver in Python

custom_dictionary= {'ID':2, 'Price': '7.95', 'Type': 'Sports'}

I'm need making dynamic code sql insert for cx_Oracle driver from custom dictionary

con = cx_Oracle.connect(connectString)
cur = con.cursor()
statement = 'insert into cx_people(ID, Price, Type) values (:2, :3, :4)'
cur.execute(statement, (2, '7.95', 'Sports'))
con.commit()

Answer 1

If you have a known set of columns to be inserted, simply use the insert with named params and pass the dictionary to the execute() method.

statement = 'insert into cx_people(ID, Price, Type) values (:ID, :Price, :Type)'

cur.execute(statement,custom_dictionary)

If the columns are dynamic, construct the insert statement using the keys and params put it into a similar execute

cols  = ','.join( list(custom_dictionary.keys() ))
params= ','.join( ':' + str(k) for k in list(custom_dictionary.keys()))
statement = 'insert into cx_people(' + cols +' ) values (' + params + ')'
cur.execute(statement,custom_dictionary)