Example #1
0
    def __init__(self,mode="ignore",infile=None):

        if infile is None:
            infile = openfilecreate('Choose a project file for importing','XML files (*.xml)|*.xml',
                           style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST)
            if infile is None:
                return None

        (conn, cursor) = openconnection()
        #
        # get the highest project number so far in the database, add 1, and assign to the
        # imported project

#......................................................................
# ask for confirmation before causing a major desaster

        dialog =  DialogImport(None,_U("import data"),\
                               _U("This will modify your databases\nIf You are sure, specify what to do with duplicate data")+\
                               _U("\nElse press CANCEL"))
        ret = dialog.ShowModal()
        if ret == wx.ID_OK:
            mode = "overwrite"
        elif ret == wx.ID_IGNORE:
            mode = "ignore"
        else:
            return

        logTrack("ImportDB in mode %s"%mode)

        # create a dom and import in it the xml project file
        self.document = xml.dom.minidom.parse(infile)
        # get the elements from the DOM
        dbs = self.document.getElementsByTagName('EinsteinDataBase')
        for db in dbs:
            tables = db.getElementsByTagName("table")
            tabledict = {}
            for table in tables:
                tablename =  str(table.getAttribute('name'))
                tablelist = []
                rows = table.getElementsByTagName("row")
                for row in rows:
                    sqlist = []
                    sqldict = {}
                    newdict = {}
                    nrow =  row.getAttribute('n')
                    elements = row.getElementsByTagName("element")

                    rowName = None #characteristic name of the row
                    rowPar1 = None
                    rowPar2 = None
                    
                    for element in elements:
                        fieldname = element.getAttribute('name').lower()
                        fieldnameCAPS = str(element.getAttribute('name'))
                        fieldnameCapitalLetters = element.getAttribute('name')
                        eltype = element.getAttribute('type')
                        elauto = element.getAttribute('auto')
                        elvalue = element.getAttribute('value')

                        if tablename == "dbfluid" and fieldname == "fluidname":
                            rowName = elvalue
                        elif tablename == "dbfuel" and fieldname == "fuelname":
                            rowName = elvalue
                        elif tablename == "auditor" and fieldname == "name":
                            rowName = elvalue
                        elif tablename == "dbelectricitymix":
                            if fieldname == "country":
                                rowName = elvalue
                            elif fieldname == "year":
                                rowPar1 = elvalue
                        elif tablename == "dbboiler":
                            if fieldname == "boilermanufacturer":
                                rowName = elvalue
                            elif fieldname == "boilermodel":
                                rowPar1 = elvalue
                        elif tablename == "dbchp":
                            if fieldname == "chpequip":
                                rowName = elvalue
                        elif tablename == "dbheatpump":
                            if fieldname == "hpmanufacturer":
                                rowName = elvalue
                            elif fieldname == "hpmodel":
                                rowPar1 = elvalue
                        elif tablename == "dbsolarthermal":
                            if fieldname == "stmanufacturer":
                                rowName = elvalue
                            elif fieldname == "stmodel":
                                rowPar1 = elvalue

                        if elauto != 'auto_increment':
                            newdict.update({fieldnameCapitalLetters:elvalue})

                        # substitute invalid chars in char fields and enclose in ''
                        if eltype.startswith('char') or \
                           eltype.startswith('varchar') or \
                           eltype.startswith('text'):
                            elvalue = elvalue.encode("utf-8")

                        if eltype.startswith('date'):
                            elvalue = "'" + self.subsIllegal(elvalue) + "'"
                        # substitute auto-increment value with NULL
                        if elauto == 'auto_increment':
                            # main key field
                            oldKey = int(elvalue)
                            elvalue = 'NULL'

#                        sqlist.append("%s=%s" % (fieldname,elvalue))
                        sqldict.update({fieldnameCAPS:elvalue})

