Python Database.execUpdateQuery方法代码示例

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
 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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
        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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
 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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
 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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
        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)
        

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
 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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import execUpdateQuery [as 别名]
    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()