The first item that we need to cover is loading in the US.txt file into our database. This information will allow us to write some SQL queries to the database for the search and update routes. The instructions for this implementation on the CS50 page is fairly straightforward, so I will only touch on this subject. The key here is to open up the US.txt file and look at the columns in the file and the datatypes that you will need to use to store the data. For instance, you will see that there are zip codes such as 02138. Now if you were to store this as an integer the leading zero would be cut off. So, the datatype of string would be better to use in this situation. Another thing to point out is that a lot of the information in US.txt only covers 10 columns and the database requires 12. So, be sure to allow fields in your database to be nullable.
Now, let’s get on to some Python code. To implement articles we are going to use a different approach from the previous problem sets. In Finance we were passing sensitive information, so it was better to send the information in the HTTP header via the POST method. Here, we are not as concerned about the sensitivity of the information we are passing. So, we are going to pass information in the URL itself which designates the GET method. Therefore, to get the geographic location from the URL we set our variable geo equal to the argument called “geo” from the request. The if not statement tests to see if this variable is null, and if it is throws a RuntimeError. Next, we pass the geo location to the lookup function that is in the helpers.py file. Look at this function and make sure you understand what is going on there. Here, an RSS feed is being used to get articles from the location of the variable that we are passing. When this function returns the list of articles, we are storing it in our variable called rows. Now, we only want to display a maximum of 5 articles. So, if we get back more than 5, we will turn the first 5 elements into JSON objects using the jsonify function. Else, we will jsonify all of the articles in rows.
Now, let’s look at how were are going to search for new locations based on the mashup.db that we created earlier. Again, we are going to access the argument passed by the URL named “q” and store it in a variable. Now, we are concatenating this variable with a “%” for a reason. This is added because of one of the requirements for search that it must pull up the first ten items starting with whatever you have typed. The “%” is an SQL wildcard character that allows us to meet this requirement. Now, when we execute the query on our database, notice that we are using the like LIKE statement. This statement allows us to search for locations based on the zip code, name, or state. Last but not least we need to convert the items into JSON objects and return that from the function.
And that is it for Part 1 of the Mashup pset. Make sure that you read all the instructions for the problem set so that you understand what all the distribution code is doing. If you have any questions, feel free to comment below. For more coding help follow me on Instagram: @jasoncoryalv and GitHub: jasonalvernaz. As always…Happy Programming!