#......................................................................
# before inserting new entry, check if entry with the same name exists
# (only for questionnaire, dbfluid, dbfuel

                    ignoreRow = False
                    existingRows = []
                    if tablename == "dbfluid":
                        existingRows = Status.DB.dbfluid.FluidName[check(rowName)]

                    elif tablename == "dbfuel":
                        existingRows = Status.DB.dbfuel.FuelName[check(rowName)]

                    elif tablename == "auditor":
                        existingRows = Status.DB.auditor.Name[check(rowName)]

                    elif tablename == "dbelectricitymix":
                        if rowPar1 is None:
                            existingRows = Status.DB.dbelectricitymix.Country[check(rowName)]
                        else:
                            existingRows = Status.DB.dbelectricitymix.Country[check(rowName)].Year[rowPar1]
                            
                    elif tablename == "dbboiler":
                        if rowPar1 is None:
                            existingRows = Status.DB.dbboiler.BoilerManufacturer[check(rowName)]
                        else:
                            existingRows = Status.DB.dbboiler.BoilerManufacturer[check(rowName)].BoilerModel[check(rowPar1)]
                            
                    elif tablename == "dbchp":
                        existingRows = Status.DB.dbchp.CHPequip[check(rowName)]
                            

                    elif tablename == "dbheatpump":
                        if rowPar1 is None:
                            existingRows = Status.DB.dbheatpump.HPManufacturer[check(rowName)]
                        else:
                            existingRows = Status.DB.dbheatpump.HPManufacturer[check(rowName)].HPModel[check(rowPar1)]
                            
                    elif tablename == "dbsolarthermal":
                        if rowPar1 is None:
                            existingRows = Status.DB.dbsolarthermal.STManufacturer[check(rowName)]
                        else:
                            existingRows = Status.DB.dbsolarthermal.STManufacturer[check(rowName)].STModel[check(rowPar1)]
                            
   
                    else:
                        existingRows = []

                    if len(existingRows) > 0:
                        if rowPar2 is None:
                            par2 = ""
                        else:
                            par2 = " " + str(rowPar2)

                        if rowPar1 is None:
                            par1 = ""
                        else:
                            par1 = " "+str(rowPar1)

                        if mode == "overwrite":
                            existingRows[0].update(newdict)
                            logWarning("%s: %s%s%s"%(tablename,check(rowName),par1,par2)+\
                                       _U(" already in database. Data from imported file will be updated"))
                        else:
                            logWarning("%s: %s%s%s"%(tablename,check(rowName),par1,par2)+\
                                       _U(" already in database. Data from imported file will be ignored"))
                            
                                             
                    else:
                        # create sql sentence and update database
#                        sql = 'INSERT INTO %s SET ' % (tablename,) + ', '.join(sqlist)
#                        cursor.execute(sql)

                        table = Table(Status.DB,tablename)
                        table.insert(sqldict)

                        # get last inserted
                        cursor.execute('SELECT LAST_INSERT_ID() AS last')
                        field = cursor.fetchone()
                        
                                                
        conn.close()
Example #2
0
    def __init__(self,infile=None):
        self.pid = None
        self.newpid = None

        if infile is None:
            infile = openfilecreate('Choose a project file for importing','XML files (*.xml)|*.xml',
                           style=wx.FD_OPEN | wx.FD_FILE_MUST_EXIST)
            if infile is None:
                return None

        (conn, cursor) = openconnection()
        #
        # get the highest project number so far in the database, add 1, and assign to the
        # imported project

