def add_appt(adv, stud, adv_email, stud_email, date, start, end, table = DEFAULT_TABLE): if is_repeat(date, start, end): print "Meeting conflict - new appointment not added." return 0 else: query = ''' INSERT INTO %s ( advisor_name, student_name, advisor_email, student_email, appointment_date, appointment_start_time, appointment_end_time) ''' % table query += ''' VALUES ( "%s", "%s", "%s", "%s", "%s", "%s", "%s"); ''' % (adv, stud, adv_email, stud_email, date, start, end); print query print execute(query) print execute('SELECT * FROM %s;' % (table)) unique_id = get_unique_id(date, start) return unique_id
def drop_appt_by_id(unique_id, table = DEFAULT_TABLE): query = ''' DELETE FROM %s WHERE ('id'=%s); ''' % (table, str(unique_id)) #print query execute(query) return
def drop_appt(date, start, table = DEFAULT_TABLE): unique_id = get_unique_id(date, start) query = ''' DELETE FROM %s WHERE ('appointment_date'="%s" AND 'appointment_start_time'="%s"); ''' % (table, date, start) query execute(query) #print execute('SELECT * FROM %s;' % (table)) return unique_id
def new_table(table): print 'Tables BEFORE:\n' print execute('show tables;') print '------------------------------------' query = ''' SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `%s`; -- Create a table to hold the appts CREATE TABLE `%s` ( `id` int NOT NULL AUTO_INCREMENT, `advisor_name` varchar(100), `student_name` varchar(100), `advisor_email` varchar(100), `student_email` varchar(100), `appointment_date` date, `appointment_start_time` time, `appointment_end_time` time, `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB; ''' % (table, table) execute(query, False) print 'Tables AFTER:\n' print execute('show tables;') print execute('show create table %s;' % table, False)
def get_student(date, start, table = DEFAULT_TABLE): query = ''' SELECT 'student_name' FROM %s WHERE ('appointment_date'="%s" AND 'appointment_start_time'="%s"); ''' % (table, date, start) student = execute(query, False).strip() return student
def get_unique_id(date, start, table = DEFAULT_TABLE): query = ''' SELECT 'student_email' FROM %s WHERE ('appointment_date'="%s" AND 'appointment_start_time'="%s"); ''' % (table, date, start) email = execute(query, False).strip() unique_id = email + '::' + date + '::' + start return unique_id
def is_repeat(date, start, end, table = DEFAULT_TABLE): conflict_found = False query = ''' SELECT 'id' FROM %s WHERE 'appointment_date'="%s" AND ( ('appointment_start_time'="%s" OR 'appointment_end_time'="%s") OR ('appointment_start_time'>"%s" AND 'appointment_start_time'<"%s") OR ('appointment_end_time'>"%s" AND 'appointment_end_time'<"%s")); ''' % (table, date, start, end, start, end, start, end) # print query # print execute(query, False) if len( execute(query, False) ) > 0: conflict_found = True return conflict_found
def drop_table(table): print 'Tables BEFORE:\n' print execute('show tables;') print '------------------------------------' query = ''' SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `%s`; ''' % (table) execute(query, False) print 'Tables AFTER:\n' print execute('show tables;')
def view_appt(table = DEFAULT_TABLE): query = 'SELECT * FROM %s' % (table) appts = execute(query) return appts
return result fullcmd = '' cmd = gettext('mysql419 >>') while (cmd != 'exit'): # handle cases of comment lines if ('#' in cmd): # ignore inline text after comment tag comment_tag = cmd.find('#') cmd = cmd[0:comment_tag] if ('-- ' in cmd) or ('--\n' in cmd) or \ ('--\t' in cmd): # ignore inline text after comment tag comment_tag = cmd.find('--') cmd = cmd[0:comment_tag] while ('/*' in cmd): # ignore text between comment tags while ('*/' not in cmd): cmd += gettext('') comment_tag1 = cmd.find('/*') comment_tag2 = cmd.find('*/') + 2 cmd = cmd[0:comment_tag1] + cmd[comment_tag2:] # comment lines have been excluded fullcmd += cmd if ';' in fullcmd: print "(query >> '" + fullcmd + "')" print sql_cmd.execute(fullcmd) fullcmd = '' cmd = gettext('mysql419 >>') else: cmd = gettext('>>')
fullcmd = '' cmd = gettext('mysql419 >>') while (cmd != 'exit'): # handle cases of comment lines if ('#' in cmd): # ignore inline text after comment tag comment_tag = cmd.find('#') cmd = cmd[0:comment_tag] if ('-- ' in cmd) or ('--\n' in cmd) or \ ('--\t' in cmd): # ignore inline text after comment tag comment_tag = cmd.find('--') cmd = cmd[0:comment_tag] while ('/*' in cmd): # ignore text between comment tags while ('*/' not in cmd): cmd += gettext('') comment_tag1 = cmd.find('/*') comment_tag2 = cmd.find('*/') + 2 cmd = cmd[0:comment_tag1] + cmd[comment_tag2:] # comment lines have been excluded table_border = False fullcmd += cmd if ';' in fullcmd: print "(query >> '" + fullcmd + "')" print sql_cmd.execute(fullcmd, table_border) fullcmd = '' cmd = gettext('mysql419 >>') else: cmd = gettext('>>')