import psycopg2

# testos
cinder_connection = psycopg2.connect("dbname=cinder user=cinder host=172.30.202.16 password='testos'")
nova_connection = psycopg2.connect("dbname=nova user=nova host=172.30.202.16 password='testos'")

cinder_cursor = cinder_connection.cursor()
nova_cursor = nova_connection.cursor()

cinder_cursor.execute("SELECT instance_uuid FROM volume_attachment WHERE attach_status='attached' AND deleted=false;")

instance_uuids = cinder_cursor.fetchall()

instances = list()
for instance in instance_uuids:
   instances.append(instance[0])

print "Attachments size is: %s" % len(instances)

nova_cursor.execute("SELECT uuid FROM instances WHERE vm_state='deleted' AND uuid in {};".format(tuple(instances)))

deleted_instances = nova_cursor.fetchall()

nova_cursor.close()
nova_connection.close()

instances = list()
for instance in deleted_instances:
   instances.append(instance[0])

with open('instances.txt', "w+") as instances_file:
    for instance in instances:
        instances_file.write("{}\n".format(instance))

print "Deleted instances size is: %s" % len(instances)

cinder_cursor.execute("SELECT volume_id FROM volume_attachment WHERE deleted=false "
                      "AND attach_status='attached' "
                      "AND instance_uuid in {};".format(tuple(instances)))

bad_volumes = cinder_cursor.fetchall()

volumes = list()
for volume in bad_volumes:
    volumes.append(volume[0])

with open('volumes.txt', "w+") as volumes_file:
    for volume in volumes:
        volumes_file.write("{}\n".format(volume))

print "Bad volumes size is: %s" % len(volumes)

cinder_cursor.execute("UPDATE volume_attachment set deleted=true,attach_status='detached' WHERE instance_uuid in {};".format(tuple(instances)))
cinder_cursor.execute("UPDATE volumes set attach_status='detached' WHERE id in {};".format(tuple(volumes)))

cinder_connection.commit()
cinder_cursor.close()
cinder_connection.close()