File size: 3,642 Bytes
4633e25 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 | 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()
|