Jul 11, 2017 Python Flask Cheat Sheet by amicheletti. Python web application flask. Route decorator is used to bind a function to a URL Example: @app.route('/') By default a route only answers to GET requests, but you can provide the methods argument.
- Find a mentor
- Find a freelancer
- Others
- Learning Center
- Enterprise
Introduction
SQLAlchemy is a deep and powerful thing made up of many layers. This cheat sheet sticks to parts of the ORM (Object Relational Mapper) layer,and aims to be a reference not a tutorial. That said, if you are familiar with SQL then this cheat sheet should get you well on your way to understanding SQLAlchemy.
Basic Models
One model is used to describe one database table. For example:
Queries and Interactions
Selecting and Filtering
Relationships
Relationships between SQL tables are described in terms of foreign key relationships. From the example, the
books
table has a foreign key field pointing to the id field of the authors table. SQLAlchemy makes leveraging and examining those relationships pretty straight forward.One to many relationships
Assume we are keeping track of the books of various authors. One author can have many books.
The marked line configures the relationship between the models. Note that
'Author'
is a string. It doesn't need to be, it can also be a class. Using a string here removes the possibility of certain NameErrors. Note that the relationship is configured in both directions in one line. A book's author is accessable via the author
attribute, and an author's books are accessable via the author's books
attribute.Here are a few ways you can make use of the relationship once it is configured:
One to one relationships
Note that the line that configures the relationship has an expression instead of just a string for the backref argument. If the string
'parent'
was used then it would be a normal many to one relationship. We can make use of this like so:Many to many relationships
A many to many relationship requires an extra table to create mappings between lines. There are two ways of doing this:
First, just using models:
Here you can specify relationships on the
Map
class. The benefit of this approach is that you can instantiate the Map
class, this is useful if you want to interact with Map
objects in any non-trivial way.This next approach is better if your map table is only a map table and requires no complex interactions:
You can make use of the relationship like this:
Self referential relationships
Sometimes you have a table with a foreign key pointing at the same table. For example, say we have a bunch of nodes in a directed tree. A node can have many child nodes but at most one parent
You can make use of this relationship like any many to one relationship:
Multiple relationships with the same table
You can interact with this just like two many to one relationships.
Engine Configuration
Connection Strings
Engine, Session and Base
Conclusion
There are many more ways in which you can configure relationships, many more ways to query a database, and many topics that this cheat sheet just didn't cover. SQLAlchemy is pretty huge, but for many applications you don't need to do anything more complex than what is shown here. If you came here looking to learn how to use SQLAlchemy and you feel you are in a position where you can make use of the methods I described here then I would suggest as a next step you read up a little bit on how to use the session to manage transactions.
Enjoy this post? Give Sheena a like if it's helpful.
6
Discover and read more posts from Sheena
Leave a like and comment for Sheena
Using raw SQL in Flask web applications to perform CRUD operations on database can be tedious. Instead, SQLAlchemy, a Python toolkit is a powerful OR Mapper that gives application developers the full power and flexibility of SQL. Flask-SQLAlchemy is the Flask extension that adds support for SQLAlchemy to your Flask application.
What is ORM (Object Relation Mapping)?
Most programming language platforms are object oriented. Data in RDBMS servers on the other hand is stored as tables. Object relation mapping is a technique of mapping object parameters to the underlying RDBMS table structure. An ORM API provides methods to perform CRUD operations without having to write raw SQL statements.
In this section, we are going to study the ORM techniques of Flask-SQLAlchemy and build a small web application.
Step 1 − Install Flask-SQLAlchemy extension.
Step 2 − You need to import SQLAlchemy class from this module.
Step 3 − Now create a Flask application object and set URI for the database to be used.
Step 4 − Then create an object of SQLAlchemy class with application object as the parameter. This object contains helper functions for ORM operations. It also provides a parent Model class using which user defined models are declared. In the snippet below, a students model is created.
Step 5 − To create / use database mentioned in URI, run the create_all() method.
The Session object of SQLAlchemy manages all persistence operations of ORM object.
The following session methods perform CRUD operations −
- db.session.add(model object) − inserts a record into mapped table
- db.session.delete(model object) − deletes record from table
- model.query.all() − retrieves all records from table (corresponding to SELECT query).
You can apply a filter to the retrieved record set by using the filter attribute. For instance, in order to retrieve records with city = ’Hyderabad’ in students table, use following statement −
With this much of background, now we shall provide view functions for our application to add a student data.
The entry point of the application is show_all() function bound to ‘/’ URL. The Record set of students table is sent as parameter to the HTML template. The Server side code in the template renders the records in HTML table form.
The HTML script of the template (‘show_all.html’) is like this −
The above page contains a hyperlink to ‘/new’ URL mapping new() function. When clicked, it opens a Student Information form. The data is posted to the same URL in POST method.
new.html
When the http method is detected as POST, the form data is added in the students table and the application returns to homepage showing the added data.
Given below is the complete code of application (app.py).
Run the script from Python shell and enter http://localhost:5000/ in the browser.
Click the ‘Add Student’ link to open Student information form.
Fill the form and submit. The home page reappears with the submitted data.
We can see the output as shown below.