Using the Rotten Tomatoes API 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) &lt; {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}&amp;q={search_term}&amp;page_limit={results_per_page}&amp;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
It's a pretty nice script! (You should open-source it, I would use it.)

Prepared statements

You should use prepared statements in your queries. It's not only easier to write, it's also more efficient. For example:

def get(self, search_query, page_number):
    with self.get_conn() as conn:
        c = conn.cursor()
        query = """SELECT search_results FROM movies
                   WHERE search_query = ?
                   AND page_number = ?
                   AND strftime('%s', 'now') - timestamp &lt; ?; """

        c.execute(query, (search_query, page_number, cache_expiration))
        return c.fetchone()


Notice that you don't need to worry about quoting anymore.

You can do the same thing to your Cache.put method too:

def put(self, search_query, page_number, search_results):
    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 (?, ?, ?, ?); """

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


Statements can be simplified

Instead of this:

results = "" 
for a in c.execute(query):
    results = a
return results


It would be simpler and cleaner like this:

c.execute(query)
return c.fetchone()


Unless, you really want to iterate over multiple results and return only the last one, as it was in your original code.

Python classes

Modern classes should extend object, for example:

class Cache(object):
    ...

class Movie(object):
    ...


Coding style

You can simplify this:

if result != "":
    ...


like this:

if result:
    ...


And it's good to follow PEP8, the official Python style guide.