This is a Python module I made to search the Rotten Tomatoes movie API. It caches the search results in a SQLite database.

What improvements can I make to the cache system? Is there a better way to store the results of the API search?

Is there a better way to communicate with the API?

import urllib2
import json
import sqlite3
import time

# sqlite database file
cache_database = "movies.db"
# how many seconds before the entry expires 
cache_expiration = 60*60  # one hour

class Cache:
    def get_conn(self):
        """
        connect with the sqlite database
        """
        conn = sqlite3.connect(cache_database)
        c = conn.cursor()
        c.execute(""" CREATE TABLE IF NOT EXISTS movies(
                        search_query TEXT PRIMARY KEY,
                        page_number INT,
                        timestamp INTEGER,
                        search_results BLOB); """)
        return conn

    def get(self, search_query, page_number):
        """
        get the search results from the database
        """
        with self.get_conn() as conn:
            c = conn.cursor()
            query = """SELECT search_results FROM movies WHERE 
                       search_query = \"{search_query}\" AND 
                       page_number = {page_number} AND 
                      (strftime('%s', 'now') - timestamp) < {cache_expiration}"""

            query = query.format(search_query = search_query,
                                 page_number = page_number,
                                 cache_expiration = cache_expiration)
            results = "" 
            for a in c.execute(query):
                results = a
            return results

    def put(self, search_query, page_number, search_results):
        """
        put the results into the database
        """
        timestamp = int( time.time() )
        with self.get_conn() as conn:
            c = conn.cursor()
            insert = """ INSERT OR REPLACE INTO movies
                         (search_query, page_number, timestamp, search_results) VALUES
                        (\"{search_query}\", {page_number}, {timestamp}, ?); """

            insert = insert.format(search_query = search_query,
                                   page_number = page_number,
                                   timestamp = timestamp)
            c.execute(insert, (search_results,))
            conn.commit()

class Movie:
    
    api_key = ""
    userAgent = "MovieInfoBot/1.0"

    def search(self, query, results_per_page=25, page_number=1):
        """
        searches for movies: movie name, result limit, page number
        """
        cache = Cache()
        result = cache.get(query, page_number)
        if result != "":
            print "using cache"
            movie_json = result[0]
        else:
            print "using web"
            #format the url
            base_url = "http://api.rottentomatoes.com/api/public/v1.0/movies.json"
            url = "{base_url}?apikey={api_key}&q={search_term}&page_limit={results_per_page}&page={page_number}"
            param = {}
            param["base_url"] = base_url
            param["api_key"] = self.api_key
            param["search_term"] = urllib2.quote(query.encode("utf8"))
            param["results_per_page"] = results_per_page
            param["page_number"] = page_number
            url = url.format(**param)
            req = urllib2.Request(url, headers={ 'User-Agent': self.userAgent })
            movie_json = urllib2.urlopen(req).read()
            
            # put the results into the movie cache
            cache.put(query, page_number, movie_json)
            
        movie_dict = json.loads(movie_json)
        return movie_dict


movie = Movie()
movie.api_key = "xua5v8mbvermd2t9v5gyeaua"
data = movie.search("the wizard of oz")

print json.dumps(data, indent=4)

Here is the module on GitHub https://github.com/kylelk/Rotten-Tomatoes