Untitled

import psycopg2
import time

# Database connection parameters
DATABASE = "tariff_digitization"
USER = 'postgres'
PASSWORD = "7Yn4mFeLNDXz&tH"
HOST = "hospital-tariff-application.ctg23hvzex2r.ap-south-1.rds.amazonaws.com"
PORT = '5432'

def check_connection():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("select version()")
    data=cursor.fetchone()
    print("Connection established to: ", data)
    conn.close()

def check_data():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""explain(format yaml) select * from schedule_of_charges where lower(service_description) like lower('%knee%') order by service_description ASC, serial_id ASC limit 50""")
    table_names = cursor.fetchall()
    table_n=[]
    for table_name in table_names:
        print(table_name)

def install_trigram():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""CREATE EXTENSION IF NOT EXISTS pg_trgm;""")
    print(cursor.statusmessage)
    conn.commit()
    print("trigram installed")
    conn.close()

def update_trigram():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""update pg_opclass set opcdefault=true where opcname='gin_trgm_ops';""")
    print("opcdefault updated")
    conn.close()

def grant_permission():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""GRANT SELECT, INSERT, UPDATE, DELETE ON pg_catalog.pg_opclass TO postgres""")
    #results=cursor.fetchall()
    #for result in results:
    #    print(result)
    print(cursor.rowcount)
    print("permission granted successfully")
    conn.close()

def query():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""select * from pg_opclass where opcname='gin_trgm_ops';""")
    table=cursor.fetchall()
    print(table)
    print("above is the query ran successfully")
    conn.close()

def create_index1():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""CREATE INDEX service_desc_trigram_index on schedule_of_charges USING GIN(lower(service_description) gin_trgm_ops);""")
    print(cursor.statusmessage)
    conn.commit()
    print("above1 is the query ran successfully")
    conn.close()


def create_index2():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""CREATE INDEX service_desc_serialID_index on schedule_of_charges(service_description, serial_id);""")
    print(cursor.statusmessage)
    print("above2 is the query ran successfully")
    conn.commit()
    conn.close()

def create_index3():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("CREATE INDEX service_desc_trigram_extra_index on schedule_of_charges USING GIN(lower(service_description) gin_trgm_ops);")
    print(cursor.statusmessage)
    conn.commit()
    print("above2 is the query ran successfully")
    conn.close()


def view_index():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("""select indexname, indexdef from pg_indexes where tablename='schedule_of_charges';""")
    res=cursor.fetchall()
    for i in res:
        print(i)
    print("above is the query ran successfully")
    conn.close()


def drop_index():
    conn=psycopg2.connect(database=DATABASE, user=USER, password=PASSWORD, host=HOST, port=PORT)
    cursor=conn.cursor()
    cursor.execute("DROP INDEX IF EXISTS service_desc_trigram_extra_index;")
    cursor.execute("DROP INDEX IF EXISTS all_col_index;")
    print(cursor.statusmessage)
    conn.commit()
    print("above is the query ran successfully")
    conn.close()

check_connection()
start=time.time()
check_data()
#install_trigram()
#update_trigram()
#grant_permission()
#query()
#drop_index()
#create_index1()
#create_index2()
#create_index3()
#view_index()
end=time.time()
final_time = end-start
print("result in: ", format(final_time))