query manager example

query.py

TABLES = {
    'client',
    'organization',
    'employee',
    'flat_email',
}

class QueryManager(object):
    
    def __init__(self, sql, engine):
        self.sql = sql
        self.engine = engine
        self.conn = None
        self.cursor = None
    
    def execute(self, *args, **kwargs):
        self.conn = self.engine.connect()
        self.cursor = self.conn.cursor()
        self.cursor.execute(self.sql, *args, **kwargs)
    
    def close(self):
        if self.cursor: self.cursor.close()
        if self.conn: self.conn.close()
        
        self.cursor = None
        self.conn   = None
    
    def rows(self, *args, **kwargs):
        if not self.cursor:
            self.execute(*args, **kwargs)    
        
        for row in self.cursor.fetchall():
            yield row
        
        self.close()
    
    def __iter__(self):
        for row in self.rows():
            yield row
        
def query_factory(sql, engine):
    def factory():
        return QueryManager(sql, engine)
    return factory

CLIENTS_QUERY = """
    SELECT * FROM client
        WHERE is_active='t' AND id not in %(ignored_ids)s
        ORDER BY id;
"""
clients_query = query_factory(CLIENTS_QUERY, engine)

for row in clients_query().rows({"ignored_ids":ignored_ids}):
    print row