def batch_insert(input_data, logger): ''' Function to insert data into database @param input_data: list containing rows of values from inputfile.txt @param logger: logger created in main script ''' # Go ahead and query Node and Sensor objects from database # We'll do this once to reduce the number of queries required try: node_objects = Node.objects.all().values_list() sensor_objects = Sensor.objects.all().values_list() except: logger.info('Error retrieving objects from database during insert') sys.exit(1) # We'll create a list of node_ids, one for each Node primary key node_id_list = [node[0] for node in node_objects] # We'll create a dictionary of sensor_ids # Key is (node_id, sensor pin) # Value is the Sensor primary key sensor_id_dict = {(sensor[2], sensor[3]) : int(sensor[0]) for sensor in sensor_objects} # Batch insert list will hold instantiated Record objects # until we're ready to insert batch_insert = [] # Iterate over each record (row of values from inputfile.txt) for record in input_data: # Get node UUID from record node_id = int(record[0]) if node_id == 0: continue # If node_id is already in database, we do nothing if node_id in node_id_list: pass # Otherwise it's a new Node and we need to add it to database else: # Create the Node and set its attributes node = Node() node.node_id = node_id node.name = 'Node %s' % node_id # Save the Node to the database try: node.save() logger.info('Created new node %s' % node_id) except: logger.info('Error creating node %s' % node_id) return -1 # Create the Sensors for the Node try: node.create_sensors() logger.info('Created sensors for node %s' % node_id) except: logger.info('Error creating sensors for node %s' % node_id) node.delete() return -1 # Retrieve new sensor objects and add sensor tuple to sensor_id_dict new_sensors = Sensor.objects.filter(node_id=node_id) for new_sensor in new_sensors: sensor_id_dict[(new_sensor.node_id, new_sensor.sensor_pin)] = new_sensor.id # Add the new node_id to our node_id list node_id_list.append(node_id) # We'll do bulk record creation in chunks of 300 if len(batch_insert) >= 300: try: Record.objects.bulk_create(batch_insert) logger.info('Batch insert successful') except: logger.info('Failed to batch insert') return -1 batch_insert = [] # Get values from record and cast to float # Slice record list so that we exclude node_id values = [float(i) for i in record[1:-1]] # Iterate over sensor pins for pin in xrange(1, len(values) + 1): # Create new Record and add value recorded by sensor new_record = Record() new_record.value = values[pin-1] new_record.node_id = node_id new_record.sensor_id = sensor_id_dict[(node_id, pin)] new_record.time_recorded = datetime.datetime.fromtimestamp(float(record[-1])) # Store the record batch_insert.append(new_record) # Dump the rest of the batch insert list #try: Record.objects.bulk_create(batch_insert)