Introduction
To keep learning Flask and everything around it, be it Jinja tricks for templating HTML or specific Python libraries or frameworks, the next thing we will develop in our application, is using a simple database ORM to connect to a SQLite3 database to display the latest 5 ingredient searches to any user, so they can be inspired on what to cook next :)
However, before we start, there's still some concepts we need to explain, like what is an ORM and talk a bit more about SQLite3.
Our end result will look like this:
where the above list of suggestions will be constantly updated to always show the latest 5 entries (non-duplicated, so, entering beans 5 times will simply show beans followed by whatever else was in the list previously) previously searched by all the users.
This is interesting because it allows the application to be more "user-driven" with such a functionality where previous uses can potentially affect current uses.
What is an ORM and why to use one
An ORM, or, Object-relational mapping is a programming technique used to convert data between incompatible type systems using an object-oriented language (more specifically, it needs to use classes to represent enriched, real-world types. Classes are a subset of what represents object-oriented programming at its core, so this definition is used quite loosely here).
It is quite convenient to use an ORM when working with databases, because:
They can abstract away lots of syntax details regarding SQL queries;
They allow effectively writing queries "in your language of choice";
In some languages, it can be hard to maintain database related code in a clean and defined state, especially if you have to write SQL queries as inline strings and use those strings as "inline queries" that later get translated by the database management system into actual SQL;
ORMs come in many shapes and flavors and it can be a great learning experience to work with some of them, either to learn different styles of abstracting SQL or to use them as a motivation to learn how it works under the hood as means to understand SQL;
Introducing Pony - the ORM we will use
As an ORM, we will be using Pony.
There are actually a lot of ORMs available to be used together with Python.
One of the most popular is SQLAlchemy, and there are many others. Some are actually easier to configure than others, and, in this link, there is a good summary of some of the most known ones.
Pony has lots of good advantages which allow a person which is learning the framework like me to get up to speed fast:
It has an easy to use, powerful syntax, so you can concentrate on writing business logic of your application and use Python syntax for interacting with the database;
Automatic query optimization: you can write Python and get the most efficient possible queries auto generated for you from a clean and high level pythonic description;
Automatic transaction management and caching of queries, no need to
commit()to write anything to the database, it all happens automatically thanks to Pony;
An example of queries using Pony
One interesting feature of Pony is that it allows interacting with the database in pure Python using the generator expressions or lambda functions, which are then translated into SQL. Such queries may easily be written by a developer familiar with Python, even without being a database expert. Let's take a look at this example:
select(c for c in Customer if sum(c.orders.price) > 1000)
which translates into the following SQLite statement:
SELECT "c"."id"
FROM "Customer" "c"
LEFT JOIN "Order" "order-1"
ON "c"."id" = "order-1"."customer"
GROUP BY "c"."id"
HAVING coalesce(SUM("order-1"."total_price"), 0) > 1000
As we can see, the first statement could have easily be written by a developer familiar with Python and its syntax, while the translation that happens under the hood could only be written by someone with some knowledge of SQL queries. This is an advantage of using an ORM.
Thinking about how to implement the feature we want
To implement the feature we described in the beginning of this article, we need to think on what we want to store in our simple database.
And, it's quite obvious, we want to store the strings the users enter as input in the search bar, split them by the commas (this is for simplification: we are storing single ingredients only), and then process it in a way that each string will be mapped to an ingredient and then we can save that ingredient in the database, since we will know it is an ingredient.
Once all the ingredients get stored, we can then perform the query we want: in our case, we want to retrieve and then display in the UI the five most recently searched ingredients.
Configuring Pony with Flask
Configuring Pony is simple. Like any other necessary Python package, we can add it to our requirements.txt configuration file, and it will all work.
Simply adding pony as a dependency will be enough for pip, the Python install package manager to correctly resolve the dependency and add it to the project.
Once it's installed, in our main application script, we can add, right below the creation of the application instance the following:
app.config.update(dict(DEBUG=False, SECRET_KEY='secret_xxx',
PONY={
'provider': 'sqlite',
'filename': 'suggestions.sqlite3',
'create_db': True
}
))
db = Database()
Note how the call to config is being done on app.
app is the current Flask application instance, and, each Flask app can be configured with specific parameters, startup modes, configuration files and properties.
These properties are passed to the application instance as a Python dict (short for dictionary, or associative array, map, hashtable, in some other languages) and then the application will have knowledge about what it needs to provide to the extra libraries and which configuration we will want to use.
As it's possible to see, we have an entry with PONY as key and a JSON as value.
There we specify the database provider we will use (sqlite), the filename that will store all the data relative to the db, and, whether or not to create this file (hence creating the DB) if it doesn't exist, or to simply read the file, assuming it exists.
Below, when doing:
db = Database()
we are actually getting our database object, which is what we will use when creating our database and creating our entities. At this point, our application knows nothing about using Pony.
Linking together Pony and our Flask application instance
With the base configuration out of the way, we can focus on the next step: linking together, Pony with our application instance.
In order to do this, we need some additional steps:
Entities need to be defined in Python, which will be directly mapped into tables in our database;
Pony needs to know how to construct the database schema given these entities, automatically, else it'd be too much overhead that every time the schema changed we'd need to adjust things in multiple places;
Pony also needs to know about the app configuration we created before, in other words, we need to bind the created configuration to Pony;
Finally, after all the steps are done, we simply wrap our Flask application instance into a Pony context, Pony(app) and everything is done, and we are now ready to use Pony. Let's go over each of these configuration steps individually since they're quite complex.
Defining entities and telling Pony to create tables from them
Let's now focus on creating a database entity that will represent a table in the database.
It is simply a wrapper around the input strings sent by the user to the server. It looks like this:
class SearchSuggestion(db.Entity):
ingredient = Required(str)
It's a simple class that receives db.Entity as a constructor argument, and, in our case, it contains an ingredient which will serve as a column in our table (SearchSuggestion). We mark it as required, since it is mandatory that we specify an ingredient for each entry. It makes no sense to attempt to store "empty" ingredient suggestions.
If you are a bit familiar with databases, you probably heard of the concept of primary key. It is defined as an attribute that uniquely identifies a record in a table and allows distinguishing one entity from the other. We didn't specify one!
Since we have not set the primary key attribute manually, it was created automatically. If the primary key is created automatically, it is named as id and has a numeric format. If the primary key attribute is created manually, you can specify the name and type of your choice. Pony also supports composite primary keys.
Now that our entity is in place, we can complete the configuration:
db.bind(**app.config['PONY'])
db.generate_mapping(create_tables=True)
Pony(app)
Here we bind the configuration to the current database instance, to know which engine to use and how to call the file which will hold the db information.
Then, we say to create tables in our db from the entities we have defined.
And, at last, we wrap our application instance with Pony and all the wiring is done!
Adapting our endpoint to save user input to database
Now that the entities are defined, the wiring with Pony is done and that we know what we want to accomplish, here's how we can adapt our endpoint:
@app.route('/', methods=['GET', 'POST'])
def get_recipe():
ingredients_searched = []
suggestions_as_json = {"searches": ingredients_searched}
if request.method == 'POST':
ingredients = "".join(request.form['restaurant_name'].split()).split(",")
with db_session:
[SearchSuggestion(ingredient=suggestion) for suggestion in ingredients]
search_suggestions = select(prod for prod in SearchSuggestion).order_by(lambda prod: desc(prod.id))[:5]
for entry in search_suggestions:
ingredients_searched.append(entry.ingredient)
suggestions_as_json = {"searches": ingredients_searched}
content = requests.get(
"https://api.spoonacular.com/recipes/findByIngredients?ingredients=" +
convert_input(request.form['restaurant_name']) +
"&apiKey=" + API_KEY)
json_response = json.loads(content.text)
print json_response
return render_template("restaurant_list.html", ans = json_response, searchHistory = suggestions_as_json) if json_response != [] else render_template( "restaurant_list.html",ans="",searchHistory=suggestions_as_json)
else:
return render_template("restaurant_list.html",searchHistory=suggestions_as_json)
From the whole code of our home endpoint, the most relevant one is:
ingredients_searched = []
suggestions_as_json = {"searches": ingredients_searched}
if request.method == 'POST':
ingredients = "".join(request.form['restaurant_name'].split()).split(",")
with db_session:
[SearchSuggestion(ingredient=suggestion) for suggestion in ingredients]
search_suggestions = select(prod for prod in SearchSuggestion).order_by(lambda prod: desc(prod.id))[:5]
for entry in search_suggestions:
ingredients_searched.append(entry.ingredient)
suggestions_as_json = {"searches": ingredients_searched}
Our suggestions_as_json is a Python dictionary (which is enclosed by { } braces) and it is what we will use to build the JSON response we will send to our Jinja template to render the suggestions.
After, we first receive the input query from the user, split it by commas, and get a list of ingredients, which we will then need to add to our database.
We do by declaring a local context started by the: with db_session: block.
This is special Pony syntax: after creating a DB record, most ORMs would require one to commit the changes, and actually save the data into the database. However, within the context of this with block, Pony will automatically commit the changes for us, after which it cleans up resources after itself.
Note how we can use list comprehension to achieve the creation of multiple db items. It's combining Python and SQL in a concise package!
After this, the items will be on our database, and we can now query it for what we need: our top 5 most recently searched ingredients:
search_suggestions = select(prod for prod in SearchSuggestion).order_by(lambda prod: desc(prod.id))[:5]
The query should be self-explanatory, we sort the items by id (which auto increments) in descending order, and then use the list slicing notation to get the first five items. All that is left, is to adjust the Jinja template.
Adapting the Jinja template to display suggestions
All that is left now, is to adapt the Jinja template to display our suggestions.
There are some remarks to be made here however:
On server-side, the template variable is directly assigned to our dictionary. We don't need to dump it into a JSON by
json.dumps(template_var)because a Python dictionary can already be mapped directly into a JSON.Let's look at the template code for displaying the suggestions:
<div id="latestSearches">Latest searches include: {{searchHistory['searches']|join(', ')}}</div>
<!--<div>-->
<!-- {% for entry in searchHistory['searches'] %}-->
<!-- <p>{{entry}}</p>-->
<!-- {% endfor %}-->
<!--</div>-->
On top is the correct way to render the list items from the JSON in a single line and comma-separated, by using the join property of Jinja.
They will be included in a single div and be comma-separated.
The old way, commented below, would render the items vertically on an individual paragraph element per item, which is not what we wanted. We see that Jinja can be quite powerful to render list items exactly the way we want it.
The CSS for this element is quite simple as well:
#latestSearches {
font-size: 12px;
font-style: italic;
margin-left: 15px;
}
And, this is it!
Conclusion
I hope you liked this article and learned a bit more about the advantages of using an ORM to delegate your database management work to be as close as possible to the language you're developing in, allowing you to leverage powerful programming constructs to manipulate SQL.
Stay tuned for more!
Top comments (1)
Always used SQLAlchemy. Next time I will use Pony, interesting writing