#NOTE: a dummy row is added and later on deleted in order to obtain the auto-increment
# status of the table. there should be a more elegant way to to this ...
# => to be changed in the future ...

        dummyID = Status.DB.questionnaire.insert({"Name":"dummy"})
        Status.SQL.commit()
        cursor.execute('SELECT MAX(Questionnaire_id) AS n FROM questionnaire')

        nrows = cursor.rowcount
        if nrows <= 0:
            self.newpid = 1
        else:
            field = cursor.fetchone()
            # new pid for this project
            self.newpid = int(field['n']) + 1
        logDebug("ExportData (importProject): dummyID = %s newpid = %s"%(dummyID,self.newpid))

        dummyRows = Status.DB.questionnaire.Questionnaire_ID[dummyID]
        if len(dummyRows) > 0:
            dummyRows[0].delete()

        # create a dom and import in it the xml project file
        self.document = xml.dom.minidom.parse(infile)
        # get the elements from the DOM
        self.projectdict = {}
        projects = self.document.getElementsByTagName('EinsteinProject')
        for project in projects:
            self.pid = project.getAttribute('pid')
            tables = project.getElementsByTagName("table")
            tabledict = {}
            for table in tables:
                tablename =  str(table.getAttribute('name'))
                tablelist = []
                rows = table.getElementsByTagName("row")
                for row in rows:
                    sqlist = []
                    sqldict = {}
                    
                    nrow =  row.getAttribute('n')
                    elements = row.getElementsByTagName("element")

                    rowName = None #characteristic name of the row
                    
                    for element in elements:
                        fieldname = element.getAttribute('name').lower()
                        fieldnameCAPS = str(element.getAttribute('name'))
                        eltype = element.getAttribute('type')
                        elauto = element.getAttribute('auto')
                        elvalue = element.getAttribute('value')

                        if tablename == "questionnaire" and fieldname == "name":
                            rowName = elvalue
                            existingRows = Status.DB.questionnaire.Name[check(rowName)]
                            if len(existingRows) > 0:
                                showWarning(_U("Project with name %s already exists in database\nProject renamed to: IMPORTED PROJECT")%rowName)
                                elvalue = 'IMPORTED PROJECT'
                        elif tablename == "dbfluid" and fieldname == "fluidname":
                            rowName = elvalue
                        elif tablename == "dbfuel" and fieldname == "fuelname":
                            rowName = elvalue
                        elif tablename == "auditor" and fieldname == "name":
                            rowName = elvalue

                        # substitute new pid in id field
                        if fieldname == 'projectid' or \
                             fieldname == 'questionnaire_id':
                            elvalue = self.newpid
                        # substitute invalid chars in char fields and enclose in ''
                        if eltype.startswith('char') or \
                           eltype.startswith('varchar') or \
                           eltype.startswith('text'):

                            elvalue = elvalue.encode("utf-8")

                        if eltype.startswith('date'):
#                            elvalue = "'" + self.subsIllegal(elvalue) + "'"
                            elvalue = self.subsIllegal(elvalue)
                          
                        # substitute auto-increment value with NULL
                        if elauto == 'auto_increment':
                            # main key field
                            oldKey = int(str(elvalue))
                            elvalue = 'NULL'

#                        sqlist.append("%s=%s" % (fieldname,elvalue))
                        sqldict.update({fieldnameCAPS:elvalue})

#......................................................................
# before inserting new entry, check if entry with the same name exists
# (only for questionnaire, dbfluid, dbfuel

                    ignoreRow = False
                    if tablename == "dbfluid":
                        existingRows = Status.DB.dbfluid.FluidName[check(rowName)]
                        if len(existingRows) > 0:
                            showWarning(_U("Fluid %s already in database. Data from imported file will be ignored")%rowName)
                            ignoreRow = True
                            newID = existingRows[0].DBFluid_ID

                    elif tablename == "dbfuel":
                        existingRows = Status.DB.dbfuel.FuelName[check(rowName)]
                        if len(existingRows) > 0:
                            showWarning(_U("Fuel %s already in database. Data from imported file will be ignored")%rowName)
                            ignoreRow = True
                            newID = existingRows[0].DBFuel_ID
                    elif tablename == "auditor":
                        existingRows = Status.DB.auditor.Name[check(rowName)]
                        if len(existingRows) > 0:
                            showWarning(_U("Auditor %s already in database. Data from imported file will be ignored")%rowName)
                            ignoreRow = True
                            newID = existingRows[0].Auditor_ID
                    else:
                        existingRows = []
                                             
                    if ignoreRow == False:
                        # create sql sentence and update database
#substituted the following block by
#                        sql = 'INSERT INTO %s SET ' % (tablename,) + ', '.join(sqlist)
#                        print sql
#                        cursor.execute(sql)
                        table = Table(Status.DB,tablename)
                        newKey = table.insert(sqldict)
                                       
                        # get last inserted
                        cursor.execute('SELECT LAST_INSERT_ID() AS last')
                        field = cursor.fetchone()
# 2009-04-06: line eliminated: gave 0 as result !!! substituted by direct assignment
# of newKey (see above)
#                        newKey = int(field['last'])
                    else:
                        newKey = newID
                    tablelist.append((oldKey,newKey))
                tabledict[tablename] = tablelist
            self.projectdict[self.pid] = tabledict
        conn.close()

#####HS2008-07-14: here restoring links added

        for PId in self.projectdict.keys():
            Status.prj.restoreLinks(self.newpid,self.projectdict[PId])