Today, I present a web application with a list of selected movies (about 3,000 titles) I extracted using developer APIs from IMDB and OMDB databases using my Python app. After extraction in the app, I reshape the data according to my needs and then export programmatically to XLSX format (for Excel). Which is then wrapped into a webapp and database using Caspio platform. Finally, the solution is hosted in the Cloud as the following page (link below). This started out as a personal project but then turned into a fun, technical know-how project, nevertheless, fully usable now for anyone to use.
Search by title, director, genre, or country of origin, or any combination (the values from the fields are AND-ed). If you want all results, just leave the fields blank and click SEARCH. Don’t worry if you don’t remember the full name or spelling for any of the field values, it will do partial matching and bring you all the possible matches.
You can sort the results by any column…one click sorts it descending and another click on same column sorts it in ascending order. Default is ascending by Title column.
The Reco column shows 🧡 icon for those that I have personally favorited and recommend. To see just those movies, select the checkbox titled ‘My Favorites Only’.
As this is in the cloud, you can access this database from any device including your smartphone as long as you have internet access.
Some Example Queries
Get all movies from the database. And sort by Title (or to sort by any other column, click on it). By default, list is sorted by Title in ascending order. If you click on the same column again, list is sorted in descending order.
Get movie information that has a title ‘budapest’ in it…(partial Title search)
Show me all movies where director was someone with the name ‘james cam’ (partial name entered)…
Show me all animated movies from France (by Genre AND Country)…
Show me Tony’s personal favorites (by Genre AND My Faves)…
Show me me all Western movies (by genre).
Get me movies that had ‘avatar’ in the title and was directed by someone named ‘cameron’ (by Title AND Director…both or either partial)…
Click on ‘Help’ button at top-right corner to open this guide anytime.
How it’s Done (the techy part)
For the curious and geeky minds this is how it’s done…
- First, get an API key to IMDB movie database. They offer free keys with some limitations, but I am a patron so I donate to them to remove any restrictions.
- Once I have the key, I can make the http requests either through Excel or any other programming language.
- That gives me a JSON response back—if I send the query URL to server exactly as it wants! There are a couple of ways to get movie information fro there, either by title or by ID. For title, the format is: http://www.omdbapi.com/?t=<title>&apikey=<APIKEY>
- Obviously, the <title> parameter has to be escaped and dealt with for space and other characters (as some movies have ‘?’, ‘!’, and other characters) accordingly. So a formatted url may look like this: “http://www.omdbapi.com/?t=%22Blade Runner 2049%22&apikey=APIKEY”
- That’s all good but fine but I’m not going to format every request manually this way! Since there’s a repeatable pattern, I can easily automate/program it.
- I have thousands of movie information to pull in…so why not build my own request URL formattor? I can type just the titles as many as I want and each one will be automatically formatted to create the request URL for the API. Excel is perfect for it. Of course, I could program it in another language/tool but I also want to build a local dataset (before I put it in the Cloud…more on that soon).
- Now, I created an Excels workbook where I can just type in a title of a movie and it’ll generate a perfectly formatted URL for API request. I can do this for hundreds and thousands of them and save the file.
- Next, I wrote a Python program that takes an array of the formatted URLs (from Excel file created above) and sends each request to the IMDB server to get response, which are JSON loads. Perfect. The JSON response includes everything anyone would care to know about a movie including its cast, plot, language, distribution format (DVD, blu-ray, stream, etc.), revenues generated, director(s), genre, country of origin, ratings by multiple systems and on and on.
- My Python code decides which parts of that vast information to parse and deal with. Then, after extracting those desired information pieces, it exports all that information (movie by movie) to an Excel file. Now I have a full dataset.
- Ok, I have all the dataset locally, but it’s not good enough for my goal. I want to share it publicy to the Cloud so that anyone can search for movies and get information. Additionally, on the local dataset, I also added column to indicate which ones are my personal favorites. Excel is not good at sharing on the Cloud with people who doesn’t have Excel installed in their machines. I want this to be pure html that can be accessed by ANYONE without Excel including using any smartphone or any device!
- Next step is to build a web app using the dataset then. That’s where I used low-code Caspio platform to do just that. The Excel dataset is imported into it, then I generated the appropriate tables and queried, and built the search interface to execute the queries. The results are highly interactive and intuitive. Once tested locally, it’s time to deploy.
- To deploy to the Cloud, I import/replicate the required files to my domain (FlyingSalmon.net) and create a HTML page with script in it to embed the code to display the web application inside it (one-time task).
And that’s how it was done from start to finish. Hope you found it interesting and useful. Now that the foundation is done, adding new titles or updating it easy and takes very little time. For example, to add 10 more titles and my own ratings, I just import my Excel sheet (as outputted in step 9) into the Caspio Cloud platform and just append the new records to the existing table(s). Refresh and done!