Python Database.open方法代码示例

# 需要导入模块: from data.database import Database [as 别名]
# 或者: from data.database.Database import open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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 open [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'
#.........这里部分代码省略.........