Databases are essential for web applications, especially if you want to work with big data. For example, we can use databases for storing information about products in online shops or user data in social networks. Flask doesn't support databases natively, but of course, there are ways to connect our application to the database. Let's find out how.
There are two main approaches for working with databases — raw connections and Object-Relational Mapping (ORM). The first one usually interfaces directly with the database by writing and executing queries using the native language supported by the database engine. The second is a programming technology that draws a parallel between SQL and programming language concepts. For example, we can assume databases as classes in Python.
ORM
In a way, ORM employs a higher level of abstraction; it connects the data representations in relational databases and the representation in memory, which, in their turn, use object-oriented programming (OOP). Imagine that you need to teach a kid to count with the help of toy cubes. You add and remove cubes to sum and subtract correspondingly. This analogy between the numbers and cubes helps to understand ORM: in terms of cubes, the kid will be able to do basic arithmetic operations, but if we want to show complex math operations, we will have to use more abstract mathematical concepts. The kid can perform calculations with cubes, the kid fully understands the notions behind these concepts. ORM connects terms in object-oriented programming that are more native to programmers (cubes, if we follow the analogy) with concepts in relational databases (abstract mathematical ideas).
Raw vs. ORM
The table below compares two ways of using DB in Flask:
Raw connections | ORM |
Lower level of abstraction (higher performance, fewer problems, more flexibility) | Higher level of abstraction. No need to dive into the nuances of the back-end; you can also avoid a lot of boilerplate code (important but repetitive pieces of code with no or few changes in it) |
Database-oriented | Programming oriented |
Higher cost, longer development time | Lower cost, shorter development time |
Understanding data types and their structure is a must | No need to fully understand the underlying querying language. However, it can be an advantage |
May lead to safety issues | You can work with DB using a programming language that you're comfortable with |
Queries come with regular expressions, which may require some getting-used-to | No need to refer to regular expressions |
The most popular Relational Database Management Systems (RDBMS) for raw connections are SQLite, MySQL, PostgreSQL. For ORM, the most popular systems include SQLAlchemy, Peewee, Records. We will take a quick look at SQLite3 and SQLAlchemy.
DB connection
There are two main types of DB: as a file on a computer and server-based. Server-based databases require authentication.
For example, we use URI (Uniform Resource Identifier) in SQLAlchemy to connect to a DB. it's like a URL but without information on the domain location. Here are some examples of URIs:
sqlite:///mydatabase.db— connects to a DB stored in a file;mysql://scott:tiger@localhost/mydatabase— connects to a DB stored on the server.
The general URI pattern in SQLAlchemy is the following:
db:engine[+driver]:[//[user[:password]@][host][:port]/][dbname][?params][#fragmentLet's go through each component:
| Defines a database URI. Optional for well-known engines. |
| A string identifying the database engine. The |
| The name of the DBAPI to connect to the database in lowercase. If not specified, a default DBAPI will be imported. |
| A user account name |
| A password for an account name. |
| A host address. |
| A network port. |
| A database name. For some engines, this will be a file name; in this case, it may be a complete or local path. |
| A URI-standard |
| Identifies a database part, such as a table or view. |
Another useful concept of database handling is migration — a process of modifying a DB's structure reversibly without recreating it. Migration is a file with information about changing a DB pattern. We can also use version control for migrations. For example, you have a huge DB, so it's quite difficult to recreate it. Migration allows to fix mistakes and adapt the data even without downtime, which is necessary for systems running 24/7.
Read more on this topic in SQL and Python: applying programming languages on Hyperskill Blog.
Conclusion
Now, we know the pros and cons of different approaches to database management, and we advise you to use ORM for future applications if they're relatively small. Writing a new Facebook will require native SQL querying. If we need to update or change data structures, we can use migrations to ensure the smoothness of the operation.