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 readBasicMemberDetails(self, householdsheet, row_index): # print book.nsheets start_row_index = row_index + 1 empty_cell_count = 0 hhid = householdsheet.name print hhid database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0, 4): cellvalue = householdsheet.cell(current_row_index, col_index).value print cellvalue if cellvalue == "": empty_cell_count = empty_cell_count + 1 cellvalue = None if col_index > 0 and valueisdigit() == False: cellvalue = None if col_index == 3 and (cellvalue == 1 or cellvalue == "yes"): cellvalue = "Yes" else: cellvalue = "No" values.append(cellvalue) if empty_cell_count == 4 or value == "PersonalCharacteristics": # check if entire row is empty break else: sex = values[0] age = values[1] yearofbirth = values[2] hhead = values[3] personid = str(sex) + str(age) query = """REPLACE INTO householdmembers (personid,hhid,headofhousehold,yearofbirth,sex,pid) VALUES ('%s',%s,'%s',%s,'%s','%s',%s)""" % ( personid, hhid, hhead, yearofbirth, self.pid, ) print query database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readProjectHouseholdsData(self,book): '''Import Project Households''' sheet1 = book.sheet_by_index(0) # Start Block of code for importing a project's households database = Database() database.open() for row in range(2,sheet1.nrows): values = [] for col in range(sheet1.ncols): skiprow =False cell = sheet1.cell(row,col) cellvalue = cell.value #cellvalue = sheet1.cell(row,col).value if cellvalue =='': #if cellvalue =='' or (col ==3 and cell.ctype!=3): skiprow =True break else: if col == 2: if cell.ctype == 3: #date date_value = xldate_as_tuple(cell.value,book.datemode) cellvalue = date(*date_value[:3]) else: cellvalue = datetime.strptime(cellvalue, "%d-%m-%Y").strftime('%Y-%m-%d') values.append(cellvalue) if skiprow ==True: continue else: hhid = values[0] hholdname = values[1] datevisited = values[2] pid= sheet1.name testquery ='''SELECT hhid,pid FROM households WHERE hhid='%s' AND pid =%s ''' % (hhid,self.pid) numrows =self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO households (hhid,householdname,dateofcollection,pid) VALUES ('%s','%s','%s',%s)''' % (hhid,hholdname,datevisited,pid) else: query ='''UPDATE households SET hhid='%s',householdname='%s',dateofcollection='%s',pid=%s WHERE hhid='%s' AND pid =%s ''' % (hhid,hholdname,datevisited,pid,hhid,pid) database.execUpdateQuery(query) database.close()
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' # FIXME: the None's look hinky. self.assertEqual([(2, u'test', None, None, u'a simple test', u'GBP')], database.execSelectQuery(query)) database.close()
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 insertSartUpValues(self): '''Insert food energy requirements by age and sex into table lookup_energy_needs''' database = Database() database.open() deleteQuery = '''DELETE FROM lookup_energy_needs''' database.execUpdateQuery(deleteQuery) insertQuery = '''INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (0,820,820), (1,820,820), (2,1150,1150), (3,1350,1350), (4,1550,1550), (5,1550,1550), (6,1850,1750), (7,1850,1750), (8,1850,1750), (9,1850,1750), (10,2100,1800), (11,2100,1800), (12,2200,1950), (13,2200,1950), (14,2400,2100), (15,2400,2100), (16,2650,2150), (17,2650,2150)''' database.execUpdateQuery(insertQuery) insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (18,2600,2600)" for i in range(19,30): insertQuery = insertQuery + ",(%s,2600,2600) " % i database.execUpdateQuery(insertQuery) insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (30,2500,2050)" for i in range(31,60): insertQuery = insertQuery + ",(%s,2500,2050) " % i database.execUpdateQuery(insertQuery) insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (60,2100,1850)" for i in range(61,100): insertQuery = insertQuery + ",(%s,2100,1850) " % i database.execUpdateQuery(insertQuery) database.close()
cell = sheet1.cell(row,col) if cell.ctype == 3: #date date_value = xldate_as_tuple(cell.value,book.datemode) value = date(*date_value[:3]) else: value = cell.value values.append(value) hhid = values[0] hholdname = values[1] datevisited = values[2] pid= sheet1.name query ='''REPLACE INTO households (hhid,householdname,dateofcollection,pid) VALUES (%s,'%s','%s',%s)''' % (hhid,hholdname,datevisited,pid) database.execUpdateQuery(query) #self.insertValuesInDB(hhid,hholdname,datevisited,pid) database.close() '''for sheet in range(2,book.sheets): huseid = sheet.name projectsheet = book.sheet_by_index(0) projectid = projectsheet.name''' #def insertValuesInDB(hhid,hholdname,datevisited,pid): #query ='''REPLACE INTO households (hhid,householdname,dateofcollection,pid) VALUES (%s,'%s',%s,%s)''' % (hhid,hholdname,datevisited,pid) #database.execUpdateQuery(query)
def insertSartUpValues(self): database = Database() database.open() query = '''REPLACE INTO setup_foods_crops (name,category,energyvalueperunit) VALUES ('Sorghum - whole','crops', %s) , ('Millet, whole', 'crops', %s), ('Sorghum flour', 'crops', %s), ('Wheat flour', 'crops', %s), ('Millet meal', 'crops', %s), ('Cassava fresh', 'crops', %s), ('Potato sweet', 'crops', %s), ('Cashew nut', 'crops', %s), ('Groundnut fresh', 'crops', %s), ('Leaves- dark green', 'crops', %s), ('Leaves- medium', 'crops', %s), ('Leaves - light green','crops', %s) , ('Onion', 'crops', %s) , ('Pumpkin', 'crops', %s) , ('Tomato', 'crops', %s), ('Banana', 'crops', %s), ('Cashew apple', 'crops', %s) , ('Mango', 'crops', %s), ('Papaya', 'crops', %s) , ('Vegetable oils', 'crops', %s) , ('Termites', 'wildfoods', %s), ('Milk, cow', 'livestock', %s) , ('Milk, goat', 'livestock', %s) , ('Milk, sheep', 'livestock', %s) , ('Mice', 'wildfoods', %s), ('Rice', 'crops', %s) , ('Ground beans', 'crops', %s) , ('Beef', 'livestock', %s) , ('Eggs(Hens & ducks)','livestock',%s) , ('Meat, goat', 'livestock', %s) , ('Meat, sheep', 'livestock', %s) , ('Meat, poultry', 'livestock', %s), ('Meat, pig', 'livestock', %s) , ('Soya', 'crops', %s), ('Nzama(Bambara groundnut)','crops', %s) , ('Baobab fruit', 'wildfoods', %s) , ('Fish', 'wildfoods', %s), ('Tamarind', 'wildfoods', %s) , ('Okra', 'crops', %s), ('Sweet potatoes', 'crops',%s), ('Brinjal', 'crops', %s), ('Coconut(ripe nut)','wildfoods', %s) , ('Fish(freshwater)','wildfoods', %s) , ('Gourd', 'crops', %s) , ('Guava', 'wildfoods', %s), ('Lentils', 'crops', %s), ('Mustard', 'crops', %s), ('Potato', 'crops', %s) , ('Radish', 'crops', %s) , ('Red Amaranth(leaf)','wildfoods', %s) , ('Sugar, white', 'crops', %s) , ('Cabbage', 'crops', %s) , ('Groundnut, dry', 'crops', %s) , ('Avocado, flesh', 'crops', %s) , ('Bambara groundnut', 'crops',%s) , ('Chillies, hot, dried', 'crops',%s) , ('coco-yam', 'crops', %s) , ('Cowpea', 'crops', %s) , ('Green maize, cob','crops',%s) , ('Millet, bullrush','crops',%s) , ('Pigeon peas', 'crops', %s) , ('Pigeon pea, green', 'crops',%s) , ('sesame', 'crops', %s) , ('Mango, medium', 'crops', %s) , ('Maize', 'crops', %s)''' % (3550,3630,3530,3460,3650,1530,1140,5900,3320,480,280,330,480,360,200,1160, 560,630,390,9000,1480,640,710,1080,1340,3540,3670,2020,75,1450,1490,1390,3710, 3820,3670,560,500,3040,330,1140,280,400,950,480,630,3390,5440,1140,180,280, 4000,230,5790,1650,3670,2910,1000,3400,492,3630,3280,2110,5920,63,3420) database.execUpdateQuery(query) database.close()
def readHCharacteristicsData(self,householdsheet,row_index): '''Import Data on Household Characteristics''' field_row_index = row_index + 1 datatype_row_index = row_index + 2 start_row_index = row_index + 3 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() #determine number of columns for pcharacteristics columns = 0 datafields=[] fielddatatypes=[] for col_index in range (0,householdsheet.ncols): datafieldvalue = householdsheet.cell(field_row_index,col_index).value fieldtype = str(householdsheet.cell(datatype_row_index,col_index).value) if datafieldvalue!='': datafields.append(datafieldvalue) fielddatatypes.append(fieldtype) columns = columns + 1 else: break empty_cell_count =0 for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,columns): exitmain = False personid ='' cellvalue = str(householdsheet.cell(current_row_index,col_index).value) datatype = str(householdsheet.cell(datatype_row_index,col_index).value) if cellvalue == 'Assets': exitmain = True break if cellvalue == '': cellvalue ='NULL' empty_cell_count = empty_cell_count + 1 if datatype=='Double': try: cellvalue = float(cellvalue) except ValueError: cellvalue = 0 elif datatype=='Integer': try: cellvalue = int(cellvalue) except ValueError: cellvalue = 0 elif datatype=='Yes/No': try: cellvalue = int(cellvalue) except: pass tempvalue = str(cellvalue) tempvalue = tempvalue.strip() if tempvalue == '1' or tempvalue.lower() =='yes' or tempvalue.lower() =='y': cellvalue = 'Yes' else: cellvalue = 'No' values.append(cellvalue) if exitmain == True or empty_cell_count==columns: break else: for dataindex in range (0,len(datafields)): paramlist=[] characteristic = datafields[dataindex] charvalue= values[dataindex] testquery='''SELECT * from householdcharacteristics WHERE hhid='%s' AND pid=%s AND characteristic='%s' ''' %(hhid,self.pid,characteristic) numrows = self.checkRecordExistence(testquery) paramlist = (hhid,datafields[dataindex],values[dataindex]) if numrows == 0: query = self.buildHCharInsertQuery(paramlist) else: query= '''DELETE FROM householdcharacteristics WHERE hhid='%s' AND pid=%s AND characteristic='%s' ''' %(hhid,self.pid,characteristic) database.execUpdateQuery(query) query = self.buildHCharInsertQuery(paramlist) database.execUpdateQuery(query) database.close()
def readTransferData(self,householdsheet,row_index,incometype): '''Import data on social and Organisational Transfers''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,7): exitmain = False digitvalue = True skiprow = False cellvalue = str(householdsheet.cell(current_row_index,col_index).value) if incometype== 'SocialTransfer' and cellvalue == 'TransferFromOrganisations': #if cellvalue == 'TransferFromOrganisations': exitmain = True break if col_index == 0 and cellvalue=='': skiprow = True break if col_index!=0 and cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue='NotSet' if col_index ==1 or(col_index >=4 and col_index <=7): try: cellvalue = float(cellvalue) digitvalue = True except ValueError: digitvalue = False if digitvalue == False: cellvalue = 0 values.append(cellvalue) if exitmain == True: break else: if skiprow==True: #check if four cell in row or cell for expenditurety are empty continue else: transfersource = values[0] cash = values[1] foodtype = values[2] unit = values[3] unitsconsumed = values[4] unitssold= values[5] unitprice= values[6] if incometype=='SocialTransfer': sourcetype='Internal' elif incometype=='TransferFromOrganisations': sourcetype='External' testquery = '''SELECT * from transfers WHERE hhid='%s' AND pid=%s AND sourcetype='%s' AND sourceoftransfer='%s' ''' %(hhid,self.pid,sourcetype,transfersource) numrows = self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO transfers (hhid,sourcetype,sourceoftransfer,cashperyear,foodtype,unitofmeasure,unitsconsumed,unitssold,priceperunit,pid) VALUES ('%s','%s','%s',%s,'%s','%s',%s,%s,%s,%s)''' % (hhid,sourcetype,transfersource,cash,foodtype,unit,unitsconsumed,unitssold,unitprice,self.pid) else: query ='''UPDATE transfers SET hhid='%s',sourcetype='%s',sourceoftransfer='%s',cashperyear=%s,foodtype='%s',unitofmeasure='%s',unitsconsumed=%s,unitssold=%s,priceperunit=%s,pid=%s WHERE hhid='%s' AND pid=%s AND sourcetype='%s' AND sourceoftransfer='%s' ''' % (hhid,sourcetype,transfersource,cash,foodtype,unit,unitsconsumed,unitssold,unitprice,self.pid,hhid,self.pid,sourcetype,transfersource) database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readEmploymentData(self,householdsheet,row_index): '''Import Employment Data''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,6): exitmain = False digitvalue = True skiprow = False cellvalue = str(householdsheet.cell(current_row_index,col_index).value) if cellvalue == 'SocialTransfer': exitmain = True break if col_index == 0 and cellvalue=='': skiprow = True break if col_index!=0 and cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue = 'NULL' if (col_index >=3 and col_index <=5): try: cellvalue = round(float(cellvalue),2) digitvalue = True except ValueError: digitvalue = False if digitvalue == False: cellvalue = 0 values.append(cellvalue) if exitmain == True: break else: if skiprow == True: #check if at least three cell in row or cell for expenditurety are empty continue else: employmenttype = values[0] foodpaid = values[1] unit = values[2] unitspaid = values[3] kcals = values[4] cashincome = values[5] testquery = '''SELECT * FROM employmentincome WHERE hhid='%s' AND incomesource='%s' AND pid =%s''' %(hhid,employmenttype,self.pid) numrows = self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO employmentincome (hhid,incomesource,foodtypepaid,unitofmeasure,unitspaid,incomekcal,cashincome,pid) VALUES ('%s','%s','%s','%s',%s,%s,%s,%s)''' % (hhid,employmenttype,foodpaid,unit,unitspaid,kcals,cashincome,self.pid) else: query = '''UPDATE employmentincome SET hhid='%s',incomesource='%s',foodtypepaid='%s',unitofmeasure='%s',unitspaid=%s,incomekcal=%s,cashincome=%s,pid=%s WHERE hhid='%s' AND incomesource='%s' AND pid =%s''' % (hhid,employmenttype,foodpaid,unit,unitspaid,kcals,cashincome,self.pid,hhid,employmenttype,self.pid) database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readCropAndFoodsIncomeData(self,householdsheet,row_index,incometype): '''Import Data for Crop, Livestock, and Wildfood Income''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,7): exitmain = False digitvalue = True skiprow = False cellvalue = str(householdsheet.cell(current_row_index,col_index).value) if incometype== 'Crops-C': if cellvalue == 'Livestock-C': exitmain = True break elif incometype== 'Livestock-C': if cellvalue == 'Wildfoods-C': exitmain = True break elif incometype== 'Wildfoods-C': if cellvalue == 'Employment': exitmain = True break if col_index == 0 and cellvalue=='': skiprow = True break if col_index!=0 and cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue='NULL' if (col_index >=2 and col_index <=6): try: cellvalue = float(cellvalue) digitvalue = True except ValueError: digitvalue = False if digitvalue == False: cellvalue = 0 values.append(cellvalue) if exitmain == True: break else: if skiprow==True: #check if four cell in row or cell for expenditurety are empty continue else: name = values[0] unit = values[1] unitsproduced = values[2] unitssold = values[3] unitprice = values[4] otheruses = values[5] unitsconsumed = values[6] if incometype=='Crops-C': tablename='cropincome' elif incometype=='Livestock-C': tablename='livestockincome' elif incometype=='Wildfoods-C': tablename='wildfoods' testquery =''' SELECT * FROM %s WHERE hhid='%s' AND incomesource='%s' AND pid=%s ''' % (tablename,hhid,name,self.pid) numrows = self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO %s (hhid,incomesource,unitofmeasure,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,pid) VALUES (%s,'%s','%s',%s,%s,%s,%s,%s,%s)''' % (tablename,hhid,name,unit,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,self.pid) else: query ='''UPDATE %s SET hhid='%s',incomesource='%s',unitofmeasure='%s',unitsproduced=%s,unitssold=%s,unitprice=%s,otheruses=%s,unitsconsumed=%s,pid=%s WHERE hhid='%s' AND incomesource='%s' AND pid=%s ''' % (tablename,hhid,name,unit,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,self.pid,hhid,name,self.pid) database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readExpenditureData(self,householdsheet,row_index): '''Import Expenditure Data''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,5): exitmain = False digitvalue = True skiprow = False cellvalue = str(householdsheet.cell(current_row_index,col_index).value) if cellvalue == 'Crops-C': exitmain = True break if col_index == 0 and cellvalue=='': skiprow = True break if col_index!=0 and cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue = 'NULL' if (col_index >=2 and col_index <=4): try: cellvalue = float(cellvalue) digitvalue = True except ValueError: digitvalue = False if digitvalue == False: cellvalue = 0 values.append(cellvalue) if exitmain == True: break else: if skiprow == True: #check if at least three cell in row or cell for expenditurety are empty continue else: expendituretype = values[0] unit = values[1] kcalperunit = values[2] unitcost = values[3] units = values[4] testquery ='''SELECT * FROM expenditure WHERE hhid='%s' AND exptype='%s' AND pid =%s''' % (hhid,expendituretype,self.pid) numrows = self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO expenditure (hhid,exptype,unitofmeasure,priceperunit,kcalperunit,totalunits,pid) VALUES ('%s','%s','%s',%s,%s,%s,%s)''' % (hhid,expendituretype,unit,unitcost,kcalperunit,units,self.pid) else: query='''UPDATE expenditure SET hhid='%s',exptype='%s',unitofmeasure='%s',priceperunit=%s,kcalperunit=%s,totalunits=%s,pid=%s WHERE hhid='%s' AND exptype='%s' AND pid =%s ''' % (hhid,expendituretype,unit,unitcost,kcalperunit,units,self.pid,hhid,expendituretype,self.pid) database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readAssetData(self,householdsheet,row_index): '''Import Asset Data''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,5): digitvalue = True skiprow = False exitmain = False cellvalue = str(householdsheet.cell(current_row_index,col_index).value) if cellvalue == 'Expenditure': exitmain = True break if col_index == 0 and cellvalue=='': skiprow =True break if cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue = 'NULL' if (col_index ==3 or col_index ==4): try: cellvalue = float(cellvalue) digitvalue = True except ValueError: digitvalue = False if digitvalue == False: cellvalue = 0 values.append(cellvalue) if exitmain == True: break else: if empty_cell_count >= 5 or skiprow == True: #check if entire row is empty continue else: category = values[0] assettype = values[1] unit = values[2] unitcost = values[3] units = values[4] testquery ='''SELECT * FROM assets WHERE hhid='%s' AND assetcategory='%s' AND assettype='%s' AND pid =%s ''' % (hhid,category,assettype,self.pid) numrows =self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO assets (hhid,assetcategory,assettype,unitofmeasure,unitcost,totalunits,pid) VALUES ('%s','%s','%s','%s',%s,%s,%s)''' % (hhid,category,assettype,unit,unitcost,units,self.pid) else: query ='''UPDATE assets SET hhid='%s',assetcategory='%s',assettype='%s',unitofmeasure='%s',unitcost=%s,totalunits=%s,pid=%s WHERE hhid='%s' AND assetcategory='%s' AND assettype='%s' AND pid =%s ''' % (hhid,category,assettype,unit,unitcost,units,self.pid,hhid,category,assettype,self.pid) database.execUpdateQuery(query) empty_cell_count = 0 database.close()
def readBasicMemberDetails(self,householdsheet,row_index): '''Import Data on Basic Personal Characteristics: - Sex,Age,year Of Birth, and household headship status''' start_row_index = row_index + 2 empty_cell_count = 0 hhid = householdsheet.name database = Database() database.open() for current_row_index in range(start_row_index, householdsheet.nrows): values = [] for col_index in range(0,5): exitmain = False skiprow =False cellvalue = householdsheet.cell(current_row_index,col_index).value if cellvalue == 'PersonalCharacteristics': exitmain = True break if (col_index == 0 or col_index ==1) and cellvalue=='': skiprow =True break try: cellvalue = int(cellvalue) digitvalue = True except ValueError: digitvalue = False if cellvalue == '': empty_cell_count = empty_cell_count + 1 cellvalue = 'NULL' if (col_index ==1 or col_index ==2 or col_index ==4) and digitvalue == False: cellvalue = 0 if col_index == 3 and (cellvalue == 1 or cellvalue.lower() =='yes' or cellvalue.lower() =='y'): cellvalue = 'Yes' elif col_index == 3 and (cellvalue != 1 or cellvalue.lower() !='yes' or cellvalue.lower() !='y'): cellvalue = 'No' values.append(cellvalue) if exitmain == True: break else: if empty_cell_count == 4 or skiprow == True: #check if entire row is empty continue else: sex = str(values[0]).strip() age = values[1] if values[2] ==0 and age !=0: yearofbirth = date.today().year - values[1] elif values[2] ==0 and age ==0: yearofbirth = date.today().year else: yearofbirth = values[2] hhead = values[3] if sex.lower() == 'male' or sex.lower() == 'm': personid = 'm' + str(age) sex = 'Male' elif sex.lower() == 'female' or sex.lower() == 'f': personid = 'f' + str(age) sex='Female' pidvalue = personid periodaway = values[4] testquery ='''SELECT * FROM householdmembers WHERE hhid='%s' AND personid ='%s' AND pid =%s ''' % (hhid,pidvalue,self.pid) numrows =self.checkRecordExistence(testquery) if numrows ==0: query ='''INSERT INTO householdmembers (personid,hhid,headofhousehold,yearofbirth,sex,periodaway,pid) VALUES ('%s','%s','%s',%s,'%s',%s,%s)''' % (personid,hhid,hhead,yearofbirth,sex,periodaway,self.pid) else: #personid = personid + '_' + str(numrows+1) query = ''' UPDATE householdmembers SET headofhousehold='%s',yearofbirth=%s,sex='%s',periodaway=%s WHERE personid='%s' AND hhid='%s' AND pid=%s''' % (hhead,yearofbirth,sex,periodaway,personid,hhid,self.pid) database.execUpdateQuery(query) empty_cell_count = 0 database.close()