Re: Varying field names and corresponding columns in the Oracle DB

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.

------------------------------------------------------------------------------
Announcing the Oxford Dictionaries API! The API offers world-renowned
dictionary content that is easy and intuitive to access. Sign up for an
account today to start using our lexical data to power your apps and
projects. Get started today and enter our developer competition.
http://sdm.link/oxford