#!/bin/python3

# Demonstrate using psycopg2 in a "standalone" program to test database
# operations outside of a web server environment.

from psycopg2 import connect
from psycopg2.extras import DictCursor
import sys

sys.path.insert(0, '/home/kelliher/.secrets')
import psqlauth
db = connect(dbname=psqlauth.db, user=psqlauth.user, password=psqlauth.pw,
             cursor_factory=DictCursor)
cur = db.cursor()

# Select query on a table, yielding a list of records.
# Each record is structured as a dictionary.
# The records will be ordered in ascending order of the id field.

print('\n\nExecuting first query')
cur.execute('select * from todo order by id asc;')
todos = cur.fetchall()

print('\n\nEntire list of todo records\n')
print (todos)

print('\n\nFirst todo record, at index 0\n')
print(todos[0])

print('\n\nTitle field of first todo record\n')
print(todos[0]['title'])

print('\n\nEach todo record\n')
for rec in todos:
    print(rec)

print('\n\nTitle field and date of each todo record\n')
for rec in todos:
    print(rec['title'], rec['created'].date())

# Query on a second table.
print('\n\nExecuting second query')
cur.execute('select * from todo2 order by todoid asc;')
todos = cur.fetchall()

print('\n\nTitle field of each todo2 record\n')
for rec in todos:
    print(rec['title'])

print('\n\nField names of todo2 table\n')
string = '| '
for field in todos[0].keys():
    string += (field + ' | ')
print(string)

print('\n\nTodo2 table\n')
for rec in todos:
    string = '| '
    for field in rec.values():
        string += (str(field) + ' | ')
    print(string)


print('\n\nIllustrate using fetchone().\n')
cur.execute('select userid from todo2;')
rec = cur.fetchone()
print(rec, '|', rec['userid'])



print('\n\nIllustrate using a cursor object as an iterable.\n')
cur.execute('select title, created from todo2 order by todoid asc;')
for rec in cur:
    print('\n', rec, '\n', rec['title'], '|', rec['created'])


cur.close()
db.commit()
db.close()