Vinay Kumar Revanuru

Upload connection_setup.py

4633e25 verified about 2 years ago

3.64 kB

import psycopg2
from dotenv import load_dotenv
import os


load_dotenv('.env')
 
# connection establishment
conn = psycopg2.connect(
    database="postgres",
    user='postgres',
    password=os.getenv('TEMBO_PASSWORD'),
    host=os.getenv('TEMBO_HOST'),
    port='5432'
)
 
conn.autocommit = True
 
# Creating a cursor object
cursor = conn.cursor()

#create a table in the  database named 'Patient' and the following attributes
# """First Name, Last Name, Date of Birth, Phone Number,
# Email, Medical Condition, Medication Regimen, Last Appointment DateTime,
# Next Appointment DateTime, Doctor’s Name."""  if the table does not exist

#delete the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS Patient;''')


cursor.execute('''CREATE TABLE Patient
            (ID INT PRIMARY KEY     NOT NULL,
            FIRST_NAME           TEXT    NOT NULL,
            LAST_NAME            TEXT     NOT NULL,
            LOCATION        VARCHAR(50),
            DOB            DATE,
            PHONE_NUMBER        VARCHAR(10),
            EMAIL        VARCHAR(50),
             
            EMERGENCY_CONTACT_NAME        VARCHAR(50),
            EMERGENCY_CONTACT_NUMBER       VARCHAR(10),
           

            MEDICAL_CONDITION        VARCHAR(50),
            MEDICATION_REGIMEN        VARCHAR(50),
            LAST_APPOINTMENT_DATE_TIME        TIMESTAMP,
            NEXT_APPOINTMENT_DATE_TIME        TIMESTAMP,
            DOCTORS_NAME        VARCHAR(50));
            ''')

print("Table created successfully")
    
    #enter a record into the table
    
cursor.execute(""" INSERT INTO Patient (ID, FIRST_NAME, LAST_NAME, LOCATION, DOB, PHONE_NUMBER, EMAIL, EMERGENCY_CONTACT_NAME, EMERGENCY_CONTACT_NUMBER, MEDICAL_CONDITION, MEDICATION_REGIMEN, LAST_APPOINTMENT_DATE_TIME, NEXT_APPOINTMENT_DATE_TIME, DOCTORS_NAME) 
VALUES (1, 'Michael','Scott', 'Scranton', '1964-03-15', '1234567890', 'Mscott@dundlermifflin.org', 'Pam Beesly', '0987654321', 'Diabetes', 'Insulin', '2024-03-15 08:00:00', '2024-03-15 09:00:00', 'Dr. Jan Levinsky');""")

cursor.execute('''DROP TABLE IF EXISTS responses;''')

cursor.execute('''CREATE TABLE responses(
             id SERIAL PRIMARY KEY,
            time_stamp TIMESTAMP,
            response_type VARCHAR(50),
            response TEXT
            );''')

cursor.execute('''INSERT INTO responses (time_stamp, response_type, response) VALUES ('2024-03-15 08:00:00', 'assistant','Hello , your personlized agent is ready to assist you');''')








# Drop the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

# Create the new table to store knowledge graphs for each patient
cursor.execute('''
    CREATE TABLE patient_knowledge_graph (
        patient_id INT PRIMARY KEY,
        graph_data JSONB
    );
''')

#Insert a record into the table
# Drop the table if it already exists
cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

# Create the new table with only timestamp and graphdata columns
cursor.execute('''
    CREATE TABLE patient_knowledge_graph (
        time_stamp TIMESTAMP PRIMARY KEY,
        graph_data JSONB
    );
''')

print("Knowledge Graph table created successfully")
# Closing the connection

import pandas as pd

# Reading the data from the csv file
data  = pd.read_sql_query("SELECT * FROM Patient", conn)  

df  = pd.read_sql_query("SELECT * FROM responses", conn)

data.to_json('data.json', orient='records')        
print(data)

