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()