def exportHouseholds(self, project, filename): database = Database() database.open() query = '''SELECT hhid, householdname, dateofcollection FROM households WHERE pid=%s''' % project.pid households = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for household in households: householdline = '''INSERT INTO households(pid,hhid,householdname,dateofcollection) VALUES({pid},%s, '%s', '%s')<endl>\n''' % (household[0], household[1], household[2]) ihmFile.write(householdline) ihmFile.close() self.exportHouseholdCharacteristics(project, filename) self.exportHouseholdAssets(project, filename) self.exportHouseholdExpenditure(project, filename) self.exportHouseholdCropIncome(project, filename) self.exportHouseholdLivestockIncome(project, filename) self.exportHouseholdWildfoodsIncome(project, filename) self.exportHouseholdEmploymentIncome(project, filename) self.exportHouseholdTransfersIncome(project, filename) self.exportHouseholdMembers(project, filename)
class GlobalHouseholdCharacteristicsManager: def __init__(self): self.database = Database() def getGlobalHouseholdCharacteristic(self, charid=0, charname=""): char = GlobalHouseholdCharacteristic(charid, charname) return char def addGlobalHouseholdCharacteristic(self, charname, datatype): char = GlobalHouseholdCharacteristic(0, charname, datatype) return char def editGlobalHouseholdCharacteristic(self, charid, charname, datatype): char = GlobalHouseholdCharacteristic(charid) char.setData(charname, datatype) def delGlobalHouseholdCharacteristic(self, charid="", charname=""): query = "DELETE FROM globalhouseholdcharacteristics WHERE id=%i OR characteristic='%s' " % (charid, charname) self.database.open() self.database.execUpdateQuery(query) self.database.close() def getGlobalHouseholdCharacteristics(self): query = "SELECT id FROM globalhouseholdcharacteristics" self.database.open() rows = self.database.execSelectQuery(query) self.database.close() chars = [] for row in rows: charid = row[0] char = GlobalHouseholdCharacteristic(charid) chars.append(char) return chars
def getProjectsMatching(self, pid="", ptitle=""): SQLcondition = "" if ( pid != "" ): SQLcondition = " WHERE pid=%s" % pid if ( ptitle != "" ): if ( SQLcondition == "" ): SQLcondition = " WHERE projectname LIKE '%" + "%s" % ( ptitle ) + "%'" else: SQLcondition = SQLcondition + " OR projectname LIKE '%" + "%s" % ( ptitle ) + "%'" query = ''' SELECT pid FROM projects%s''' % ( SQLcondition ) database = Database() database.open() rows = database.execSelectQuery( query ) database.close() projects = [] for row in rows: pid = row[0] project = Project(pid) projects.append( project ) return projects
def logIhmTransfer(self, pid, pid_access, projectname, startdate, currency ): query = '''INSERT INTO transferlog(pid,pid_access,projectname,datecollected,currency) VALUES(%s,%s,'%s','%s','%s')''' % (pid, pid_access, projectname, startdate, currency) database = Database() database.open() database.execUpdateQuery( query ) database.close()
def submit_answer(cls, answer_text, student_id, assigment_id): db = Database() task_type = db.get("SELECT Task_type FROM Assignments WHERE id=(?)", (assigment_id, ))[0][0] if task_type == "Personal": cls.submit_personal_answer(answer_text, student_id, assigment_id) elif task_type == "Team": cls.submit_team_answer(answer_text, student_id, assigment_id) db.close()
def test_execDefinitionQuery(self): self.helper.setup_clean_db() database = Database() database.open() database.execDefinitionQuery('create table simples (test int)') database.close() # and just to prove it's there to put something into. database.open() database.execUpdateQuery('insert into simples values (3)') database.close()
def get_student_by_id(cls, idx): db = Database() query = """SELECT * FROM Student WHERE id =(?)""" person = db.get(query, (idx, ))[0] print(person) person_object = Student(person[0], person[1], person[2], person[3], person[4], person[5], person[6], person[7], person[9], person[10]) db.close() return person_object
def get_list_of_mentors(cls): list_of_mentors = [] db = Database() query = """SELECT * FROM Mentor;""" for person in db.get(query): person_object = Mentor(person[0], person[1], person[2], person[3], person[4], person[5], person[6], person[7]) list_of_mentors.append(person_object) db.close() return list_of_mentors
def get_list_of_employees(cls): list_of_employees = [] db = Database() query = """SELECT * FROM Employee;""" for person in db.get(query): person_object = Employee(person[0], person[1], person[2], person[3], person[4], person[5], person[6], person[7]) list_of_employees.append(person_object) db.close() return list_of_employees
def get_list_of_students(cls): list_of_students = [] db = Database() query = """SELECT * FROM Student;""" for person in db.get(query): person_object = Student(person[0], person[1], person[2], person[3], person[4], person[5], person[6], person[7], person[9], person[10]) list_of_students.append(person_object) db.close() return list_of_students
def save(self): db = Database() values = (self.name, self.surname, self.email, self.date_of_birth, self.city, self.phone, self.login, self.user_id) if not self.user_id: values = values[:-1] query = """INSERT INTO Employee(Name, Surname, Email, Date_of_birth, City, Phone, Login) VALUES (?,?,?,?,?,?,?);""" else: query = """UPDATE Employee SET Name=(?), Surname=(?), Email=(?), Date_of_birth=(?), City=(?), Phone=(?), Login=(?) WHERE id =(?);""" db.set(query, values) db.close()
def getProjects(self): query = "SELECT pid FROM projects" database = Database() database.open() rows = database.execSelectQuery( query ) database.close() projects = [] for row in rows: pid = row[0] project = Project(pid) projects.append( project ) return projects
def test_execUpdateQuery(self): self.helper.setup_clean_db() database = Database() database.open() database.execUpdateQuery(""" insert into projects (projectname, startdate, enddate, description, currency) values ('test', '2012-06-04', '2013-07-03', 'a simple test', 'GBP')""") query = 'select * from projects' self.assertEqual([(2, u'test', datetime.date(2012, 6, 4), datetime.date(2013, 7, 3), u'a simple test', u'GBP')], database.execSelectQuery(query)) database.close()
def get_assigment_by_id(task_id): db = Database() assigment_data = db.get("SELECT * FROM Assignments WHERE id=(?)", (task_id, ))[0] answers = db.get( "SELECT * FROM Answers WHERE Assignment_ID=(?)", (assigment_data[0], )) # get answers for assigment with a[0]-id answers = [ Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6]) for d in answers ] assigment_object = Assigment(assigment_data[0], assigment_data[1], assigment_data[2], answers) db.close() return assigment_object
def submit_personal_answer(cls, answer_text, student_id, assigment_id): db = Database() is_answer_exist = db.get( "SELECT EXISTS (SELECT * FROM Answers WHERE Student_ID=(?) AND Assignment_ID=(?))", (student_id, assigment_id)) if is_answer_exist[0][0]: db.set( "UPDATE Answers SET Answer_text=(?) WHERE Student_ID=(?) AND Assignment_ID=(?)", (answer_text, student_id, assigment_id)) else: db.set( "INSERT INTO Answers(Answer_text, Student_ID, Assignment_ID) VALUES (?,?,?)", (answer_text, student_id, assigment_id)) db.close()
def getGlobalPersonCharacteristics(self): query = "SELECT id FROM globalpersonalcharacteristics" database = Database() atabase.open() rows = database.execSelectQuery( query ) database.close() chars = [] for row in rows: charid = row[0] char = GlobalPersonCharacteristic(charid) chars.append( char ) return chars
def submit_team_answer(cls, answer_text, student_id, assigment_id): db = Database() team_id = db.get("SELECT Team_ID FROM Student WHERE id=(?)", (student_id, ))[0][0] is_answer_exist = db.get( "SELECT EXISTS (SELECT * FROM Answers WHERE Team_ID=(?) AND Assignment_ID=(?))", (team_id, assigment_id)) if is_answer_exist[0][0]: db.set( "UPDATE Answers SET Answer_text=(?) WHERE Team_ID=(?) AND Assignment_ID=(?)", (answer_text, team_id, assigment_id)) else: db.set( "INSERT INTO Answers(Answer_text, Team_ID, Assignment_ID) VALUES (?,?,?)", (answer_text, team_id, assigment_id)) db.close()
def exportProjectIncomeSources(self, project, filename): database = Database() database.open() query = '''SELECT incomesource, incometype FROM projectincomesources WHERE pid=%s''' % project.pid incomes = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for inc in incomes: incomeline = '''INSERT INTO projectincomesources(pid,incomesource,incometype) VALUES({pid},'%s','%s')<endl>\n''' % (inc[0], inc[1]) ihmFile.write(incomeline) ihmFile.close()
def get_list_of_assigments(): db = Database() assigments_data_list = db.get("SELECT * FROM Assignments") assigments_objects_list = [] for a in assigments_data_list: answers = db.get( "SELECT * FROM Answers WHERE Assignment_ID=(?)", (a[0], )) # get answers for assigment with a[0]-id answers = [ Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6]) for d in answers ] assigments_objects_list.append(Assigment(a[0], a[1], a[2], answers)) db.close() return assigments_objects_list
def delCorrespondingIhmProject(self, projectname, startdate, currency): ''' Delete if the project was transfered before ''' query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid AND transferlog.projectname='%s' AND transferlog.datecollected='%s' AND transferlog.currency='%s' ''' % (projectname, startdate, currency) db = Database() db.open() records = db.execSelectQuery( query ) for record in records: pid = record[0] query = '''DELETE FROM projects WHERE pid=%s''' % pid db.execUpdateQuery( query ) db.close()
def exportHouseholdAssets(self, project, filename): database = Database() database.open() query = '''SELECT hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits FROM assets WHERE pid=%s''' % project.pid assets = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for asset in assets: assetline = '''INSERT INTO assets (pid, hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits ) VALUES({pid},%s,'%s','%s','%s',%s,%s)<endl>\n''' % (asset[0], asset[1], asset[2], asset[3], asset[4], asset[5]) ihmFile.write(assetline) ihmFile.close()
def exportProjectStandardOfLiving(self, project, filename): database = Database() database.open() query = '''SELECT summary, scope, gender, agebottom, agetop, item, costperyear FROM standardofliving WHERE pid=%s''' % project.pid stdLvs = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for stdLv in stdLvs: stdLvline = '''INSERT INTO standardofliving (pid, summary, scope, gender, agebottom, agetop, item, costperyear ) VALUES({pid},'%s','%s','%s',%s,%s,'%s',%s)<endl>\n''' % (stdLv[0], stdLv[1], stdLv[2], stdLv[3], stdLv[4], stdLv[5], stdLv[6]) ihmFile.write(stdLvline) ihmFile.close()
def exportProjectAssets(self, project, filename): database = Database() database.open() query = '''SELECT assetname, assettype FROM projectassets WHERE pid=%s''' % project.pid assets = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for asset in assets: assetline = '''INSERT INTO projectassets(pid,assetname,assettype) VALUES({pid},'%s','%s')<endl>\n''' % (asset[0], asset[1]) ihmFile.write(assetline) ihmFile.close()
def exportHouseholdCharacteristics(self, project, filename): database = Database() database.open() query = '''SELECT hhid, characteristic, charvalue FROM householdcharacteristics WHERE pid=%s''' % project.pid chars = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for char in chars: charline = '''INSERT INTO householdcharacteristics (pid,hhid, characteristic, charvalue ) VALUES({pid},%s,'%s','%s')<endl>\n''' % (char[0], char[1], char[2]) ihmFile.write(charline) ihmFile.close()
def exportHouseholdEmploymentIncome(self, project, filename): database = Database() database.open() query = '''SELECT hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome FROM employmentincome WHERE pid=%s''' % project.pid items = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for item in items: empline = '''INSERT INTO employmentincome(pid, hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome) VALUES({pid},%s,'%s','%s','%s',%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6]) ihmFile.write(empline) ihmFile.close()
def exportHouseholdWildfoodsIncome(self, project, filename): database = Database() database.open() query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM wildfoods WHERE pid=%s''' % project.pid items = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for item in items: wfline = '''INSERT INTO wildfoods(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed) VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7]) ihmFile.write(wfline) ihmFile.close()
def exportHouseholdCropIncome(self, project, filename): database = Database() database.open() query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome WHERE pid=%s''' % project.pid crops = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for crop in crops: cropline = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed) VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (crop[0], crop[1], crop[2], crop[3], crop[4], crop[5], crop[6], crop[7]) ihmFile.write(cropline) ihmFile.close()
def exportProjectDiet(self, project, filename): database = Database() database.open() query = '''SELECT fooditem, unitofmeasure, percentage, priceperunit FROM diet WHERE pid=%s''' % project.pid diets = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for diet in diets: dietline = '''INSERT INTO diet (pid, fooditem,unitofmeasure,percentage, priceperunit ) VALUES({pid},'%s','%s',%s,%s)<endl>\n''' % (diet[0], diet[1], diet[2], diet[3]) ihmFile.write(dietline) ihmFile.close()
def existsCorrespondingIhmProject(self, projectname, startdate, currency): ''' Checks if the project was transfered before ''' query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid AND transferlog.projectname='%s' AND transferlog.datecollected='%s' AND transferlog.currency='%s' ''' % (projectname, startdate, currency) db = Database() db.open() records = db.execSelectQuery( query ) exists = False if len(records) == 1: exists = True db.close() return exists
def exportProjectCharacteristics(self, project, filename): database = Database() database.open() query = '''SELECT characteristic, chartype, datatype FROM projectcharacteristics WHERE pid=%s''' % project.pid chars = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for char in chars: charline = '''INSERT INTO projectcharacteristics (pid, characteristic, chartype, datatype ) VALUES({pid},'%s','%s',%s)<endl>\n''' % (char[0], char[1], char[2]) ihmFile.write(charline) ihmFile.close()
def exportHouseholdTransfersIncome(self, project, filename): database = Database() database.open() query = '''SELECT hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit FROM transfers WHERE pid=%s''' % project.pid items = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for item in items: transline = '''INSERT INTO transfers(pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven, unitsconsumed, unitssold, priceperunit) VALUES({pid},%s,'%s','%s',%s,'%s','%s',%s, %s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9]) ihmFile.write(transline) ihmFile.close()
def importIhmProjectData(self, project, filename): dbfile = file(filename, 'r') contents = dbfile.read() dbfile.close() queries = contents.split('<endl>\n') database = Database() database.open() for index in range(len(queries)): if index != 0: # ignore the first row containing project (already created above) query = queries[index].strip() if ( query.startswith("INSERT") ): query = query.replace("{pid}", str(project.pid) ) print str(index) + " " + query database.execUpdateQuery( query ) database.close()
def exportHouseholdMembers(self, project, filename): database = Database() database.open() query = '''SELECT hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto FROM householdmembers WHERE pid=%s''' % project.pid members = database.execSelectQuery( query ) database.close() ihmFile = open(filename, 'a') for member in members: memberline = '''INSERT INTO householdmembers(pid, hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto) VALUES({pid},%s, '%s', %s,'%s','%s','%s',%s,'%s','%s')<endl>\n''' % (member[0], member[1], member[2], member[3], member[4], member[5], member[6], member[7], member[8]) ihmFile.write(memberline) ihmFile.close() self.exportPersonalCharacteristics(project, filename)
def login(cls, login, passw): db = Database() query = (""" SELECT * from (SELECT id, name, Login, Password, 'Student' as user_type FROM Student UNION SELECT id, Name, Login, Password, 'Mentor' as user_type FROM Mentor UNION SELECT id, Name, Login, Password, 'Employee' as user_type FROM Employee UNION SELECT id, Name, Login, Password, 'Manager' as user_type FROM Manager) where Login = ? and Password = ? """) # awesome query, I love union <3 values = ((login, passw)) user = db.get(query, values) if not user: return user = db.get(query, values)[0] db.close() user_dict = {'id': user[0], 'name': user[1], 'type': user[4]} return user_dict
def test_close(self): database = Database() database.open() database.close()
def delete(self): db = Database() query = """DELETE FROM Mentor WHERE id =(?)""" db.set(query, (self.user_id, )) db.close()
def delGlobalCharacteristic(self, charname=""): database = Database() query = "DELETE FROM globalcharacteristics WHERE characteristic='%s' " % ( charname ) database.open() database.execUpdateQuery( query ) database.close()
def deleteProject(self, pid): query = "DELETE FROM projects WHERE pid=%s" % pid database = Database() database.open() database.execUpdateQuery( query ) database.close()
class CurrencyManager( object ): """Manages currencies. Allows adding, editing, deleting and retrieval of currencies. """ def __init__( self ) : self.database = Database() self.currencies = None self.currency_names = None self.getCurrencies() def existsCurrency(self, name): """Check if currency 'name' exists """ currency = Currency(currencyname=name) if not currency.name : return False else: return True def getCurrencyByID(self, currencyid): """return currency currencyid from the database (returns by currency id) """ currency = Currency(currencyid) if not currency.name : raise CurrencyError( "Can't get Currency id %r which does not exist" % currencyid ) return currency def getCurrencyByName(self, name): """return currency currencyname from the database (returns by currency name) """ currency = Currency(currencyname=name) if not currency.name : raise CurrencyError( "Can't get Currency '%r' which does not exist" % name ) return currency def addCurrency(self, currencyname, abbreviation, symbol ): """Adds currency 'currencyname' to the currencies database. """ currency = Currency( 0 , currencyname, abbreviation, symbol ) self.getCurrencies() # update currency list return currency def editCurrency(self, currencyid, currencyname, abbreviation, symbol ): """Edit currency 'currencyid' providing new name, abbreviation, symbol. """ currency = Currency(currencyid) if not currency.name : raise CurrencyError( "Can't find Currency id %r which does not exist" % currencyid ) currency.editData( currencyname, abbreviation, symbol ) self.getCurrencies() # update currency list return def delCurrency(self, currencyid): """Delete currency 'currencyid' from the database. """ currency = Currency(currencyid) if not currency.name : raise CurrencyError( "Can't delete Currency id %r which does not exist" %currencyid ) query = "DELETE FROM currencies WHERE id=%i " % ( currencyid ) self.database.open() self.database.execUpdateQuery( query ) self.database.close() self.getCurrencies() # update currency list return def getCurrencies(self): """Return a list of currencies stored in the database At same time set self.currencies to this list. """ query = "SELECT id FROM currencies" self.database.open() rows = self.database.execSelectQuery( query ) self.database.close() self.currencies = list() for row in rows: curr_id = row[0] currency = Currency(curr_id) self.currencies.append( currency ) self.currency_names = [ x.name for x in self.currencies ] return self.currencies
def delGlobalPersonCharacteristic(self, charid="", charname=""): query = "DELETE FROM globalpersonalcharacteristics WHERE id=%i OR characteristic='%s' " % ( charid, charname ) database = Database() database.open() database.execUpdateQuery( query ) database.close()
def get_answer_by_id(cls, id): db = Database() task_data = db.get("SELECT * FROM Answers WHERE id=(?)", (id, ))[0] return Answer(*task_data) db.close()
def save(self): db = Database() db.set("UPDATE Answers SET Grade=(?),Grade_date=(?) WHERE ID=(?)", (self.grade, self.grade_date, self.id)) db.close()
def add_new_assigment(name, task_type): db = Database() db.set("INSERT INTO Assignments(name, task_type) VALUES (?,?)", (name, task_type)) db.close()