def get_by_key(cls, key):
     session = DBSession()
     if py_ver == 2:
         the_key = (key or b'').encode('utf-8')
     else:
         the_key = str(key or b'', 'utf-8')
     return session.query(cls).filter(cls.key==the_key).first()

def init_db(path='eta.db'):
    """Initialze database
    """
    engine = create_engine("sqlite:///%s" % path)
    Base.metadata.create_all(engine)
    DBSession.configure(bind=engine)
    log.info("Initialized database %s", path)

 def FlushofGamelist(self):
     
     ''' No.1 clear table of gamelist '''
     localgamelist = DBSession.query(GameList).all()
     
     if len(localgamelist) != 0:
         for eachlist in range(len(localgamelist)):
             DBSession.delete(localgamelist[eachlist])
             
     DBSession.commit()
     
     ''' No.2 analyst Config and create conn '''
     self.getConfig = readFromConfigFile().get_config_zonelist('/WebserviceInterface/ServiceConfig/setting.ini')
     
     for eachTuple in range(len(self.getConfig['Zonelist'])):
         if self.getConfig['Zonelist'][eachTuple][0] == 'username':
             self.change['username'] = self.getConfig['Zonelist'][eachTuple][1]
         elif self.getConfig['Zonelist'][eachTuple][0] == 'password':
             self.change['password'] = self.getConfig['Zonelist'][eachTuple][1]
         elif self.getConfig['Zonelist'][eachTuple][0] == 'ipaddress':
             self.change['ipaddress'] = self.getConfig['Zonelist'][eachTuple][1]
         elif self.getConfig['Zonelist'][eachTuple][0] == 'port':
             self.change['port'] = int(self.getConfig['Zonelist'][eachTuple][1])
         elif self.getConfig['Zonelist'][eachTuple][0] == 'dbname':
             self.change['dbname'] = self.getConfig['Zonelist'][eachTuple][1]
         elif self.getConfig['Zonelist'][eachTuple][0] == 'tablename':
             self.change['tablename'] = self.getConfig['Zonelist'][eachTuple][1]
     
     (conn, cursor) = Connect().create(self.change)
     
     sql = 'select * from %s' % self.change['tablename']
     cursor.execute(sql)
     result=cursor.fetchall()
     for i in result:
         print i

def create_entry(data):
    """Write data to database
    """
    data = map_data(data)
    entry = Entry(date=datetime.now(), **data)
    DBSession.add(entry)
    DBSession.commit()
    log.debug("Write entry %r", data)

    def auth(cls, username, password):
        session = DBSession()
        u = session.query(cls).filter(cls.username==username).first()
        if not u:
            return False
        password_md5 = md5(password.encode('utf-8')).hexdigest()
        password_final = md5((password_md5 + u.salt).encode('utf-8')).hexdigest()

        if u.password == password_final:
            return u

 def set_password(self, new_password):
     salt = random_str()
     password_md5 = md5(new_password.encode('utf-8')).hexdigest()
     password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
     
     session = DBSession()
     self.salt = salt
     self.password = password_final
     session.add(self)
     session.commit()

 def flush(self, oracleCursor):
     
     # execute in oracle VIEW
     oracleCursor.execute('select * from MEP_ASSETREPORT')
     self.all = self.cursor.fetchall()
     
     for eachline in range(len(self.all)):
         DBSession.add(ASSET(eachline, self.all[eachline][1], self.all[eachline][2], self.all[eachline][3], self.all[eachline][4], self.all[eachline][5], self.all[eachline][6], self.all[eachline][7], self.all[eachline][8], self.all[eachline][9], self.all[eachline][10], self.all[eachline][11], self.all[eachline][13], self.all[eachline][14], self.all[eachline][15], self.all[eachline][17], self.all[eachline][22]))
     
     DBSession.commit()

    def new(cls, username, password):
        salt = random_str()
        password_md5 = md5(password.encode('utf-8')).hexdigest()
        password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
        level = USER_LEVEL.ADMIN if cls.count() == 0 else USER_LEVEL.NORMAL  # 首个用户赋予admin权限
        the_time = int(time.time())

        session = DBSession()
        ret = cls(username=username, password=password_final, salt=salt, level=level, key=random_str(32),
                          key_time = the_time, reg_time = the_time)
        session.add(ret)
        session.commit()
        return ret

    def new(cls, username, password):
        username = username.lower()
        salt = random_str()
        password_md5 = md5(password.encode('utf-8')).hexdigest()
        password_final = md5((password_md5 + salt).encode('utf-8')).hexdigest()
        state = USER_STATE.ADMIN if cls.count() == 0 else USER_STATE.NORMAL  # first user is admin
        the_time = int(time.time())

        session = DBSession()
        ret = cls(username=username, password=password_final, salt=salt, state=state, key=random_str(32),
                          key_time = the_time, reg_time = the_time)
        session.add(ret)
        session.commit()
        return ret

    def Recovery(self):
        
        localAsset = DBSession.query(ASSET).all()

        if len(localAsset) > 100:
            for eachAsset in range(len(localAsset)):
                DBSession.delete(localAsset[eachAsset])
            DBSession.commit()
            
            self.flush(self.cursor)
        else:
            self.flush(self.cursor)
            
        self.cursor.close() 
        self.OracleConn.close()

