Python model.DBSession類代碼示例

 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