print(df)  
# Closing the connection
conn.close()

	import psycopg2
	from dotenv import load_dotenv
	import os


	load_dotenv('.env')

	# connection establishment
	conn = psycopg2.connect(
	database="postgres",
	user='postgres',
	password=os.getenv('TEMBO_PASSWORD'),
	host=os.getenv('TEMBO_HOST'),
	port='5432'
	)

	conn.autocommit = True

	# Creating a cursor object
	cursor = conn.cursor()

	#create a table in the database named 'Patient' and the following attributes
	# """First Name, Last Name, Date of Birth, Phone Number,
	# Email, Medical Condition, Medication Regimen, Last Appointment DateTime,
	# Next Appointment DateTime, Doctor’s Name.""" if the table does not exist

	#delete the table if it already exists
	cursor.execute('''DROP TABLE IF EXISTS Patient;''')


	cursor.execute('''CREATE TABLE Patient
	(ID INT PRIMARY KEY NOT NULL,
	FIRST_NAME TEXT NOT NULL,
	LAST_NAME TEXT NOT NULL,
	LOCATION VARCHAR(50),
	DOB DATE,
	PHONE_NUMBER VARCHAR(10),
	EMAIL VARCHAR(50),

	EMERGENCY_CONTACT_NAME VARCHAR(50),
	EMERGENCY_CONTACT_NUMBER VARCHAR(10),


	MEDICAL_CONDITION VARCHAR(50),
	MEDICATION_REGIMEN VARCHAR(50),
	LAST_APPOINTMENT_DATE_TIME TIMESTAMP,
	NEXT_APPOINTMENT_DATE_TIME TIMESTAMP,
	DOCTORS_NAME VARCHAR(50));
	''')

	print("Table created successfully")

	#enter a record into the table

	cursor.execute(""" INSERT INTO Patient (ID, FIRST_NAME, LAST_NAME, LOCATION, DOB, PHONE_NUMBER, EMAIL, EMERGENCY_CONTACT_NAME, EMERGENCY_CONTACT_NUMBER, MEDICAL_CONDITION, MEDICATION_REGIMEN, LAST_APPOINTMENT_DATE_TIME, NEXT_APPOINTMENT_DATE_TIME, DOCTORS_NAME)
	VALUES (1, 'Michael','Scott', 'Scranton', '1964-03-15', '1234567890', 'Mscott@dundlermifflin.org', 'Pam Beesly', '0987654321', 'Diabetes', 'Insulin', '2024-03-15 08:00:00', '2024-03-15 09:00:00', 'Dr. Jan Levinsky');""")

	cursor.execute('''DROP TABLE IF EXISTS responses;''')

	cursor.execute('''CREATE TABLE responses(
	id SERIAL PRIMARY KEY,
	time_stamp TIMESTAMP,
	response_type VARCHAR(50),
	response TEXT
	);''')

	cursor.execute('''INSERT INTO responses (time_stamp, response_type, response) VALUES ('2024-03-15 08:00:00', 'assistant','Hello , your personlized agent is ready to assist you');''')








	# Drop the table if it already exists
	cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

	# Create the new table to store knowledge graphs for each patient
	cursor.execute('''
	CREATE TABLE patient_knowledge_graph (
	patient_id INT PRIMARY KEY,
	graph_data JSONB
	);
	''')

	#Insert a record into the table
	# Drop the table if it already exists
	cursor.execute('''DROP TABLE IF EXISTS patient_knowledge_graph;''')

	# Create the new table with only timestamp and graphdata columns
	cursor.execute('''
	CREATE TABLE patient_knowledge_graph (
	time_stamp TIMESTAMP PRIMARY KEY,
	graph_data JSONB
	);
	''')

	print("Knowledge Graph table created successfully")
	# Closing the connection

	import pandas as pd

	# Reading the data from the csv file
	data = pd.read_sql_query("SELECT * FROM Patient", conn)

	df = pd.read_sql_query("SELECT * FROM responses", conn)

	data.to_json('data.json', orient='records')
	print(data)

	print(df)
	# Closing the connection
	conn.close()