Python Database.execSelectQuery方法代碼示例

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def executeQuery(self,query):
     '''Execute queries under adult equivalent calculations '''
     databaseConnector = Database()
     databaseConnector.open()
     result = databaseConnector.execSelectQuery( query )
     databaseConnector.close()
     return result

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def executeSelectQuery(self,query):
     '''Run Select Query'''
     dbconnector = Database()
     dbconnector.open()
     recset = dbconnector.execSelectQuery(query)
     dbconnector.close()
     return recset

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def executeQuery(self,query):
     '''run various select queries'''
     
     dbconnector = Database()
     dbconnector.open()
     recordset = dbconnector.execSelectQuery(query)
     dbconnector.close()
     return recordset

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def getincomeSources(self,query):
     '''run various select queries'''
     dbconnector = Database()
     dbconnector.open()
     print query
     recordset = dbconnector.execSelectQuery(query)
     dbconnector.close()
     return recordset

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def getReportHouseholdIDs(self,query):
     
     reporthouseholdIDs=[]
     databaseConnector = Database()
     if query !='':
         databaseConnector.open()
         reporthouseholdIDs = databaseConnector.execSelectQuery( query )
         databaseConnector.close()
     return reporthouseholdIDs

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
    def checkRecordExistence(self,testquery):
        '''Test if a record with some given primary key already exists'''
        database = Database()
        database.open()
        testrecset = database.execSelectQuery(testquery)
        numrows =0
        for row in testrecset:
	    numrows = numrows + 1
        database.open()
	return numrows

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
 def test_execSelectQuery(self):
     self.helper.setup_clean_db()
     self.helper.execute_instruction("""
         insert into projects
             (projectname, startdate, enddate, description, currency)
         values
             ('test', 2012-06-04, 2013-07-03, 'a simple test', 'GBP')""")
     database = Database()
     query = 'select * from projects'
     database.open()
     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 execSelectQuery [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 execSelectQuery [as 別名]
class DataEntrySheets:
    def __init__(self,projectid):
        self.database = Database()
        self.pcharstable = 'p' + str(projectid) +'PersonalCharacteristics'
        self.hcharstable = 'p' + str(projectid) +'HouseholdCharacteristics'
        self.pid = projectid
        self.config = Config.dbinfo().copy()
    
    def getPersonalCharacteristics(self):
        query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Personal' ''' %(self.pid)
        self.database.open()
        pchars = self.database.execSelectQuery(query)
        self.database.close()
        return pchars

    def getHouseholdCharacteristics(self):
        query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Household' ''' %(self.pid)
        self.database.open()
        hchars = self.database.execSelectQuery(query)
        self.database.close()
        return hchars

        # Income sources
    def buildQueries(self,incometype):
        query = '''SELECT incomesource FROM projectincomesources WHERE incometype='%s' AND pid=%s''' % (incometype,self.pid)
        return query

    def getProjectCropsFoods(self,incometype,projectincomes):
        #incomesourcelist = ','.join(projectincomes)
        incomes = []
        recordset = []
        if len(projectincomes)!= 0:
            for income in projectincomes:
                tempname = "'" + income[0] + "'"
                incomes.append(tempname)

            incomesourcelist = ','.join(incomes)
            query = '''SELECT name, unitofmeasure FROM  setup_foods_crops WHERE  category='%s' AND name in (%s)''' % (incometype,incomesourcelist)
            print query
            recordset = self.getincomeSources(query)
        return recordset

    def getincomeSources(self,query):
        '''run various select queries'''
        dbconnector = Database()
        dbconnector.open()
        print query
        recordset = dbconnector.execSelectQuery(query)
        dbconnector.close()
        return recordset

    def getCropsFoodsIncomeSourceDetails(self,incometype):
        '''Get Income-source Names and Units of Measure for Crops, Livestocks, and Wildfoods, to be displayed in data entry spreadsheet'''
        incomesquery = self.buildQueries(incometype)
        projectincomes = self.getincomeSources(incomesquery)
        incomesourcedetails = self.getProjectCropsFoods(incometype,projectincomes)
        return incomesourcedetails

    def getprojetAssets(self):
        query = '''SELECT assettype, assetname FROM projectassets WHERE pid=%s ORDER BY assettype, assetname''' % self.pid
        self.database.open()
        assets = self.database.execSelectQuery(query)
        self.database.close()
        return assets
    
    def getProjectSocialTransfers(self):
        query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Social Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
        self.database.open()
        transfers = self.database.execSelectQuery(query)
        self.database.close()
        return transfers

    def getProjectOfficialTransfers(self):
        query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Official Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
        self.database.open()
        transfers = self.database.execSelectQuery(query)
        self.database.close()
        return transfers

    def populateSocialTranfers(self,book,style1,style2,row):
        recordset = self.getProjectSocialTransfers()
        sheet = book.get_sheet(3)
        col = 0
        #set section Headings
        sheet.write(row, col, "SocialTransfer", style1)
        row = row + 1
        transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
        for itemheader in transferheadings:
            sheet.write(row, col, itemheader, style2)
            col = col + 1
        row = row +1

        #write transfer sources
        col = 0
        for rec in recordset:
            celvalue = rec[col]
            sheet.write(row, col, celvalue)
            row = row + 1
        row = row + 4 # set space between Income source type sections
        return row
#.........這裏部分代碼省略.........

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
class DataEntrySheets:
    def __init__(self,projectid):
        self.database = Database()
        self.pcharstable = 'p' + str(projectid) +'personalcharacteristics'
        self.hcharstable = 'p' + str(projectid) +'householdcharacteristics'
        self.pid = projectid
        self.config = Config.dbinfo().copy()

    def getPersonalCharacteristics(self):
        query = '''SHOW columns FROM %s''' %(self.pcharstable)
        self.database.open()
        pchars = self.database.execSelectQuery(query)
        self.database.close()
        return pchars
        

    def getHouseholdCharacteristics(self):
        query = '''SHOW columns FROM %s''' %(self.hcharstable)
        self.database.open()
        hchars = self.database.execSelectQuery(query)
        self.database.close()
        return hchars
            def buildQueries(self,incometype):
            '''Build queries for getting project income sources'''
            
            query = '''SELECT * FROM projectincomesources WHERE incometype='%s' ''' % incometype
            return query

    def getincomeSources(self,query):
        
        '''run income source select queries'''
        
        dbconnector = Database()
        dbconnector.open()
        recordset = dbconnector.execSelectQuery(query)
        dbconnector.close()
        return recordset
       
    def writeDataSheets(self):
        book = Workbook(encoding="utf-8")
        
        #set style for headers
        style1 = easyxf('font: name Arial;''font: bold True;')
        style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
        style3 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')

        
        
        #create sheet for entering project households
        #projectid = self.pid
        sheettitle = "%s" % self.pid
        sheet1 = book.add_sheet(sheettitle)
        sheet1.write(0, 0, "Project Households", style1)
        sheet1.write(1, 0, "HouseholdNumber", style2)
        sheet1.write(1, 1, "HouseholdName", style2)
        sheet1.write(1, 2, "DateVisited", style2)

        #set column width for sheet1
        for i in range(0,3):
            sheet1.col(i).width = 6000


        #Basic Details for Household Members
        sheet2 = book.add_sheet("Template")
        sheet2.write(1, 0, "HouseholdMembers", style1)
        sheet2.write(2, 0, "Sex", style2)
        sheet2.write(2, 1, "Age", style2)
        sheet2.write(2, 2, "YearofBirth", style2)
        sheet2.write(2, 3, "HouseholdHead", style2)

        #Basic Details for Household Members
        sheet3 = book.add_sheet("Income Sources")
        sheet3.write(1, 0, "Crop Types", style1)
        sheet3.write(1, 2, "Employment Types", style2)
        sheet3.write(1, 4, "Livestock Types", style2)
        sheet3.write(1, 6, "Transfer Types", style2)
        sheet3.write(1, 8, "Wild Food Types", style2)

        #set column width for sheet3
        for i in range(0,10):
            sheet3.col(i).width = 6000


        #get personal and household characteristics, configured for current project
        pchars = self.getPersonalCharacteristics()
        hchars = self.getHouseholdCharacteristics()

        #section for extended personal characteristics
        sheet2.write(8, 0, "PersonalCharacteristics", style1)
        col = 0
        for char in pchars:
            value = char[0]
            typep = char[1]
            if value!='pid' and value !='hhid':
                stringvar = 'varchar'
                boolvar = 'enum'
                intvar = 'bigint'
                doublevar ='double'
                if typep.startswith(tuple(stringvar)):
                    vartype ='String'
#.........這裏部分代碼省略.........

# 需要導入模塊: from data.database import Database [as 別名]
# 或者: from data.database.Database import execSelectQuery [as 別名]
class HouseholdsByCharacteristics:
    def __init__(self):
        self.database = Database()

    def buildPCharacteristicsQuery(self,pcharacteristics, tablename,projectid):
        ''' build query for selecting households that meet selected personal characteristics from the report interface'''
        
        houseid = tablename + '.hhid'
        basequery = '''SELECT households.pid,households.hhid, households.householdname
                            FROM households,personalcharacteristics WHERE households.pid=%s AND households.hhid = personalcharacteristics.hhid AND households.pid=personalcharacteristics.pid''' % projectid
        for currentcharacteristic in pcharacteristics:
            #currentcharacteristic =  'personalcharacteristics' + '.' + '%s' % characteristic
            basequery = basequery + " AND personalcharacteristics.characteristic ='%s'  AND personalcharacteristics.charvalue='Yes'" % (currentcharacteristic)

        basequery = basequery + " GROUP BY households.pid,households.hhid" 
        print basequery
        return basequery
        
    def buildHCharacteristicsQuery(self,hcharacteristics, tablename,projectid):
        ''' build query for selecting households that meet selected household characteristics from the report interface'''
        
        #settingsmanager = ReportsSettingsManager()
        houseid = tablename + '.hhid'
        
        basequery = basequery = '''SELECT households.pid,households.hhid, households.householdname
                            FROM households,householdcharacteristics WHERE households.pid=%s AND households.hhid = householdcharacteristics.hhid AND households.pid=householdcharacteristics.pid''' % projectid
        for currentcharacteristic in hcharacteristics:
            #currentcharacteristic =  'householdcharacteristics' + '.' + '%s' % characteristic
            basequery = basequery + " AND householdcharacteristics.characteristic ='%s'  AND householdcharacteristics.charvalue='Yes'" % (currentcharacteristic)
            
        basequery = basequery + " GROUP BY households.pid,households.hhid" 
        print basequery
        return basequery

    def getReportTable(self,projectid,pcharselected,hcharselected,pquery,hquery):
        ''' generate report tables'''
        
        pcharstable = self.getPcharacteristicsTable(pquery)
        hcharstable = self.getHcharacteristicsTable(hquery)
        x = len(pcharstable)
        y = len(hcharstable)
        reporttable = []

        if (x ==0 and y== 0)or (x == 0 and pcharselected !=0)or (y == 0 and hcharselected !=0):
            return reporttable
        elif (x !=0 and y != 0):
            query = ''' SELECT * FROM (%s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 2''' % (pquery,hquery)
            reporttable = self.getFinalReportTableData(query)
            print reporttable
            return reporttable
        elif (x == 0 and pcharselected ==0):
            return hcharstable
        elif (y == 0 and hcharselected ==0):
            return pcharstable

    def getPcharacteristicsTable(self,pquery):
        ''' get households where selected personal characteristics from the interface are met'''
        self.database.open()
        print pquery
        ptable = self.database.execSelectQuery( pquery )
        self.database.close()
        return ptable

    def getHcharacteristicsTable(self,hquery):
        ''' get households where selected household characteristics from the interface are met'''
        self.database.open()
        htable = self.database.execSelectQuery( hquery )
        self.database.close()
        return htable
    
    def getFinalReportTableData(self, query):
        '''get reporttable where user has selected both household and personal characteristics as output criteria'''
        self.database.open()
        reporttable = self.database.execSelectQuery( query )
        self.database.close()
        return reporttable
    def getHouseholdsForReport(self):
        pass