Scripts for setting up a PostgreSQL db and a test script

###pg_setup.sh starts###
#!/bin/bash

mkdir /usr/local/pgsql
chown postgres /usr/local/pgsql
su postgres
pg_ctl -D /usr/local/pgsql/data initdb

/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
ECHO "Click enter to stop"
read
/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data
###pg_setup.sh ends###

#remember to change the postgress pw and create the "cfh" db
--
sudo -u postgres psql cfh
#in psql:
ALTER USER "postgres" PASSWORD 'cfh';
--

###tmp_db_connect_test.py starts###
#!/usr/bin/env python
# -*- coding: utf-8 -*-

import psycopg2

import sys

db_con = None

try:

#Create a database session

    db_con = psycopg2.connect(database='cfh', user='postgres', password='cfh')

#Create a client cursor to execute commands

    VAL1 = '1' * 20
    VAL2 = '2' * 20
    #print(VAL1, type(VAL1))
    cursor = db_con.cursor()

#Uncomment and comment lines to test different operations
#Note that requests table is gimped down to just two unique columns
    cursor.execute("DROP TABLE Requests;")
    cursor.execute("CREATE TABLE Requests (id SERIAL PRIMARY KEY UNIQUE, service_request_id VARCHAR UNIQUE, status_notes VARCHAR NULL, status VARCHAR, service_code VARCHAR, service_name VARCHAR, description VARCHAR, agency_responsible VARCHAR, service_notice VARCHAR, requested_datetime DATE, updated_datetime DATE, expected_datetime DATE, address_string VARCHAR, media_url VARCHAR, distance VARCHAR, location BYTEA, service_object_id VARCHAR, title VARCHAR, service_object_type VARCHAR, detailed_status VARCHAR, api_key VARCHAR, email VARCHAR, first_name VARCHAR, last_name VARCHAR, phone VARCHAR);")
    #Add stuff
    cursor.execute("INSERT INTO Requests ( service_request_id) VALUES (%s);" % VAL1)
    cursor.execute("INSERT INTO Requests ( service_request_id) VALUES (%s);" % VAL2)

#Comment this line to test for errors, uncomment to change the database content
    db_con.commit()


    cursor.execute("SELECT * FROM Requests")
    #fetches only one
    print(cursor.fetchone())
except psycopg2.DatabaseError as e:

    print ('Error: %s' % e)

    sys.exit(1)

finally:

    if db_con:

        db_con.close()
###tmp_db_connect_test.py starts###