#!/usr/bin/python3

import getpass
import pg8000

secret = getpass.getpass()
try:
    db = pg8000.connect(user='cpainter', password=secret, host='flowers.mines.edu', database='csci403')
except pg8000.Error as e:
    print('Database error: ', e.args[2])
    exit()

db.autocommit = False  # we'll work in one transaction for faster performance
cursor = db.cursor()

# reset to all lowercase
q = "UPDATE fiveletterstrings SET x = lower(x)"
try:
    cursor.execute(q)
except pg8000.Error as e:
    print('Database error: ', e.args[2])
    exit()

print("fiveletterstrings reset to all lowercase...")

# retrieve rows
q = "SELECT x FROM fiveletterstrings FOR UPDATE"
try:
    cursor.execute(q)
except pg8000.Error as e:
    print('Database error: ', e.args[2])
    exit()

results = cursor.fetchall()
print("retrieved all rows from fiveletterstrings...")

# convert each row by uppercasing vowels and updating
# the database
trans = str.maketrans({'a':'A', 'e':'E', 'i':'I', 'o':'O', 'u':'U'})

q = "UPDATE fiveletterstrings SET x = %s WHERE x = %s"
counter = 0
try:
    for row in results:
        if counter % 1000 == 0:
            print("Processed " + str(counter) + " rows...")
        word = row[0].translate(trans)
        cursor.execute(q, (word, row[0]))
        counter = counter + 1

except pg8000.Error as e:
    print('Database error: ', e.args[2])
    exit()

db.commit()

print(counter, "rows updated.")

cursor.close()
db.close()