Python Database.execSelectQuery方法代码示例

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def getProjectsMatching(self,  pid="",  ptitle=""):
     SQLcondition 	= ""
     if ( pid != "" ):
         SQLcondition = " WHERE pid=%s" % pid
     
     if ( ptitle != "" ):
         if ( SQLcondition == "" ):
             SQLcondition = " WHERE projectname LIKE '%" + "%s" % ( ptitle ) + "%'" 
         else:
             SQLcondition = SQLcondition + " OR projectname LIKE '%" + "%s" % ( ptitle ) + "%'" 
             
     query = ''' SELECT pid FROM projects%s''' % ( SQLcondition )
     
     database = Database()
     database.open()
     rows = database.execSelectQuery(  query )
     database.close()
     
     projects = []
     for row in rows:
         pid = row[0]
         project = Project(pid)
         projects.append( project )
     
     return projects

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportHouseholds(self, project,  filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, householdname, dateofcollection FROM households
                   WHERE pid=%s''' % project.pid
                   
     households = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for household in households:
         householdline = '''INSERT INTO households(pid,hhid,householdname,dateofcollection) 
                 VALUES({pid},%s, '%s', '%s')<endl>\n''' % (household[0], household[1], household[2])
         ihmFile.write(householdline)
     ihmFile.close()
     
     self.exportHouseholdCharacteristics(project, filename)
     self.exportHouseholdAssets(project, filename)
     self.exportHouseholdExpenditure(project, filename)
     self.exportHouseholdCropIncome(project, filename)
     self.exportHouseholdLivestockIncome(project, filename)
     self.exportHouseholdWildfoodsIncome(project, filename)
     self.exportHouseholdEmploymentIncome(project, filename)
     self.exportHouseholdTransfersIncome(project, filename)
     self.exportHouseholdMembers(project, filename)

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
class GlobalHouseholdCharacteristicsManager:
    def __init__(self):
        self.database = Database()

    def getGlobalHouseholdCharacteristic(self, charid=0, charname=""):
        char = GlobalHouseholdCharacteristic(charid, charname)
        return char

    def addGlobalHouseholdCharacteristic(self, charname, datatype):
        char = GlobalHouseholdCharacteristic(0, charname, datatype)
        return char

    def editGlobalHouseholdCharacteristic(self, charid, charname, datatype):
        char = GlobalHouseholdCharacteristic(charid)
        char.setData(charname, datatype)

    def delGlobalHouseholdCharacteristic(self, charid="", charname=""):
        query = "DELETE FROM globalhouseholdcharacteristics WHERE id=%i OR characteristic='%s' " % (charid, charname)
        self.database.open()
        self.database.execUpdateQuery(query)
        self.database.close()

    def getGlobalHouseholdCharacteristics(self):
        query = "SELECT id FROM globalhouseholdcharacteristics"
        self.database.open()
        rows = self.database.execSelectQuery(query)
        self.database.close()
        chars = []

        for row in rows:
            charid = row[0]
            char = GlobalHouseholdCharacteristic(charid)
            chars.append(char)

        return chars

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.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 model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def getGlobalPersonCharacteristics(self):        
    query = "SELECT id FROM globalpersonalcharacteristics"
    database = Database()
    atabase.open()
    rows = database.execSelectQuery( query )
    database.close()
    chars = []
    
    for row in rows:
        charid = row[0]
        char = GlobalPersonCharacteristic(charid)
        chars.append( char )
        
    return chars

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def getProjects(self):       
     query = "SELECT pid FROM projects"
     database = Database()
     database.open()
     rows = database.execSelectQuery( query )
     database.close()
     projects = []
     
     for row in rows:
         pid = row[0]
         project = Project(pid)
         projects.append( project )
         
     return projects

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportProjectIncomeSources(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT incomesource, incometype FROM projectincomesources
                   WHERE pid=%s''' % project.pid
                   
     incomes = database.execSelectQuery( query )
     
     database.close()
     ihmFile = open(filename, 'a')
     for inc in incomes:
         incomeline = '''INSERT INTO projectincomesources(pid,incomesource,incometype) 
         VALUES({pid},'%s','%s')<endl>\n''' % (inc[0], inc[1])
         ihmFile.write(incomeline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def delCorrespondingIhmProject(self, projectname, startdate,  currency):
     ''' Delete if the project was transfered before '''
     
     query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid  
                  AND transferlog.projectname='%s' AND transferlog.datecollected='%s' 
                  AND transferlog.currency='%s' ''' % (projectname,  startdate, currency)
                  
     db = Database()
     db.open() 
     records = db.execSelectQuery( query )
     for record in records:
         pid = record[0]
         query = '''DELETE FROM projects WHERE pid=%s''' % pid
         db.execUpdateQuery( query )
    
     db.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def existsCorrespondingIhmProject(self, projectname, startdate,  currency):
     ''' Checks if the project was transfered before '''
     
     query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid
                  AND transferlog.projectname='%s' AND transferlog.datecollected='%s' 
                  AND transferlog.currency='%s' ''' % (projectname,  startdate, currency)
                  
     db = Database()
     db.open() 
     records = db.execSelectQuery( query )
     
     exists = False
     if len(records) == 1:
         exists = True
    
     db.close()
     return exists

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportProjectAssets(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT assetname, assettype FROM projectassets
                   WHERE pid=%s''' % project.pid
                   
     assets = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for asset in assets:
         assetline = '''INSERT INTO projectassets(pid,assetname,assettype) 
         VALUES({pid},'%s','%s')<endl>\n''' % (asset[0], asset[1])
         ihmFile.write(assetline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportHouseholdCropIncome(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome
                   WHERE pid=%s''' % project.pid
                   
     crops = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for crop in crops:
         cropline = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
             VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (crop[0],  crop[1], crop[2], crop[3], crop[4], crop[5], crop[6], crop[7])
         ihmFile.write(cropline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportProjectStandardOfLiving(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT summary, scope, gender, agebottom, agetop, item, costperyear FROM standardofliving
                   WHERE pid=%s''' % project.pid
                   
     stdLvs = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for stdLv in stdLvs:
         stdLvline = '''INSERT INTO standardofliving (pid, summary, scope, gender, agebottom, agetop, item, costperyear )
             VALUES({pid},'%s','%s','%s',%s,%s,'%s',%s)<endl>\n''' % (stdLv[0], stdLv[1], stdLv[2], stdLv[3], stdLv[4], stdLv[5], stdLv[6])
         ihmFile.write(stdLvline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportHouseholdEmploymentIncome(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome FROM employmentincome
                   WHERE pid=%s''' % project.pid
                   
     items = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for item in items:
         empline = '''INSERT INTO employmentincome(pid, hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome)
             VALUES({pid},%s,'%s','%s','%s',%s,%s,%s)<endl>\n''' % (item[0],  item[1], item[2], item[3], item[4], item[5], item[6])
         ihmFile.write(empline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportHouseholdWildfoodsIncome(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM wildfoods
                   WHERE pid=%s''' % project.pid
                   
     items = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for item in items:
         wfline = '''INSERT INTO wildfoods(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
             VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (item[0],  item[1], item[2], item[3], item[4], item[5], item[6], item[7])
         ihmFile.write(wfline)
     ihmFile.close()

# 需要导入模块: from model.database import Database [as 别名]
# 或者: from model.database.Database import execSelectQuery [as 别名]
 def exportProjectDiet(self, project, filename):
     database = Database()
     database.open()
     
     query = '''SELECT fooditem, unitofmeasure, percentage, priceperunit FROM diet
                   WHERE pid=%s''' % project.pid
                   
     diets = database.execSelectQuery( query )
     
     database.close()
     
     ihmFile = open(filename, 'a')
     for diet in diets:
         dietline = '''INSERT INTO diet (pid, fooditem,unitofmeasure,percentage, priceperunit )
                     VALUES({pid},'%s','%s',%s,%s)<endl>\n''' % (diet[0], diet[1], diet[2], diet[3])
         ihmFile.write(dietline)
     ihmFile.close()