def main():
""" Autopopulate the databse with 2 GPs and 3 Patients """
gp1 = ('Jane', 'Allen', '*****@*****.**', 'Claire Lane', 'London',
'N1', '07912378346', dt.date.today())
gp2_date = dt.date.today() + relativedelta(months=-6)
gp2 = ('Theresa', 'Chng', '*****@*****.**', 'Orion street',
'London', 'N1', '07658911937', gp2_date)
patient1 = ('Nicola', 'Hughes', '*****@*****.**',
'Berkeley street', 'London', 'N1', '07878842306',
dt.date.today(), 'Charlie', 'Guo', '*****@*****.**',
'Berkeley street', 'London', 'N1', '07869229113', 'husband')
patient2_date = dt.date.today() + relativedelta(months=-4)
patient2 = ('Norman', 'Smith', '*****@*****.**', 'Packington street',
'London', 'N1', '07988504379', patient2_date, 'Damien',
'Smith', '*****@*****.**', 'Canary street', 'London',
'NW1', '07604482117', 'son')
patient3 = ('Nicola', 'Jones', '*****@*****.**', 'Wells street',
'London', 'N1', '07878842458', dt.date.today(), 'Mary',
'Jones', '*****@*****.**', 'Barker street', 'London',
'E1', '07869227801', 'sister')
database = connect.db_path(2)
conn = connect.create_connection(database)
if conn is not None:
#add gps to database
dbu.insert_gp(conn, gp1)
dbu.insert_gp(conn, gp2)
#add patient info and record to database
insert_patient1 = dbu.insert_patient(conn, patient1)
print(insert_patient1)
patient1_record = ('NHS001', insert_patient1, dt.date(1984, 12, 14),
'penecillin', 'none', 'none', 'no', 5,
'gym classes three time a week')
dbu.insert_patient_record(conn, patient1_record)
insert_patient2 = dbu.insert_patient(conn, patient2)
patient2_record = ('NHS002', insert_patient2, dt.date(1950, 8, 23),
'none', 'diabetes', 'none', 'yes', 0,
'moderate activity level, mainly walking')
dbu.insert_patient_record(conn, patient2_record)
insert_patient3 = dbu.insert_patient(conn, patient3)
patient3_record = ('NHS003', insert_patient3, dt.date(1984, 12, 14),
'aspirin', 'ventricular septum defect', 'none',
'no', 2, 'yoga')
dbu.insert_patient_record(conn, patient3_record)
conn.commit()
else:
print("Error! cannot create the database connection.")
conn.close()
def main():
database = connect.db_path(2)
create_admin_table = """ CREATE TABLE IF NOT EXISTS Admin (
administrator text NOT NULL,
passwd text NOT NULL
); """
admin = ('admin', 'admin')
create_gp_table = """ CREATE TABLE IF NOT EXISTS GPs (
gpid integer PRIMARY KEY AUTOINCREMENT,
fname text NOT NULL,
lname text NOT NULL,
email text NOT NULL,
passwd text,
street text NOT NULL,
city text NOT NULL,
postcode text NOT NULL,
tel text NOT NULL,
begin_date text NOT NULL,
active text DEFAULT "yes" NOT NULL
); """
create_patient_table = """ CREATE TABLE IF NOT EXISTS Patients (
patientid integer PRIMARY KEY AUTOINCREMENT,
fname text NOT NULL,
lname text NOT NULL,
email text NOT NULL,
passwd text,
street text NOT NULL,
city text NOT NULL,
postcode text NOT NULL,
tel text NOT NULL,
begin_date text NOT NULL,
contact_fname text NOT NULL,
contact_lname text NOT NULL,
contact_email text NOT NULL,
contact_street text NOT NULL,
contact_city text NOT NULL,
contact_postcode text NOT NULL,
contact_tel text NOT NULL,
contact_relationship text NOT NULL,
active text DEFAULT "yes" NOT NULL
); """
create_patient_record_table = """ CREATE TABLE IF NOT EXISTS Patient_Record (
NHSno text NOT NULL PRIMARY KEY,
patientid integer NOT NULL,
DOB text NOT NULL,
drug_allergies text NOT NULL,
medical_conditions text NOT NULL,
disabilities text NOT NULL,
smoker text NOT NULL,
alcohol_units_per_week integer NOT NULL,
exercise text NOT NULL,
FOREIGN KEY (patientid) REFERENCES Patients (patientid)
); """
create_vaccine_record_table = """ CREATE TABLE IF NOT EXISTS Vaccine_Record (
NHSno text NOT NULL,
patientid integer NOT NULL,
gpid integer NOT NULL,
date_v text NOT NULL,
vaccine text NOT NULL,
FOREIGN KEY (patientid) REFERENCES Patients (patientid),
FOREIGN KEY (gpid) REFERENCES GPs (gpid),
FOREIGN KEY (NHSno) REFERENCES Patient_Record (NHSno),
PRIMARY KEY (NHSno, date_v, vaccine)
); """
create_medical_history_table = """ CREATE TABLE IF NOT EXISTS Medical_History (
NHSno text NOT NULL,
patientid integer NOT NULL,
gpid integer NOT NULL,
date_mh text NOT NULL,
record text NOT NULL,
FOREIGN KEY (patientid) REFERENCES Patients (patientid),
FOREIGN KEY (gpid) REFERENCES GPs (gpid),
FOREIGN KEY (NHSno) REFERENCES Patient_Record (NHSno),
PRIMARY KEY (NHSno, gpid, date_mh)
); """
create_prescriptions_table = """ CREATE TABLE IF NOT EXISTS Prescriptions (
prescriptionid integer PRIMARY KEY AUTOINCREMENT,
NHSno text NOT NULL,
patientid integer NOT NULL,
gpid integer NOT NULL,
medication text NOT NULL,
dosage text NOT NULL,
date_p text NOT NULL,
FOREIGN KEY (patientid) REFERENCES Patients (patientid),
FOREIGN KEY (gpid) REFERENCES GPs (gpid),
FOREIGN KEY (NHSno) REFERENCES Patient_Record (NHSno)
); """
create_appointments_table = """ CREATE TABLE IF NOT EXISTS Appointments (
appointmentid integer PRIMARY KEY AUTOINCREMENT,
gpid integer NOT NULL,
date_time text NOT NULL,
patientid integer,
available text DEFAULT "yes" NOT NULL,
FOREIGN KEY (patientid) REFERENCES Patients (patientid),
FOREIGN KEY (gpid) REFERENCES GPs (gpid)
); """
# create a database connection
conn = connect.create_connection(database)
print(conn)
# create tables
if conn is not None:
# create admin table
create_table(conn, create_admin_table)
#insert admin details
create_admin(conn, admin)
# create gp table
create_table(conn, create_gp_table)
# create patient table
create_table(conn, create_patient_table)
# create patient record table
create_table(conn, create_patient_record_table)
# create vaccine record table
create_table(conn, create_vaccine_record_table)
# create medical history table
create_table(conn, create_medical_history_table)
# create precriptions table
create_table(conn, create_prescriptions_table)
# create apointments table
create_table(conn, create_appointments_table)
#commit changes
conn.commit()
else:
print("Error! cannot create the database connection.")
conn.close()
def connect_to_db(self):
global conn, cursor
db_file = connect.db_path(3)
conn = connect.create_connection(db_file)
cursor = conn.cursor()