I'm writing a python script that collects data from a number of sensors defined in an accompanying config.ini and attempts to insert their values into a postgresql database. My question is how to get this variable number of readings into one insert into/ one row in the table.
The script works if I specify a number of variables to insert in the psycopg2 execute() statement. Now however the python script produces a dict of un-prespecified length. Have tried passing the dictionary as variable and json.dumps(dictionary) and as a list of values (am merely interested in the values)
sql = "insert into " + self.config['postgresql']['table'] + " ("
for sensor, value in ow_readings.items():
sql += sensor + ", "
sql += "weather, timestamp"
sql += ") values ("
for number in range(int(len(ow_readings))):
sql += "%s, "
sql += "%s, %s)"
now = pytz.utc.localize(datetime.datetime.utcnow()).isoformat()
pointer.execute(sql,(ow_readings,weather,now,))
psycopg2.ProgrammingError: can't adapt type 'dict' from the above
or IndexError: tuple index out of range if json.dumps(ow_readings) or if list(ow_readings.values())
I would like to insert a reading from a variable number of sensors on one row in the table in the postgresql db. (The db table will have the appropriate number of columns as that's in the comments section of the config.ini where the sensors are defined). In this way a user can indicate his sensors, however many, in the config.ini and not touch the python code. Is this doable?
