Re: [cx-oracle-users] Varying field names and corresponding columns in the Oracle DB
Brought to you by:
atuining
|
From: Shai B. <sh...@pl...> - 2017-03-09 16:01:49
|
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.
|