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

[Shai B. <sh...@pl...>]

Hi Derry,

On Thursday 09 March 2017 17:17:30 Derry Holding wrote:
> 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)
> 

This looks wrong twice: Once, because you're inserting each field into its own 
record here, and the sample contents you posted indicate that each such dict 
should be stored as a single record. And a second time, because you're 
formatting the values into the sql statement, which would expose you to sql 
injections (malicious or just erroneous ones).

> 
>         # 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)

You don't need executemany to insert a single record.
You need to construct your insert statement and values, basically like so:

	columns, values = zip(smsreport_dict.items())
	value_placeholders = [":%s" % (i+1) for i in range(len(values))]
	sql_insert = 'insert into {0} ({1}) values ({2})'.format(
		database_table_name,
		", ".join(columns),
		", ".join(value_placehoders)
	)
	cursor.execute(sql_insert, values)

Error handling, transaction handling, dealing with columns whose names need to 
be quoted, etc. are left as an exercise to the reader.

HTH,
	Shai.