Example Pyramid Shell Script ( using a custom api )

_api.py

from pyramid.paster import bootstrap
from pyramid.request import Request
from pyramid.scripting import prepare as prepare_request

import MYAPP
import MYAPP.lib.handlers

import sqlalchemy
import time

class IntegratedApi():
    env = None
    bootstrapped = None
            
    def __init__( self, ini_file="production.ini" ):
        self.env = bootstrap( '../../../web-pylons/MYAPP/%s' % ini_file )
        self.setup_handler()
    
    def setup_handler(self):
        self.bootstrapped = MYAPP.lib.handlers.HandlerBootstrapped( self.env['request'] )

    def finish(self):
        """DEPRECATED"""
        self.request_finish()
        
    def request_new(self,request=None):
        app = self.env['app']
        new_env = prepare_request(request)
        new_env['app'] = app
        self.env = new_env  
        self.setup_handler()

    def request_finish(self):
        self.env['closer']()
        self.request_new()




def column_windows(session, column, windowsize):
    """Return a series of WHERE clauses against 
    a given column that break it into windows.

    Result is an iterable of tuples, consisting of
    ((start, end), whereclause), where (start, end) are the ids.
    
    Requires a database that supports window functions, 
    i.e. Postgresql, SQL Server, Oracle.

    Enhance this yourself !  Add a "where" argument
    so that windows of just a subset of rows can
    be computed.

    """
    def int_for_range(start_id, end_id):
        if end_id:
            return sqlalchemy.and_(
                column>=start_id,
                column<end_id
            )
        else:
            return column>=start_id

    q = session.query(\
                column,
                sqlalchemy.func.row_number()\
                    .over(order_by=column)\
                    .label('rownum')
                )\
                .from_self(column)
    if windowsize > 1:
        q = q.filter("rownum %% %d=1" % windowsize)

    intervals = [id for id, in q]

    while intervals:
        start = intervals.pop(0)
        if intervals:
            end = intervals[0]
        else:
            end = None
        yield int_for_range(start, end)


def windowed_query(q, column, windowsize, print_iteration=False, commit=True ):
    """"Break a Query into windows on a given column."""

    i = 0
    for whereclause in column_windows(
                                        q.session, 
                                        column, windowsize):
        i += 1
        t_start = time.time()
        if print_iteration:
            print "windowed_query.%s - start" % i
        for row in q.filter(whereclause).order_by(column):
            yield row
        if commit:
            q.session.commit()
        if print_iteration:
            print "windowed_query.%s - time | %s" % ( i , (time.time() - t_start) )

sample_script.py

from _api import IntegratedApi
from _api import windowed_query

import pdb
import sqlalchemy

import MYAPP
from MYAPP import models as model
from MYAPP import lib

import logging
import pprint
import re

import simplejson as json
import time


api = IntegratedApi()

## my app attaches a dbSession onto request

useraccounts = api.bootstrapped.request.dbSession.writer.query( model.core.Useraccount )
    .filter(\
        model.core.Useraccount.id.in_( 1 , 2, 3 ),
    )\
    .all()


for user in useraccounts :
    print "--------"
    print user.username

    ## commit if you're not using transaction
    api.bootstrapped.request.dbSession.writer.commit()

## cleanup
api.finish()



## maybe we need to do a bunch of requests?

for i in range( 1, 100 ):
    ## do soomething
    
    ## cleanup routine also creates a new , fresh request for the next iteration
    ## commit needed if you're not using transaction, otherwise transaction should run
    api.finish()