def add_article(request):
    if 'form.submitted' in request.POST:
        with transaction.manager:
            article = Article(
                title=request.POST['title'],
                body=request.POST['body']
            )
            DBSession.add(article)

        return HTTPFound(
            location=route_url('article_list', request)
        )
    else:
        return render_to_response(
            'templates/add_article.pt',
            {'back_url': route_url('article_list', request)},
            request=request
        )

    def searchObject(self, machineroom, requestobject):
        
        try:
            allsearch = DBSession.query(ASSET).filter(and_(ASSET.partNum.like("%"+requestobject+"%"), ASSET.machineRoom.like("%"+machineroom+"%"), ASSET.useNow == 'Y')).all()

            for eachAsset in range(len(allsearch)):
                self.assetBack[allsearch[eachAsset].id] = dict(confNum=allsearch[eachAsset].confNum, serialNum=allsearch[eachAsset].serialNum, hostname=allsearch[eachAsset].hostname, companyIp=allsearch[eachAsset].companyIp, outIp=allsearch[eachAsset].outIp, storeIp=allsearch[eachAsset].storeIp, zcbm=allsearch[eachAsset].zcbm)
             
        except Exception, e:
            return dict(Status='false', Return=e)

 def getDatabase(self):
 
     searchLocal = DBSession.query(ASSET).all()
     if len(searchLocal) > 100:
         print "local table.asset has data. needn't."
     else:
         self.flush(self.cursor)
         
     self.cursor.close()
     self.OracleConn.close()

