Untitled
unknown
python
a month ago
4.7 kB
1
Indexable
Never
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))