[cx-oracle-users] Varying field names and corresponding columns in the Oracle DB

[Derry H. <con...@de...>]

Hi there,

I'm trying to parse data with variable fields and am looking for a more
pythonic, efficient way to do this.
Firstly I have a dict like so:

smsreport_dict = dict: {'date': '2007-05-27 14:00:12 +0200', 'msgid':
'2D67C865FB6FA25A9261C75E8D2F0F2B ', 'msgparts': '1', 'result': 'success'}
        
This is compiled from an xml file, and the fields are not always the same in
each xml file, however there are a maximum number of fields (and so field names)
that the xml file can be. Consequently the Oracle DB has about 12 or so columns.
I see two possible ways to execute the command, as follows:   
        
        database_table_name = 'messaging'
        
        for field_name, input in smsreport_dict.items():
            sql_insert = 'insert into ' + database_table_name + ' {0} values
{1}'.format(field_name, input)
            cursor.execute(sql_insert)
            
        connection.commit()
        cursor.close()
        connection.close()
        
        # OR...
        
        sql_insert = 'insert into ' + database_table_name + ' (a, b, c, d)
values (:1, :2, :3, :4)'
        cursor.prepare(sql_insert)
        cursor.executemany(None, smsreport_dict)
        connection.commit()
        cursor.close()
        connection.close()
        
However if the field names are different depending on the xml file (therefore so
too can the number of fields/columns) then how can I accomodate for this
variation? I'm thinking it may be best to loop through "smsreport_dict" and
match each field name i.e date, msgid etc.
with the corresponding field/column name, that is already established in my
Oracle DB. Any ideas?

Regards,
Derry.