def main(global_config, **settings):
    config = Configurator(settings=settings)
    config.add_route('article_list', '/')
    config.add_route('show_article', '/article/{article}')
    config.add_route('add_article', '/add_article')
    config.scan()

    # SQL Alchemy stuff.
    engine = engine_from_config(settings, 'sqlalchemy.')
    DBSession.configure(bind=engine)
    try:
        Base.metadata.create_all(engine)
        with transaction.manager:
            article = Article(title='Test article', body="Test test test")
            DBSession.add(article)
    except IntegrityError:
        print "Skipping creating, integrity error was thrown"

    return config.make_wsgi_app()

    def analyNode(self, AgentZone):

        try:
            getNodeInform = DBSession.query(NodeList).filter_by(NodeZone = AgentZone).all()
            if getNodeInform:
                for eachNodeInform in range(len(getNodeInform)):
                    self.Nodeinform[getNodeInform[eachNodeInform].NodeID]=dict(NodeType=getNodeInform[eachNodeInform].NodeType, NodeZone=getNodeInform[eachNodeInform].NodeZone, NodeName=getNodeInform[eachNodeInform].NodeName, IP=getNodeInform[eachNodeInform].IP, Port=getNodeInform[eachNodeInform].Port)
            else:
                msg = 'MySQLdb : select from table.nodelist error.'
                return msg
      
        except Exception, e:
            return e

    def flushAgent(self,  machineroomSimple, Agent='Agent'):
        
        try:
            getSearchAgent = DBSession.query(Translate).filter_by(simple = Agent).first()
            AgentSimple = getSearchAgent.detail
            
            getSearchMachineroom = DBSession.query(Translate).filter_by(simple = machineroomSimple).first()
            MrDetail = getSearchMachineroom.detail
            
            getAgentList = AssetInformation().searchObject(MrDetail, AgentSimple)

            print "######## LEN ", len(getAgentList['Return'])

            for key,value in getAgentList['Return'].items():
                AgentID = key
                AgentZone = machineroomSimple
                AgentName = value['hostname']
                IP = self.iprule(key, machineroomSimple, value['companyIp'], value['outIp'], value['storeIp'])
                Port = 'NULL'
                IsUse = 1
            
            
        except Exception, e:
            return e

    def getFlushoftable(self, signal='ALL'):
        
        # No.1 clear table of gamename
        localGameName = DBSession.query(Gameinform).all()
        
        if len(localGameName) != 0:
            for eachGamename in range(len(localGameName)):
                DBSession.delete(localGameName[eachGamename])
        
        DBSession.commit()
       
        # No.2 get each Game information & fill in table
       
        self.flush = Urlex().getInformationMultiple(signal)
    
        for key,value in self.flush.items():
            if key != 'NULL':
                DBSession.add(Gameinform(self.count, key, value['ipaddress'], value['port'], value['dbname']))
                self.count += 1

        DBSession.commit()

def show_article(context, request):
    article = {}
    db_result = DBSession.query(Article).filter(
        Article.id == context.article
    ).first()

    if not db_result:
        raise HTTPNotFound(
            "Article could not be found"
        )

    article['id'] = db_result.id
    article['title'] = db_result.title
    article['body'] = db_result.body

    return {
        'article': article,
        'back_url': route_url('article_list', request)
    }

def article_list(request):
    results = []
    db_results = DBSession.query(Article).all()

    for db_result in db_results:
        results.append({
            'id': db_result.id,
            'url': route_url(
                'show_article',
                request,
                article=str(db_result.id)
            ),
            'title': db_result.title
        })

    return {
        'articles': results,
        'add_article': route_url('add_article', request)
    }

 def FromConfNum(self, confNum):
     
     Attitude = ""
     ServerInform = {}
     Internet = {}
     Device = {}
     
     try:
         self.allLineofConfnum = DBSession.query(ASSET).filter_by(confNum = confNum).first()
         print self.allLineofConfnum
         
         if self.allLineofConfnum:
             
             # All information collect
             Attitude = self.allLineofConfnum.useNow
                   
             Device['confNum'] = self.allLineofConfnum.confNum
             Device['partNum'] = self.allLineofConfnum.partNum
             Device['serialNum'] = self.allLineofConfnum.serialNum
             Device['zcbm'] = self.allLineofConfnum.zcbm
             Device['machineRoom'] = self.allLineofConfnum.machineRoom
             
             ServerInform['useProperty'] = self.allLineofConfnum.useProperty
             ServerInform['conProject'] = self.allLineofConfnum.conProject
             ServerInform['username'] = self.allLineofConfnum.username
             ServerInform['userId'] = self.allLineofConfnum.userId
             ServerInform['usage'] = self.allLineofConfnum.usage
             
             Internet['hostname'] = self.allLineofConfnum.hostname
             Internet['companyIp'] = self.allLineofConfnum.companyIp
             Internet['outIp'] = self.allLineofConfnum.outIp
             Internet['storeIp'] = self.allLineofConfnum.storeIp
             
             return dict(State=Attitude, Device=Device, ServerInform=ServerInform, Internet=Internet)
             
         else:
             msg = 'MySQLdb : could not found any confNum.'
             return msg
     
     except Exception, e:
         msg = e
         return msg