A wrapper for queries that need to be run multiple times with different arguments in an application.

query.py

class Query(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 Query(sql, engine)
    return factory

query_orders_example.py

# An example query factory to select orders by email for a particular date range. 

import sqlite3

ORDERS_QUERY = """
  SELECT o.* FROM orders o
    JOIN customers c on o.customer_id = c.id 
  WHERE c.email=%(email)s AND o.date >= %(after)s AND o.date < %(before)s
  ORDER BY o.date DESC;
"""

class SQLiteEngine(object):
  
  def connect(self):
      return sqlite3.connect(':memory:')

orders_query = query_factory(ORDERS_QUERY, SQLiteEngine())

for row in orders_query().rows({"email":"[email protected]", "after":"2015-01-01", "before": "2015-04-01"}):
    print row