version: '3'
services:
cache_jvds:
image: redis:7
networks:
- jvds
env_file:
- .env
directus_jvds:
image: directus/directus:10.6.1
ports:
- 8056:8055 # external:internal
volumes:
- ./uploads:/directus/uploads
- ./database:/directus/database
- ./extensions:/directus/extensions
networks:
- jvds
depends_on:
- cache_jvds
env_file:
- .env
adminer_jvds:
image: 'adminer:standalone'
ports:
- 8080:8080
depends_on:
- directus_jvds
networks:
- jvds
env_file:
- .env
networks:
jvds:
name: jvds
external: true
Adminr should open on https://jvds.scores-catalog.org:8080 if I am not mistaken. Can anybody tell me, why this is not working?
]]>I got a problem when I’m setting the register.html and would like to get the info to the database for login used. I’m struggling the login / register setup…
the problem i got from now:
sqlite3.OperationalError: table posts has no column named username
{% extends 'base.html' %}
{% block content %}
<form method="post">
<br>
<div class="bg-img">
<div class="content">
<header>Sign Up</header>
<form action="#">
<div class="field">
<span class="fa fa-user"></span>
<input type="text" name="username" required placeholder="Username" value="{{ request.form['Username'] }}">
</div>
<div class="field space">
<span class="fa fa-user"></span>
<input type="text" name="email" required placeholder="Email" value="{{ request.form['Email'] }}">
</div>
<div class="field space">
<span class="fa fa-user"></span>
<input type="text" name="phone" required placeholder="Phone" value="{{ request.form['Phone'] }}">
</div>
<div class="field space">
<span class="fa fa-lock"></span>
<input type="password" name="password" class="pass-key" required placeholder="Password" value="{{ request.form['Password'] }}">
<span class="show">SHOW</span>
</div>
<div class="field space">
<span class="fa fa-lock"></span>
<input type="password" class="pass-key" required placeholder="Confirm Password">
<span class="show">SHOW</span>
</div>
<div class="field signup">
<input type="submit" value="Sign up">
</div>
</form>
<div class="signup">
Or Sign up with
</div>
<div class="links">
<div class="facebook">
<i class="fab fa-facebook-f"><span>Facebook</span></i>
</div>
<div class="instagram">
<i class="fab fa-instagram"><span>Instagram</span></i>
</div>
</div>
</div>
</div>
</form>
<script>
const pass_field = document.querySelector('.pass-key');
const showBtn = document.querySelector('.show');
showBtn.addEventListener('click', function () {
if (pass_field.type === "password") {
pass_field.type = "text";
showBtn.textContent = "HIDE";
showBtn.style.color = "#3498db";
} else {
pass_field.type = "password";
showBtn.textContent = "SHOW";
showBtn.style.color = "#222";
}
});
</script>
{% endblock %}
@app.route('/register/', methods=('GET', 'POST'))
def register():
if request.method == 'POST':
username = request.form['username']
email = request.form['email']
phone = request.form['phone']
if not username:
flash('Usename is required!')
elif not email:
flash('Email is required!')
elif not phone:
flash('Phone is required!')
else:
conn = get_db_connection()
conn.execute ('INSERT INTO posts (username, email, phone) VALUES (?, ?, ?)',
(username, email, phone))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('register.html')
]]>thanks a lot for your help!
]]>I created my first web app using the 1-click web app. I am using Flask and SQLAlchemy. Right now my db is the SQLite file. The problem I am having is, that user can write to the db via the website, but I have no way of accessing /managing this data. So I want to move to a managed MySQL database.
Has someone done something like this before, could help me or send me in the right direction.
Any help greatly appreciated!
]]>Also if I cant use sqlite can you point me to resources how to integrate digitaloceans hosted db
Thanks in advance, I’m new to web dev and digitalocean seams really nice.
]]>Flask is a small and lightweight Python web framework that provides useful tools and features that make creating web applications in Python easier. It gives developers flexibility and is a more accessible framework for new developers since you can build a web application quickly using only a single Python file. Flask is also extensible and doesn’t force a particular directory structure or require complicated boilerplate code before getting started.
As part of this tutorial, you’ll use the Bootstrap toolkit to style your application so it is more visually appealing. Bootstrap will help you incorporate responsive web pages in your web application so that it also works well on mobile browsers without writing your own HTML, CSS, and JavaScript code to achieve these goals. The toolkit will allow you to focus on learning how Flask works.
Flask uses the Jinja template engine to dynamically build HTML pages using familiar Python concepts such as variables, loops, lists, and so on. You’ll use these templates as part of this project.
In this tutorial, you’ll build a small web blog using Flask and SQLite in Python 3. Users of the application can view all the posts in your database and click on the title of a post to view its contents with the ability to add a new post to the database and edit or delete an existing post.
Simplify deploying Flask applications with DigitalOcean App Platform. Deploy Flask directly from GitHub in minutes.
Before you start following this guide, you will need:
flask_blog
.In this step, you’ll activate your Python environment and install Flask using the pip
package installer.
If you haven’t already activated your programming environment, make sure you’re in your project directory (flask_blog
) and use the following command to activate the environment:
- source env/bin/activate
Once your programming environment is activated, your prompt will now have an env
prefix that may look as follows:
-
This prefix is an indication that the environment env
is currently active, which might have another name depending on how you named it during creation.
Note: You can use Git, a version control system, to effectively manage and track the development process for your project. To learn how to use Git, you might want to check out our Introduction to Git Installation Usage and Branches article.
If you are using Git, it is a good idea to ignore the newly created env
directory in your .gitignore
file to avoid tracking files not related to the project.
Now you’ll install Python packages and isolate your project code away from the main Python system installation. You’ll do this using pip
and python
.
To install Flask, run the following command:
- pip install flask
Once the installation is complete, run the following command to confirm the installation:
- python -c "import flask; print(flask.__version__)"
You use the python
command line interface with the option -c
to execute Python code. Next you import the flask
package with import flask;
then print the Flask version, which is provided via the flask.__version__
variable.
The output will be a version number similar to the following:
Output1.1.2
You’ve created the project folder, a virtual environment, and installed Flask. You’re now ready to move on to setting up your base application.
Now that you have your programming environment set up, you’ll start using Flask. In this step, you’ll make a small web application inside a Python file and run it to start the server, which will display some information on the browser.
In your flask_blog
directory, open a file named hello.py
for editing, use nano
or your favorite text editor:
- nano hello.py
This hello.py
file will serve as a minimal example of how to handle HTTP requests. Inside it, you’ll import the Flask
object, and create a function that returns an HTTP response. Write the following code inside hello.py
:
from flask import Flask
app = Flask(__name__)
@app.route('/')
def hello():
return 'Hello, World!'
In the preceding code block, you first import the Flask
object from the flask
package. You then use it to create your Flask application instance with the name app
. You pass the special variable __name__
that holds the name of the current Python module. It’s used to tell the instance where it’s located—you need this because Flask sets up some paths behind the scenes.
Once you create the app
instance, you use it to handle incoming web requests and send responses to the user. @app.route
is a decorator that turns a regular Python function into a Flask view function, which converts the function’s return value into an HTTP response to be displayed by an HTTP client, such as a web browser. You pass the value '/'
to @app.route()
to signify that this function will respond to web requests for the URL /
, which is the main URL.
The hello()
view function returns the string 'Hello, World!'
as a response.
Save and close the file.
To run your web application, you’ll first tell Flask where to find the application (the hello.py
file in your case) with the FLASK_APP
environment variable:
- export FLASK_APP=hello
Then run it in development mode with the FLASK_ENV
environment variable:
- export FLASK_ENV=development
Lastly, run the application using the flask run
command:
- flask run
Once the application is running the output will be something like this:
Output * Serving Flask app "hello" (lazy loading)
* Environment: development
* Debug mode: on
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
* Restarting with stat
* Debugger is active!
* Debugger PIN: 813-894-335
The preceding output has several pieces of information, such as:
Debug mode: on
signifies that the Flask debugger is running. This is useful when developing because it gives us detailed error messages when things go wrong, which makes troubleshooting easier.http://127.0.0.1:5000/
, 127.0.0.1
is the IP that represents your machine’s localhost
and :5000
is the port number.Open a browser and type in the URL http://127.0.0.1:5000/
, you will receive the string Hello, World!
as a response, this confirms that your application is successfully running.
Warning Flask uses a simple web server to serve our application in a development environment, which also means that the Flask debugger is running to make catching errors easier. This development server should not be used in a production deployment. See the Deployment Options page on the Flask documentation for more information, you can also check out this Flask deployment tutorial.
You can now leave the development server running in the terminal and open another terminal window. Move into the project folder where hello.py
is located, activate the virtual environment, set the environment variables FLASK_ENV
and FLASK_APP
, and continue to the next steps. (These commands are listed earlier in this step.)
Note: When opening a new terminal, it is important to remember activating the virtual environment and setting the environment variables FLASK_ENV
and FLASK_APP
.
While a Flask application’s development server is already running, it is not possible to run another Flask application with the same flask run
command. This is because flask run
uses the port number 5000
by default, and once it is taken, it becomes unavailable to run another application on so you would receive an error similar to the following:
OutputOSError: [Errno 98] Address already in use
To solve this problem, either stop the server that’s currently running via CTRL+C
, then run flask run
again, or if you want to run both at the same time, you can pass a different port number to the -p
argument, for example, to run another application on port 5001
use the following command:
- flask run -p 5001
You now have a small Flask web application. You’ve run your application and displayed information on the web browser. Next, you’ll use HTML files in your application.
Currently your application only displays a simple message without any HTML. Web applications mainly use HTML to display information for the visitor, so you’ll now work on incorporating HTML files in your app, which can be displayed on the web browser.
Flask provides a render_template()
helper function that allows use of the Jinja template engine. This will make managing HTML much easier by writing your HTML code in .html
files as well as using logic in your HTML code. You’ll use these HTML files, (templates) to build all of your application pages, such as the main page where you’ll display the current blog posts, the page of the blog post, the page where the user can add a new post, and so on.
In this step, you’ll create your main Flask application in a new file.
First, in your flask_blog
directory, use nano
or your favorite editor to create and edit your app.py
file. This will hold all the code you’ll use to create the blogging application:
- nano app.py
In this new file, you’ll import the Flask
object to create a Flask application instance as you previously did. You’ll also import the render_template()
helper function that lets you render HTML template files that exist in the templates
folder you’re about to create. The file will have a single view function that will be responsible for handling requests to the main /
route. Add the following content:
from flask import Flask, render_template
app = Flask(__name__)
@app.route('/')
def index():
return render_template('index.html')
The index()
view function returns the result of calling render_template()
with index.html
as an argument, this tells render_template()
to look for a file called index.html
in the templates folder. Both the folder and the file do not yet exist, you will get an error if you were to run the application at this point. You’ll run it nonetheless so you’re familiar with this commonly encountered exception. You’ll then fix it by creating the needed folder and file.
Save and exit the file.
Stop the development server in your other terminal that runs the hello
application with CTRL+C
.
Before you run the application, make sure you correctly specify the value for the FLASK_APP
environment variable, since you’re no longer using the application hello
:
- export FLASK_APP=app
- flask run
Opening the URL http://127.0.0.1:5000/
in your browser will result in the debugger page informing you that the index.html
template was not found. The main line in the code that was responsible for this error will be highlighted. In this case, it is the line return render_template('index.html')
.
If you click this line, the debugger will reveal more code so that you have more context to help you solve the problem.
To fix this error, create a directory called templates
inside your flask_blog
directory. Then inside it, open a file called index.html
for editing:
- mkdir templates
- nano templates/index.html
Next, add the following HTML code inside index.html
:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>FlaskBlog</title>
</head>
<body>
<h1>Welcome to FlaskBlog</h1>
</body>
</html>
Save the file and use your browser to navigate to http://127.0.0.1:5000/
again, or refresh the page. This time the browser should display the text Welcome to FlaskBlog
in an <h1>
tag.
In addition to the templates
folder, Flask web applications also typically have a static
folder for hosting static files, such as CSS files, JavaScript files, and images the application uses.
You can create a style.css
style sheet file to add CSS to your application. First, create a directory called static
inside your main flask_blog
directory:
- mkdir static
Then create another directory called css
inside the static
directory to host .css
files. This is typically done to organize static files in dedicated folders, as such, JavaScript files typically live inside a directory called js
, images are put in a directory called images
(or img
), and so on. The following command will create the css
directory inside the static
directory:
- mkdir static/css
Then open a style.css
file inside the css
directory for editing:
- nano static/css/style.css
Add the following CSS rule to your style.css
file:
h1 {
border: 2px #eee solid;
color: brown;
text-align: center;
padding: 10px;
}
The CSS code will add a border, change the color to brown, center the text, and add a little padding to <h1>
tags.
Save and close the file.
Next, open the index.html
template file for editing:
- nano templates/index.html
You’ll add a link to the style.css
file inside the <head>
section of the index.html
template file:
. . .
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="{{ url_for('static', filename= 'css/style.css') }}">
<title>FlaskBlog</title>
</head>
. . .
Here you use the url_for()
helper function to generate the appropriate location of the file. The first argument specifies that you’re linking to a static file and the second argument is the path of the file inside the static directory.
Save and close the file.
Upon refreshing the index page of your application, you will notice that the text Welcome to FlaskBlog
is now in brown, centered, and enclosed inside a border.
You can use the CSS language to style the application and make it more appealing using your own design. However, if you’re not a web designer, or if you aren’t familiar with CSS, then you can use the Bootstrap toolkit, which provides easy-to-use components for styling your application. In this project, we’ll use Bootstrap.
You might have guessed that making another HTML template would mean repeating most of the HTML code you already wrote in the index.html
template. You can avoid unnecessary code repetition with the help of a base template file, which all of your HTML files will inherit from. See Template Inheritance in Jinja for more information.
To make a base template, first create a file called base.html
inside your templates
directory:
- nano templates/base.html
Type the following code in your base.html
template:
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<title>{% block title %} {% endblock %}</title>
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskBlog</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item active">
<a class="nav-link" href="#">About</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% block content %} {% endblock %}
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>
</html>
Save and close the file once you’re done editing it.
Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta>
tags provide information for the web browser, the <link>
tag links the Bootstrap CSS files, and the <script>
tags are links to JavaScript code that allows some additional Bootstrap features, check out the Bootstrap documentation for more.
However, the following highlighted parts are specific to the Jinja template engine:
{% block title %} {% endblock %}
: A block that serves as a placeholder for a title, you’ll later use it in other templates to give a custom title for each page in your application without rewriting the entire <head>
section each time.{{ url_for('index')}}
: A function call that will return the URL for the index()
view function. This is different from the past url_for()
call you used to link a static CSS file, because it only takes one argument, which is the view function’s name, and links to the route associated with the function instead of a static file.{% block content %} {% endblock %}
: Another block that will be replaced by content depending on the child template (templates that inherit from base.html
) that will override it.Now that you have a base template, you can take advantage of it using inheritance. Open the index.html
file:
- nano templates/index.html
Then replace its contents with the following:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskBlog {% endblock %}</h1>
{% endblock %}
In this new version of the index.html
template, you use the {% extends %}
tag to inherit from the base.html
template. You then extend it via replacing the content
block in the base template with what is inside the content
block in the preceding code block.
This content
block contains an <h1>
tag with the text Welcome to FlaskBlog
inside a title
block, which in turn replaces the original title
block in the base.html
template with the text Welcome to FlaskBlog
. This way, you can avoid repeating the same text twice, as it works both as a title for the page and a heading that appears below the navigation bar inherited from the base template.
Template inheritance also gives you the ability to reuse the HTML code you have in other templates (base.html
in this case) without having to repeat it each time it is needed.
Save and close the file and refresh the index page on your browser. You’ll see your page with a navigation bar and styled title.
You’ve used HTML templates and static files in Flask. You also used Bootstrap to start refining the look of your page and a base template to avoid code repetition. In the next step, you’ll set up a database that will store your application data.
In this step, you’ll set up a database to store data, that is, the blog posts for your application. You’ll also populate the database with a few example entries.
You’ll use a SQLite database file to store your data because the sqlite3
module, which we will use to interact with the database, is readily available in the standard Python library. For more information about SQLite, check out this tutorial.
First, because data in SQLite is stored in tables and columns, and since your data mainly consists of blog posts, you first need to create a table called posts
with the necessary columns. You’ll create a .sql
file that contains SQL commands to create the posts
table with a few columns. You’ll then use this file to create the database.
Open a file called schema.sql
inside your flask_blog
directory:
- nano schema.sql
Type the following SQL commands inside this file:
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
);
Save and close the file.
The first SQL command is DROP TABLE IF EXISTS posts;
, this deletes any already existing tables named posts
so you don’t get confusing behavior. Note that this will delete all of the content you have in the database whenever you use these SQL commands, so ensure you don’t write any important content in the web application until you finish this tutorial and experiment with the final result. Next, CREATE TABLE posts
is used to create the posts
table with the following columns:
id
: An integer that represents a primary key, this will get assigned a unique value by the database for each entry (that is a blog post).created
: The time the blog post was created at. NOT NULL
signifies that this column should not be empty and the DEFAULT
value is the CURRENT_TIMESTAMP
value, which is the time at which the post was added to the database. Just like id
, you don’t need to specify a value for this column, as it will be automatically filled in.title
: The post title.content
: The post content.Now that you have a SQL schema in the schema.sql
file, you’ll use it to create the database using a Python file that will generate an SQLite .db
database file. Open a file named init_db.py
inside the flask_blog
directory using your preferred editor:
- nano init_db.py
And then add the following code.
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('First Post', 'Content for the first post')
)
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('Second Post', 'Content for the second post')
)
connection.commit()
connection.close()
You first import the sqlite3
module and then open a connection to a database file named database.db
, which will be created once you run the Python file. Then you use the open()
function to open the schema.sql
file. Next you execute its contents using the executescript()
method that executes multiple SQL statements at once, which will create the posts
table. You create a Cursor object that allows you to use its execute()
method to execute two INSERT
SQL statements to add two blog posts to your posts
table. Finally, you commit the changes and close the connection.
Save and close the file and then run it in the terminal using the python
command:
- python init_db.py
Once the file finishes execution, a new file called database.db
will appear in your flask_blog
directory. This means you’ve successfully set up your database.
In the next step, you’ll retrieve the posts you inserted into your database and display them in your application’s homepage.
Now that you’ve set up your database, you can now modify the index()
view function to display all the posts you have in your database.
Open the app.py
file to make the following modifications:
- nano app.py
For your first modification, you’ll import the sqlite3
module at the top of the file:
import sqlite3
from flask import Flask, render_template
. . .
Next, you’ll create a function that creates a database connection and return it. Add it directly after the imports:
. . .
from flask import Flask, render_template
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
. . .
This get_db_connection()
function opens a connection to the database.db
database file, and then sets the row_factory
attribute to sqlite3.Row
so you can have name-based access to columns. This means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn
connection object you’ll be using to access the database.
After defining the get_db_connection()
function, modify the index()
function to look like the following:
. . .
@app.route('/')
def index():
conn = get_db_connection()
posts = conn.execute('SELECT * FROM posts').fetchall()
conn.close()
return render_template('index.html', posts=posts)
In this new version of the index()
function, you first open a database connection using the get_db_connection()
function you defined earlier. Then you execute an SQL query to select all entries from the posts
table. You implement the fetchall()
method to fetch all the rows of the query result, this will return a list of the posts you inserted into the database in the previous step.
You close the database connection using the close()
method and return the result of rendering the index.html
template. You also pass the posts
object as an argument, which contains the results you got from the database, this will allow you to access the blog posts in the index.html
template.
With these modifications in place, save and close the app.py
file.
Now that you’ve passed the posts you fetched from the database to the index.html
template, you can use a for
loop to display each post on your index page.
Open the index.html
file:
- nano templates/index.html
Then, modify it to look as follows:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskBlog {% endblock %}</h1>
{% for post in posts %}
<a href="#">
<h2>{{ post['title'] }}</h2>
</a>
<span class="badge badge-primary">{{ post['created'] }}</span>
<hr>
{% endfor %}
{% endblock %}
Here, the syntax {% for post in posts %}
is a Jinja for
loop, which is similar to a Python for
loop except that it has to be later closed with the {% endfor %}
syntax. You use this syntax to loop over each item in the posts
list that was passed by the index()
function in the line return render_template('index.html', posts=posts)
. Inside this for
loop, you display the post title in an <h2>
heading inside an <a>
tag (you’ll later use this tag to link to each post individually).
You display the title using a literal variable delimiter ({{ ... }}
). Remember that post
will be a dictionary-like object, so you can access the post title with post['title']
. You also display the post creation date using the same method.
Once you are done editing the file, save and close it. Then navigate to the index page in your browser. You’ll see the two posts you added to the database on your page.
Now that you’ve modified the index()
view function to display all the posts you have in the database on your application’s homepage, you’ll move on to display each post in a single page and allow users to link to each individual post.
In this step, you’ll create a new Flask route with a view function and a new HTML template to display an individual blog post by its ID.
By the end of this step, the URL http://127.0.0.1:5000/1
will be a page that displays the first post (because it has the ID 1
). The http://127.0.0.1:5000/ID
URL will display the post with the associated ID
number if it exists.
Open app.py
for editing:
- nano app.py
Since you’ll need to get a blog post by its ID from the database in multiple locations later in this project, you’ll create a standalone function called get_post()
. You can call it by passing it an ID and receive back the blog post associated with the provided ID, or make Flask respond with a 404 Not Found
message if the blog post does not exist.
To respond with a 404
page, you need to import the abort()
function from the Werkzeug
library, which was installed along with Flask, at the top of the file:
import sqlite3
from flask import Flask, render_template
from werkzeug.exceptions import abort
. . .
Then, add the get_post()
function right after the get_db_connection()
function you created in the previous step:
. . .
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
def get_post(post_id):
conn = get_db_connection()
post = conn.execute('SELECT * FROM posts WHERE id = ?',
(post_id,)).fetchone()
conn.close()
if post is None:
abort(404)
return post
. . .
This new function has a post_id
argument that determines what blog post to return.
Inside the function, you use the get_db_connection()
function to open a database connection and execute a SQL query to get the blog post associated with the given post_id
value. You add the fetchone()
method to get the result and store it in the post
variable then close the connection. If the post
variable has the value None
, meaning no result was found in the database, you use the abort()
function you imported earlier to respond with a 404
error code and the function will finish execution. If however, a post was found, you return the value of the post
variable.
Next, add the following view function at the end of the app.py
file:
. . .
@app.route('/<int:post_id>')
def post(post_id):
post = get_post(post_id)
return render_template('post.html', post=post)
In this new view function, you add a variable rule <int:post_id>
to specify that the part after the slash (/
) is a positive integer (marked with the int
converter) that you need to access in your view function. Flask recognizes this and passes its value to the post_id
keyword argument of your post()
view function. You then use the get_post()
function to get the blog post associated with the specified ID and store the result in the post
variable, which you pass to a post.html
template that you’ll soon create.
Save the app.py
file and open a new post.html
template file for editing:
- nano templates/post.html
Type the following code in this new post.html
file. This will be similar to the index.html
file, except that it will only display a single post, in addition to also displaying the contents of the post:
{% extends 'base.html' %}
{% block content %}
<h2>{% block title %} {{ post['title'] }} {% endblock %}</h2>
<span class="badge badge-primary">{{ post['created'] }}</span>
<p>{{ post['content'] }}</p>
{% endblock %}
You add the title
block that you defined in the base.html
template to make the title of the page reflect the post title that is displayed in an <h2>
heading at the same time.
Save and close the file.
You can now navigate to the following URLs to see the two posts you have in your database, along with a page that tells the user that the requested blog post was not found (since there is no post with an ID number of 3
so far):
http://127.0.0.1:5000/1
http://127.0.0.1:5000/2
http://127.0.0.1:5000/3
Going back to the index page, you’ll make each post title link to its respective page. You’ll do this using the url_for()
function. First, open the index.html
template for editing:
- nano templates/index.html
Then change the value of the href
attribute from #
to {{ url_for('post', post_id=post['id']) }}
so that the for
loop will look exactly as follows:
{% for post in posts %}
<a href="{{ url_for('post', post_id=post['id']) }}">
<h2>{{ post['title'] }}</h2>
</a>
<span class="badge badge-primary">{{ post['created'] }}</span>
<hr>
{% endfor %}
Here, you pass 'post'
to the url_for()
function as a first argument. This is the name of the post()
view function and since it accepts a post_id
argument, you give it the value post['id']
. The url_for()
function will return the proper URL for each post based on its ID.
Save and close the file.
The links on the index page will now function as expected. With this, you’ve now finished building the part of the application responsible for displaying the blog posts in your database. Next, you’ll add the ability to create, edit, and delete blog posts to your application.
Now that you’ve finished displaying the blog posts that are present in the database on the web application, you need to allow the users of your application to write new blog posts and add them to the database, edit the existing ones, and delete unnecessary blog posts.
Up to this point, you have an application that displays the posts in your database but provides no way of adding a new post unless you directly connect to the SQLite database and add one manually. In this section, you’ll create a page on which you will be able to create a post by providing its title and content.
Open the app.py
file for editing:
- nano app.py
First, you’ll import the following from the Flask framework:
request
object to access incoming request data that will be submitted via an HTML form.url_for()
function to generate URLs.flash()
function to flash a message when a request is processed.redirect()
function to redirect the client to a different location.Add the imports to your file like the following:
import sqlite3
from flask import Flask, render_template, request, url_for, flash, redirect
from werkzeug.exceptions import abort
. . .
The flash()
function stores flashed messages in the client’s browser session, which requires setting a secret key. This secret key is used to secure sessions, which allow Flask to remember information from one request to another, such as moving from the new post page to the index page. The user can access the information stored in the session, but cannot modify it unless they have the secret key, so you must never allow anyone to access your secret key. See the Flask documentation for sessions for more information.
To set a secret key, you’ll add a SECRET_KEY
configuration to your application via the app.config
object. Add it directly following the app
definition before defining the index()
view function:
. . .
app = Flask(__name__)
app.config['SECRET_KEY'] = 'your secret key'
@app.route('/')
def index():
conn = get_db_connection()
posts = conn.execute('SELECT * FROM posts').fetchall()
conn.close()
return render_template('index.html', posts=posts)
. . .
Remember that the secret key should be a long random string.
After setting a secret key, you’ll create a view function that will render a template that displays a form you can fill in to create a new blog post. Add this new function at the bottom of the file:
. . .
@app.route('/create', methods=('GET', 'POST'))
def create():
return render_template('create.html')
This creates a /create
route that accepts both GET and POST requests. GET requests are accepted by default. To also accept POST requests, which are sent by the browser when submitting forms, you’ll pass a tuple with the accepted types of requests to the methods
argument of the @app.route()
decorator.
Save and close the file.
To create the template, open a file called create.html
inside your templates
folder:
- nano templates/create.html
Add the following code inside this new file:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Create a New Post {% endblock %}</h1>
<form method="post">
<div class="form-group">
<label for="title">Title</label>
<input type="text" name="title"
placeholder="Post title" class="form-control"
value="{{ request.form['title'] }}"></input>
</div>
<div class="form-group">
<label for="content">Content</label>
<textarea name="content" placeholder="Post content"
class="form-control">{{ request.form['content'] }}</textarea>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
{% endblock %}
Most of this code is standard HTML. It will display an input box for the post title, a text area for the post content, and a button to submit the form.
The value of the post title input is {{ request.form['title'] }}
and the text area has the value {{ request.form['content'] }}
, this is done so that the data you enter does not get lost if something goes wrong. For example, if you write a long post and you forget to give it a title, a message will be displayed informing you that the title is required. This will happen without losing the post you wrote since it will be stored in the request
global object that you have access to in your templates.
Now, with the development server running, use your browser to navigate to the /create
route:
http://127.0.0.1:5000/create
You will see a Create a New Post page with a box for a title and content.
This form submits a POST request to your create()
view function. However, there is no code to handle a POST request in the function yet, so nothing happens after filling in the form and submitting it.
You’ll handle the incoming POST request when a form is submitted. You’ll do this inside the create()
view function. You can separately handle the POST request by checking the value of request.method
. When its value is set to 'POST'
it means the request is a POST request, you’ll then proceed to extract submitted data, validate it, and insert it into your database.
Open the app.py
file for editing:
- nano app.py
Modify the create()
view function to look exactly as follows:
. . .
@app.route('/create', methods=('GET', 'POST'))
def create():
if request.method == 'POST':
title = request.form['title']
content = request.form['content']
if not title:
flash('Title is required!')
else:
conn = get_db_connection()
conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)',
(title, content))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('create.html')
In the if
statement you ensure that the code following it is only executed when the request is a POST request via the comparison request.method == 'POST'
.
You then extract the submitted title and content from the request.form
object that gives you access to the form data in the request. If the title is not provided, the condition if not title
would be fulfilled, displaying a message to the user informing them that the title is required. If, on the other hand, the title is provided, you open a connection with the get_db_connection()
function and insert the title and the content you received into the posts
table.
You then commit the changes to the database and close the connection. After adding the blog post to the database, you redirect the client to the index page using the redirect()
function passing it the URL generated by the url_for()
function with the value 'index'
as an argument.
Save and close the file.
Now, navigate to the /create
route using your web browser:
http://127.0.0.1:5000/create
Fill in the form with a title of your choice and some content. Once you submit the form, you will see the new post listed on the index page.
Lastly, you’ll display flashed messages and add a link to the navigation bar in the base.html
template to have easy access to this new page. Open the template file:
- nano templates/base.html
Edit the file by adding a new <li>
tag following the About
link inside the <nav>
tag. Then add a new for
loop directly above the content
block to display the flashed messages below the navigation bar. These messages are available in the special get_flashed_messages()
function Flask provides:
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskBlog</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item">
<a class="nav-link" href="#">About</a>
</li>
<li class="nav-item">
<a class="nav-link" href="{{url_for('create')}}">New Post</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% for message in get_flashed_messages() %}
<div class="alert alert-danger">{{ message }}</div>
{% endfor %}
{% block content %} {% endblock %}
</div>
Save and close the file. The navigation bar will now have a New Post
item that links to the /create
route.
For a blog to be up to date, you’ll need to be able to edit your existing posts. This section will guide you through creating a new page in your application to simplify the process of editing a post.
First, you’ll add a new route to the app.py
file. Its view function will receive the ID of the post that needs to be edited, the URL will be in the format /post_id/edit
with the post_id
variable being the ID of the post. Open the app.py
file for editing:
- nano app.py
Next, add the following edit()
view function at the end of the file. Editing an existing post is similar to creating a new one, so this view function will be similar to the create()
view function:
. . .
@app.route('/<int:id>/edit', methods=('GET', 'POST'))
def edit(id):
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
content = request.form['content']
if not title:
flash('Title is required!')
else:
conn = get_db_connection()
conn.execute('UPDATE posts SET title = ?, content = ?'
' WHERE id = ?',
(title, content, id))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('edit.html', post=post)
The post you edit is determined by the URL and Flask will pass the ID number to the edit()
function via the id
argument. You add this value to the get_post()
function to fetch the post associated with the provided ID from the database. The new data will come in a POST request, which is handled inside the if request.method == 'POST'
condition.
Just like when you create a new post, you first extract the data from the request.form
object then flash a message if the title has an empty value, otherwise, you open a database connection. Then you update the posts
table by setting a new title and new content where the ID of the post in the database is equal to the ID that was in the URL.
In the case of a GET request, you render an edit.html
template passing in the post
variable that holds the returned value of the get_post()
function. You’ll use this to display the existing title and content on the edit page.
Save and close the file, then create a new edit.html
template:
- nano templates/edit.html
Write the following code inside this new file:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Edit "{{ post['title'] }}" {% endblock %}</h1>
<form method="post">
<div class="form-group">
<label for="title">Title</label>
<input type="text" name="title" placeholder="Post title"
class="form-control"
value="{{ request.form['title'] or post['title'] }}">
</input>
</div>
<div class="form-group">
<label for="content">Content</label>
<textarea name="content" placeholder="Post content"
class="form-control">{{ request.form['content'] or post['content'] }}</textarea>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
<hr>
{% endblock %}
Save and close the file.
This code follows the same pattern except for the {{ request.form['title'] or post['title'] }}
and {{ request.form['content'] or post['content'] }}
syntax. This displays the data stored in the request if it exists, otherwise it displays the data from the post
variable that was passed to the template containing current database data.
Now, navigate to the following URL to edit the first post:
http://127.0.0.1:5000/1/edit
You will see an Edit “First Post” page.
Edit the post and submit the form, then make sure the post was updated.
You now need to add a link that points to the edit page for each post on the index page. Open the index.html
template file:
- nano templates/index.html
Edit the file to look exactly like the following:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskBlog {% endblock %}</h1>
{% for post in posts %}
<a href="{{ url_for('post', post_id=post['id']) }}">
<h2>{{ post['title'] }}</h2>
</a>
<span class="badge badge-primary">{{ post['created'] }}</span>
<a href="{{ url_for('edit', id=post['id']) }}">
<span class="badge badge-warning">Edit</span>
</a>
<hr>
{% endfor %}
{% endblock %}
Save and close the file.
Here you add an <a>
tag to link to the edit()
view function, passing in the post['id']
value to link to the edit page of each post with the Edit
link.
Sometimes a post no longer needs to be publicly available, which is why the functionality of deleting a post is crucial. In this step you will add the delete functionality to your application.
First, you’ll add a new /ID/delete
route that accepts POST requests, similar to the edit()
view function. Your new delete()
view function will receive the ID of the post to be deleted from the URL. Open the app.py
file:
- nano app.py
Add the following view function at the bottom of the file:
# ....
@app.route('/<int:id>/delete', methods=('POST',))
def delete(id):
post = get_post(id)
conn = get_db_connection()
conn.execute('DELETE FROM posts WHERE id = ?', (id,))
conn.commit()
conn.close()
flash('"{}" was successfully deleted!'.format(post['title']))
return redirect(url_for('index'))
This view function only accepts POST requests. This means that navigating to the /ID/delete
route on your browser will return an error because web browsers default to GET requests.
However you can access this route via a form that sends a POST request passing in the ID of the post you want to delete. The function will receive the ID value and use it to get the post from the database with the get_post()
function.
Then you open a database connection and execute a DELETE FROM
SQL command to delete the post. You commit the change to the database and close the connection while flashing a message to inform the user that the post was successfully deleted and redirect them to the index page.
Note that you don’t render a template file, this is because you’ll just add a Delete
button to the edit page.
Open the edit.html
template file:
- nano templates/edit.html
Then add the following <form>
tag after the <hr>
tag and directly before the {% endblock %}
line:
<hr>
<form action="{{ url_for('delete', id=post['id']) }}" method="POST">
<input type="submit" value="Delete Post"
class="btn btn-danger btn-sm"
onclick="return confirm('Are you sure you want to delete this post?')">
</form>
{% endblock %}
You use the confirm()
method to display a confirmation message before submitting the request.
Now navigate again to the edit page of a blog post and try deleting it:
http://127.0.0.1:5000/1/edit
At the end of this step, the source code of your project will look like the code on this page.
With this, the users of your application can now write new blog posts and add them to the database, edit, and delete existing posts.
This tutorial introduced essential concepts of the Flask Python framework. You learned how to make a small web application, run it in a development server, and allow the user to provide custom data via URL parameters and web forms. You also used the Jinja template engine to reuse HTML files and use logic in them. At the end of this tutorial, you now have a fully functioning web blog that interacts with an SQLite database to create, display, edit, and delete blog posts using the Python language and SQL queries. If you would like to learn more about working with Flask and SQLite check out this tutorial on How To Use One-to-Many Database Relationships with Flask and SQLite.
You can further develop this application by adding user authentication so that only registered users can create and modify blog posts, you may also add comments and tags for each blog post, and add file uploads to give users the ability to include images in the post. See the Flask documentation for more information.
Flask has many community-made Flask extensions. The following is a list of extensions you might consider using to make your development process easier:
This checkpoint is intended to help you assess what you learned from our introductory articles to Databases, where we defined databases and introduced common database management systems. You can use this checkpoint to test your knowledge on these topics, review key terms and commands, and find resources for continued learning.
A database is any logically modeled collection of information or data. When people refer to a “database” in the context of websites, applications, and the cloud, they often mean a computer program that manages data stored on a computer. These programs, known formally as database management systems (DBMS), can be combined with other programs (like a web server and a front-end framework) to form production-ready applications.
In this checkpoint, you’ll find two sections that synthesize the central ideas from the introductory articles: a brief explanation of what a database is (including subsections on relational and non-relational databases) and a section on how to interact with your DBMS through the command line or graphical user interfaces. In each of these sections, there are interactive components to help you test your knowledge. At the end of this checkpoint, you will find opportunities for continued learning about database management systems, fully managed databases, and building your apps with backend databases.
A database is any logically modeled collection of information, and a database management system is what most people think of when they think “I know what a database is!” You use a database management system (DBMS), which is a computer program designed to interact with the information, to access and manipulate the information stored in your database.
Terms to Know
Define the following terms, then use the dropdown feature to check your work.
Replication refers to the practice of synchronizing data across multiple separate databases. This practice provides redundancy, improves scalability, and reduces read latencies.
Database sharding is an architectural practice of separating data into chunks called logical shards that are distributed across separate nodes called physical shards. For more on this practice, you can review our article on Understanding Database Sharding.
There are three common relational models used for database systems:
Relational Model | Relationship |
---|---|
One-to-one | In a one-to-one relationship, rows in one table (sometimes called the parent table) are related to one and only one row in another table (sometimes called the child table). |
One-to-many | In a one-to-many relationship, a row in the initial table (sometimes called the parent table) can relate to multiple rows in another table (sometimes called the child table). |
Many-to-many | In a many-to-many relationship, rows in one table can related to multiple rows in the other table, and vice versa. While these tables may also be referred to as parent and child tables, the multidirectional relationship does not necessitate a hierarchical relationship. |
These relational models structure how databases can relate to each other.
There are two categories for database management: relational and non-relational databases. In the following subsections, you will learn about each type and the common DBMSs for those types.
A relational database organizes information through relations, which you might recognize as a table.
Check Yourself
A relation is a set of tuples, or rows in a table, with each tuple sharing a set of attributes, or columns. A tuple is a unique instance of what type of data the table holds, whereas an attribute specifies the data type for what is allowed in the column.
A primary key refers to the column that will uniquely identify each row in a relational table, whereas a foreign key is a copy of the primary key inserted into a second relation in order to create a relationship between two tables.
When information is stored in a database and organized in the relation, it can be accessed through queries that make a structured request for information. Many relational databases use the Structured Query Language, commonly referred to as SQL to manage queries to the database.
You can use SQL constraints when designing your database. These constraints impose restrictions on what changes can be made to the data in the table.
Check Yourself
PRIMARY KEY
requires every entry in the given column to be both unique and not NULL
, and allows you to use that column to identify each individual row in the table.FOREIGN KEY
requires that every entry in the given column must already exist in a specific column from another table.UNIQUE
prohibits any duplicate values from being added to the given column.CHECK
defines a requirement for a column, known as a predicate, that every value entered into it must meet.NOT NULL
prohibits any NULL
values from being added to the given column.Some open-source relational database management systems built with SQL include MySQL, MariaDB, PostgreSQL, and SQLite. Continue learning about relational databases with Understanding Relational Databases and review common relational DBMS with SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
Relational Database Terms To Know
Through each of the articles, you have developed a vocabulary about relational databases. Define each of the following terms, then use the dropdown feature to check your work.
A constraint is any rule applied to a column or table that limits what data can be entered into it.
A data type dictates what kind of entries are allowed in a column.
An object database uses object-oriented structures for information. PostgreSQL is a relational database that incorporates some features from object databases.
A serverless database, like SQLite, allows any process that accesses the database to write and write to the database disk file directly. This behavior is in contrast to the interprocess communication that is implemented by other relational database engines.
You can write a serverless function to practice running a serverless application.
Some numeric data types are signed, which means they can represent both positive and negative numbers, while others are unsigned and can only represent the positive number.
Now that you know about relational databases, you can understand their counterpart: non-relational databases.
If you need to store data in an unstructured way, a non-relational database provides an alternative model. Because a non-relational database does not use SQL, it is sometimes referred to as a NoSQL database.
There are a variety of available options for a non-relational database, such as key-value stores, columnar databases, document stores, and graph databases. Each of these models attends to possible issues with using a relational database, including horizontal scaling, eventual consistency across nodes, and unstructured data management.
Non-relational Database Terms to Know
Each of the non-relational database models has specific features that make it unique. Define the type of model, then use the dropdown feature to check your work.
Key-value databases store and manage associate arrays that contain key-value pairs where the key is a unique identifier that retrieves its associated value.
Columnar databases are column-oriented, which means that they store data in columns. The data appears in record order where the first entry in one column is related to the first entry in other columns.
Also known as a document store, these are NoSQL databases that store data in the form of documents. Each document contains metadata to structure the data, and an API or query language can be used to retrieve documents.
Graph databases are a document-store subcategory, and this type of database highlights the relationships between documents.
You can check your knowledge about which popular non-relational databases management systems align with the type of database model with the following interactive dropdown feature.
Check Yourself
Match the following database management system to its operational database model.
Operational Database Model | Example DBMSs |
---|---|
Key-value store | Redis, MemcacheDB |
Columnar database | Cassandra, Apache HBase |
Document store | MongoDB, Couchbase |
Graph database | OrientDB, Neo4j |
Whether you are using a relational or a non-relational database, you are likely building an application that includes a database management system as part of its stack.
The database management system is most often deployed as an essential aspect of a larger application. These applications are sometimes called stacks, such as the LAMP stack or Elastic stack.
Check Yourself
Use the dropdown feature to get the answers.
LAMP is an acronym for the technology that makes up this stack:
There are other L*MP options, such as the LEMP stack where E stands for Nginx or the LOMP stack where O stands for OpenLiteSpeed.
Elastic stack is built around Elasticsearch, which is both a search engine and a document-oriented database.
If you set up a remote server with your application stack, it is recommended that you encrypt your data to protect your system from malicious interference. You can encrypt communications using transport layer security (TLS), which will convert data in motion into a ciphertext that can only be decrypting by the right cipher. The static data stored in your database will remain unencrypted unless using a DBMS that offers data at rest encryption.
To manage your database, you may opt do so directly from the command line interface or through a graphical user interface.
You began to use the Linux command line with our introductory articles on cloud servers and you configured a web server with the introductory articles on web server solutions. Through the articles on databases, you have continued to develop familiarity with the command line using commands such as:
grep
to search plain-text data for a specific text or string.netstat
to check network configuration with the flags -lnp
to show listening sockets (-l
), numeric addresses (-n
), and the PID and name of the program for each socket (-p
).systemctl
to control the systemd
service.You have also experimented with the command line tools that come with different database management systems in order to interact with the database installation. The CLI tool enables you to execute commands on the database server and work interactivley from your terminal window. The following table lists common DBMSs and their associated CLI tool:
DBMS | CLI tool |
---|---|
MongoDB | MongoDB shell |
MySQL | mysql |
PostgreSQL | psql |
Redis | redis-cli |
There are also third-party command line clients for some database management systems, such as Redli for Redis.
When you use the command line to work with your database system, you open a database-specific server prompt, typically associated with your user account for that database management system. For example, if you were to open a MySQL server prompt and log in with your MySQL user, you would review a database prompt like so:
-
Each DBMS command-line client has its own syntax for commands.
After learning about SQL constraints, you might use those constraints with a MySQL database by running these commands:
CREATE DATABASE
to create a database.USE
to select a database.CREATE TABLE
to create a table with specifications for the columns and constraints applied to those columns.ALTER TABLE
with ADD
to add constraints to an existing table and with DROP CONSTRAINT
to delete a constraint from an existing table.You can continue to develop your MySQL database skills with the How To Use SQL series.
With Redis, you installed and secured Redis with the following commands and experimented with renaming commands:
auth
to authenticate clients for database access.exit
and quit
to exit the Redis-CLI prompt.get
to retrieve the key value.ping
to test connectivity.set
to set keys.And, in MongoDB shell, you used binary JSON (known as BSON) to run CRUD operations with the following methods of query filtering:
count
method to check the object count in a specified collection.deleteOne
to remove the first document that matches the specifications.deleteMany
to remove multiple objects at once.find
to retrieve documents in your MongoDB database with the pretty
printing feature to make the lines more readable.insertOne
method to create individual documents.insertMany
method to insert multiple documents in a single operation or collection.ObjectId
object datatype for storing object identifiers.updateOne
to update a single document with specified keys.updateMany
to update every document in a collection that matches the specified filters.You will likely use CRUD operations to interact with your data across many database management systems.
Check Yourself
CRUD is an acronym used to describe the following four fundamental data operations:
While you may opt to manage your database directly from the command line, you can also use a graphical user interface (GUI) for many common database management systems.
There are many different GUI tools for working with your database if you decide against using the designed CLI tool.
To handle MySQL administration over the web, you can use phpMyAdmin by installing and securing phpMyAdmin on many different operating systems or connecting remotely to a MySQL Managed Database. You can also use MySQL Workbench to connect to a MySQL server remotely.
Similar to phpMyAdmin, pgAdmin is a web interface for managing PostgreSQL. You can install and configure pgAdmin in server mode or use it to schedule automatic backups with pgAgent.
For MongoDB, you might consider using MongoDB Compass as the graphical interface to access your database.
Whether you choose to use the command line or a graphical interface to manage your database, you now have the tools necessary to manage your database system.
With a stronger understanding of databases and popular database management systems, you can store and manage your data or build an application that uses a database system.
For more about working with specific database management systems, you can follow our How To Use SQL and How To Manage Data with MongoDB series. If you run into issues with MySQL, you can debug with How To Troubleshoot Issues in MySQL. For MongoDB issues, assess how your issues related to How To Perform CRUD Operations in MongoDB.
When you’re ready to build your apps with databases, try following these tutorials for common application stack setups:
If you prefer building your apps with fully managed databases, check out the DigitalOcean offerings for managed MongoDB clusters, MySQL or PostgreSQL hosting, and managed Redis. You can also choose a popular database option for a 1-click installation in the DigitalOcean Marketplace.
With your newfound knowledge of databases, you can also continue your cloud journey with containers and security. If you haven’t yet, check out our introductory articles on cloud servers and web servers.
]]>A CSV is a plain text file format for storing tabular data. The CSV file uses a comma delimiter to separate values in table cells, and a new line delineates where rows begin and end. Most spreadsheet programs and databases can export and import CSV files. Because CSV is a plain-text file, any programming language can parse and write to a CSV file. Node.js has many modules that can work with CSV files, such as node-csv
, fast-csv
, and papaparse
.
In this tutorial, you will use the node-csv
module to read a CSV file using Node.js streams, which lets you read large datasets without consuming a lot of memory. You will modify the program to move data parsed from the CSV file into a SQLite database. You will also retrieve data from the database, parse it with node-csv
, and use Node.js streams to write it to a CSV file in chunks.
Deploy your Node applications from GitHub using DigitalOcean App Platform. Let DigitalOcean focus on scaling your app.
To follow this tutorial, you will need:
Node.js installed on your local or server environment. Follow How to Install Node.js and Create a Local Development Environment to install Node.js.
SQLite installed on your local or server environment, which you can install by following step 1 in How To Install and Use SQLite on Ubuntu 20.04. Knowledge on how to use SQLite is helpful and can be learned in steps 2-7 of the installation guide.
Familiarity with writing a Node.js program. See How To Write and Run Your First Program in Node.js.
Familiarity with Node.js streams. See How To Work with Files Using Streams in Node.js.
In this section, you will create the project directory and download packages for your application. You will also download a CSV dataset from Stats NZ, which contains international migration data in New Zealand.
To get started, make a directory called csv_demo
and navigate into the directory:
- mkdir csv_demo
- cd csv_demo
Next, initialize the directory as an npm project using the npm init
command:
- npm init -y
The -y
option notifies npm init
to say “yes” to all the prompts. This command creates a package.json
with default values that you can change anytime.
With the directory initialized as an npm project, you can now install the necessary dependencies: node-csv
and node-sqlite3
.
Enter the following command to install node-csv
:
- npm install csv
The node-csv
module is a collection of modules that allows you to parse and write data to a CSV file. The command installs all four modules that are part of the node-csv
package: csv-generate
, csv-parse
, csv-stringify
, and stream-transform
. You will use the csv-parse
module to parse a CSV file and the csv-stringify
module to write data to a CSV file.
Next, install the node-sqlite3
module:
- npm install sqlite3
The node-sqlite3
module allows your app to interact with the SQLite database.
After installing the packages in your project, download the New Zealand migration CSV file with the wget
command:
- wget https://www.stats.govt.nz/assets/Uploads/International-migration/International-migration-September-2021-Infoshare-tables/Download-data/international-migration-September-2021-estimated-migration-by-age-and-sex-csv.csv
The CSV file you downloaded has a long name. To make it easier to work with, rename the file name to a shorter name using the mv
command:
- mv international-migration-September-2021-estimated-migration-by-age-and-sex-csv.csv migration_data.csv
The new CSV filename, migration_data.csv
, is shorter and easier to work with.
Using nano
, or your favorite text editor, open the file:
- nano migration_data.csv
Once open, you will see contents similar to this:
year_month,month_of_release,passenger_type,direction,sex,age,estimate,standard_error,status
2001-01,2020-09,Long-term migrant,Arrivals,Female,0-4 years,344,0,Final
2001-01,2020-09,Long-term migrant,Arrivals,Male,0-4 years,341,0,Final
...
The first line contains the column names, and all subsequent lines have the data corresponding to each column. A comma separates each piece of data. This character is known as a delimiter because it delineates the fields. You are not limited to using commas. Other popular delimiters include colons(:
), semicolons(;
), and tabs(\td
). You need to know which delimiter is used in the file since most modules require it to parse the files.
After reviewing the file and identifying the delimiter, exit your migration_data.csv
file using CTRL+X
.
You have now installed the necessary dependencies for your project. In the next section, you will read a CSV file.
In this section, you will use node-csv
to read a CSV file and log its content in the console. You will use the fs
module’s createReadStream()
method to read the data from the CSV file and create a readable stream. You will then pipe the stream to another stream initialized with the csv-parse
module to parse the chunks of data. Once the chunks of data have been parsed, you can log them in the console.
Create and open a readCSV.js
file in your preferred editor:
- nano readCSV.js
In your readCSV.js
file, import the fs
and csv-parse
modules by adding the following lines:
const fs = require("fs");
const { parse } = require("csv-parse");
In the first line, you define the fs
variable and assign it the fs
object that the Node.js require()
method returns when it imports the module.
In the second line, you extract the parse
method from the object returned by the require()
method into the parse
variable using the destructuring syntax.
Add the following lines to read the CSV file:
...
fs.createReadStream("./migration_data.csv")
.pipe(parse({ delimiter: ",", from_line: 2 }))
.on("data", function (row) {
console.log(row);
})
The createReadStream()
method from the fs
module accepts an argument of the filename you want to read, which is migration_data.csv
here. Then, it creates a readable stream, which takes a large file and breaks it into smaller chunks. A readable stream allows you to only read data from it and not write to it.
After creating the readable stream, Node’s pipe()
method forwards chunks of data from the readable stream to another stream. The second stream is created when the csv-parse
module’s parse()
method is invoked inside the pipe()
method. The csv-parse
module implements a transform stream (a readable and writable stream), taking a data chunk and transforming it to another form. For example, when it receives a chunk like 2001-01,2020-09,Long-term migrant,Arrivals,Female,0-4 years,344
, the parse()
method will transform it into an array.
The parse()
method takes an object that accepts properties. The object then configures and provides more information about the data the method will parse. The object takes the following properties:
delimiter
defines the character that separates each field in the row. The value ,
tells the parser that commas demarcate the fields.
from_line
defines the line where the parser should start parsing the rows. With the value 2
, the parser will skip line 1 and start at line 2. Because you will insert the data in the database later, this property helps you avoid inserting the column names in the first row of the database.
Next, you attach a streaming event using the Node.js on()
method. A streaming event allows the method to consume a chunk of data if a certain event is emitted. The data
event is triggered when data transformed from the parse()
method is ready to be consumed. To access the data, you pass a callback to the on()
method, which takes a parameter named row
. The row
parameter is a data chunk transformed into an array. Within the callback, you log the data in the console using the console.log()
method.
Before running the file, you will add more stream events. These stream events handle errors and write a success message to the console when all the data in the CSV file has been consumed.
Still in your readCSV.js
file, add the highlighted code:
...
fs.createReadStream("./migration_data.csv")
.pipe(parse({ delimiter: ",", from_line: 2 }))
.on("data", function (row) {
console.log(row);
})
.on("end", function () {
console.log("finished");
})
.on("error", function (error) {
console.log(error.message);
});
The end
event is emitted when all the data in the CSV file has been read. When that happens, the callback is invoked and logs a message that says it has finished.
If an error occurs anywhere while reading and parsing the CSV data, the error
event is emitted, which invokes the callback and logs the error message in the console.
Your complete file should now look like the following:
const fs = require("fs");
const { parse } = require("csv-parse");
fs.createReadStream("./migration_data.csv")
.pipe(parse({ delimiter: ",", from_line: 2 }))
.on("data", function (row) {
console.log(row);
})
.on("end", function () {
console.log("finished");
})
.on("error", function (error) {
console.log(error.message);
});
Save and exit out of your readCSV.js
file using CTRL+X
.
Next, run the file using the node
command:
- node readCSV.js
The output will look similar to this (edited for brevity):
Output[
'2001-01',
'2020-09',
'Long-term migrant',
'Arrivals',
'Female',
'0-4 years',
'344',
'0',
'Final'
]
...
[
'2021-09',
...
'70',
'Provisional'
]
finished
All the rows in the CSV file have been transformed into arrays using the csv-parse
transform stream. Because logging happens each time a chunk is received from the stream, the data appears as though it is being downloaded rather than being displayed all at once.
In this step, you read data in a CSV file and transformed it into arrays. Next, you will insert data from a CSV file into the database.
Inserting data from a CSV file into the database using Node.js gives you access to a vast library of modules that you can use to process, clean, or enhance the data before inserting it into the database.
In this section, you will establish a connection with the SQLite database using the node-sqlite3
module. You will then create a table in the database, copy the readCSV.js
file, and modify it to insert all the data read from the CSV file into the database.
Create and open a db.js
file in your editor:
- nano db.js
In your db.js
file, add the following lines to import the fs
and node-sqlite3
modules:
const fs = require("fs");
const sqlite3 = require("sqlite3").verbose();
const filepath = "./population.db";
...
In the third line, you define the path of the SQLite database and store it in the variable filepath
. The database file doesn’t exist yet, but it will be needed for node-sqlite3
to establish a connection with the database.
In the same file, add the following lines to connect Node.js to a SQLite database:
...
function connectToDatabase() {
if (fs.existsSync(filepath)) {
return new sqlite3.Database(filepath);
} else {
const db = new sqlite3.Database(filepath, (error) => {
if (error) {
return console.error(error.message);
}
console.log("Connected to the database successfully");
});
return db;
}
}
Here, you define a function named connectToDatabase()
to establish a connection to the database. Within the function, you invoke the fs
module’s existsSync()
method in an if
statement, which checks if the database file exists in the project directory. If the if
condition evaluates to true
, you instantiate the SQLite’s Database()
class of the node-sqlite3
module with the database filepath. Once the connection is established, the function returns the connection object and exits.
However, if the if
statement evaluates to false
(if the database file doesn’t exist), execution will skip to the else
block. In the else
block, you instantiate the Database()
class with two arguments: the database file path and a callback.
The first argument is the path of the SQLite database file, which is ./population.db
. The second argument is a callback that will be invoked automatically when the connection with the database has been established successfully or if an error occurred. The callback takes an error
object as a parameter, which is null
if the connection is successful. Within the callback, the if
statement checks if the error
object is set. If it evaluates to true
, the callback logs an error message and returns. If it evaluates to false
, you log a success message confirming that the connection has been established.
Currently, the if
and else
blocks establish the connection object. You pass a callback when invoking the Database
class in the else
block to create a table in the database, but only if the database file does not exist. If the database file already exists, the function will execute the if
block, connect with the database, and return the connection object.
To create a table if the database file doesn’t exist, add the highlighted code:
const fs = require("fs");
const sqlite3 = require("sqlite3").verbose();
const filepath = "./population.db";
function connectToDatabase() {
if (fs.existsSync(filepath)) {
return new sqlite3.Database(filepath);
} else {
const db = new sqlite3.Database(filepath, (error) => {
if (error) {
return console.error(error.message);
}
createTable(db);
console.log("Connected to the database successfully");
});
return db;
}
}
function createTable(db) {
db.exec(`
CREATE TABLE migration
(
year_month VARCHAR(10),
month_of_release VARCHAR(10),
passenger_type VARCHAR(50),
direction VARCHAR(20),
sex VARCHAR(10),
age VARCHAR(50),
estimate INT
)
`);
}
module.exports = connectToDatabase();
Now the connectToDatabase()
invokes the createTable()
function, which accepts the connection object stored in the db
variable as an argument.
Outside the connectToDatabase()
function, you define the createTable()
function, which accepts the connection object db
as a parameter. You invoke the exec()
method on the db
connection object that takes a SQL statement as an argument. The SQL statement creates a table named migration
with 7 columns. The column names match the headings in the migration_data.csv
file.
Finally, you invoke the connectToDatabase()
function and export the connection object returned by the function so that it can be reused in other files.
Save and exit your db.js
file.
With the database connection established, you will now copy and modify the readCSV.js
file to insert the rows that the csv-parse
module parsed into the database.
Copy and rename the file to insertData.js
with the following command:
- cp readCSV.js insertData.js
Open the insertData.js
file in your editor:
- nano insertData.js
Add the highlighted code:
const fs = require("fs");
const { parse } = require("csv-parse");
const db = require("./db");
fs.createReadStream("./migration_data.csv")
.pipe(parse({ delimiter: ",", from_line: 2 }))
.on("data", function (row) {
db.serialize(function () {
db.run(
`INSERT INTO migration VALUES (?, ?, ? , ?, ?, ?, ?)`,
[row[0], row[1], row[2], row[3], row[4], row[5], row[6]],
function (error) {
if (error) {
return console.log(error.message);
}
console.log(`Inserted a row with the id: ${this.lastID}`);
}
);
});
});
In the third line, you import the connection object from the db.js
file and store it in the variable db
.
Inside the data
event callback attached to the fs
module stream, you invoke the serialize()
method on the connection object. The method ensures that a SQL statement finishes executing before another one starts executing, which can help prevent database race conditions where the system runs competing operations simultaneously.
The serialize()
method takes a callback. Within the callback, you invoke the run
method on the db
connection object. The method accepts three arguments:
The first argument is a SQL statement that will be passed and executed in the SQLite database. The run()
method only accepts SQL statements that don’t return results. The INSERT INTO migration VALUES (?, ..., ?
statement inserts a row in the table migration
, and the ?
are placeholders that are later substituted with the values in the run()
method second argument.
The second argument is an array [row[0], ... row[5], row[6]]
. In the previous section, the parse()
method receives a chunk of data from the readable stream and transforms it into an array. Since the data is received as an array, to get each field value, you must use array indexes to access them like [row[1], ..., row[6]]
, etc.
The third argument is a callback that runs when the data has been inserted or if an error occurred. The callback checks if an error occurred and logs the error message. If there are no errors, the function logs a success message in the console using the console.log()
method, letting you know that a row has been inserted along with the id.
Finally, remove the end
and error
events from your file. Due to the asynchronous nature of the node-sqlite3
methods, the end
and error
events execute before the data is inserted into the database, so they are no longer required.
Save and exit your file.
Run the insertData.js
file using node
:
- node insertData.js
Depending on your system, it may take some time, but node
should return the output below:
OutputConnected to the database successfully
Inserted a row with the id: 1
Inserted a row with the id: 2
...
Inserted a row with the id: 44308
Inserted a row with the id: 44309
Inserted a row with the id: 44310
The message, especially the ids, proves that the row from the CSV file has been saved into the database.
You can now read a CSV file and insert its content into the database. Next, you will write a CSV file.
In this section, you will retrieve data from the database and write it into a CSV file using streams.
Create and open writeCSV.js
in your editor:
- nano writeCSV.js
In your writeCSV.js
file, add the following lines to import the fs
and csv-stringify
modules and the database connection object from db.js
:
const fs = require("fs");
const { stringify } = require("csv-stringify");
const db = require("./db");
The csv-stringify
module transforms data from an object or array into a CSV text format.
Next, add the following lines to define a variable that contains the name of the CSV file you want to write data to and a writable stream that you will write data to:
...
const filename = "saved_from_db.csv";
const writableStream = fs.createWriteStream(filename);
const columns = [
"year_month",
"month_of_release",
"passenger_type",
"direction",
"sex",
"age",
"estimate",
];
The createWriteStream
method takes an argument of the filename you want to write your stream of data to, which is the saved_from_db.csv
file name stored in the filename
variable.
In the fourth line, you define a columns
variable, which stores an array containing the names of the headers for the CSV data. These headers will be written in the first line of the CSV file when you start writing the data to the file.
Still in your writeCSV.js
file, add the following lines to retrieve data from the database and write each row in the CSV file:
...
const stringifier = stringify({ header: true, columns: columns });
db.each(`select * from migration`, (error, row) => {
if (error) {
return console.log(error.message);
}
stringifier.write(row);
});
stringifier.pipe(writableStream);
console.log("Finished writing data");
First, you invoke the stringify
method with an object as an argument, which creates a transform stream. The transform stream converts the data from an object into CSV text. The object passed into the stringify()
method has two properties:
header
accepts a boolean value and generates a header if the boolean value is set to true
.columns
takes an array containing the names of the columns that will be written in the first line of the CSV file if the header
option is set to true
.Next, you invoke the each()
method from the db
connection object with two arguments. The first argument is the SQL statement select * from migration
that retrieves the rows one by one in the database. The second argument is a callback invoked each time a row is retrieved from the database. The callback takes two parameters: an error
object and a row
object containing data retrieved from a single row in the database. Within the callback, you check if the error
object is set in the if
statement. If the condition evaluates to true
, an error message is logged in the console using the console.log()
method. If there is no error, you invoke the write()
method on stringifier
, which writes the data into the stringifier
transform stream.
When the each()
method finishes iterating, the pipe()
method on the stringifier
stream starts sending data in chunks and writing it in the writableStream
. The writable stream will save each chunk of data in the saved_from_db.csv
file. Once all the data has been written to the file, console.log()
will log a success message.
The complete file will now look like the following:
const fs = require("fs");
const { stringify } = require("csv-stringify");
const db = require("./db");
const filename = "saved_from_db.csv";
const writableStream = fs.createWriteStream(filename);
const columns = [
"year_month",
"month_of_release",
"passenger_type",
"direction",
"sex",
"age",
"estimate",
];
const stringifier = stringify({ header: true, columns: columns });
db.each(`select * from migration`, (error, row) => {
if (error) {
return console.log(error.message);
}
stringifier.write(row);
});
stringifier.pipe(writableStream);
console.log("Finished writing data");
Save and close your file, then run the writeCSV.js
file in the terminal:
- node writeCSV.js
You will receive the following output:
OutputFinished writing data
To confirm that the data has been written, inspect the contents in the file using the cat
command:
- cat saved_from_db.csv
cat
will return all the rows written in the file (edited for brevity):
Outputyear_month,month_of_release,passenger_type,direction,sex,age,estimate
2001-01,2020-09,Long-term migrant,Arrivals,Female,0-4 years,344
2001-01,2020-09,Long-term migrant,Arrivals,Male,0-4 years,341
2001-01,2020-09,Long-term migrant,Arrivals,Female,10-14 years,
...
You can now retrieve data from the database and write each row in a CSV file using streams.
In this article, you read a CSV file and inserted its data into a database using the node-csv
and node-sqlite3
modules. You then retrieved data from the database and wrote it to another CSV file.
You can now read and write CSV files. As a next step, you can now work with large CSV datasets using the same implementation with memory-efficient streams, or you might look into a package like event-stream
that make working with streams much easier.
To explore more about node-csv
, visit their documentation CSV Project - Node.js CSV package. To learn more about node-sqlite3
, visit their Github documentation. To continue growing your Node.js skills, see the How To Code in Node.js series.
In web applications, you usually need a database, which is an organized collection of data. You use a database to store and maintain persistent data that can be retrieved and manipulated efficiently. For example, in a social media application, you have a database where user data (personal information, posts, comments, followers) is stored in a way that can be efficiently manipulated. You can add data to a database, retrieve it, modify it, or delete it, depending on different requirements and conditions. In a web application, these requirements might be a user adding a new post, deleting a post, or deleting their account, which may or may not delete their posts. The actions you perform to manipulate data will depend on specific features in your application. For example, you might not want users to add posts with no titles.
Deploy your Flask applications from GitHub using DigitalOcean App Platform. Let DigitalOcean focus on scaling your app.
Flask is a lightweight Python web framework that provides useful tools and features for creating web applications in the Python Language. SQLAlchemy is an SQL toolkit that provides efficient and high-performing database access for relational databases. It provides ways to interact with several database engines such as SQLite, MySQL, and PostgreSQL. It gives you access to the database’s SQL functionalities. It also gives you an Object Relational Mapper (ORM), which allows you to make queries and handle data using simple Python objects and methods. Flask-SQLAlchemy is a Flask extension that makes using SQLAlchemy with Flask easier, providing you tools and methods to interact with your database in your Flask applications through SQLAlchemy.
In this tutorial, you’ll build a small student management system that demonstrates how to use the Flask-SQLAlchemy extension. You’ll use it with Flask to perform basic tasks, such as connecting to a database server, creating a table, adding data to your table, retrieving it, and updating and deleting items from your database. You’ll use SQLAlchemy with SQLite, although you can use it with other database engines too, such as PostgreSQL and MySQL. SQLite works well with Python because the Python standard library provides the sqlite3
module, which is used by SQLAlchemy behind the scenes to interact with SQLite databases without having to install anything. SQlite is installed on Linux systems by default, and is installed as part of the Python package on Windows.
A local Python 3 programming environment. Follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial we’ll call our project directory flask_app
.
An understanding of basic Flask concepts, such as routes, view functions, and templates. If you are not familiar with Flask, check out How to Create Your First Web Application Using Flask and Python and How to Use Templates in a Flask Application.
An understanding of basic HTML concepts. You can review our How To Build a Website with HTML tutorial series for background knowledge.
In this step, you’ll install the necessary packages for your application.
With your virtual environment activated, use pip
to install Flask and Flask-SQLAlchemy:
- pip install Flask Flask-SQLAlchemy
Once the installation is successfully finished, you’ll see a line similar to the following at the end of the output:
OutputSuccessfully installed Flask-2.0.3 Flask-SQLAlchemy-2.5.1 Jinja2-3.0.3 MarkupSafe-2.1.0 SQLAlchemy-1.4.31 Werkzeug-2.0.3 click-8.0.4 greenlet-1.1.2 itsdangerous-2.1.0
With the required Python packages installed, you’ll set up the database next.
In this step, you’ll set up your database connection, and create an SQLAlchemy database model, which is a Python class that represents the table that stores your data. You’ll initiate the database, create a table for students based on the model you’ll declare, and add a few students into your students table.
Open a file called app.py
in your flask_app
directory. This file will have code for setting up the database and your Flask routes:
- nano app.py
This file will connect to an SQLite database called database.db
, and have a class called Student
that represents your database students table for storing student information, in addition to your Flask routes. Add the following import
statements at the top of app.py
:
import os
from flask import Flask, render_template, request, url_for, redirect
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func
Here, you import the os
module, which gives you access to miscellaneous operating system interfaces. You’ll use it to construct a file path for your database.db
database file.
From the flask
package, you then import the necessary helpers you need for your application: the Flask
class to create a Flask application instance, the render_template()
function to render templates, the request
object to handle requests, the url_for()
function to construct URLs for routes, and the redirect()
function for redirecting users. For more information on routes and templates, see How To Use Templates in a Flask Application.
You then import the SQLAlchemy
class from the Flask-SQLAlchemy extension, which gives you access to all the functions and classes from SQLAlchemy, in addition to helpers, and functionality that integrates Flask with SQLAlchemy. You’ll use it to create a database object that connects to your Flask application, allowing you to create and manipulate tables using Python classes, objects, and functions without needing to use the SQL language.
You also import the func
helper from the sqlalchemy.sql
module to access SQL functions. You’ll need it in your student management system to set a default creation date and time for when a student record is created.
Below the imports, you’ll set up a database file path, instantiate your Flask application, and configure and connect your application with SQLAlchemy. Add the following code:
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =\
'sqlite:///' + os.path.join(basedir, 'database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
Here, you construct a path for your SQLite database file. You first define a base directory as the current directory. You use the os.path.abspath()
function to get the absolute path of the current file’s directory. The special __file__
variable holds the pathname of the current app.py
file. You store the absolute path of the base directory in a variable called basedir
.
You then create a Flask application instance called app
, which you use to configure two Flask-SQLAlchemy configuration keys:
SQLALCHEMY_DATABASE_URI
: The database URI to specify the database you want to establish a connection with. In this case, the URI follows the format sqlite:///path/to/database.db
. You use the os.path.join()
function to intelligently join the base directory you constructed and stored in the basedir
variable, and the database.db
file name. This will connect to a database.db
database file in your flask_app
directory. The file will be created once you initiate the database.
SQLALCHEMY_TRACK_MODIFICATIONS
: A configuration to enable or disable tracking modifications of objects. You set it to False
to disable tracking and use less memory. For more, see the configuration page in the Flask-SQLAlchemy documentation.
Note:
If you want to use another database engine such as PostgreSQL or MySQL, you’ll need to use the proper URI.
For PostgreSQL, use the following format:
postgresql://username:password@host:port/database_name
For MySQL:
mysql://username:password@host:port/database_name
For more, see the SQLAlchemy documentation for engine configuration.
After configuring SQLAlchemy by setting a database URI and disabling tracking, you create a database object using the SQLAlchemy
class, passing the application instance to connect your Flask application with SQLAlchemy. You store your database object in a variable called db
. You’ll use this db
object to interact with your database.
With the database connection established and the database object created, you’ll use the database object to create a database table for students, which is represented by a model — a Python class that inherits from a base class Flask-SQLAlchemy provides through the db
database instance you created earlier. To define a student table as a model, add the following class to your app.py
file:
# ...
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True)
firstname = db.Column(db.String(100), nullable=False)
lastname = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(80), unique=True, nullable=False)
age = db.Column(db.Integer)
created_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
bio = db.Column(db.Text)
def __repr__(self):
return f'<Student {self.firstname}>'
Here, you create a Student
model, which inherits from the db.Model
class. This represents the student table. You use the db.Column
class to define columns for your table. The first argument represents the column type, and additional arguments represent column configuration.
You define the following columns for the Student
model:
id
: The student ID. You define it as an integer with db.Integer
. primary_key=True
defines this column as a primary key, which will assign it a unique value by the database for each entry (that is a student).firstname
: The student’s first name. A string with a maximum length of 100
characters. nullable=False
signifies that this column should not be empty.lastname
: The student’s last name. A string with a maximum length of 100
characters. nullable=False
signifies that this column should not be empty.email
: The student’s email. A string with a maximum length of 80
characters. unique=True
signifies that each email should be unique for each student. nullable=False
signifies that this column should not be empty.age
: The student’s age.created_at
: The time the student record was created at in the database. You use db.DateTime
to define it as a Python datetime
object. timezone=True
enables timezone support. server_default
sets the default value in the database when creating the table, so that default values are handled by the database rather than the model. You pass it the func.now()
function which calls the SQL now()
datetime function. In SQLite, it is rendered as CURRENT_TIMESTAMP
when creating the student table.bio
: The student’s bio. db.Text()
indicates the column holds long texts.See the SQLAlchemy documentation for column types other than the types you used in the preceding code block.
The special __repr__
function allows you to give each object a string representation to recognize it for debugging purposes. In this case you use the student’s first name.
The app.py
file will now look as follows:
import os
from flask import Flask, render_template, request, url_for, redirect
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql import func
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] =\
'sqlite:///' + os.path.join(basedir, 'database.db')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class Student(db.Model):
id = db.Column(db.Integer, primary_key=True)
firstname = db.Column(db.String(100), nullable=False)
lastname = db.Column(db.String(100), nullable=False)
email = db.Column(db.String(80), unique=True, nullable=False)
age = db.Column(db.Integer)
created_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
bio = db.Column(db.Text)
def __repr__(self):
return f'<Student {self.firstname}>'
Save and close app.py
.
Now that you’ve set the database connection and the student model, you’ll use the Flask shell to create your database and your student table based on the Student
model.
With your virtual environment activated, set the app.py
file as your Flask application using the FLASK_APP
environment variable. Then open the Flask shell using the following command in your flask_app
directory:
- export FLASK_APP=app
- flask shell
A Python interactive shell will be opened. This special shell runs commands in the context of your Flask application, so that the Flask-SQLAlchemy functions you’ll call are connected to your application.
Import the database object and the student model, and then run the db.create_all()
function to create the tables that are associated with your models. In this case you only have one model, which means that the function call will only create one table in your database:
- from app import db, Student
- db.create_all()
Leave the shell running, open another terminal window and navigate to your flask_app
directory. You will now see a new file called database.db
in flask_app
.
Note:
The db.create_all()
function does not recreate or update a table if it already exists. For example, if you modify your model by adding a new column, and run the db.create_all()
function, the change you make to the model will not be applied to the table if the table already exists in the database. The solution is to delete all existing database tables with the db.drop_all()
function and then recreate them with the db.create_all()
function like so:
- db.drop_all()
- db.create_all()
This will apply the modifications you make to your models, but will also delete all the existing data in the database. To update the database and preserve existing data, you’ll need to use schema migration, which allows you to modify your tables and preserve data. You can use the Flask-Migrate
extension to perform SQLAlchemy schema migrations through the Flask command-line interface.
If you receive an error, make sure your database URI and your model declaration are correct.
After creating the database and student table, you’ll use the flask shell to add some students to your database through the Student
model.
Use the same flask shell you opened earlier, or open a new one with your virtual environment activated in your flask_app
directory:
- flask shell
To add a student to your database, you’ll import the database object and the Student
model, and create an instance of the Student
model, passing it student data through keyword arguments as follows:
- from app import db, Student
- student_john = Student(firstname='john', lastname='doe',
- email='jd@example.com', age=23,
- bio='Biology student')
The student_john
object represents a student that will be added to the database, but this object has not been written to the database yet. Check out the object in the flask shell to see its representation string you constructed with the __repr__()
method:
- student_john
You’ll receive the following output:
Output<Student john>
You can get the value of columns using the class attributes you defined in the Student
model:
- student_john.firstname
- student_john.bio
Output'john'
'Biology student'
Because this student has not been added to the database yet, its ID will be None
:
- print(student_john.id)
OutputNone
To add this student to the database, you’ll first need to add it to a database session, which manages a database transaction. Flask-SQLAlchemy provides the db.session
object through which you can manage your database changes. Add the student_john
object to the session using the db.session.add()
method to prepare it to be written to the database:
- db.session.add(student_john)
This will issue an INSERT
statement, but you won’t get an ID back because the database transaction is still not committed. To commit the transaction and apply the change to database, use the db.session.commit()
method:
- db.session.commit()
Now that student John is added to the database, you can get its ID:
- print(student_john.id)
Output1
You can also use the db.session.add()
method to edit an item in the database. For example, you can modify the student’s email like so:
- student_john.email = 'john_doe@example.com'
- db.session.add(student_john)
- db.session.commit()
Use the Flask shell to add a few more students to your database:
- sammy = Student(firstname='Sammy',
- lastname='Shark',
- email='sammyshark@example.com',
- age=20,
- bio='Marine biology student')
-
- carl = Student(firstname='Carl',
- lastname='White',
- email='carlwhite@example.com',
- age=22,
- bio='Marine geology student')
-
- db.session.add(sammy)
- db.session.add(carl)
- db.session.commit()
Now, you can query all the records in the student table using the query
attribute with the all()
method:
- Student.query.all()
You’ll receive the following output:
Output[<Student john>, <Student Sammy>, <Student Carl>]
At this point, you have three students in your database. Next, you’ll create a Flask route for the index page and display all of the students in your database on it.
In this step, you’ll create a route and a template to display all the students in the database on the index page.
Leave the Flask shell running and open a new terminal window.
Open your app.py
file to add a route for the index page to it:
- nano app.py
Add the following route at the end of the file:
# ...
@app.route('/')
def index():
students = Student.query.all()
return render_template('index.html', students=students)
Save and close the file.
Here, you create an index()
view function using the app.route()
decorator. In this function, you query the database and get all the students using the Student
model with the query
attribute, which allows you to retrieve one or more items from the database using different methods. You use the all()
method to get all student entries in the database. You store the query result in a variable called students
and pass it to a template called index.html
that you render using the render_template()
helper function.
Before you create the index.html
template file on which you’ll display the existing students in the database, you’ll first create a base template, which will have all the basic HTML code other templates will also use to avoid code repetition. Then you’ll create the index.html
template file you rendered in your index()
function. To learn more about templates, see How to Use Templates in a Flask Application.
Create a templates
directory, then open a new template called base.html
:
- mkdir templates
- nano templates/base.html
Add the following code inside the base.html
file:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>{% block title %} {% endblock %} - FlaskApp</title>
<style>
.title {
margin: 5px;
}
.content {
margin: 5px;
width: 100%;
display: flex;
flex-direction: row;
flex-wrap: wrap;
}
.student {
flex: 20%;
padding: 10px;
margin: 5px;
background-color: #f3f3f3;
inline-size: 100%;
}
.bio {
padding: 10px;
margin: 5px;
background-color: #ffffff;
color: #004835;
}
.name a {
color: #00a36f;
text-decoration: none;
}
nav a {
color: #d64161;
font-size: 3em;
margin-left: 50px;
text-decoration: none;
}
</style>
</head>
<body>
<nav>
<a href="{{ url_for('index') }}">FlaskApp</a>
<a href="#">Create</a>
<a href="#">About</a>
</nav>
<hr>
<div class="content">
{% block content %} {% endblock %}
</div>
</body>
</html>
Save and close the file.
This base template has all the HTML boilerplate you’ll need to reuse in your other templates. The title
block will be replaced to set a title for each page, and the content
block will be replaced with the content of each page. The navigation bar has three links: one for the index page, which links to the index()
view function using the url_for()
helper function, one for a Create page, and one for an About page if you choose to add one to your application. You’ll edit this file later after you add a page for creating new students to make the Create link functional.
Next, open a new index.html
template file. This is the template you referenced in the app.py
file:
- nano templates/index.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1 class="title">{% block title %} Students {% endblock %}</h1>
<div class="content">
{% for student in students %}
<div class="student">
<p><b>#{{ student.id }}</b></p>
<b>
<p class="name">{{ student.firstname }} {{ student.lastname }}</p>
</b>
<p>{{ student.email }}</p>
<p>{{ student.age }} years old.</p>
<p>Joined: {{ student.created_at }}</p>
<div class="bio">
<h4>Bio</h4>
<p>{{ student.bio }}</p>
</div>
</div>
{% endfor %}
</div>
{% endblock %}
Save and close the file.
Here, you extend the base template and replace the contents of the content block. You use an <h1>
heading that also serves as a title. You use a Jinja for
loop in the line {% for student in students %}
to go through each student in the students
variable that you passed from the index()
view function to this template. You display the student ID, their first and last name, email, age, the date at which they were added to the database, and their bio.
While in your flask_app
directory with your virtual environment activated, tell Flask about the application (app.py
in this case) using the FLASK_APP
environment variable. Then set the FLASK_ENV
environment variable to development
to run the application in development mode and get access to the debugger. For more information about the Flask debugger, see How To Handle Errors in a Flask Application. Use the following commands to do this:
- export FLASK_APP=app
- export FLASK_ENV=development
Next, run the application:
- flask run
With the development server running, visit the following URL using your browser:
http://127.0.0.1:5000/
You’ll see the students you added to the database in a page similar to the following:
You’ve displayed the students you have in your database on the index page. Next, you’ll create a route for a student page, where you can display the details of each individual student.
In this step, you’ll use the Flask shell to query students by their ID, and create a route and a template to display the details of each student on a dedicated page.
By the end of this step, the URL http://127.0.0.1:5000/1
will be a page that displays the first student (because it has the ID 1
). The URL http://127.0.0.1:5000/ID
will display the post with the associated ID
number, if it exists.
Leave the development server running and open a new terminal window.
Open the Flask shell for a demonstration of how to query students:
- flask shell
To query records and retrieve data from the database, Flask-SQLAlchemy provides a query
attribute on the model class. You can use its methods to get records with a specific filter.
For example, you can use the filter_by()
method with a parameter such as firstname
that matches a column in the table with an argument to retrieve a specific student:
- from app import db, Student
- Student.query.filter_by(firstname='Sammy').all()
Output[<Student Sammy>]
Here you retrieve all the students with Sammy
as their first name. You use the all()
method to get a list of all the results. To get the first result, which is the only result here, you can use the first()
method:
- Student.query.filter_by(firstname='Sammy').first()
Output<Student Sammy>
To get a student by its ID, you can use filter_by(id=ID)
:
- Student.query.filter_by(id=3).first()
Or, you can use the shorter get()
method, which allows you to retrieve a specific item using its primary key:
- Student.query.get(3)
Both will give the same output:
Output<Student Carl>
You can now exit the shell:
- exit()
To retrieve a student by their ID, you’ll create a new route that renders a page for each individual student. You’ll use the get_or_404()
method Flask-SQLAlchemy provides, which is a variant of the get()
method. The difference is that get()
returns the value None
when no result matches the given ID, and get_or_404()
returns a 404 Not Found
HTTP response. Open app.py
for modification:
- nano app.py
Add the following route at the end of the file:
# ...
@app.route('/<int:student_id>/')
def student(student_id):
student = Student.query.get_or_404(student_id)
return render_template('student.html', student=student)
Save and close the file.
Here, you use the route '/<int:student_id>/'
, with int:
being a converter that converts the default string in the URL into an integer. And student_id
is the URL variable that will determine the student you’ll display on the page.
The ID is passed from the URL to the student()
view function through the student_id
parameter. Inside the function, you query the students collection and retrieve a student by the ID using the get_or_404()
method. This will save the student data in the student
variable if it exists, and respond with a 404 Not Found
HTTP error if no student with the given ID exists in the database.
You render a template called student.html
and pass it the student you retrieved.
Open this new student.html
template file:
- nano templates/student.html
Type the following code in this new student.html
file. This will be similar to the index.html
template, except that it will only display a single student:
{% extends 'base.html' %}
{% block content %}
<span class="title">
<h1>{% block title %} {{ student.firstname }} {{ student.lastname }}{% endblock %}</h1>
</span>
<div class="content">
<div class="student">
<p><b>#{{ student.id }}</b></p>
<b>
<p class="name">{{ student.firstname }} {{ student.lastname }}</p>
</b>
<p>{{ student.email }}</p>
<p>{{ student.age }} years old.</p>
<p>Joined: {{ student.created_at }}</p>
<div class="bio">
<h4>Bio</h4>
<p>{{ student.bio }}</p>
</div>
</div>
</div>
{% endblock %}
Save and close the file.
In this file, you extend the base template, setting the student’s full name as a title for the page. You display the student ID, the student’s first and last name, email, age, the date of record creation, and their bio.
Use your browser to navigate to the URL for the second student:
http://127.0.0.1:5000/2
You’ll see a page similar to the following:
Now, edit index.html
to make each student name link to their page:
- nano templates/index.html
Edit the for
loop to look as follows:
{% for student in students %}
<div class="student">
<p><b>#{{ student.id }}</b></p>
<b>
<p class="name">
<a href="{{ url_for('student', student_id=student.id)}}">
{{ student.firstname }} {{ student.lastname }}
</a>
</p>
</b>
<p>{{ student.email }}</p>
<p>{{ student.age }} years old.</p>
<p>Joined: {{ student.created_at }}</p>
<div class="bio">
<h4>Bio</h4>
<p>{{ student.bio }}</p>
</div>
</div>
{% endfor %}
Save and close the file.
You added an <a>
tag to the student’s full name which links to the student page using the url_for()
function, passing the student ID that’s stored in student.id
to the student()
view function.
Navigate to your index page or refresh it:
http://127.0.0.1:5000/
You’ll now see that each student name links to the proper student page.
After creating a page for individual students, you’ll next add a page for adding new students to the database.
In this step, you’ll add a new route to your application for adding new students to the database using web forms.
You’ll render a page with a web form where users enter the student’s data. Then you’ll handle the form submission, create an object for the new student using the Student
model, add it to the session, then commit the transaction, similar to how you’ve added student entries in Step 2.
Leave the development server running and open a new terminal window.
First, open your app.py
file:
- nano app.py
Add the following route at the end of the app.py
file:
# ...
@app.route('/create/', methods=('GET', 'POST'))
def create():
return render_template('create.html')
Save and close the file.
In this route, you pass the tuple ('GET', 'POST')
to the methods
parameter to allow both GET and POST requests. GET requests are used to retrieve data from the server. POST requests are used to post data to a specific route. By default, only GET requests are allowed. When the user first requests the /create
route using a GET request, a template file called create.html
will be rendered. You will later edit this route to handle POST requests for when users fill in and submit the web form for adding new students.
Open the new create.html
template:
- nano templates/create.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1 style="width: 100%">{% block title %} Add a New Student {% endblock %}</h1>
<form method="post">
<p>
<label for="firstname">First Name</label>
<input type="text" name="firstname"
placeholder="First name">
</input>
</p>
<p>
<label for="lastname">Last Name</label>
<input type="text" name="lastname"
placeholder="Last name">
</input>
</p>
<p>
<label for="email">Email</label>
<input type="email" name="email"
placeholder="Student email">
</input>
</p>
<p>
<label for="age">Age</label>
<input type="number" name="age"
placeholder="Age">
</input>
</p>
<p>
<label for="bio">Bio</label>
<br>
<textarea name="bio"
placeholder="Bio"
rows="15"
cols="60"
></textarea>
</p>
<p>
<button type="submit">Submit</button>
</p>
</form>
{% endblock %}
Save and close the file.
You extend the base template, set a heading as a title, and use a <form>
tag with the attribute method
set to post
to indicate that the form will submit a POST request.
You have two text fields with the names firstname
and lastname
. You’ll use these names to access the form data the user submits in your view function later.
You have an email field with the name email
, a number field for the student’s age, and a text area for the student’s bio.
Last, you have a Submit button at the end of the form.
Now, with the development server running, use your browser to navigate to the /create
route:
http://127.0.0.1:5000/create
You will see an Add a New Student page with a web form and a Submit button like so:
If you fill in the form and submit it, sending a POST request to the server, nothing happens because you did not handle POST requests on the /create
route.
Open app.py
to handle the POST request the user submits:
- nano app.py
Edit the /create
route to look as follows:
@app.route('/create/', methods=('GET', 'POST'))
def create():
if request.method == 'POST':
firstname = request.form['firstname']
lastname = request.form['lastname']
email = request.form['email']
age = int(request.form['age'])
bio = request.form['bio']
student = Student(firstname=firstname,
lastname=lastname,
email=email,
age=age,
bio=bio)
db.session.add(student)
db.session.commit()
return redirect(url_for('index'))
return render_template('create.html')
Save and close the file.
You handle POST requests inside the if request.method == 'POST'
condition. You extract the first name, last name, email, age, and bio the user submits from the request.form
object. You convert the age that gets passed as a string into an integer using the int()
Python function. You construct a student
object using the Student
model. You add the student object to the database session, then commit the transaction.
Lastly, you redirect the user to the index page where they can see the newly added student below the existing students.
With the development server running, use your browser to navigate to the /create
route:
http://127.0.0.1:5000/create
Fill in the form with some data and submit it.
You’ll be redirected to the index page where you’ll see your newly added student.
Now that you have the functionality to add new students, you’ll need to add a link to the Create page in the navigation bar. Open base.html
:
- nano templates/base.html
Edit the <body>
tag by modifying the value of the href
attribute for the Create
link:
<body>
<nav>
<a href="{{ url_for('index') }}">FlaskApp</a>
<a href="{{ url_for('create') }}">Create</a>
<a href="#">About</a>
</nav>
<hr>
<div class="content">
{% block content %} {% endblock %}
</div>
</body>
Save and close the file.
Refresh your index page and you’ll notice the Create link in the navigation bar is now functional.
You now have a page with a web form for adding new students. For more on web forms, see How To Use Web Forms in a Flask Application. For a more advanced and more secure method of managing web forms, see How To Use and Validate Web Forms with Flask-WTF. Next, you’ll add a page for editing the data of existing students.
In this step, you’ll add a new page to your application for editing existing student data. You’ll add a new /ID/edit/
route to edit the data of students based on their ID.
Open app.py
:
- nano app.py
Add the following route to the end of the file. This fetches the student entry you want to edit using its ID. It extracts the new student data submitted via a web form you’ll will create later. Then it edits the student data, and redirects the user to the index page:
# ...
@app.route('/<int:student_id>/edit/', methods=('GET', 'POST'))
def edit(student_id):
student = Student.query.get_or_404(student_id)
if request.method == 'POST':
firstname = request.form['firstname']
lastname = request.form['lastname']
email = request.form['email']
age = int(request.form['age'])
bio = request.form['bio']
student.firstname = firstname
student.lastname = lastname
student.email = email
student.age = age
student.bio = bio
db.session.add(student)
db.session.commit()
return redirect(url_for('index'))
return render_template('edit.html', student=student)
Save and close the file.
Here, you have the route /<int:student_id>/edit/
that accepts both POST and GET methods, with student_id
as a URL variable that passes the ID to the edit()
view function.
You use the get_or_404()
query method on the Student
model to get the student associated with the given student ID. This will respond with a 404 Not Found
error in case no student with the given ID exists in the database.
If the given ID has a student associated with it, code execution continues to the if request.method == 'POST'
condition. If the request was a GET request, meaning that the user did not submit a form, then this condition is false, and the code inside it will be skipped to the line return render_template('edit.html', student=student)
. This renders an edit.html
template, passing it the student object you got from the database, allowing you to fill the student web form with current student data. You will create this edit.html
template later.
When a user edits student data and submits the form, the code inside the if request.method == 'POST'
is executed. You extract the submitted student data from the request.form
object into corresponding variables. You set each attribute of the student
object to the newly submitted data to change column values as you’ve done in Step 2. If no change was performed on a field on the web form, the value of that column will stay the same in the database.
After you set the student data to the newly submitted data, you add the student
object to the database session, then you commit the changes. Lastly, you redirect the user to the index page.
Next, you need to create a page where users can do the editing. Open a new edit.html
template:
- nano templates/edit.html
This new file will have a web form similar to the one that’s in the create.html
file with current student data as default values for the fields. Add the following code inside it:
{% extends 'base.html' %}
{% block content %}
<h1 style="width: 100%">
{% block title %} Edit {{ student.firstname }}
{{ student.lastname }}'s Details
{% endblock %}
</h1>
<form method="post">
<p>
<label for="firstname">First Name</label>
<input type="text" name="firstname"
value={{ student.firstname }}
placeholder="First name">
</input>
</p>
<p>
<label for="lastname">Last Name</label>
<input type="text" name="lastname"
value={{ student.lastname }}
placeholder="Last name">
</input>
</p>
<p>
<label for="email">Email</label>
<input type="email" name="email"
value={{ student.email }}
placeholder="Student email">
</input>
</p>
<p>
<label for="age">Age</label>
<input type="number" name="age"
value={{ student.age }}
placeholder="Age">
</input>
</p>
<p>
<label for="bio">Bio</label>
<br>
<textarea name="bio"
placeholder="Bio"
rows="15"
cols="60"
>{{ student.bio }}</textarea>
</p>
<p>
<button type="submit">Submit</button>
</p>
</form>
{% endblock %}
Save and close the file.
The title has the student’s first name and last name. The value
attribute of each input field and the value of the bio text area are set to the corresponding value in the student
object you passed from the edit()
view function to the edit.html
template.
Now, navigate to the following URL to edit the first student’s details:
http://127.0.0.1:5000/1/edit
You will see a page similar to the following:
Edit the student’s data and submit the form. You’ll be redirected to the index page, and the student’s information will be updated.
Next, you’ll add an Edit button below each student on the index page to link to their edit page. Open the index.html
template file:
- nano templates/index.html
Edit the for
loop in this index.html
file to look exactly like the following:
{% for student in students %}
<div class="student">
<p><b>#{{ student.id }}</b></p>
<b>
<p class="name">
<a href="{{ url_for('student', student_id=student.id)}}">
{{ student.firstname }} {{ student.lastname }}
</a>
</p>
</b>
<p>{{ student.email }}</p>
<p>{{ student.age }} years old.</p>
<p>Joined: {{ student.created_at }}</p>
<div class="bio">
<h4>Bio</h4>
<p>{{ student.bio }}</p>
</div>
<a href="{{ url_for('edit', student_id=student.id) }}">Edit</a>
</div>
{% endfor %}
Save and close the file.
Here you add an <a>
tag to link to the edit()
view function, passing in the student.id
value to link to the edit page of each student with an Edit link.
You now have a page for editing existing students. Next, you’ll add a Delete button to delete students from the database.
In this step, you’ll add a new route and Delete button for deleting existing students.
First, you’ll add a new /id/delete
route that accepts POST requests. Your new delete()
view function will receive the ID of the student you want to delete, pass the ID to the get_or_404()
query method on the Student
model to get it if it exists, or respond with a 404 Not Found
page if no student with the given ID was found on the database.
Open app.py
for editing:
- nano app.py
Add the following route to the end of the file:
# ...
@app.post('/<int:student_id>/delete/')
def delete(student_id):
student = Student.query.get_or_404(student_id)
db.session.delete(student)
db.session.commit()
return redirect(url_for('index'))
Save and close the file.
Here, instead of using the usual app.route
decorator, you use the app.post
decorator introduced in Flask version 2.0.0, which added shortcuts for common HTTP methods. For example, @app.post("/login")
is a shortcut for @app.route("/login", methods=["POST"])
. This means that this view function only accepts POST requests, and navigating to the /ID/delete
route on your browser will return a 405 Method Not Allowed
error, because web browsers default to GET requests. To delete a student, the user clicks on a button that sends a POST request to this route.
This delete()
view function receives the ID of the student to be deleted via the student_id
URL variable. You use the get_or_404()
method to get a student and save it in a student
variable, or respond with a 404 Not Found
in case the student doesn’t exist. You use the delete()
method on the database session in the line db.session.delete(student)
, passing it the student object. This sets up the session to delete the student whenever the transaction is committed. Because you don’t need to perform any other modifications, you directly commit the transaction using db.session.commit()
. Lastly, you redirect the user to the index page.
Next, edit the index.html
template to add a Delete Student button:
- nano templates/index.html
Edit the for
loop by adding a new <form>
tag directly below the Edit link:
{% for student in students %}
<div class="student">
<p><b>#{{ student.id }}</b></p>
<b>
<p class="name">
<a href="{{ url_for('student', student_id=student.id)}}">
{{ student.firstname }} {{ student.lastname }}
</a>
</p>
</b>
<p>{{ student.email }}</p>
<p>{{ student.age }} years old.</p>
<p>Joined: {{ student.created_at }}</p>
<div class="bio">
<h4>Bio</h4>
<p>{{ student.bio }}</p>
</div>
<a href="{{ url_for('edit', student_id=student.id) }}">Edit</a>
<hr>
<form method="POST"
action="{{ url_for('delete', student_id=student.id) }}">
<input type="submit" value="Delete Student"
onclick="return confirm('Are you sure you want to delete this entry?')">
</form>
</div>
{% endfor %}
Save and close the file.
Here, you have a web form that submits a POST request to the delete()
view function. You pass student.id
as an argument for the student_id
parameter to specify the student entry to be deleted. You use the confirm()
method function available in web browsers to display a confirmation message before submitting the request.
Now refresh your index page.
You’ll see a Delete Student button below each student entry. Click on it, and confirm the deletion. You’ll be redirected to the index page, and the student will no longer be there.
You now have a way of deleting students from the database in your student management application.
You built a small Flask web application for managing students using Flask and Flask-SQLAlchemy with an SQLite database. You learned how to connect to your database, set up database models that represent your tables, add items to your database, query your table, and modify database data.
Using SQLAlchemy in your application allows you to use Python classes and objects to manage your SQL database. Instead of SQLite, you can use another database engine, and other than the SQLALCHEMY_DATABASE_URI
configuration responsible for the connection, you don’t need to change anything in your core application code. That allows you to move from one SQL database engine to another with minimal code change. See the Flask-SQLAlchemy documentation for more information.
If you would like to read more about Flask, check out the other tutorials in the How To Build Web Applications with Flask series.
]]>The relational data model, which organizes data in tables of rows and columns, predominates in database management tools. Today there are other data models, including NoSQL and NewSQL, but relational database management systems (RDBMSs) remain dominant for storing and managing data worldwide.
This article compares and contrasts three of the most widely implemented open-source RDBMSs: SQLite, MySQL, and PostgreSQL. Specifically, it will explore the data types that each RDBMS uses, their advantages and disadvantages, and situations where they are best optimized.
Databases are logically modelled clusters of information, or data. A database management system (DBMS), on the other hand, is a computer program that interacts with a database. A DBMS allows you to control access to a database, write data, run queries, and perform any other tasks related to database management.
Although database management systems are often referred to as “databases,” the two terms are not interchangeable. A database can be any collection of data, not just one stored on a computer. In contrast, a DBMS specifically refers to the software that allows you to interact with a database.
All database management systems have an underlying model that structures how data is stored and accessed. A relational database management system is a DBMS that employs the relational data model. In this relational model, data is organized into tables. Tables, in the context of RDBMSs, are more formally referred to as relations. A relation is a set of tuples, which are the rows in a table, and each tuple shares a set of attributes, which are the columns in a table:
Most relational databases use structured query language (SQL) to manage and query data. However, many RDBMSs use their own particular dialect of SQL, which may have certain limitations or extensions. These extensions typically include extra features that allow users to perform more complex operations than they otherwise could with standard SQL.
Note: The term “standard SQL” comes up several times throughout this guide. SQL standards are jointly maintained by the American National Standards Institute (ANSI), the International Organization for Standardization (ISO), and the International Electrotechnical Commission (IEC). Whenever this article mentions “standard SQL” or “the SQL standard,” it’s referring to the current version of the SQL standard published by these bodies.
It should be noted that the full SQL standard is large and complex: full core SQL:2011 compliance requires 179 features. Because of this, most RDBMSs don’t support the entire standard, although some do come closer to full compliance than others.
Each column is assigned a data type which dictates what kind of entries are allowed in that column. Different RDBMSs implement different data types, which aren’t always directly interchangeable. Some common data types include dates, strings, integers, and Booleans.
Storing integers in a database is more nuanced than putting numbers in a table. Numeric data types can either be signed, meaning they can represent both positive and negative numbers, or unsigned, which means they can only represent positive numbers. For example, MySQL’s tinyint
data type can hold 8 bits of data, which equates to 256 possible values. The signed range of this data type is from -128 to 127, while the unsigned range is from 0 to 255.
Being able to control what data is allowed into a database is important. Sometimes, a database administrator will impose a constraint on a table to limit what values can be entered into it. A constraint typically applies to one particular column, but some constraints can also apply to an entire table. Here are some constraints that are commonly used in SQL:
UNIQUE
: Applying this constraint to a column ensures that no two entries in that column are identical.NOT NULL
: This constraint ensures that a column doesn’t have any NULL
entries.PRIMARY KEY
: A combination of UNIQUE
and NOT NULL
, the PRIMARY KEY
constraint ensures that no entry in the column is NULL
and that every entry is distinct.FOREIGN KEY
: A FOREIGN KEY
is a column in one table that refers to the PRIMARY KEY
of another table. This constraint is used to link two tables together. Entries to the FOREIGN KEY
column must already exist in the parent PRIMARY KEY
column for the write process to succeed.CHECK
: This constraint limits the range of values that can be entered into a column. For example, if your application is intended only for residents of Alaska, you could add a CHECK
constraint on a ZIP code column to only allow entries between 99501 and 99950.If you’d like to learn more about database management systems, check out our article on A Comparison of NoSQL Database Management Systems and Models.
Now that we’ve covered relational database management systems generally, let’s move onto the first of the three open-source relational databases this article will cover: SQLite.
SQLite is a self-contained, file-based, and fully open-source RDBMS known for its portability, reliability, and strong performance even in low-memory environments. Its transactions are ACID-compliant, even in cases where the system crashes or undergoes a power outage.
The SQLite project’s website describes it as a “serverless” database. Most relational database engines are implemented as a server process in which programs communicate with the host server through an interprocess communication that relays requests. In contrast, SQLite allows any process that accesses the database to read and write to the database disk file directly. This simplifies SQLite’s setup process, since it eliminates any need to configure a server process. Likewise, there’s no configuration necessary for programs that will use the SQLite database: all they need is access to the disk.
SQLite is free and open-source software, and no special license is required to use it. However, the project does offer several extensions — each for a one-time fee — that help with compression and encryption. Additionally, the project offers various commercial support packages, each for an annual fee.
SQLite allows a variety of data types, organized into the following storage classes:
Data Type | Explanation |
---|---|
null |
Includes any NULL values. |
integer |
Signed integers, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. |
real |
Real numbers, or floating point values, stored as 8-byte floating point numbers. |
text |
Text strings stored using the database encoding, which can either be UTF-8, UTF-16BE or UTF-16LE. |
blob |
Any blob of data, with every blob stored exactly as it was input. |
In the context of SQLite, the terms “storage class” and “data type” are considered interchangeable. If you’d like to learn more about SQLite’s data types and SQLite type affinity, check out SQLite’s official documentation on the subject.
According to the DB-Engines Ranking, MySQL has been the most popular open-source RDBMS since the site began tracking database popularity in 2012. It is a feature-rich product that powers many of the world’s largest websites and applications, including Twitter, Facebook, Netflix, and Spotify. Getting started with MySQL is relatively straightforward, thanks in large part to its exhaustive documentation and large community of developers, as well as the abundance of MySQL-related resources online.
MySQL was designed for speed and reliability, at the expense of full adherence to standard SQL. The MySQL developers continually work towards closer adherence to standard SQL, but it still lags behind other SQL implementations. It does, however, come with various SQL modes and extensions that bring it closer to compliance.
Unlike applications using SQLite, applications using a MySQL database access it through a separate daemon process. Because the server process stands between the database and other applications, it allows for greater control over who has access to the database.
MySQL has inspired a wealth of third-party applications, tools, and integrated libraries that extend its functionality and help make it easier to work with. Some of the more widely-used of these third-party tools are phpMyAdmin, DBeaver, and HeidiSQL.
MySQL’s data types can be organized into three broad categories: numeric types, date and time types, and string types.
Numeric types:
Data Type | Explanation |
---|---|
tinyint |
A very small integer. The signed range for this numeric data type is -128 to 127, while the unsigned range is 0 to 255. |
smallint |
A small integer. The signed range for this numeric type is -32768 to 32767, while the unsigned range is 0 to 65535. |
mediumint |
A medium-sized integer. The signed range for this numeric data type is -8388608 to 8388607, while the unsigned range is 0 to 16777215. |
int or integer |
A normal-sized integer. The signed range for this numeric data type is -2147483648 to 2147483647, while the unsigned range is 0 to 4294967295. |
bigint |
A large integer. The signed range for this numeric data type is -9223372036854775808 to 9223372036854775807, while the unsigned range is 0 to 18446744073709551615. |
float |
A small (single-precision) floating-point number. |
double , double precision , or real |
A normal sized (double-precision) floating-point number. |
dec , decimal , fixed , or numeric |
A packed fixed-point number. The display length of entries for this data type is defined when the column is created, and every entry adheres to that length. |
bool or boolean |
A Boolean is a data type that only has two possible values, usually either true or false . |
bit |
A bit value type for which you can specify the number of bits per value, from 1 to 64. |
Date and time types:
Data Type | Explanation |
---|---|
date |
A date, represented as YYYY-MM-DD . |
datetime |
A timestamp showing the date and time, displayed as YYYY-MM-DD HH:MM:SS . |
timestamp |
A timestamp indicating the amount of time since the Unix epoch (00:00:00 on January 1, 1970). |
time |
A time of day, displayed as HH:MM:SS . |
year |
A year expressed in either a 2 or 4 digit format, with 4 digits being the default. |
String types:
Data Type | Explanation |
---|---|
char |
A fixed-length string; entries of this type are padded on the right with spaces to meet the specified length when stored. |
varchar |
A string of variable length. |
binary |
Similar to the char type, but a binary byte string of a specified length rather than a nonbinary character string. |
varbinary |
Similar to the varchar type, but a binary byte string of a variable length rather than a nonbinary character string. |
blob |
A binary string with a maximum length of 65535 (2^16 - 1) bytes of data. |
tinyblob |
A blob column with a maximum length of 255 (2^8 - 1) bytes of data. |
mediumblob |
A blob column with a maximum length of 16777215 (2^24 - 1) bytes of data. |
longblob |
A blob column with a maximum length of 4294967295 (2^32 - 1) bytes of data. |
text |
A string with a maximum length of 65535 (2^16 - 1) characters. |
tinytext |
A text column with a maximum length of 255 (2^8 - 1) characters. |
mediumtext |
A text column with a maximum length of 16777215 (2^24 - 1) characters. |
longtext |
A text column with a maximum length of 4294967295 (2^32 - 1) characters. |
enum |
An enumeration, which is a string object that takes a single value from a list of values that are declared when the table is created. |
set |
Similar to an enumeration, a string object that can have zero or more values, each of which must be chosen from a list of allowed values that are specified when the table is created. |
FULL JOIN
clauses.PostgreSQL, also known as Postgres, bills itself as “the most advanced open-source relational database in the world.” It was created with the goal of being highly extensible and standards compliant. PostgreSQL is an object-relational database, meaning that although it’s primarily a relational database it also includes features — like table inheritance and function overloading — that are more often associated with object databases.
Postgres is capable of efficiently handling multiple tasks at the same time, a characteristic known as concurrency. It achieves this without read locks thanks to its implementation of Multiversion Concurrency Control (MVCC), which ensures the atomicity, consistency, isolation, and durability of its transactions, also known as ACID compliance.
PostgreSQL isn’t as widely used as MySQL, but there are still a number of third-party tools and libraries designed to simplify working with with PostgreSQL, including pgAdmin and Postbird.
PostgreSQL supports numeric, string, and date and time data types like MySQL. In addition, it supports data types for geometric shapes, network addresses, bit strings, text searches, and JSON entries, as well as several idiosyncratic data types.
Numeric types:
Data Type | Explanation |
---|---|
bigint |
A signed 8 byte integer. |
bigserial |
An auto-incrementing 8 byte integer. |
double precision |
An 8 byte double precision floating-point number. |
integer |
A signed 4 byte integer. |
numeric or decimal |
A number of selectable precision, recommended for use in cases where exactness is crucial, such as monetary amounts. |
real |
A 4 byte single precision floating-point number. |
smallint |
A signed 2 byte integer. |
smallserial |
An auto-incrementing 2 byte integer. |
serial |
An auto-incrementing 4 byte integer. |
Character types:
Data Type | Explanation |
---|---|
character |
A character string with a specified fixed length. |
character varying or varchar |
A character string with a variable but limited length. |
text |
A character string of a variable, unlimited length. |
Date and time types:
Data Type | Explanation |
---|---|
date |
A calendar date consisting of the day, month, and year. |
interval |
A time span. |
time or time without time zone |
A time of day, not including the time zone. |
time with time zone |
A time of day, including the time zone. |
timestamp or timestamp without time zone |
A date and time, not including the time zone. |
timestamp with time zone |
A date and time, including the time zone. |
Geometric types:
Data Type | Explanation |
---|---|
box |
A rectangular box on a plane. |
circle |
A circle on a plane. |
line |
An infinite line on a plane. |
lseg |
A line segment on a plane. |
path |
A geometric path on a plane. |
point |
A geometric point on a plane. |
polygon |
A closed geometric path on a plane. |
Network address types:
Data Type | Explanation |
---|---|
cidr |
An IPv4 or IPv6 network address. |
inet |
An IPv4 or IPv6 host address. |
macaddr |
A Media Access Control (MAC) address. |
Bit string types:
Data Type | Explanation |
---|---|
bit |
A fixed-length bit string. |
bit varying |
A variable-length bit string. |
Text search types:
Data Type | Explanation |
---|---|
tsquery |
A text search query. |
tsvector |
A text search document. |
JSON types:
Data Type | Explanation |
---|---|
json |
Textual JSON data. |
jsonb |
Decomposed binary JSON data. |
Other data types:
Data Type | Explanation |
---|---|
boolean |
A logical Boolean, representing either true or false . |
bytea |
Short for “byte array”, this type is used for binary data. |
money |
An amount of currency. |
pg_lsn |
A PostgreSQL Log Sequence Number. |
txid_snapshot |
A user-level transaction ID snapshot. |
uuid |
A universally unique identifier. |
xml |
XML data. |
Today, SQLite, MySQL, and PostgreSQL are the three most popular open-source relational database management systems in the world. Each has its own unique features and limitations, and excels in particular scenarios. There are quite a few variables at play when deciding on an RDBMS, and the choice is rarely as simple as picking the fastest one or the one with the most features. The next time you’re in need of a relational database solution, be sure to research these and other tools in depth to find the one that best suits your needs.
If you’d like to learn more about SQL and how to use it to manage a relational database, we encourage you to refer to our How To Manage an SQL Database cheat sheet. On the other hand, if you’d like to learn about non-relational (or NoSQL) databases, check out our Comparison Of NoSQL Database Management Systems.
When I go to deploy, the build phase seems to be successful (I get the checkmark and the build log finishes with “build complete”) but the deploy phase fails.
Two problems I hope to solve:
[2022-01-01 23:43:18] => Building app
[2022-01-01 23:43:18]
[2022-01-01 23:43:19] -----> Using Python version specified in runtime.txt
[2022-01-01 23:43:19] -----> Installing python-3.10.1
[2022-01-01 23:43:35] -----> Installing pip 21.3.1, setuptools 57.5.0 and wheel 0.37.0
[2022-01-01 23:43:47] -----> Installing SQLite3
[2022-01-01 23:44:00] rm: cannot remove '/workspace/.heroku/python/apt/state/lists': Directory not empty
[2022-01-01 23:44:00] Sqlite3 failed to install.
[2022-01-01 23:47:16] * Serving Flask app 'application' (lazy loading)
[2022-01-01 23:47:16] * Environment: production
[2022-01-01 23:47:16] WARNING: This is a development server. Do not use it in a production deployment.
[2022-01-01 23:47:16] Use a production WSGI server instead.
[2022-01-01 23:47:16] * Debug mode: off
[2022-01-01 23:47:19] INFO: * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
Thanks for any help or suggestions you can offer!
]]>In web applications, you usually need a database, which is an organized collection of data. You use a database to store and maintain persistent data that can be retrieved and manipulated efficiently. For example, in a social media application, you have a database where user data (personal information, posts, comments, followers) is stored in a way that can be efficiently manipulated. You can add data to a database, retrieve it, modify it, or delete it, depending on different requirements and conditions. In a web application, these requirements might be a user adding a new post, deleting a post, or deleting their account, which might or might not delete their posts. The actions you perform to manipulate data will depend on specific features in your application. For example, you might not want users to add posts with no titles.
Flask is a lightweight Python web framework that provides useful tools and features for creating web applications in the Python Language. SQLite is a simple and fast open source SQL engine that can be used with Python to store and manipulate application data. SQLite works well with Python because the Python standard library provides the sqlite3
module, which you can use to interact with any SQLite database without having to install anything. Using SQLite with Python also requires minimal setup compared to other database engines.
In this tutorial, you’ll build a small web application that demonstrates how to use SQLite with Flask to perform basic data manipulation covering CRUD: Create, Read, Update, and Delete. The web application will be a basic blog that displays posts on the index page. Users can create, edit, and delete individual posts.
A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial we’ll call our project directory flask_app
.
An understanding of basic Flask concepts, such as routes, view functions, and templates. If you are not familiar with Flask, check out How to Create Your First Web Application Using Flask and Python and How to Use Templates in a Flask Application.
An understanding of basic HTML concepts. You can review our How To Build a Website with HTML tutorial series for background knowledge.
A basic understanding of how to use SQLite. See How To Install and Use SQLite on Ubuntu 20.04.
In this step, you’ll set up the SQLite database you’ll use to store your data (the blog posts for your application). You’ll then populate the database with a few example entries.
You will use the sqlite3
module to interact with the database, which is readily available in the standard Python library.
Data in SQLite is stored in tables and columns, so you first need to create a table called posts
with the necessary columns. You’ll create a .sql
file that contains SQL commands to create the posts
table with a few columns. You’ll then use this schema file to create the database.
Open a database schema file called schema.sql
inside your flask_app
directory:
- nano schema.sql
Type the following SQL commands inside this file:
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL,
content TEXT NOT NULL
);
Save and close the file.
In this schema file, you first delete the posts
table if it already exists. This avoids the possibility of another table named posts
existing, which might result in confusing behavior (for example, if it has different columns). This isn’t the case here, because you haven’t created the table yet, so the SQL command won’t be executed. Note that this will delete all of the existing data whenever you execute this schema file. For our purposes, you will only execute this schema once, but you might want to execute it again to delete whatever data you inserted and start with an empty database again.
Next, you use CREATE TABLE posts
to create the posts
table with the following columns:
id
: An integer that represents a primary key. This key will get assigned a unique value by the database for each entry (that is, each blog post). AUTOINCREMENT
automatically increments the post IDs, so that the first post will have an ID of 1
, and the post added after it will have an ID of 2
, and so on. Each post will always have the same ID, even if other posts are deleted.created
: The time the blog post was created. NOT NULL
signifies that this column should not be empty, and the DEFAULT
value is the CURRENT_TIMESTAMP
value, which is the time at which the post was added to the database. Just like id
, you don’t need to specify a value for this column, as it will be automatically filled in.title
: The post title. NOT NULL
signifies that this column can’t be empty.content
: The post content. NOT NULL
signifies that this column can’t be empty.Now, you’ll use the schema.sql
file to create the database. To do so, you’ll create a Python file that will generate an SQLite .db
database file based on this schema.sql
file. Open a file named init_db.py
inside your flask_app
directory:
- nano init_db.py
Add the following code to it:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('First Post', 'Content for the first post')
)
cur.execute("INSERT INTO posts (title, content) VALUES (?, ?)",
('Second Post', 'Content for the second post')
)
connection.commit()
connection.close()
You first import the sqlite3
module. You open a connection to a database file named database.db
, which will be created once you run the Python file. Then you use the open()
function to open the schema.sql
file. Next you execute its contents using the executescript()
method that executes multiple SQL statements at once, which will create the posts
table. You create a Cursor object that allows you to process rows in a database. In this case, you’ll use the cursor’s execute()
method to execute two INSERT
SQL statements to add two blog posts to your posts
table. Finally, you commit the changes and close the connection.
Save and close the file and then run it in the terminal using the python
command:
- python init_db.py
Once the file finishes execution, a new file called database.db
will appear in your flask_app
directory. This means you’ve successfully set up your database.
Next, you’ll create a small Flask application, retrieve the two posts you inserted into the database, and display them on the index page.
In this step, you will create a Flask application with an index page where the blog posts you have in your database are displayed.
With your programming environment activated and Flask installed, open a file called app.py
for editing inside your flask_app
directory:
- nano app.py
This file will set up your database connection and create a single Flask route to use that connection. Add the following code to the file:
import sqlite3
from flask import Flask, render_template
app = Flask(__name__)
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
@app.route('/')
def index():
conn = get_db_connection()
posts = conn.execute('SELECT * FROM posts').fetchall()
conn.close()
return render_template('index.html', posts=posts)
Save and close the file.
In the code above, you first import the sqlite3
module to use it to connect to your database. Then you import the Flask
class and the render_template()
function from the flask
package. You make a Flask application instance called app
. You define a function called get_db_connection()
, which opens a connection to the database.db
database file you created earlier, and sets the row_factory
attribute to sqlite3.Row
so you can have name-based access to columns. This means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn
connection object you’ll be using to access the database.
You then use the app.route()
decorator to create a Flask view function called index()
. You use the get_db_connection()
function to open a database connection. Then you execute an SQL query to select all entries from the posts table. You use the fetchall()
method to fetch all the rows of the query result, this will return a list of the posts you inserted into the database in the previous step.
You close the database connection using the close()
method and return the result of rendering the index.html
template. You also pass the posts
object as an argument, which contains the results you got from the database. This will allow you to access the blog posts in the index.html
template.
To display the posts you have in your database on the index page, you will first create a base template, which will have all the basic HTML code other templates will also use to avoid code repetition. Then you’ll create the index.html
template file you rendered in your index()
function. To learn more about templates, see How to Use Templates in a Flask Application.
Create a templates directory, then open a new template called base.html
:
- mkdir templates
- nano templates/base.html
Add the following code inside the base.html
file:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>{% block title %} {% endblock %}- FlaskApp</title>
<style>
.post {
padding: 10px;
margin: 5px;
background-color: #f3f3f3
}
nav a {
color: #d64161;
font-size: 3em;
margin-left: 50px;
text-decoration: none;
}
</style>
</head>
<body>
<nav>
<a href="{{ url_for('index') }}">FlaskApp</a>
<a href="#">About</a>
</nav>
<hr>
<div class="content">
{% block content %} {% endblock %}
</div>
</body>
</html>
Save and close the file.
This base template has all the HTML boilerplate you’ll need to reuse in your other templates. The title
block will be replaced to set a title for each page, and the content
block will be replaced with the content of each page. The navigation bar has two links, one for the index page where you use the url_for()
helper function to link to the index()
view function, and the other for an About page if you choose to include one in your application.
Next, open a template called index.html
. This is the template you referenced in the app.py
file:
- nano templates/index.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Posts {% endblock %}</h1>
{% for post in posts %}
<div class='post'>
<p>{{ post['created'] }}</p>
<h2>{{ post['title'] }}</h2>
<p>{{ post['content'] }}</p>
</div>
{% endfor %}
{% endblock %}
Save and close the file.
In the code above, you extend the base.html
template and replace the contents of the content
block. You use an <h1>
heading that also serves as a title.
You use a Jinja for
loop in the line {% for post in posts %}
to go through each post in the posts
list. You access the creation date via {{ post['created'] }}
, the title via {{ post['title'] }}
, and the post content via {{ post['content'] }}
.
While in your flask_app
directory with your virtual environment activated, tell Flask about the application (app.py
in this case) using the FLASK_APP
environment variable:
- export FLASK_APP=app
Then set the FLASK_ENV
environment variable to development
to run the application in development mode and get access to the debugger. For more information about the Flask debugger, see How To Handle Errors in a Flask Application. Use the following commands to do this (on Windows, use set
instead of export
):
- export FLASK_ENV=development
Next, run the application:
- flask run
With the development server running, visit the following URL using your browser:
http://127.0.0.1:5000/
You’ll see the posts you added to the database on the first initiation.
You’ve displayed the posts in your database on the index page. You now need to allow users to add new posts. You’ll add a new route for adding posts in the next step.
In this step, you will add a new route to your Flask application that allows users to add new blog posts to the database, which will then appear on the index page.
You’ll add a page with a web form where users enter the post title and post content. This form will be validated to make sure users don’t submit an empty form. To inform users the form is invalid, you’ll use a flash message which will only be shown once and will disappear on the next request (if you navigate to another page for example).
Leave the development server running and open a new terminal window.
First, open your app.py
file:
- nano app.py
For handling the web form, you’ll need to import a few things from the flask
package:
request
object to access submitted data.url_for()
function to generate URLs.flash()
function to flash a message if a request is invalid.redirect()
function to redirect users to the index page after adding the posts to the database.Add these imports to the first line in the file:
from flask import Flask, render_template, request, url_for, flash, redirect
# ...
The flash()
function stores flashed messages in the client’s browser session, which requires setting a secret key to secure sessions that remember information from one request to another. You must never allow anyone to access your secret key. See the Flask documentation for sessions for more information.
Set a secret key by adding a SECRET_KEY
configuration to your application via the app.config
object. Add it next to the app
instance definition.
# ...
app = Flask(__name__)
app.config['SECRET_KEY'] = 'your secret key'
Remember that the secret key should be a long random string. For more on web forms and the secret key configuration, see How To Use Web Forms in a Flask Application.
Next, add the following route at the end of the app.py
file:
# ...
@app.route('/create/', methods=('GET', 'POST'))
def create():
return render_template('create.html')
Save and close the file.
In this route, you pass the tuple ('GET', 'POST')
to the methods
parameter to allow both GET and POST requests. GET requests are used to retrieve data from the server. POST requests are used to post data to a specific route. By default, only GET requests are allowed. When the user first requests the /create
route using a GET request, a template file called create.html
will be rendered. You will later edit this route to handle POST requests for when users fill and submit the web form for creating new posts.
Open the new create.html
template:
- nano templates/create.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Add a New Post {% endblock %}</h1>
<form method="post">
<label for="title">Title</label>
<br>
<input type="text" name="title"
placeholder="Post title"
value="{{ request.form['title'] }}"></input>
<br>
<label for="content">Post Content</label>
<br>
<textarea name="content"
placeholder="Post content"
rows="15"
cols="60"
>{{ request.form['content'] }}</textarea>
<br>
<button type="submit">Submit</button>
</form>
{% endblock %}
Save and close the file.
You extend the base template, set a heading as a title, and use a <form>
tag with the attribute method
set to post
to indicate that the form will submit a POST request. You have a text field with the name title
, which you’ll use to access the title data in your /create
route. You set the value of the text field to request.form['title']
which is either empty or a saved version of the title if the form is invalid, so that the title does not get lost when things go wrong.
After the title input field, you add a text area named content
with the value {{ request.form['content'] }}
to restore post content if the form is invalid.
Last, you have a Submit button at the end of the form.
Now, with the development server running, use your browser to navigate to the /create
route:
http://127.0.0.1:5000/create
You will see an Add a New Post page with an input field for a post title, a text area for the post’s content, and a Submit button.
If you fill in the form and submit it, sending a POST request to the server, nothing happens because you did not handle POST requests on the /create
route.
Open app.py
to handle the POST request the user submits:
- nano app.py
Edit the /create
route to look as follows:
# ...
@app.route('/create/', methods=('GET', 'POST'))
def create():
if request.method == 'POST':
title = request.form['title']
content = request.form['content']
if not title:
flash('Title is required!')
elif not content:
flash('Content is required!')
else:
conn = get_db_connection()
conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)',
(title, content))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('create.html')
Save and close the file.
You handle POST requests inside the if request.method == 'POST'
condition. You extract the title and content the user submits from the request.form
object. If the title is empty, you use the flash()
function to flash the message Title is required!
. You do the same in case of empty content.
If both the title and the content are supplied, you open a database connection using the get_db_connection()
function. You use the execute()
method to execute an INSERT INTO
SQL statement to add a new post to the posts
table with the title and content the user submits as values. You use the ?
placeholder to insert data into the table safely. You commit the transaction and close the connection. Lastly, you redirect the user to the index page where they can see their new post below existing posts.
Warning: Never use Python string operations to dynamically create an SQL statement string. Always use the ?
placeholder in your SQL statements to dynamically substitute values. Pass a tuple of values as the second argument to the execute()
method to bind your values to the SQL statement. This prevents SQL injection attacks.
With the development server running, use your browser to navigate to the /create
route:
http://127.0.0.1:5000/create
Fill the form and submit it.
You’ll be redirected to the index page where you’ll see your new post.
If you submit a form without a title or one without any content, your post won’t be added to the database, you won’t be redirected to the index page, and you won’t receive any feedback for why that is. This is because you haven’t set up flashed messages to be displayed anywhere yet.
Open base.html
to add a link to the Create page in the navigation bar, and to display flashed messages below it.
- nano templates/base.html
Edit the file to look as follows:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>{% block title %} {% endblock %} - FlaskApp</title>
<style>
.post {
padding: 10px;
margin: 5px;
background-color: #f3f3f3
}
nav a {
color: #d64161;
font-size: 3em;
margin-left: 50px;
text-decoration: none;
}
.alert {
padding: 20px;
margin: 5px;
color: #970020;
background-color: #ffd5de;
}
</style>
</head>
<body>
<nav>
<a href="{{ url_for('index') }}">FlaskApp</a>
<a href="{{ url_for('create') }}">Create</a>
<a href="#">About</a>
</nav>
<hr>
<div class="content">
{% for message in get_flashed_messages() %}
<div class="alert">{{ message }}</div>
{% endfor %}
{% block content %} {% endblock %}
</div>
</body>
</html>
Save and close the file.
Here, you add a new <a>
link to the navigation bar that points to the Create page.
You use a Jinja for
loop to go through the flashed messages. These are available in the get_flashed_messages()
special function. Each message is displayed in a <div>
tag with a CSS class called alert
. You style this <div>
tag inside the <style>
tag in the <head>
section.
Refresh your index page and you’ll see the new link in the navigation bar.
Click the Create link, and submit an empty form. You’ll receive the flashed message “Title is required!”
Fill in the title field and leave the content text area empty. Submit the form again, and you’ll receive a “Content is required!” message. Notice how the “Title is required!” message is gone. This is because it’s a flashed message and not a permanent one.
You now have a way of adding new posts. Next, you’ll add a new route for allowing users to edit existing posts.
In this step, you will add a new route to the application to allow users to edit existing posts.
First, to avoid code repetition and to isolate code and make it easier to maintain, you’ll add a new function that takes an ID and retrieves a post associated with it from the database. You’ll use this function to get the post data you want to edit, and you’ll use it to get the post for when you want to delete it in the next step.
Open app.py
:
- nano app.py
The function you’ll use to retrieve a post will respond with a 404 Not Found
error if the ID of the requested post does not correspond with any of the existing posts. To do this, you’ll use the abort()
function, which aborts a request and responds with an error message. For more, see How To Handle Errors in a Flask Application.
Add the abort()
function to the imports:
from flask import Flask, render_template, request, url_for, flash, redirect, abort
Add a new function called get_post()
below your get_db_connection()
function:
# ...
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
def get_post(post_id):
conn = get_db_connection()
post = conn.execute('SELECT * FROM posts WHERE id = ?',
(post_id,)).fetchone()
conn.close()
if post is None:
abort(404)
return post
# ...
This new function has a post_id
argument that determines what post to retrieve and return. You open a database connection with get_db_connection()
and execute an SQL query to get the post associated with the given post_id
value. You get the post with the fetchone()
method, store it in the post
variable, and close the connection.
If the post
variable has the value None
, meaning no result was found in the database, you use the abort()
function you imported earlier to respond with a 404
error code and the function will finish execution. If, however, a post was found, you return the value of the post
variable.
Next, add a new route for editing posts at the end of the file:
# ...
@app.route('/<int:id>/edit/', methods=('GET', 'POST'))
def edit(id):
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
content = request.form['content']
if not title:
flash('Title is required!')
elif not content:
flash('Content is required!')
else:
conn = get_db_connection()
conn.execute('UPDATE posts SET title = ?, content = ?'
' WHERE id = ?',
(title, content, id))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('edit.html', post=post)
Save and close the file.
You use the route /<int:id>/edit/
, with int:
being a converter that accepts positive integers. And id
is the URL variable that will determine the post you want to edit. For example, /2/edit/
will allow you to edit the post with the ID of 2
. The ID is passed from the URL to the edit()
view function. You pass the value of the id
argument to the get_post()
function to fetch the post associated with the provided ID from the database. Remember that this will respond with a 404 Not Found
error if no post with the given ID exists.
The last line renders a template file called edit.html
, and passes in the post
variable that has the post data. You’ll use this to display the existing title and content on the Edit page.
The if request.method == 'POST'
block handles the new data the user submits. Similar to adding a new post, you extract the title and content. You flash a message if the title or the content is not provided.
If the form is valid, you open a database connection and use the UPDATE
SQL statement to update the posts
table by setting the new title and new content, where the ID of the post in the database is equal to the ID that was in the URL. You commit the transaction, close the connection, and redirect to the index page.
Next you need to create a page where users can do the editing. Open a new edit.html
template:
- nano templates/edit.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Edit "{{ post['title'] }}" {% endblock %}</h1>
<form method="post">
<label for="title">Title</label>
<br>
<input type="text" name="title"
placeholder="Post title"
value="{{ request.form['title'] or post['title'] }}"></input>
<br>
<label for="content">Post Content</label>
<br>
<textarea name="content"
placeholder="Post content"
rows="15"
cols="60"
>{{ request.form['content'] or post['content'] }}</textarea>
<br>
<button type="submit">Submit</button>
</form>
{% endblock %}
Save and close the file.
This is similar to the code in the create.html
template, except for displaying the post title inside the page’s title in the line {% block title %} Edit "{{ post['title'] }}" {% endblock %}
, the value of the input in {{ request.form['title'] or post['title'] }}
, and the value of the text area in {{ request.form['content'] or post['content'] }}
. This displays the data stored in the request if it exists; otherwise it displays the data from the post
variable that was passed to the template containing current database data.
With the development server running, use your browser to navigate to the following URL to edit the first post:
http://127.0.0.1:5000/1/edit
You’ll see a page that looks as follows:
Edit the post and submit the form. You’ll see your changes applied on the index page. If you submit a form without a title or without any content, you’ll receive a flashed message.
You now need to add a link that points to the Edit page for each post on the index page. Open the index.html
template file:
- nano templates/index.html
Edit the file to look exactly like the following:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Posts {% endblock %}</h1>
{% for post in posts %}
<div class='post'>
<p>{{ post['created'] }}</p>
<h2>{{ post['title'] }}</h2>
<p>{{ post['content'] }}</p>
<a href="{{ url_for('edit', id=post['id']) }}">Edit</a>
</div>
{% endfor %}
{% endblock %}
Save and close the file.
You added an <a>
tag that links to the edit()
view function. You pass the post ID you have in post['id'])
to the url_for()
function to generate the post’s edit link. This adds a link to the Edit page of each post below it.
Refresh the index page and click the Edit link to edit a post.
You can now add new posts and edit existing ones. Next, you’ll add a button to allow users to delete existing posts.
In this step, you will add a Delete button to the Edit page to allow users to delete a post.
First, you’ll add a new /id/delete
route that accepts POST requests, similar to the edit()
view function. Your new delete()
view function will receive the ID of the post to be deleted from the URL, retrieve it using the get_post()
function, and then delete it from the database if it exists.
Open the app.py
file:
- nano app.py
Add the following route at the end:
# ...
@app.route('/<int:id>/delete/', methods=('POST',))
def delete(id):
post = get_post(id)
conn = get_db_connection()
conn.execute('DELETE FROM posts WHERE id = ?', (id,))
conn.commit()
conn.close()
flash('"{}" was successfully deleted!'.format(post['title']))
return redirect(url_for('index'))
Save and close the file.
This view function only accepts POST requests in the methods
parameter. This means that navigating to the /ID/delete
route on your browser will return a 405 Method Not Allowed
error, because web browsers default to GET requests. To delete a post, the user clicks on a button that sends a POST request to this route.
The function receives the ID of the post to be deleted. You use this ID to retrieve the post using the get_post()
function. This responds with a 404 Not Found
error if no post with the given ID exists. You open a database connection and execute a DELETE FROM
SQL command to delete the post. You use WHERE id = ?
to specify the post you want to delete.
You commit the change to the database and close the connection. You flash a message to inform the user that the post was successfully deleted and redirect them to the index page.
Note that you don’t render a template file. This is because you’ll just add a Delete button to the Edit page.
Open the edit.html
template file:
- nano templates/edit.html
Then add the following <hr>
and <form>
tags directly before the {% endblock %}
line:
<button type="submit">Submit</button>
</form>
<hr>
<form action="{{ url_for('delete', id=post['id']) }}" method="POST">
<input type="submit" value="Delete Post"
onclick="return confirm('Are you sure you want to delete this post?')">
</form>
{% endblock %}
Save and close the file.
Here, you have a web form that submits a POST request to the delete()
view function. You pass post['id']
to specify the post that will be deleted. You use the confirm()
method available in web browsers to display a confirmation message before submitting the request.
Now navigate again to the Edit page of a post and try deleting it:
http://127.0.0.1:5000/1/edit
After you confirm the deletion, you’ll be redirected to the index page, and the post will no longer be there. A flash message will appear below the navigation bar informing you that the post was successfully deleted.
You now have a way of deleting unwanted posts from the database in your Flask application.
You built a small web blog that communicates with an SQLite database. You have basic functionalities in your Flask application, such as adding new data to the database, retrieving data and displaying it on a page, and editing and deleting existing data.
For more on how to use SQLite with Python and Flask, see the following tutorials:
If you would like to read more about Flask, check out the other tutorials in the Flask series.
]]>Fathom Analytics is an open-source, self-hosted web analytics application that focuses on simplicity and privacy. It is written in Go and ships as a single binary file, making installation relatively straightforward.
In this tutorial you will install and configure Fathom, then install Nginx to act as a reverse proxy for the Fathom app. Finally, you will enable secure HTTPS connections by using Certbot to download and configure SSL certificates from the Let’s Encrypt Certificate Authority.
In order to complete this tutorial, you’ll first need the following:
sudo
privileges configured. Please read our Initial Server Setup with Ubuntu 20.04 to learn more about setting up these requirementsexample.com
or fathom.example.com
, for instance. If you’re using DigitalOcean, please see our DNS Quickstart for information on creating domain resources in our control panel.When you’ve satisfied all the prerequisites, proceed to Step 1, where you’ll download and install Fathom.
To install the Fathom software, you’ll first download the latest release, then extract the executable file to the /usr/local/bin
directory.
First, move to a directory you can write to. The /tmp
directory is a good choice:
- cd /tmp
In your web browser, visit the GitHub page for Fathom’s latest software release, then find the file with a name like fathom_1.2.1_linux_amd64.tar.gz
. The version number may be different.
Right-click on the link to the file, then select Copy Link (or similar, depending on your browser).
Use the curl
command to download the file from the link you just copied:
- curl -L -O https://github.com/usefathom/fathom/releases/download/v1.2.1/fathom_1.2.1_linux_amd64.tar.gz
You should now have a fathom_1.2.1_linux_amd64.tar.gz
file in your /tmp
directory. Use the tar
command to extract the fathom
executable and move it to /usr/local/bin
:
- sudo tar -C /usr/local/bin/ -xzf fathom*.tar.gz fathom
The sudo
command is necessary because /usr/local/bin
is a protected directory, so you need superuser privileges to write to it.
Now use sudo
and chmod
to update the permissions of the fathom
binary:
- sudo chmod +x /usr/local/bin/fathom
This makes fathom
executable. To test it out, run fathom --version
:
- fathom --version
OutputFathom version 1.2.1, commit 8f7c6d2e45ebb28651208e2a7320e29948ecdb2c, built at 2018-11-30T09:21:37Z
The command will print out Fathom’s version number and some additional details. You’ve successfully downloaded and installed the Fathom binary. Next you’ll configure and run Fathom for the first time.
Before configuring Fathom you’re going to create a new fathom user on your system. This new user account will be used to run the Fathom server, which will help isolate and secure the service.
Make a new user named fathom with the adduser
command:
- sudo adduser --system --group --home /opt/fathom fathom
This creates a special --system
user, meaning it has no password and cannot log in like a normal user could. We also make a fathom group using the --group
flag, and a home directory in /opt/fathom
.
Move to the fathom user’s home directory now:
- cd /opt/fathom
Now we have to execute a few commands that need to be run as the fathom user. To do this, open a bash
shell as the fathom user using sudo
:
- sudo -u fathom bash
Your prompt will change to something like fathom@host:~$
. Until we exit
this shell, every command we run will be run as the fathom user.
Now you’re ready to set up a configuration file for Fathom. One item we’ll need in this configuration file is a random string that Fathom will use for signing and encryption purposes. Use the openssl
command to generate a random string now:
- openssl rand --base64 32
OutputiKo/rYHFa2hDINjgCcIeeCe9pNglQreQrzrs+qK5tYg=
Copy the string to your clipboard, or note it down on a temporary scratch document of some sort, then open a new .env
file for the configuration:
- nano /opt/fathom/.env
This will open a new blank file in the nano
text editor. Feel free to use your favorite editor instead.
Paste the following into the file, making sure to update the random string to the one you generated previously:
FATHOM_SERVER_ADDR="127.0.0.1:8080"
FATHOM_DATABASE_DRIVER="sqlite3"
FATHOM_DATABASE_NAME="fathom.db"
FATHOM_SECRET="your_random_string_here"
This configuration first specifies that the server should only listen on localhost (127.0.0.1
) port 8080
, and that it should use an SQLite database file called fathom.db
.
Save and close the file. In nano
you can press CTRL+O
then ENTER
to save, then press CTRL+X
to exit.
Now that the database is configured, we can add the first user to our Fathom instance:
- fathom user add --email="your_email" --password="your_password"
Since this is the first time you’re running fathom
with the database configured, you should notice some initial database migrations happening:
OutputINFO[0000] Fathom version 1.2.1, commit 8f7c6d2e45ebb28651208e2a7320e29948ecdb2c, built at 2018-11-30T09:21:37Z
INFO[0000] Configuration file: /opt/fathom/.env
INFO[0000] Connected to sqlite3 database: /opt/fathom/fathom.db
INFO[0000] Applied 26 database migrations!
INFO[0000] Created user sammy@example.com
Your fathom.db
database file is now created and the user is added.
Start the Fathom server now to test it out:
- fathom server
OutputINFO[0000] Fathom version 1.2.1, commit 8f7c6d2e45ebb28651208e2a7320e29948ecdb2c, built at 2018-11-30T09:21:37Z
INFO[0000] Configuration file: /opt/fathom/.env
INFO[0000] Connected to sqlite3 database: /opt/fathom/fathom.db
In a second terminal connected to your server, fetch the homepage of your Fathom instance using curl
:
- curl localhost:8080
Output<!DOCTYPE html>
<html class="no-js" lang="en">
<head>
<title>Fathom - simple website analytics</title>
<link href="assets/css/styles.css?t=1543569696966" rel="stylesheet">
. . .
You should see a few lines of HTML code printed to your screen. This shows that the server is up and responding to requests on localhost.
Back in your first terminal, exit the fathom server
process by pressing CTRL+C
.
You’re all done running commands as the fathom user, so exit
that session as well:
- exit
Your shell prompt should return to normal.
Fathom is now fully configured and you’ve successfully run it manually from the command line. Next we’ll set Fathom up to run as a Systemd service.
To run fathom serve
at all times, even when you’re not logged into the server, you’ll set it up as a service with Systemd. Systemd is a service manager that handles starting, stopping, and restarting services on Ubuntu and many other Linux distributions.
The fathom.service
file you create will contain all the configuration details that Systemd needs to properly run the server. Open the new file now:
- sudo nano /etc/systemd/system/fathom.service
Add the following into the file:
[Unit]
Description=Fathom Analytics server
Requires=network.target
After=network.target
[Service]
Type=simple
User=fathom
Group=fathom
Restart=always
RestartSec=3
WorkingDirectory=/opt/fathom
ExecStart=/usr/local/bin/fathom server
[Install]
WantedBy=multi-user.target
This file specifies when the service should be launched (After=network.target
, meaning after the network is up), that it should be run as the fathom user and group, that Systemd should always try to restart the process if it exits (Restart=always
), that it should be run from the /opt/fathom
directory, and what command to use to run the server (ExecStart=/usr/local/bin/fathom server
).
Save and close the file. Reload the Systemd config:
- sudo systemctl daemon-reload
Enable the service:
- sudo systemctl enable fathom.service
Enabling the service means that Systemd will start it automatically during system startup. We’ll also need to start the service manually now, just this once:
- sudo systemctl start fathom
Note in the previous command that you can leave off the .service
portion of the service name. Finally, check the status of the service to make sure it’s running:
- sudo systemctl status fathom
Output● fathom.service - Fathom Analytics server
Loaded: loaded (/etc/systemd/system/fathom.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2021-11-03 15:32:45 UTC; 13s ago
Main PID: 3748 (fathom)
Tasks: 5 (limit: 1136)
Memory: 10.3M
CGroup: /system.slice/fathom.service
└─3748 /usr/local/bin/fathom server
Nov 03 15:32:45 ubuntu-fathom systemd[1]: Started Fathom Analytics server.
Nov 03 15:32:46 ubuntu-fathom fathom[3748]: time="2021-11-03T15:32:46Z" level=info msg="Fathom version 1.2.1, commit 8f>
Nov 03 15:32:46 ubuntu-fathom fathom[3748]: time="2021-11-03T15:32:46Z" level=info msg="Configuration file: /opt/fathom>
Nov 03 15:32:46 ubuntu-fathom fathom[3748]: time="2021-11-03T15:32:46Z" level=info msg="Connected to sqlite3 database: >
The service is up and running again on localhost port 8080
. Next we’ll set up Nginx as a reverse proxy to expose the Fathom service to the outside world.
Putting a web server such as Nginx in front of your application server can improve performance by offloading caching, compression, and static file serving to a more efficient process. We’re going to install Nginx and configure it to reverse proxy requests to Fathom, meaning it will take care of handing requests from your users to Fathom and back again.
First, refresh your package list, then install Nginx using apt
:
- sudo apt update
- sudo apt install nginx
Allow public traffic to ports 80
and 443
(HTTP and HTTPS) using the “Nginx Full” UFW application profile:
- sudo ufw allow "Nginx Full"
OutputRule added
Rule added (v6)
Next, open up a new Nginx configuration file in the /etc/nginx/sites-available
directory. We’ll call ours fathom.conf
but you could use a different name:
- sudo nano /etc/nginx/sites-available/fathom.conf
Paste the following into the new configuration file, being sure to replace your_domain_here
with the domain that you’ve configured to point to your Fathom server. This should be something like fathom.example.com
, for instance:
server {
listen 80;
listen [::]:80;
server_name your_domain_here;
access_log /var/log/nginx/fathom.access.log;
error_log /var/log/nginx/fathom.error.log;
location / {
proxy_pass http://localhost:8080;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header Host $host;
}
}
This configuration is HTTP-only for now. We’ll let Certbot take care of configuring SSL in the next step. The rest of the config sets up logging locations and then passes all traffic along to our Fathom server at http://localhost:8080
, adding a few important proxy forwarding headers along the way.
Save and close the file, then enable the configuration by linking it into /etc/nginx/sites-enabled/
:
- sudo ln -s /etc/nginx/sites-available/fathom.conf /etc/nginx/sites-enabled/
Use nginx -t
to verify that the configuration file syntax is correct:
- sudo nginx -t
Outputnginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
And finally, reload the nginx
service to pick up the new configuration:
- sudo systemctl reload nginx
Your Fathom site should now be available on plain HTTP. Load http://your_domain_here
and it will look like this:
Now that you have your site up and running over HTTP, it’s time to secure the connection with Certbot and Let’s Encrypt certificates.
Thanks to Certbot and the Let’s Encrypt free certificate authority, adding SSL encryption to our Fathom app will take only two commands.
First, install Certbot and its Nginx plugin:
- sudo apt install certbot python3-certbot-nginx
Next, run certbot
in --nginx
mode, and specify the same domain you used in the Nginx server_name
config:
- sudo certbot --nginx -d your_domain_here
You’ll be prompted to agree to the Let’s Encrypt terms of service, and to enter an email address.
Afterwards, you’ll be asked if you want to redirect all HTTP traffic to HTTPS. It’s up to you, but this is generally recommended and safe to do.
After that, Let’s Encrypt will confirm your request and Certbot will download your certificate:
OutputCongratulations! You have successfully enabled https://Fathom.example.com
You should test your configuration at:
https://www.ssllabs.com/ssltest/analyze.html?d=Fathom.example.com
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMPORTANT NOTES:
- Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/Fathom.example.com/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/Fathom.example.com/privkey.pem
Your cert will expire on 2021-12-06. To obtain a new or tweaked
version of this certificate in the future, simply run certbot again
with the "certonly" option. To non-interactively renew *all* of
your certificates, run "certbot renew"
- Your account credentials have been saved in your Certbot
configuration directory at /etc/letsencrypt. You should make a
secure backup of this folder now. This configuration directory will
also contain certificates and private keys obtained by Certbot so
making regular backups of this folder is ideal.
- If you like Certbot, please consider supporting our work by:
Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate
Donating to EFF: https://eff.org/donate-le
Certbot will automatically reload Nginx to pick up the new configuration and certificates. Reload your site and it should switch you over to HTTPS automatically if you chose the redirect option.
Your site is now secure and it’s safe to log in with the user details you set up in Step 2.
When you successfully log in, you’ll see a prompt to get your first website set up with Fathom:
Once that is done you’ll see the (currently empty) dashboard for the site you just set up:
You have successfully installed and secured your Fathom analytics software.
In this tutorial, you downloaded, installed, and configured the Fathom Analytics app, then set up an Nginx reverse proxy and secured it using Let’s Encrypt SSL certificates.
You’re now ready to finish setting up your website by adding the Fathom Analytics tracking script to it. Please see the official Fathom Analytics documentation for further information on using the software and setting up your site.
]]>I’m deploying Python worker app.
[sariqdevbot] [2021-09-23 11:07:49] rm: cannot remove '/workspace/.heroku/python/apt/state/lists': Directory not empty
[sariqdevbot] [2021-09-23 11:07:49] Sqlite3 failed to install.
Full Log below:
[sariqdevbot] [2021-09-23 11:06:00] => Initializing build
[sariqdevbot] [2021-09-23 11:06:00] => Retrieving source code to /workspace
[sariqdevbot] [2021-09-23 11:06:00] => Selecting branch "sqlite"
[sariqdevbot] [2021-09-23 11:06:01] => Checking out commit "bf9fb484fc683a1b9ae3b304830a99634f911248"
[sariqdevbot] [2021-09-23 11:06:02] => Got source_dir: /
[sariqdevbot] [2021-09-23 11:06:02] => Using workspace root /workspace
[sariqdevbot] [2021-09-23 11:06:02]
[sariqdevbot] [2021-09-23 11:06:02] => Building app using buildpacks
[sariqdevbot] [2021-09-23 11:06:02] => Injecting app environment variables:
[sariqdevbot] [2021-09-23 11:06:02] ADMINS BOT_TOKEN ip
[sariqdevbot] [2021-09-23 11:06:03] => Running buildpack detection
[sariqdevbot] [2021-09-23 11:06:03]
[sariqdevbot] [2021-09-23 11:06:03] 3 of 4 buildpacks participating
[sariqdevbot] [2021-09-23 11:06:03] digitalocean/python-appdetect 0.0.2
[sariqdevbot] [2021-09-23 11:06:03] heroku/python 0.192.4
[sariqdevbot] [2021-09-23 11:06:03] digitalocean/procfile 0.0.3
[sariqdevbot] [2021-09-23 11:06:03]
[sariqdevbot] [2021-09-23 11:06:03] For documentation on the buildpacks used to build your app, please see:
[sariqdevbot] [2021-09-23 11:06:03] Python: https://do.co/apps-buildpack-python
[sariqdevbot] [2021-09-23 11:06:04]
[sariqdevbot] [2021-09-23 11:06:04] => Checking for cache from a previous build
[sariqdevbot] [2021-09-23 11:06:06] Layer cache not found
[sariqdevbot] [2021-09-23 11:06:09] Layer cache not found
[sariqdevbot] [2021-09-23 11:06:09]
[sariqdevbot] [2021-09-23 11:06:09] => Building app
[sariqdevbot] [2021-09-23 11:06:09]
[sariqdevbot] [2021-09-23 11:06:11] -----> Installing python-3.9.4
[sariqdevbot] [2021-09-23 11:06:27] -----> Installing pip 20.1.1, setuptools 47.1.1 and wheel 0.34.2
[sariqdevbot] [2021-09-23 11:06:56] -----> Installing dependencies with Pipenv 2020.11.15
[sariqdevbot] [2021-09-23 11:06:57] Installing dependencies from Pipfile.lock (074029)...
[sariqdevbot] [2021-09-23 11:07:25] -----> Installing SQLite3
[sariqdevbot] [2021-09-23 11:07:49] rm: cannot remove '/workspace/.heroku/python/apt/state/lists': Directory not empty
[sariqdevbot] [2021-09-23 11:07:49] Sqlite3 failed to install.
[sariqdevbot] [2021-09-23 11:08:10]
[sariqdevbot] [2021-09-23 11:08:10] => Uploading the built Docker image to the container registry...
[sariqdevbot] [2021-09-23 11:08:12] Reusing layers from image <registry-uri-0>
[sariqdevbot] [2021-09-23 11:08:14] Reusing layer 'heroku/python:profile'
[sariqdevbot] [2021-09-23 11:08:43] Adding 1/1 app layer(s)
[sariqdevbot] [2021-09-23 11:08:43] Reusing layer 'launcher'
[sariqdevbot] [2021-09-23 11:08:43] Reusing layer 'config'
[sariqdevbot] [2021-09-23 11:08:43] Adding label 'io.buildpacks.lifecycle.metadata'
[sariqdevbot] [2021-09-23 11:08:43] Adding label 'io.buildpacks.build.metadata'
[sariqdevbot] [2021-09-23 11:08:43] Adding label 'io.buildpacks.project.metadata'
[sariqdevbot] [2021-09-23 11:10:13] *** Images (sha256:ab3d02bdf9908e942beed438bfa951f0f28fa85b3192de5ef094191eb48dc3a6):
[sariqdevbot] [2021-09-23 11:10:13] <image-1>
[sariqdevbot] [2021-09-23 11:10:13] Layer cache not found
[sariqdevbot] [2021-09-23 11:10:25] Adding cache layer 'heroku/python:shim'
[sariqdevbot] [2021-09-23 11:11:34] => Uploaded the built image to the container registry
[sariqdevbot] [2021-09-23 11:11:34] => Build complete
]]>Etherpad is a web application that enables real-time collaborative text editing in the browser. It is written in Node.js and can be self-hosted on a variety of platforms and operating systems.
In this tutorial we will install Etherpad on an Ubuntu 20.04 server, using the SQLite database engine to store our data. We’ll also install and configure Nginx to act as a reverse proxy for the application, and we’ll fetch and install SSL certificates from the Let’s Encrypt certificate authority to enable secure HTTPS connections to our Etherpad instance.
Note: If you’d prefer to use our App Platform service to self-host Etherpad, please see our Deploy the Etherpad Collaborative Web Editor to App Platform tutorial, where we create an App Platform app to run the Etherpad Docker container, and connect it to a managed PostgreSQL database.
Before starting this tutorial, you will need the following:
sudo
-enabled user, and with the UFW firewall enabled. Please read our Initial Server Setup with Ubuntu 20.04 to learn more about setting up these requirements.example.com
or etherpad.example.com
, for instance.Note: If you’re using DigitalOcean, our DNS Documentation can help you get your domain name set up in the control panel.
When you have the prerequisites in place, continue to Step 1, where we’ll download and configure the Etherpad application.
To install Etherpad, you’ll need to download the source code, install dependencies, and configure systemd to run the server.
The Etherpad maintainers recommend running the software as its own user, so your first step will be to create a new etherpad user using the adduser
command:
- sudo adduser --system --group --home /opt/etherpad etherpad
This creates a --system
user, meaning that it can’t log in directly and has no password or shell assigned. We give it a home directory of /opt/etherpad
, which is where we’ll download and configure the Etherpad software. We also create an etherpad group using the --group
flag.
You now need to run a few commands as the etherpad user. To do so, you’ll use the sudo
command to open a bash
shell as the etherpad user. Then you’ll change directories (cd
) to /opt/etherpad
:
- sudo -u etherpad bash
- cd /opt/etherpad
Your shell prompt will update to show that you’re the etherpad user. It should look similar to etherpad@host:~$
.
Now clone the Etherpad repository into /opt/etherpad
using Git:
- git clone --branch master https://github.com/ether/etherpad-lite.git .
This will pull the master
branch of the Etherpad source code into the current directory (.
). When that’s done, run Etherpad’s installDeps.sh
script to install the dependencies:
- ./bin/installDeps.sh
This can take a minute. When it’s done, you’ll need to manually install one last dependency. We need to cd
into the Etherpad src
folder and install the sqlite3
package in order to use SQLite as our database.
First, change into the src
directory:
- cd src
Then install the sqlite3
package using npm
:
- npm install sqlite3
Your final task as the etherpad user is to update the Etherpad settings.json
file to use SQLite for its database, and to work well with Nginx. Move back up to the /opt/etherpad
directory:
- cd /opt/etherpad
Then open the settings file using your favorite text editor:
- nano settings.json
The file is formatted as JSON, but with extensive comments throughout explaining each setting. There’s a lot you can configure, but for now we’re interested in two values that update the database configuration:
"dbType": "dirty",
"dbSettings": {
"filename": "var/dirty.db"
},
Scroll down and look for the dbType
and dbSettings
section, shown here. Update the settings to sqlite
and a filename of your choice, like the following:
"dbType": "sqlite",
"dbSettings": {
"filename": "var/sqlite.db"
},
Finally, scroll down some more, find the trustProxy
setting, and update it to true
:
"trustProxy": true,
Save and close the settings file. In nano
you can save and close by typing CTRL+O
then ENTER
to save, and CTRL+X
to exit.
When that’s done, be sure to exit the etherpad user’s shell:
- exit
You’ll be returned to your normal user’s shell.
Etherpad is installed and configured. Next we’ll create a systemd service to start and manage the Etherpad process.
In order to start Etherpad on boot and to manage the process using systemctl
, we need to create a systemd service file. Open up the new file in your favorite text editor:
- sudo nano /etc/systemd/system/etherpad.service
We’re going to create a service definition based on information in Etherpad’s documentation wiki. The How to deploy Etherpad Lite as a service page gives an example configuration that needs just a few changes to make it work for us.
Add the following content into your text editor, then save and close the file:
[Unit]
Description=Etherpad, a collaborative web editor.
After=syslog.target network.target
[Service]
Type=simple
User=etherpad
Group=etherpad
WorkingDirectory=/opt/etherpad
Environment=NODE_ENV=production
ExecStart=/usr/bin/node --experimental-worker /opt/etherpad/node_modules/ep_etherpad-lite/node/server.js
Restart=always
[Install]
WantedBy=multi-user.target
This file gives systemd the information it needs to run Etherpad, including the user and group to run it as, and the command used to start the process (ExecStart=...
).
After closing the file, reload the systemd daemon to pull in the new configuration:
- sudo systemctl daemon-reload
Next, enable the etherpad
service. This means the service will start up whenever your server reboots:
- sudo systemctl enable etherpad
And finally, we can start the service:
- sudo systemctl start etherpad
Check that the service started properly using systemctl status
:
- sudo systemctl status etherpad
Output● etherpad.service - Etherpad, a collaborative web editor.
Loaded: loaded (/etc/systemd/system/etherpad.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2021-09-09 14:12:43 UTC; 18min ago
Main PID: 698 (node)
Tasks: 13 (limit: 1136)
Memory: 152.0M
CGroup: /system.slice/etherpad.service
└─698 /usr/bin/node --experimental-worker /opt/etherpad/node_modules/ep_etherpad-lite/node/server.js
The output should indicate that the service is active (running)
.
Etherpad is now running, but it is unavailable to the public because port 9001
is blocked by your firewall. In the next step we’ll make Etherpad public by setting up Nginx as a reverse proxy in front of the Etherpad process.
Putting a web server such as Nginx in front of your Node.js server can improve performance by offloading caching, compression, and static file serving to a more efficient process. We’re going to install Nginx and configure it to proxy requests to Etherpad, meaning it will take care of handing requests from your users to Etherpad and back again.
First, refresh your package list, then install Nginx using apt
:
- sudo apt update
- sudo apt install nginx
Allow traffic to ports 80
and 443
(HTTP and HTTPS) using the “Nginx Full” UFW application profile:
- sudo ufw allow "Nginx Full"
OutputRule added
Rule added (v6)
Next, open up a new Nginx configuration file in the /etc/nginx/sites-available
directory. We’ll call ours etherpad.conf
but you could use a different name:
- sudo nano /etc/nginx/sites-available/etherpad.conf
Paste the following into the new configuration file, being sure to replace your_domain_here
with the domain that is pointing to your Etherpad server. This will be something like etherpad.example.com
, for instance.
server {
listen 80;
listen [::]:80;
server_name your_domain_here;
access_log /var/log/nginx/etherpad.access.log;
error_log /var/log/nginx/etherpad.error.log;
location / {
proxy_pass http://127.0.0.1:9001;
proxy_buffering off;
proxy_set_header Host $host;
proxy_pass_header Server;
# proxy headers
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $remote_addr;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_http_version 1.1;
# websocket proxying
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection "upgrade";
}
}
This configuration is loosely based on a configuration provided on the Etherpad wiki. It is HTTP-only for now, as we’ll let Certbot take care of configuring SSL in the next step. The rest of the config sets up logging locations and then passes all traffic along to http://127.0.0.1:9001
, the Etherpad instance we started up in the previous step. We also set various headers that are required for well-behaved proxying and for websockets (persistent HTTP connections that enable real-time two-way communication) to work through a proxy.
Save and close the file, then enable the configuration by linking it into /etc/nginx/sites-enabled/
:
- sudo ln -s /etc/nginx/sites-available/etherpad.conf /etc/nginx/sites-enabled/
Use nginx -t
to verify that the configuration file syntax is correct:
- sudo nginx -t
[secondary_lable Output]
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
And finally, reload the nginx
service to pick up the new configuration:
- sudo systemctl reload nginx
Your Etherpad site should now be available on plain HTTP, and it will look something like this:
Now that we have our site up and running over HTTP, it’s time to secure the connection with Certbot and Let’s Encrypt certificates.
Thanks to Certbot and the Let’s Encrypt free certificate authority, adding SSL encryption to our Etherpad app will take only two commands.
First, install Certbot and its Nginx plugin:
- sudo apt install certbot python3-certbot-nginx
Next, run certbot
in --nginx
mode, and specify the same domain you used in the Nginx server_name
config:
- sudo certbot --nginx -d your_domain_here
You’ll be prompted to agree to the Let’s Encrypt terms of service, and to enter an email address.
Afterwards, you’ll be asked if you want to redirect all HTTP traffic to HTTPS. It’s up to you, but this is generally recommended and safe to do.
After that, Let’s Encrypt will confirm your request and Certbot will download your certificate:
OutputCongratulations! You have successfully enabled https://etherpad.example.com
You should test your configuration at:
https://www.ssllabs.com/ssltest/analyze.html?d=etherpad.example.com
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
IMPORTANT NOTES:
- Congratulations! Your certificate and chain have been saved at:
/etc/letsencrypt/live/etherpad.example.com/fullchain.pem
Your key file has been saved at:
/etc/letsencrypt/live/etherpad.example.com/privkey.pem
Your cert will expire on 2021-12-06. To obtain a new or tweaked
version of this certificate in the future, simply run certbot again
with the "certonly" option. To non-interactively renew *all* of
your certificates, run "certbot renew"
- Your account credentials have been saved in your Certbot
configuration directory at /etc/letsencrypt. You should make a
secure backup of this folder now. This configuration directory will
also contain certificates and private keys obtained by Certbot so
making regular backups of this folder is ideal.
- If you like Certbot, please consider supporting our work by:
Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate
Donating to EFF: https://eff.org/donate-le
Certbot will automatically reload Nginx to pick up the new configuration and certificates. Reload your site and it should switch you over to HTTPS automatically if you chose the redirect option.
You’re done! Try out your new Etherpad editor and invite some collaborators.
In this tutorial we set up Etherpad, with Nginx and Let’s Encrypt SSL certificates. Your Etherpad is now ready to use, but there’s more configuration you may want to do, including adding authenticated users, adding plugins, and customizing the user interface through skins.
Your SQLite-backed Etherpad instance will be able to handle a moderate number of active users, but if you anticipate very high traffic, you may want to look into configuring a MySQL or PostgreSQL database instead.
All of these configuration options are documented on the official Etherpad wiki.
]]>SQLite is a free, cross-platform database management system. It is popular for its efficiency and ability to interface with many different programming languages.
In this tutorial you will install SQLite on Ubuntu 20.04. You will then create a database, read data from it, insert items, delete items, and join items from separate tables.
Note: This tutorial includes practical instructions for installing and using SQLite. It does not cover larger conceptual issues and production concerns, such as when one should, or should not, consider using a SQLite database. For an excellent overview of popular relational databases and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3
Module in Python 3.
To complete this tutorial, you will need:
To install the SQLite command-line interface on Ubuntu, first update your package list:
- sudo apt update
Now install SQLite:
- sudo apt install sqlite3
To verify the installation, check the software’s version:
- sqlite3 --version
You will receive an output like this:
Output3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
With SQLite installed, you are now ready to create a new database.
In this step you will create a database containing different sharks and their attributes. To create the database, open your terminal and run this sqlite3
command:
- sqlite3 sharks.db
This will create a new database named sharks
. If the file sharks.db
already exists, SQLite will open a connection to it; if it does not exist, SQLite will create it.
You will receive an output like this:
OutputSQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Following this, your prompt will change. A new prefix, sqlite>
, now appears:
-
If the file sharks.db
does not already exist and if you exit the sqlite
promote without running any queries the file sharks.db
will not be created. To make sure that the file gets created, you could run an empty query by typing ;
and then pressing “Enter”. That way you will make sure that the database file was actually created.
With your Shark database created, you will now create a new table and populate it with data.
SQLite databases are organized into tables. Tables store information. To better visualize a table, one can imagine rows and columns.
The rest of this tutorial will follow a common convention for entering SQLite commands. SQLite commands are uppercase and user information is lowercase. Lines must end with a semicolon.
Now let’s create a table and some columns for various data:
Use the following command to create the table:
- CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);
Using NOT NULL
makes that field required. We will discuss NOT NULL
in greater detail in the next section.
After creating the table, an empty prompt will return. Now let’s insert some values into it.
In SQLite, the command for inserting values into a table follows this general form:
- INSERT INTO tablename VALUES(values go here);
Where tablename
is the name of your table, and values
go inside parentheses.
Now insert three rows of VALUES
into your sharks
table:
- INSERT INTO sharks VALUES (1, "Sammy", "Greenland Shark", 427);
- INSERT INTO sharks VALUES (2, "Alyoshka", "Great White Shark", 600);
- INSERT INTO sharks VALUES (3, "Himari", "Megaladon", 1800);
Because you earlier specified NOT NULL
for each of the variables in your table, you must enter a value for each.
For example, try adding another shark without setting its length:
- INSERT INTO sharks VALUES (4, "Faiza", "Hammerhead Shark");
You will receive this error:
OutputError: table sharks has 4 columns but 3 values were supplied
In this step you created a table and inserted values into it. In the next step you will read from your database table.
In this step, we will focus on the most basic methods of reading data from a table. Recognize that SQLite provides more specific methods for viewing data in tables.
To view your table with all of the inserted values, use SELECT
:
- SELECT * FROM sharks;
You will see the previously inserted entries:
Output1|Sammy|Greenland Shark|427
2|Alyoshka|Great White Shark|600
3|Himari|Megaladon|1800
To view an entry based on its id
(the values we set manually), add the WHERE
command to your query:
- SELECT * FROM sharks WHERE id IS 1;
This will return the shark whose id
equals 1
:
Output1|Sammy|Greenland Shark|427
Let’s take a closer look at this command.
SELECT
all (*
) values from our database, sharks
.id
values.id
is equal to 1
.So far you have created a table, inserted data into it, and queried that saved data. Now you will update the existing table.
In the following two sections you will first add a new column into your existing table and then update existing values in the table.
SQLite allows you to change your table using the ALTER TABLE
command. This means that you can create new rows and columns, or modify existing rows and columns.
Use ALTER TABLE
to create a new column. This new column will track each shark’s age in years:
- ALTER TABLE sharks ADD COLUMN age integer;
You now have a fifth column, age
.
Using the UPDATE
command, add new age
values for each of your sharks:
- UPDATE sharks SET age = 272 WHERE id=1;
- UPDATE sharks SET age = 70 WHERE id=2;
- UPDATE sharks SET age = 40 WHERE id=3;
Output1|Sammy|Greenland Shark|427|272
2|Alyoshka|Great White Shark|600|70
3|Himari|Megaladon|1800|40
In this step you altered your table’s composition and then updated values inside the table. In the next step you will delete information from a table.
In this step you will delete entries in your table based on the evaluation of an argument.
In the following command you are querying your database and requesting that it delete all sharks in your sharks
table whose age is less than 200:
- DELETE FROM sharks WHERE age <= 200;
Typing SELECT * FROM sharks;
will verify that Alyoshka
and Himari
, who were each less than 200 years old, were deleted. Only Sammy
the Greenland Shark remains:
Output1|Sammy|Greenland Shark|427|272
Let’s imagine that we had two tables: our current sharks
table and an endangered
table. Now what if the endangered
table had an id
value that mapped to the id
s in your sharks
table, and it also had a status
value that indicated each shark’s conservation status?
If you wanted to query data from both tables, you could use one of SQLite’s four join commands:
INNER JOIN
OUTER JOIN
LEFT JOIN
CROSS JOIN
Let’s create that second table and then use INNER JOIN
to join some data.
First, create your endangered
table:
- CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
- INSERT INTO endangered VALUES (1, "near threatened");
Now join your tables:
- SELECT * FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
Your output will look like this:
Output1|Sammy|Greenland Shark|427|272|1|near threatened
Note that the output also includes the id
value from endangered
. You can specify desired output with a more explicit command:
- SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered on sharks.id = endangered.id;
This time the output excludes the second id
value:
Output1|Sammy|Greenland Shark|427|272|near threatened
You have now successfully joined information from multiple tables.
SQLite is a useful tool for database management. One can quickly create a database and manipulate it with various commands. Following this tutorial, you now have a basic understanding of SQLite and you are prepared dive deeper into this database management system.
For an excellent overview of relational database systems and how they compare, check out our article, SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
Additionally, many languages maintain integrations with SQLite. For instructions on using SQLite inside your Python code, check out our tutorial, How To Use the sqlite3
Module in Python 3.
For specific help with SQLite’s syntax, the official documentation is another excellent resource.
]]>GraphQL is a specification and therefore language agnostic. When it comes GraphQL development with Node.js, there are various options available, including graphql-js
, express-graphql
, and apollo-server
. In this tutorial, you will set up a fully featured GraphQL server in Node.js with Apollo Server.
Since the launch of Apollo Server 2, creating a GraphQL server with Apollo Server has become more efficient, not to mention the other features that came with it.
For the purpose of this demonstration, you will build a GraphQL server for a recipe app.
To complete this tutorial, you’ll need:
This tutorial was verified with Node v14.4.0, npm
v6.14.5, apollo-server
v2.15.0, graphql
v15.1.0, sequelize
v5.21.13, and sqlite3
v4.2.0.
GraphQL is a declarative data fetching specification and query language for APIs. It was created by Facebook. GraphQL is an effective alternative to REST, as it was created to overcome some of the shortcomings of REST-like under and over fetching.
Unlike REST, GraphQL uses one endpoint. This means we make one request to the endpoint and we’ll get one response as JSON. This JSON response can contain as little or as much data as we want. Like REST, GraphQL can be operated over HTTP, though GraphQL is protocol agnostic.
A typical GraphQL server is comprised of schema and resolvers. A schema (or GraphQL schema) contains type definitions that would make up a GraphQL API. A type definition contains field(s), each with what it is expected to return. Each field is mapped to a function on the GraphQL server called a resolver. Resolvers contain the implementation logic and return data for a field. In other words, schemas contain type definitions, while resolvers contain the actual implementations.
We’ll start by setting up our database. We’ll be using SQLite for our database. Also, we’ll be using Sequelize, which is an ORM for Node.js, to interact with our database.
First, let’s create a new project:
- mkdir graphql-recipe-server
Navigate to the new project directory:
- cd graphql-recipe-server
Initialize a new project:
- npm init -y
Next, let’s install Sequelize:
- npm install sequelize sequelize-cli sqlite3
In addition to installing Sequelize, we are also installing the sqlite3
package for Node.js. To help us scaffold our project, we’ll be using the Sequelize CLI, which we are installing as well.
Let’s scaffold our project with the CLI:
- node_modules/.bin/sequelize init
This will create the following folders:
config
: contains a config file, which tells Sequelize how to connect with our database.models
: contains all models for our project, and also contains an index.js
file which integrates all the models together.migrations
: contains all migration files.seeders
: contains all seed files.For the purpose of this tutorial, we won’t be using any seeders. Open config/config.json
and replace it with the following content:
{
"development": {
"dialect": "sqlite",
"storage": "./database.sqlite"
}
}
We set the dialect
to sqlite
and set the storage
to point to a SQLite database file.
Next, we need to create the database file directly inside the project’s root directory:
- touch database.sqlite
Now the dependencies for your project are installed to use SQLite.
With the database setup out of the way, we can start creating the models for our project. Our recipe app will have two models: User
and Recipe
. We’ll be using the Sequelize CLI for this:
- node_modules/.bin/sequelize model:create --name User --attributes name:string,email:string,password:string
This is will create a user.js
file inside the models
directory and a corresponding migration file inside the migrations
directory.
Since we don’t want any fields on the User
model to be nullable, we need to explicitly define that. Open migrations/XXXXXXXXXXXXXX-create-user.js
and update the fields definitions as follows:
name: {
allowNull: false,
type: Sequelize.STRING
},
email: {
allowNull: false,
type: Sequelize.STRING
},
password: {
allowNull: false,
type: Sequelize.STRING
}
Then we’ll do the same in the User
model:
name: {
allowNull: false,
type: DataTypes.STRING
},
email: {
allowNull: false,
type: DataTypes.STRING
},
password: {
allowNull: false,
type: DataTypes.STRING
}
Next, let’s create the Recipe
model:
- node_modules/.bin/sequelize model:create --name Recipe --attributes title:string,ingredients:string,direction:string
Just as we did with the User
model, we’ll do the same for the Recipe
model. Open migrations/XXXXXXXXXXXXXX-create-recipe.js
and update the fields definitions as follows:
userId: {
allowNull: false,
type: Sequelize.INTEGER.UNSIGNED
},
title: {
allowNull: false,
type: Sequelize.STRING
},
ingredients: {
allowNull: false,
type: Sequelize.STRING
},
direction: {
allowNull: false,
type: Sequelize.STRING
},
You’ll notice we have an additional field: userId
, which would hold the ID of the user that created a recipe. More on this shortly.
Update the Recipe
model as well:
title: {
allowNull: false,
type: DataTypes.STRING
},
ingredients: {
allowNull: false,
type: DataTypes.STRING
},
direction: {
allowNull: false,
type: DataTypes.STRING
}
Let’s define the one-to-many relationship between the user and recipe models.
Open models/user.js
and update the User.associate
function as below:
User.associate = function(models) {
// associations can be defined here
User.hasMany(models.Recipe)
};
We need to also define the inverse of the relationship on the Recipe
model:
Recipe.associate = function(models) {
// associations can be defined here
Recipe.belongsTo(models.User, { foreignKey: 'userId' });
};
By default, Sequelize will use a camelcase name from the corresponding model name and its primary key as the foreign key. So in our case, it will expect the foreign key to be UserId
. Since we named the column differently, we need to explicitly define the foreignKey
on the association.
Now, we can run the migrations:
- node_modules/.bin/sequelize db:migrate
Now the setup for your models and migrations is complete.
As mentioned earlier, we’ll be using Apollo Server for building our GraphQL server. So, let’s install it:
- npm install apollo-server graphql bcryptjs
Apollo Server requires graphql
as a dependency, hence the need to install it as well. Also, we install bcryptjs
, which we’ll use to hash user passwords later on.
With those installed, create a src
directory, then within it, create an index.js
file and add the following code to it:
const { ApolloServer } = require('apollo-server');
const typeDefs = require('./schema');
const resolvers = require('./resolvers');
const models = require('../models');
const server = new ApolloServer({
typeDefs,
resolvers,
context: { models },
});
server
.listen()
.then(({ url }) => console.log('Server is running on localhost:4000'));
Here, we create a new instance of Apollo Server, passing to it our schema and resolvers (both of which we’ll create shortly). We also pass the models as the context to the Apollo Server. This will allow us to have access to the models from our resolvers.
Finally, we start the server.
GraphQL schema is used to define the functionality a GraphQL API would have. A GraphQL schema is comprised of types. A type can be for defining the structure of our domain-specific entity. In addition to defining types for our domain-specific entities, we can also define types for GraphQL operations, which will in turn translates to the functionality a GraphQL API will have. These operations are queries, mutations, and subscriptions. Queries are used to perform read operations (fetching of data) on a GraphQL server. Mutations on the other hand are used to perform write operations (inserting, updating, or deleting data) on a GraphQL server. Subscriptions are completely different from these two, as they are used to add real-time functionality to a GraphQL server.
We’ll be focusing only on queries and mutations in this tutorial.
Now that we understand what a GraphQL schema is, let’s create the schema for our app. Within the src
directory, create a schema.js
file and add the following code into it:
const { gql } = require('apollo-server');
const typeDefs = gql`
type User {
id: Int!
name: String!
email: String!
recipes: [Recipe!]!
}
type Recipe {
id: Int!
title: String!
ingredients: String!
direction: String!
user: User!
}
type Query {
user(id: Int!): User
allRecipes: [Recipe!]!
recipe(id: Int!): Recipe
}
type Mutation {
createUser(name: String!, email: String!, password: String!): User!
createRecipe(
userId: Int!
title: String!
ingredients: String!
direction: String!
): Recipe!
}
`;
module.exports = typeDefs;
First, we require
the gql
package from apollo-server
. Then we use it to define our schema. Ideally, we’d want our GraphQL schema to mirror our database schema as much as possible. So we define two types, User
and Recipe
, which corresponds to our models. On the User
type, in addition to defining the fields we have on the User
model, we also define a recipes
fields, which will be used to retrieve the user’s recipes. Same with the Recipe
type; we define a user
field, which will be used to get the user of a recipe.
Next, we define three queries: for fetching a single user, for fetching all recipes that have been created, and for fetching a single recipe respectively. Both the user
and recipe
queries can either return a user or recipe respectively or return null
if no corresponding match was found for the ID. The allRecipes
query will always return an array of recipes, which might be empty if no recipe as been created yet.
Note: The !
denotes a field is required, while []
denotes the field will return an array of items.
Lastly, we define mutations for creating a new user as well as creating a new recipe. Both mutations return back the created user and recipe respectively.
Resolvers define how the fields in a schema are executed. In other words, our schema is useless without resolvers. Create a resolvers.js
file inside the src
directory and add the following code in it:
const resolvers = {
Query: {
async user(root, { id }, { models }) {
return models.User.findById(id);
},
async allRecipes(root, args, { models }) {
return models.Recipe.findAll();
},
async recipe(root, { id }, { models }) {
return models.Recipe.findById(id);
},
},
};
module.exports = resolvers;
Note: Modern versions of sequelize
have deprecated findById
and replaced it with findByPk
. If you encounter errors like models.Recipe.findById is not a function
or models.User.findById is not a function
, you may need to update this snippet.
We start by creating the resolvers for our queries. Here, we are making use of the models to perform the necessary queries on the database and return the results.
Still inside src/resolvers.js
, let’s import bcryptjs
at the top of the file:
const bcrypt = require('bcryptjs');
Then add the following code immediately after the Query
object:
Mutation: {
async createUser(root, { name, email, password }, { models }) {
return models.User.create({
name,
email,
password: await bcrypt.hash(password, 10),
});
},
async createRecipe(
root,
{ userId, title, ingredients, direction },
{ models }
) {
return models.Recipe.create({ userId, title, ingredients, direction });
},
},
The createUser
mutation accepts the name, email, and password of a user, and creates a new record in the database with the supplied details. We make sure to hash the password using the bcrypt
package before persisting it to the database. It returns the newly created user. The createRecipe
mutation accepts the ID of the user that’s creating the recipe as well as the details for the recipe itself, persists them to the database, and returns the newly created recipe.
To wrap up with the resolvers, let’s define how we want our custom fields (recipes
on the User
and user
on Recipe
) to be resolved. Add the following code inside src/resolvers.js
just immediately after the Mutation
object:
User: {
async recipes(user) {
return user.getRecipes();
},
},
Recipe: {
async user(recipe) {
return recipe.getUser();
},
},
These use the methods, getRecipes()
and getUser()
, which are made available on our models by Sequelize due to the relationships we defined.
It’s time to test our GraphQL server out. First, we need to start the server with:
- node src/index.js
This will be running on localhost:4000
, and we will see GraphQL Playground running if we access it.
Let’s try creating a new user:
# create a new user
mutation{
createUser(
name: "John Doe",
email: "johndoe@example.com",
password: "password"
)
{
id,
name,
email
}
}
This will produce the following result:
Output{
"data": {
"createUser": {
"id": 1,
"name": "John Doe",
"email": "johndoe@example.com"
}
}
}
Let’s try creating a new recipe and associate it with the user that was created:
# create a new recipe
mutation {
createRecipe(
userId: 1
title: "Salty and Peppery"
ingredients: "Salt, Pepper"
direction: "Add salt, Add pepper"
) {
id
title
ingredients
direction
user {
id
name
email
}
}
}
This will produce the following result:
Output{
"data": {
"createRecipe": {
"id": 1,
"title": "Salty and Peppery",
"ingredients": "Salt, Pepper",
"direction": "Add salt, Add pepper",
"user": {
"id": 1,
"name": "John Doe",
"email": "johndoe@example.com"
}
}
}
}
Other queries you can perform here include: user(id: 1)
, recipe(id: 1)
, and allRecipes
.
In this tutorial, we looked at how to create a GraphQL server in Node.js with Apollo Server. We also saw how to integrate a database with a GraphQL server using Sequelize.
]]>Thanks in advance
]]>Flask is a framework for building web applications using Python and SQLite is a database engine that you can use with Python to store application data.
In this tutorial, you will build a URL shortener, a service that takes any URL and generates a shorter, more readable version like bit.ly.
Hashids is a library that generates a short unique ID from integers. For example, you can use it to convert a number like 12
to a unique string like 1XcId
. You will use Hashids to generate unique strings for URL IDs.
You can use unique strings to generate IDs for videos on a video-sharing site or IDs for images on a service to upload images. This unique string gives you unpredictable IDs; therefore, if a user can access an image at your_domain/image/J32Fr
, they can’t predict the location of other images. This is not possible if you use integer IDs in a URL shortener—for example, your_domain/image/33
would allow users to predict the location of other images. Unpredictable URLs add a form of privacy to your service because they prevent users from working out different URLs shortened by other users.
You will use Flask, SQLite, and the Hashids library to build your URL shortener. Your application will allow users to enter a URL and generate a shorter version, in addition to a statistics page where users can view the number of times a URL has been clicked. You’ll use the Bootstrap toolkit to style your application.
A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial, we’ll call our project directory flask_shortener
.
An understanding of basic Flask concepts such as creating routes, rendering HTML templates, and connecting to an SQLite database. Check out How To Make a Web Application Using Flask in Python 3 and How To Use the sqlite3 Module in Python 3 if you are not familiar with these concepts, but it’s not necessary.
In this step, you will activate your Python environment and install Flask and the Hashids library using the pip package installer. Then you’ll create the database you will use to store URLs.
First, activate your programming environment if you haven’t already:
- source env/bin/activate
Once you have activated your programming environment, install Flask and the Hashids library using the following command:
- pip install flask hashids
Then create a database schema file called schema.sql
, containing SQL commands to create a urls
table. Open a file called schema.sql
inside your flask_shortener
directory:
- nano schema.sql
Type the following SQL commands inside this file:
DROP TABLE IF EXISTS urls;
CREATE TABLE urls (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
original_url TEXT NOT NULL,
clicks INTEGER NOT NULL DEFAULT 0
);
In the schema file, you first delete the urls
table if it already exists. This avoids the possibility of another table named urls
existing, which might result in confusing behavior; for example, if it has different columns. Note that this will delete all of the existing data whenever the schema file executes.
You then create the table with the following columns:
id
: The ID of the URL, this will be a unique integer value for each URL entry. You will use it to get the original URL from a hash string.created
: The date the URL was shortened.original_url
: The original long URL to which you will redirect users.clicks
: The number of times a URL has been clicked. The initial value will be 0
, which will increment with each redirect.Save and close the file.
To execute the schema.sql
file to create the urls
table, open a file named init_db.py
inside your flask_shortener
directory:
- nano init_db.py
Then add the following code:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
connection.commit()
connection.close()
Here you connect to a file called database.db
that your program will create once you execute this program. This file is the database that will hold all of your application’s data. You then open the schema.sql
file and run it using the executescript()
method that executes multiple SQL statements at once. This will create the urls
table. Finally, you commit the changes and close the connection.
Save and close the file.
Run the program:
- python init_db.py
After execution, a new file called database.db
will appear in your flask_shortener
directory.
With this, you’ve installed Flask and the Hashids library, created the database schema, and created the SQLite database with a table called urls
to store the URL shortener’s original URLs. Next, you’ll use Flask to create the index page where your users can enter a URL to generate a short URL.
In this step, you will create a Flask route for the index page, which will allow users to enter a URL that you then save into the database. Your route will use the ID of the URL to generate a short string hash with the Hashids library, construct the short URL, and then render it as a result.
First, open a file named app.py
inside your flask_shortener
directory. This is the main Flask application file:
- nano app.py
Add the following code to the file:
import sqlite3
from hashids import Hashids
from flask import Flask, render_template, request, flash, redirect, url_for
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
In this code, you first import the sqlite3
module, the Hashids
class from the hashids
library, and Flask helpers.
The get_db_connection()
function opens a connection to the database.db
database file and then sets the row_factory
attribute to sqlite3.Row
. As a result, you can have name-based access to columns; the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn
connection object you’ll be using to access the database.
Next, add the following:
. . .
app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'
hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
You create the Flask application object and set a secret key to secure sessions. Since the secret key is a secret random string, you’ll also use it to specify a salt for the Hashids library; this will ensure the hashes are unpredictable since every time the salt changes, the hashes also change.
Note: A salt is a random string that is provided to the hashing function (that is, hashids.encode()
) so that the resulting hash is shuffled based on the salt. This process ensures the hash you get is specific to your salt so that the hash is unique and unpredictable, like a secret password that only you can use to encode and decode hashes. Remember to keep it secret for security purposes (which is why you use the application’s secret key).
You create a hashids
object specifying that a hash should be at least 4
characters long by passing a value to the min_length
parameter. You use the application’s secret key as a salt.
Next, add the following code to the end of your file:
. . .
@app.route('/', methods=('GET', 'POST'))
def index():
conn = get_db_connection()
if request.method == 'POST':
url = request.form['url']
if not url:
flash('The URL is required!')
return redirect(url_for('index'))
url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
(url,))
conn.commit()
conn.close()
url_id = url_data.lastrowid
hashid = hashids.encode(url_id)
short_url = request.host_url + hashid
return render_template('index.html', short_url=short_url)
return render_template('index.html')
The index()
functions is a Flask view function, which is a function decorated using the special @app.route
decorator. Its return value gets converted into an HTTP response that an HTTP client, such as a web browser, displays.
Inside the index()
view function, you accept both GET and POST requests by passing methods=('GET', 'POST')
to the app.route()
decorator. You open a database connection.
Then if the request is a GET request, it skips the if request.method == 'POST'
condition until the last line. This is where you render a template called index.html
, which will contain a form for users to enter a URL to shorten.
If the request is a POST request, the if request.method == 'POST'
condition is true, which means a user has submitted a URL. You store the URL in the url
variable; if the user has submitted an empty form, you flash the message The URL is required!
and redirect to the index page.
If the user has submitted a URL, you use the INSERT INTO
SQL statement to store the submitted URL in the urls
table. You include the ?
placeholder in the execute()
method and pass a tuple containing the submitted URL to insert data safely into the database. Then you commit the transaction and close the connection.
In a variable called url_id
, you store the ID of the URL you inserted into the database. You can access the ID of the URL using the lastrowid
attribute, which provides the row ID of the last inserted row.
You construct a hash using the hashids.encode()
method, passing it the URL ID; you save the result in a variable called hashid
. As an example, the call hashids.encode(1)
might result in a unique hash like KJ34
depending on the salt you use.
You then construct the short URL using request.host_url
, which is an attribute that Flask’s request
object provides to access the URL of the application’s host. This will be http://127.0.0.1:5000/
in a development environment and your_domain
if you deploy your application. For example, the short_url
variable will have a value like http://127.0.0.1:5000/KJ34
, which is the short URL that will redirect your users to the original URL stored in the database with the ID that matches the hash KJ34
.
Lastly, you render the index.html
template passing the short_url
variable to it.
After all the additions, the file will be as follows:
import sqlite3
from hashids import Hashids
from flask import Flask, render_template, request, flash, redirect, url_for
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'
hashids = Hashids(min_length=4, salt=app.config['SECRET_KEY'])
@app.route('/', methods=('GET', 'POST'))
def index():
conn = get_db_connection()
if request.method == 'POST':
url = request.form['url']
if not url:
flash('The URL is required!')
return redirect(url_for('index'))
url_data = conn.execute('INSERT INTO urls (original_url) VALUES (?)',
(url,))
conn.commit()
conn.close()
url_id = url_data.lastrowid
hashid = hashids.encode(url_id)
short_url = request.host_url + hashid
return render_template('index.html', short_url=short_url)
return render_template('index.html')
Save and close the file.
Next, you’ll create a base template and the index.html
template file.
In your flask_shortener
directory, create a templates
directory and open a file called base.html
inside it:
- mkdir templates
- nano templates/base.html
Add the following code inside base.html
. Note that, for styling, you’re using Bootstrap here too. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<title>{% block title %} {% endblock %}</title>
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskShortener</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item active">
<a class="nav-link" href="#">About</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% for message in get_flashed_messages() %}
<div class="alert alert-danger">{{ message }}</div>
{% endfor %}
{% block content %} {% endblock %}
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>
</html>
Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta>
tags provide information for the web browser, the <link>
tag links the Bootstrap CSS files, and the <script>
tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.
The <title>{% block title %} {% endblock %}</title>
tag allows the inheriting templates to define a custom title. You use the for message in get_flashed_messages()
loop to display the flashed messages (warnings, alerts, and so on). The {% block content %} {% endblock %}
placeholder is where inheriting templates place the content so that all templates have access to this base template, which avoids repetition.
Save and close the file.
Next, create the index.html
file that will extend this base.html
file:
- nano templates/index.html
Add the following code to it:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskShortener {% endblock %}</h1>
<form method="post">
<div class="form-group">
<label for="url">URL</label>
<input type="text" name="url"
placeholder="URL to shorten" class="form-control"
value="{{ request.form['url'] }}" autofocus></input>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
{% if short_url %}
<hr>
<span>{{ short_url }}</span>
{% endif %}
{% endblock %}
Here you extend base.html
, define a title, and create a form with an input named url
. The url
input will allow users to enter URLs to shorten. It has a value of request.form['url']
, which stores data in cases of submission failure; that is if the user provides no URL. You also add a submit button.
Then you check if the short_url
variable has any value—this is true if the form submits and the short URL generates successfully. If the condition is true, you display the short URL under the form.
Set the environment variables Flask needs and run the application using the following commands:
- export FLASK_APP=app
- export FLASK_ENV=development
- flask run
The FLASK_APP
environment variable specifies the application you want to run (the app.py
file). The FLASK_ENV
environment variable specifies the mode. development
means that the application will run in development mode with the debugger running. Remember to avoid using this mode in production. You run the application using the flask run
command.
Open a browser and type in the URL http://127.0.0.1:5000/
. You will find a Welcome to FlaskShortener page.
Submit a URL, and you will receive a short URL.
You created a Flask application with a page that accepts URLs and generates shorter ones, but the URLs don’t do anything yet. In the next step, you’ll add a route that extracts the hash from the short URL, finds the original URL, and redirects users to it.
In this step, you will add a new route that takes the short hash the application generates and decodes the hash into its integer value, which is the original URL’s ID. Your new route will also use the integer ID to fetch the original URL and increment the clicks
value. Finally, you will redirect users to the original URL.
First, open the app.py
to add a new route:
- nano app.py
Add the following code to the end of the file:
. . .
@app.route('/<id>')
def url_redirect(id):
conn = get_db_connection()
original_id = hashids.decode(id)
if original_id:
original_id = original_id[0]
url_data = conn.execute('SELECT original_url, clicks FROM urls'
' WHERE id = (?)', (original_id,)
).fetchone()
original_url = url_data['original_url']
clicks = url_data['clicks']
conn.execute('UPDATE urls SET clicks = ? WHERE id = ?',
(clicks+1, original_id))
conn.commit()
conn.close()
return redirect(original_url)
else:
flash('Invalid URL')
return redirect(url_for('index'))
This new route accepts a value id
through the URL and passes it to the url_redirect()
view function. For example, visiting http://127.0.0.1:5000/KJ34
would pass the string 'KJ34'
to the id
parameter.
Inside the view function, you first open a database connection. Then you use the decode()
method of the hashids
object to convert the hash to its original integer value and store it in the original_id
variable. You check that the original_id
has a value—meaning decoding the hash was successful. If it has a value, you extract the ID from it. As the decode()
method returns a tuple, you fetch the first value in the tuple with original_id[0]
, which is the original ID.
You then use the SELECT
SQL statement to fetch the original URL and its number of clicks from the urls
table, where the ID of the URL matches the original ID you extracted from the hash. You fetch the URL data with the fetchone()
method. Next, you extract the data into the two original_url
and clicks
variables.
You then increment the number of clicks of the URL with the UPDATE
SQL statement.
You commit the transaction and close the connection, and redirect to the original URL using the redirect()
Flask helper function.
If decoding the hash fails, you flash a message to inform the user that the URL is invalid, and redirect them to the index page.
Save and close the file.
Run your development server:
- flask run
Use your browser to go to http://127.0.0.1:5000/
. Enter a URL and visit the resulting short URL; your application will redirect you to the original URL.
You created a new route that redirects users from the short URL to the original URL. Next, you’ll add a page to show how many times each URL has been visited.
In this step, you’ll add a new route for a statistics page that displays how many times each URL has been clicked. You’ll also add a button that links to the page on the navigation bar.
Allowing users to see the number of visits each shortened link has received will provide visibility into each URL’s popularity, which is useful for projects, like marketing ad campaigns. You can also use this workflow as an example of adding a feature to an existing Flask application.
Open app.py
to add a new route for a statistics page:
- nano app.py
Add the following code to the end of the file:
. . .
@app.route('/stats')
def stats():
conn = get_db_connection()
db_urls = conn.execute('SELECT id, created, original_url, clicks FROM urls'
).fetchall()
conn.close()
urls = []
for url in db_urls:
url = dict(url)
url['short_url'] = request.host_url + hashids.encode(url['id'])
urls.append(url)
return render_template('stats.html', urls=urls)
In this view function, you open a database connection. Then you fetch the ID, the creation date, the original URL, and the number of clicks for all of the entries in the urls
table. You use the fetchall()
method to get a list of all the rows. You then save this data in the db_urls
variable and close the connection.
To display the short URL for each entry, you will need to construct it and add it to each item in the list of the URLs you fetched from the database (db_urls
). You create an empty list called urls
and loop through the db_urls
list with for url in db_urls
.
You use the dict()
Python function to convert the sqlite3.Row
object to a dictionary to allow assignment. You add a new key called short_url
to the dictionary with the value request.host_url + hashids.encode(url['id'])
, which is what you used before to construct short URLs in the index view function. You append this dictionary to the urls
list.
Finally, you render a template file called stats.html
, passing the urls
list to it.
Save and close the file.
Next, create the new stats.html
template file:
- nano templates/stats.html
Type the following code into it:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} FlaskShortener Statistics {% endblock %}</h1>
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">Short</th>
<th scope="col">Original</th>
<th scope="col">Clicks</th>
<th scope="col">Creation Date</th>
</tr>
</thead>
<tbody>
{% for url in urls %}
<tr>
<th scope="row">{{ url['id'] }}</th>
<td>{{ url['short_url'] }}</td>
<td>{{ url['original_url'] }}</td>
<td>{{ url['clicks'] }}</td>
<td>{{ url['created'] }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
Here you extend the base.html
base template by specifying a title and defining a table with the following columns:
#
: The ID of the URL.Short
: The short URL.Original
: The original URL.Clicks
: The number of times a short URL has been visited.Creation Date
: The creation date of the short URL.Each row is filled using a for
loop that goes through the urls
list and displays the value of each column for each URL.
Run the development server with the following:
- flask run
Use your browser to go to http://127.0.0.1:5000/stats
. You will find all the URLs in a table.
Next, add a Stats button to the navigation bar. Open the base.html
file:
- nano templates/base.html
Edit the file as per the following highlighted lines:
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<title>{% block title %} {% endblock %}</title>
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item active">
<a class="nav-link" href="#">About</a>
</li>
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('stats')}}">Stats</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% for message in get_flashed_messages() %}
<div class="alert alert-danger">{{ message }}</div>
{% endfor %}
{% block content %} {% endblock %}
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>
</html>
Here you incorporate a new <li>
item to the navigation bar. You use the url_for()
function to link to the stats()
view function. You can now access the statistics page from the navigation bar.
Your statistics page shows information about each URL, including its shorter equivalent and how many times it has been visited.
You can reuse this code for monitoring number of clicks in other contexts, such as keeping track of how many times a post has been liked or updated on a social media site or how many times a photo/video has been viewed.
You can access the full code for the application from this repository.
You have created a Flask application that allows users to enter a long URL and generate a shorter version. You have transformed integers into short string hashes, redirected users from one link to another, and set up a page for statistics so you can monitor shortened URLs. For further projects and tutorials on working with Flask, check out the following tutorials:
]]>This time I have a problem with the server, Currently I am using $15 server which has 2 cores and 2gb RAM, but the performance is poor now I don’t know what is the reason here.
This is the API:
var express = require('express');
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
var router = express.Router();
/* GET users listing. */
router.get('/', function(req, res, next) {
if(Object.keys(req.query).length === 0){
res.send("No input given");
} else {
let query = req.query.q;
let SQLquery = 'SELECT * FROM recipe ';
if(query){
SQLquery += 'WHERE RecipeName LIKE "%' + query + '%"';
}
//console.log(SQLquery);
let result = [];
var db = new sqlite3.Database(path.resolve(__dirname, '../recipe.sqlite'));
db.serialize(function() {
db.each(SQLquery, (err, row) => {
if (err) {
console.error(err.message);
} else {
result.push(row);
}
}, function(){
//console.log(result);
res.send(result);
});
});
db.close();
}
});
module.exports = router;
So basically I have stored data in Sqlite3 which would be 3 Million rows, 280 people or fewer than that, fetching data at a time, but the server is not responding - 100% CPU and RAM.
Help me with this. The structure of script is not good? or $15 server is not enough or what is the problem please explain this.
Thank You! Regards
Note: This tutorial uses a deprecated version of Ubuntu. Click here to read an updated version written for Ubuntu 20.04.
Sqlite is a very simple and fast open source SQL engine. This tutorial will explain when it is optimal to use Sqlite, as opposed to a full-blown RDBMS such as Mysql or Postgres, as well as how to install it and basic usage examples covering CRUD - Create, Read, Update, and Delete.
Don’t be deceived into thinking that Sqlite is only for testing and development. For example, it works fine for websites receiving up to 100,000 hits a day-- and this is a conservative limit. The maximum size for a Sqlite database is 140 Terabytes (which should be enough, right?), and it can be substantially faster than a full-blown RDBMS. The full database and all other necessary data is stored in a normal file in the host’s file system, so no separate server process is needed (cutting out all need for slow inter-process communication).
Sqlite is focused on simplicity. Because it is completely internal, it is often significantly faster than alternatives. If you are looking for portability (with regards to both languages and platforms), simplicity, speed, and a small memory footprint–Sqlite is ideal. Its shortcomings are only apparent if you need high reading or writing concurrency: Sqlite can only support one writer at a time, and the normally high file system latency may be inconvenient if there is a need for many clients to access a Sqlite database simultaneously. A final possible disadvantage is that its syntax, though similar to other SQL systems, is unique. While it’s fairly trivial to move to another system, if you do ‘outgrow’ Sqlite, there will be some overhead involved in the transition.
For more information, there are some very good outlines on the pros and cons of Sqlite inside the official documentation.
The sqlite3 module is part of the standard Python library, so on a standard Ubuntu installation or any system with Python installed, no further installation is strictly necessary. To install the Sqlite command line interface on Ubuntu, use these commands:
sudo apt-get update
sudo apt-get install sqlite3 libsqlite3-dev
If you need to compile it from source, then grab the latest autoconf version from the official SQLite website. At the time of writing:
wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz
tar xvfz sqlite-autoconf-3080100.tar.gz
cd sqlite-autoconf-3080100
./configure
make
make install
(Notes for building from source: 1) Don’t do this on a standard Ubuntu installation, as you’ll probably get a “header and source version mismatch” error, due to conflict between an already installed version and the newly installed one. 2) If the make
command seems to expect further input, just be patient, as the source can take a while to compile).
To create a database, run the command:
sqlite3 database.db
Where ‘database’ is the name of your database. If the file database.db
already exists, Sqlite will open a connection to it; if it does not exist, it will be created. You should see output similar to:
SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
Now let’s create a table and insert some data. This table named “wines” has four columns: for an ID, the wine’s producer, the wine’s kind, and country of the wine’s origin. As it’s not Friday yet, we’ll insert only three wines into our database:
CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20));
INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa");
INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa");
INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");
We’ve created the database, a table, and some entries. Now press Ctrl + D
to exit Sqlite and type the following (again substituting your database’s name for ‘database’), which will reconnect to the database we just created:
sqlite3 database.db
Now type:
SELECT * FROM wines;
And you should see the entries we’ve just made:
1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|France
Great. That’s it for creating and reading. Let’s do an update and delete:
UPDATE wines SET country="South Africa" WHERE country="France";
Which will update the database so all wines which are listed as coming from France will instead be listed as coming from South Africa. Check the result with:
SELECT * FROM wines;
And you should see:
1|Rooiberg|Pinotage|South Africa
2|KWV|Shiraz|South Africa
3|Marks & Spencer|Pinot Noir|South Africa
Now all our wines come from South Africa. Let’s drink the KWV in celebration, and delete it from our database:
DELETE FROM wines WHERE id=2;
SELECT * FROM wines;
And we should see one fewer wine listed in our cellar:
1|Rooiberg|Pinotage|South Africa
3|Marks & Spencer|Pinot Noir|South Africa
And that covers all of the basic database operations. Before we finish, let’s try one more (slightly) less trivial example, which uses two tables and a basic join.
Exit from Sqlite with the command Ctrl + D
and reconnect to a new database with sqlite3 database2.db
.
We’ll be creating a very similar wines
table, but also a countries
table, which stores the country’s name and its current president. Let’s create the countries table first and insert South Africa and France into it with (note that you can copy-paste several lines of sqlite code at once):
CREATE TABLE countries (id integer, name varchar(30), president varchar(30));
INSERT INTO countries VALUES (1, "South Africa", "Jacob Zuma");
INSERT INTO countries VALUES(2, "France", "Francois Hollande");
And then we can recreate our wines table with:
CREATE TABLE wines (id integer, kind varchar(30), country_id integer);
INSERT INTO wines VALUES (1, "Pinotage", 1);
INSERT INTO wines VALUES (2, "Shiraz", 1);
INSERT INTO wines VALUES (3, "Pinot Noir", 2);
Now let’s see what kinds of wine there are in South Africa with:
SELECT kind FROM wines JOIN countries ON country_id=countries.id WHERE countries.name="South Africa";
And you should see:
Pinotage
Shiraz
And that covers a basic Join. Notice that Sqlite does a lot for you. In the join statement above, it defaults to INNER JOIN
, although we just use the keyword JOIN
. Also we don’t have to specify wines.country_id
as it’s unambiguous. On the other hand, if we try the command:
SELECT kind FROM wines JOIN countries ON country_id=id WHERE country_id=1;
We’ll get the error message Error: ambiguous column name: id
. Which is fair enough as both of our tables have an id
column. But generally Sqlite is fairly forgiving. Its error messages tend to make it fairly trivial to locate and fix any issues, and this helps speed up the development process.
For further help with syntax, the official documentation is full of diagrams like this one, which can be helpful, but if you prefer concrete examples, here is a link to a tutorial with a nice overview of most of the join types.
Finally, Sqlite has wrappers and drivers in all the major languages, and can run on most systems. [A list of many of them can be found here](http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers" target="_blank).
]]>Do I need to SQLite via a build script?
Thanks!
]]>Flask is a framework for building web applications using the Python language, and SQLite is a database engine that you can use with Python to store application data. In this tutorial, you’ll modify an application built using Flask and SQLite by adding a many-to-many relationship to it.
Although you can follow this tutorial independently, it is also a continuation of the How To Modify Items in a One-to-Many Database Relationships with Flask and SQLite tutorial in which we managed a multi-table database with a one-to-many relationship using a to-do application example. The application allows users to add new to-do items, categorize items under different lists, and modify items.
A many-to-many database relationship is a relationship between two tables where a record in each table can reference several records in the other table. For example, in a blog, a table for posts can have a many-to-many relationship with a table for storing authors. Each post can reference many authors, and each author can reference many posts. Each post can have many authors, and each author can write many posts. Therefore, there is a many-to-many relationship between posts and authors. For another example, in a social media application, each post may have many hashtags, and each hashtag may have many posts.
By the end of the tutorial, your application will have a new feature for assigning to-do items to different users. We will refer to the users that get assigned to-dos with the word assignees. For example, you can have a household to-do item for Cleaning the kitchen
, which you can assign to both Sammy
and Jo
—each to-do can have many assignees (that is, Sammy
and Jo
). Also each user can have many to-dos assigned to them (that is, Sammy
can be assigned multiple to-do items), this is a many-to-many relationship between to-do items and assignees.
At the end of this tutorial, the application will include an Assigned to tag with the names of the assignees listed.
Before you start following this guide, you will need:
A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial we’ll call our project directory flask_todo
.
(Optional) In Step 1 you’ll have the option of cloning the to-do application you’ll be working on in this tutorial. However, you can optionally work through How To Use One-to-Many Database Relationships with Flask and SQLite and How To Modify Items in a One-to-Many Database Relationships with Flask and SQLite. You can access the final code from this page.
(Optional) An understanding of basic Flask concepts such as creating routes, rendering HTML templates, and connecting to a SQLite database. Check out How To Make a Web Application Using Flask in Python 3 and How To Use the sqlite3 Module in Python 3, if you are not familiar with these concepts, but it’s not necessary.
In this step, you will set up the to-do application ready for modification. You will also review the database schema to understand the structure of the database. If you followed the tutorial in the prerequisites section and still have the code and the virtual environment on your local machine, you can skip this step.
To demonstrate adding a many-to-many relationship to a Flask web application, you will use the previous tutorial’s application code, which is a to-do management web application built using Flask, SQLite, and the Bootstrap framework. With this application users can create new to-dos, modify and delete existing to-dos, and mark to-dos as complete.
Clone the repository and rename it from flask-todo-2
to flask_todo
with the following command:
- git clone https://github.com/do-community/flask-todo-2 flask_todo
Navigate to flask_todo
:
- cd flask_todo
Then create a new virtual environment:
- python -m venv env
Activate the environment:
- source env/bin/activate
Install Flask:
- pip install Flask
Then, initialize the database using the init_db.py
program:
- python init_db.py
Next, set the following environment variables:
- export FLASK_APP=app
- export FLASK_ENV=development
FLASK_APP
indicates the application you are currently developing, which is app.py
in this case. FLASK_ENV
specifies the mode—set it to development
for development mode; this will allow you to debug the application. (Remember not to use this mode in a production environment.)
Then run the development server:
- flask run
If you go to your browser, you’ll have the application running at the following URL: http://127.0.0.1:5000/
.
To stop the development server, use CTRL + C
.
Next, you will go through the database schema to understand the current relationships between tables. If you are familiar with the contents of the schema.sql
file, you can skip to the next step.
Open the schema.sql
file:
- nano schema.sql
The file contents are as follows:
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
list_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
content TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (list_id) REFERENCES lists (id)
);
In the schema.sql
file, you have two tables: lists
for storing lists (such as Home
or Study
), and items
for storing to-do items (such as Do the dishes
or Learn Flask
).
The lists
table has the following columns:
id
: The ID of the list.created
: The list’s creation date.title
: The list’s title.The items
table has the following columns:
id
: The ID of the item.list_id
: The ID of the list the item belongs to.created
: The item’s creation date.content
: The item’s content.done
: The item’s state, the value 0
indicates the item has not been done yet, while 1
indicates item completion.In the items
table you have a foreign key constraint, in which the list_id
column references the id
column of the lists
parent table. This is a one-to-many relationship between items and lists, indicating that a list can have multiple items, and items belong to a single list:
FOREIGN KEY (list_id) REFERENCES lists (id)
In the next step, you will use a many-to-many relationship to create a link between two tables.
In this step, you will review how to implement a many-to-many relationship and joins table. Then you’ll add a new table for storing assignees.
A many-to-many relationship links two tables where each item in a table has many related items in the other table.
Let’s say you have a simple table for to-do items as follows:
Items
+----+-------------------+
| id | content |
+----+-------------------+
| 1 | Buy eggs |
| 2 | Fix lighting |
| 3 | Paint the bedroom |
+----+-------------------+
And a table for assignees like so:
assignees
+----+------+
| id | name |
+----+------+
| 1 | Sammy|
| 2 | Jo |
+----+------+
Let’s say you want to assign the to-do Fix lighting
to both Sammy
and Jo
, you could do this by adding a new row in the items
table like so:
items
+----+-------------------+-----------+
| id | content | assignees |
+----+-------------------+-----------+
| 1 | Buy eggs | |
| 2 | Fix lighting | 1, 2 |
| 3 | Paint the bedroom | |
+----+-------------------+-----------+
This is the wrong approach because each column should only have one value; if you have multiple values, basic operations such as adding and updating data become cumbersome and slow. Instead, there should be a third table that references primary keys of related tables—this table is often called a join table, and it stores IDs of each item from each table.
Here is an example of a join table that links between items and assignees:
item_assignees
+----+---------+-------------+
| id | item_id | assignee_id |
+----+---------+-------------+
| 1 | 2 | 1 |
| 2 | 2 | 2 |
+----+---------+-------------+
In the first row, the item with the ID 2
(that is, Fix lighting
) relates to the assignee with the ID 1
(Sammy
). In the second row, the same item also relates to the assignee with the ID 2
(Jo
). This means that the to-do item is assigned to both Sammy
and Jo
. Similarly, you can assign each assignee to multiple items.
Now, you will modify the to-do application’s database to add a table for storing assignees.
First, open schema.sql
to add a new table named assignees
:
- nano schema.sql
Add a line to delete the assignees
table if it already exists. This is to avoid potential future issues when reinitiating the database, such as an already existing assignees
table with different columns, which might break the code unexpectedly if it does not follow the same schema. You also add the SQL code for the table:
DROP TABLE IF EXISTS assignees;
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
list_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
content TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (list_id) REFERENCES lists (id)
);
CREATE TABLE assignees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
Save and close the file.
This new assignees
table has the following columns:
id
: The ID of the assignee.name
: The name of the assignee.Edit the init_db.py
program to add a few assignees to the database. You use this program to initialize the database:
- nano init_db.py
Modify the file to look as follows:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(1, 'Morning meeting')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Buy fruit')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Cook dinner')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn Flask')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn SQLite')
)
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Sammy',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Jo',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Charlie',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Ashley',))
connection.commit()
connection.close()
Save and close the file.
In the highlighted lines, you use the cursor object to execute an INSERT
SQL statement to insert four names into the assignees
table. You use the ?
placeholder in the execute()
method and pass a tuple containing the name of the assignee to safely insert data into the database. Then you commit the transaction with connection.commit()
and close the connection using connection.close()
.
This will add four assignees to the database, with the names Sammy
, Jo
, Charlie
, and Ashley
.
Run the init_db.py
program to reinitialize the database:
- python init_db.py
You now have a table for storing assignees in the database. Next you will add a join table to create a many-to-many relationship between items and assignees.
In this step, you will use a join table to link to-do items with assignees. First you’ll edit your database schema file to add the new join table, edit the database initialization program to add a few assignments, then use a demonstration program to display the assignees of each to-do.
Open schema.sql
to add a new table:
- nano schema.sql
Because the table joins items and assignees, you will call it item_assignees
. Add a line to delete the table if it already exists, then add the SQL code for the table itself:
DROP TABLE IF EXISTS assignees;
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS item_assignees;
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
list_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
content TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (list_id) REFERENCES lists (id)
);
CREATE TABLE assignees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE item_assignees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item_id INTEGER,
assignee_id INTEGER,
FOREIGN KEY(item_id) REFERENCES items(id),
FOREIGN KEY(assignee_id) REFERENCES assignees(id)
);
Save and close the file.
This new item_assignees
table has the following columns:
id
: The ID of the entry that establishes a relationship between to-dos and assignees; each row represents a relationship.item_id
: The ID of the to-do item that will be assigned to the assignee with the corresponding assignee_id
.assignee_id
: The ID of the assignee who will get assigned the item with the corresponding item_id
.The item_assignees
table also has two foreign key constraints: one that links the item_id
column with the id
column of the items
table, and another one linking between the assignee_id
column with the id
column of the assignees
table.
Open init_db.py
to add a few assignments:
- nano init_db.py
Modify the file to look as follows:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(1, 'Morning meeting')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Buy fruit')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Cook dinner')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn Flask')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn SQLite')
)
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Sammy',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Jo',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Charlie',))
cur.execute("INSERT INTO assignees (name) VALUES (?)", ('Ashley',))
# Assign "Morning meeting" to "Sammy"
cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
(1, 1))
# Assign "Morning meeting" to "Jo"
cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
(1, 2))
# Assign "Morning meeting" to "Ashley"
cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
(1, 4))
# Assign "Buy fruit" to "Sammy"
cur.execute("INSERT INTO item_assignees (item_id, assignee_id) VALUES (?, ?)",
(2, 1))
connection.commit()
connection.close()
In the highlighted code, you assign to-do items to assignees by inserting into the item_assignees
join table. You insert the item_id
of the to-do item you want to assign to the assignee with the ID corresponding to the assignee_id
value. In the first highlighted line, you assign the to-do item Morning meeting
, which has an ID of 1
, to the assignee Sammy
, who has an ID of 1
. The rest of the lines follow the same pattern. Once again, you use the ?
placeholders to safely pass the values you want to insert in a tuple to the cur.execute()
method.
Save and close the file.
Run the init_db.py
program to reinitialize the database:
- python init_db.py
Run the list_example.py
program that displays the to-do items you have on the database:
- python list_example.py
Here is the output:
OutputHome
Buy fruit | id: 2 | done: 0
Cook dinner | id: 3 | done: 0
Study
Learn Flask | id: 4 | done: 0
Learn SQLite | id: 5 | done: 0
Work
Morning meeting | id: 1 | done: 0
This displays the to-do items under the lists they belong to. You have each item’s content, its ID, and whether it’s completed or not (0
means the item is not completed yet, and 1
means it’s completed). You now need to display the assignees of each to-do.
Open list_example.py
to modify it to display item assignees:
- nano list_example.py
Modify the file to look as follows:
from itertools import groupby
from app import get_db_connection
conn = get_db_connection()
todos = conn.execute('SELECT i.id, i.done, i.content, l.title \
FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
# Create an empty list for items
items = []
# Go through each to-do item row in the groupby() grouper object
for item in g:
# Get the assignees of the current to-do item
assignees = conn.execute('SELECT a.id, a.name FROM assignees a \
JOIN item_assignees i_a \
ON a.id = i_a.assignee_id \
WHERE i_a.item_id = ?',
(item['id'],)).fetchall()
# Convert the item row into a dictionary to add assignees
item = dict(item)
item['assignees'] = assignees
items.append(item)
# Build the list of dictionaries
# the list's name (ex: Home/Study/Work) as the key
# and a list of dictionaries of to-do items
# belonging to that list as the value
lists[k] = list(items)
for list_, items in lists.items():
print(list_)
for item in items:
assignee_names = ', '.join(a['name'] for a in item['assignees'])
print(' ', item['content'], '| id:',
item['id'], '| done:', item['done'],
'| assignees:', assignee_names)
Save and close the file.
You use the groupby()
function to group to-do items by the title of the list they belong to. (See Step 2 of How To Use One-to-Many Database Relationships with Flask and SQLite for more information.) While going through the grouping process, you create an empty list called items
, which will hold all of the to-do item data, such as the item’s ID, content, and assignees. Next, in the for item in g
loop, you go through each to-do item, get the assignees of the item, and save it in the assignees
variable.
The assignees
variable holds the result of a SELECT
SQL query. This query gets the assignee’s id (a.id
) and the assignee’s name (a.name
) from the assignees
table (which is aliased to a
to shorten the query). The query joings the id and name with the item_assignees
join table (aliased to i_a
) on the condition a.id = i_a.assignee_id
where the i_a.item_id
value equals that of the current item’s ID (item['id']
). Then you use the fetchall()
method to get the results as a list.
With the line item = dict(item)
, you convert the item into a dictionary because a regular sqlite3.Row
object does not support assignment, which you will need to add assignees to the item. Next, with the line item['assignees'] = assignees
, you add a new key 'assignees'
to the item
dictionary to access the item’s assignees directly from the item’s dictionary. Then you append the modified item to the items
list. You build the list of dictionaries that will hold all of the data; each dictionary key is the to-do list’s title, and its value is a list of all the items that belong to it.
To print the results, you use the for list_, items in lists.items()
loop to go through each to-do list title and the to-do items that belong to it, you print the list’s title (list_
), then loop through the to-do items of the list. You added a variable named assignee_names
, the value of which uses the join()
method to join between the items of the generator expression a['name'] for a in item['assignees']
, which extracts the assignee’s name (a['name']
), from the data of each assignee in the item['assignees']
list. This joined list of assignee names, you then print with the rest of the to-do item’s data in the print()
function.
Run the list_example.py
program:
- python list_example.py
Here is the output (with assignees highlighted):
OutputHome
Buy fruit | id: 2 | done: 0 | assignees: Sammy
Cook dinner | id: 3 | done: 0 | assignees:
Study
Learn Flask | id: 4 | done: 0 | assignees:
Learn SQLite | id: 5 | done: 0 | assignees:
Work
Morning meeting | id: 1 | done: 0 | assignees: Sammy, Jo, Ashley
You can now display the assignees of each to-do item with the rest of the data.
You have now displayed the assignee names of each to-do item. Next, you will use this to display the names below each to-do item in the web application’s index page.
In this step, you’ll modify the index page of the to-do management application to show the assignees of each to-do item. You will first edit the app.py
file, which contains the code for the Flask application, then edit the index.html
template file to display the assignees below each to-do item on the index page.
First, open app.py
to edit the index()
view function:
- nano app.py
Modify the function to look as follows:
@app.route('/')
def index():
conn = get_db_connection()
todos = conn.execute('SELECT i.id, i.done, i.content, l.title \
FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
# Create an empty list for items
items = []
# Go through each to-do item row in the groupby() grouper object
for item in g:
# Get the assignees of the current to-do item
assignees = conn.execute('SELECT a.id, a.name FROM assignees a \
JOIN item_assignees i_a \
ON a.id = i_a.assignee_id \
WHERE i_a.item_id = ?',
(item['id'],)).fetchall()
# Convert the item row into a dictionary to add assignees
item = dict(item)
item['assignees'] = assignees
items.append(item)
# Build the list of dictionaries
# the list's name (ex: Home/Study/Work) as the key
# and a list of dictionaries of to-do items
# belonging to that list as the value
lists[k] = list(items)
conn.close()
return render_template('index.html', lists=lists)
Save and close the file.
This is the same code you used in the list_example.py
demonstration program in Step 3. With this, the lists
variable will contain all the data you need, including assignee data, which you will use to access assignee names in the index.html
template file.
Open the index.html
file to add assignee names following each item:
- nano templates/index.html
Modify the file to look as follows:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
{% for list, items in lists.items() %}
<div class="card" style="width: 18rem; margin-bottom: 50px;">
<div class="card-header">
<h3>{{ list }}</h3>
</div>
<ul class="list-group list-group-flush">
{% for item in items %}
<li class="list-group-item"
{% if item['done'] %}
style="text-decoration: line-through;"
{% endif %}
>{{ item['content'] }}
{% if not item ['done'] %}
{% set URL = 'do' %}
{% set BUTTON = 'Do' %}
{% else %}
{% set URL = 'undo' %}
{% set BUTTON = 'Undo' %}
{% endif %}
<div class="row">
<div class="col-12 col-md-3">
<form action="{{ url_for(URL, id=item['id']) }}"
method="POST">
<input type="submit" value="{{ BUTTON }}"
class="btn btn-success btn-sm">
</form>
</div>
<div class="col-12 col-md-3">
<a class="btn btn-warning btn-sm"
href="{{ url_for('edit', id=item['id']) }}">Edit</a>
</div>
<div class="col-12 col-md-3">
<form action="{{ url_for('delete', id=item['id']) }}"
method="POST">
<input type="submit" value="Delete"
class="btn btn-danger btn-sm">
</form>
</div>
</div>
<hr>
{% if item['assignees'] %}
<span style="color: #6a6a6a">Assigned to</span>
{% for assignee in item['assignees'] %}
<span class="badge badge-primary">
{{ assignee['name'] }}
</span>
{% endfor %}
{% endif %}
</li>
{% endfor %}
</ul>
</div>
{% endfor %}
{% endblock %}
Save and close the file.
With this modification, you added a line break below each item using the <hr>
tag. If the item has any assignees (which you know via the statement if item['assignees']
), you display a gray Assigned to
text and loop through the item assignees (that is, the item['assignees']
list), and display the assignee name (assignee['name']
) in a badge.
Finally, run the development server:
- flask run
Then visit the index page: http://127.0.0.1:5000/
.
Each to-do item can now have many assignees, and you can assign each assignee multiple to-dos. The index page displays all of the items and the assignees of each item.
You can access the final code from this repository.
In this tutorial, you have learned what a many-to-many relationship is, how to use it in a Flask and SQLite web application, how to join between tables, and how to group relational data in Python.
You now have a complete to-do application in which users can create new to-do items, mark an item as complete, edit or delete existing items, and create new lists. And each item can be assigned to different assignees.
To learn more about web development with Python and Flask see these Flask tutorials.
]]>Flask is a framework for building web applications using the Python language, and SQLite is a database engine that can be used with Python to store application data. In this tutorial, you’ll modify items in an application built using Flask and SQLite with a One-to-Many relationship.
This tutorial is a continuation of How To Use One-to-Many Database Relationships with Flask and SQLite. After having followed it, you’ve successfully created a Flask application to manage to-do items, organize items in lists, and add new items to the database. In this tutorial, you will add the functionality to mark to-do items as complete, to edit and delete items, and to add new lists to the database. By the end of the tutorial, your application will include edit and delete buttons and strikethroughs for completed to-dos.
Before you start following this guide, you will need:
A local Python 3 programming environment, follow the tutorial for your distribution in How To Install and Set Up a Local Programming Environment for Python 3 series. In this tutorial we’ll call our project directory flask_todo
.
(Optional) In Step 1 you’ll have the option of cloning the to-do application we’ll be working on in this tutorial. However, you can optionally work through How To Use One-to-Many Database Relationships with Flask and SQLite. You can access the final code from this page.
An understanding of basic Flask concepts such as creating routes, rendering HTML templates, and connecting to a SQLite database. Check out How To Make a Web Application Using Flask in Python 3, and How To Use the sqlite3 Module in Python 3 if you are not familiar with these concepts, but it’s not necessary.
In this step, you will set up the to-do application to be ready for modification. If you followed the tutorial in the prerequisites section and still have the code and the virtual environment in your local machine, you can skip this step.
First use Git to clone the repository of the previous tutorial’s code:
- git clone https://github.com/do-community/flask-todo
Navigate to flask-todo
:
- cd flask-todo
Then create a new virtual environment:
- python -m venv env
Activate the environment:
- source env/bin/activate
Install Flask:
- pip install Flask
Then, initialize the database using the init_db.py
program:
- python init_db.py
Next, set the following environment variables:
- export FLASK_APP=app
- export FLASK_ENV=development
FLASK_APP
indicates the application you are currently developing, which is app.py
in this case. FLASK_ENV
specifies the mode—set it to development
for development mode, this will allow you to debug the application. (Remember not to use this mode in a production environment.)
Then run the development server:
- flask run
If you go to your browser, you’ll have the application running on the following URL at http://127.0.0.1:5000/
.
To close the development server, use the CTRL + C
key combination.
Next, you will modify the application to add the ability to mark items as complete.
In this step, you’ll add a button to mark each to-do item as complete.
To be able to mark items as complete, you’ll add a new column to the items
table in your database to have a marker for each item so you know whether it is completed or not, then you will create a new route in your app.py
file to change the value of this column depending on the user’s action.
As a reminder the columns in the items
table are currently the following:
id
: The ID of the item.list_id
: The ID of the list the item belongs to.created
: The item’s creation date.content
: The item’s content.First, open schema.sql
to modify the items
table:
- nano schema.sql
Add a new column named done
to the items
table:
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
list_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
content TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (list_id) REFERENCES lists (id)
);
Save and close the file.
This new column will hold the integer values 0
or 1
; the value 0
represents the Boolean value false
and 1
represents the value true
. The default is 0
, which means any new items you add will automatically be unfinished until the user marks the item as complete, in which case the value of the done
column will change to 1
.
Then, initialize the database again using the init_db.py
program to apply the modifications you have performed on schema.sql
:
- python init_db.py
Next, open app.py
for modification:
- nano app.py
You’ll fetch the id
of the item and the value of the done
column in the index()
function, which fetches the lists and items from the database and sends them to the index.html
file for display. The necessary changes to the SQL statement are highlighted in the following file:
@app.route('/')
def index():
conn = get_db_connection()
todos = conn.execute('SELECT i.id, i.done, i.content, l.title \
FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)
conn.close()
return render_template('index.html', lists=lists)
Save and close the file.
With this modification, you get the IDs of the to-do items using i.id
and the values of the done
column using i.done
.
To understand this change, open list_example.py
, which is a small, example program you can use to understand the contents of the database:
- nano list_example.py
Perform the same modification to the SQL statement as before, then change the last print()
function to display the item ID and the value of done
:
from itertools import groupby
from app import get_db_connection
conn = get_db_connection()
todos = conn.execute('SELECT i.id, i.done, i.content, l.title \
FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)
for list_, items in lists.items():
print(list_)
for item in items:
print(' ', item['content'], '| id:',
item['id'], '| done:', item['done'])
Save and exit the file.
Run the example program:
- python list_example.py
Here is the output:
OutputHome
Buy fruit | id: 2 | done: 0
Cook dinner | id: 3 | done: 0
Study
Learn Flask | id: 4 | done: 0
Learn SQLite | id: 5 | done: 0
Work
Morning meeting | id: 1 | done: 0
None of the items has been marked as completed so the value of done
for each item is 0
, which means false
. To allow users to change this value and mark items as completed, you will add a new route to the app.py
file.
Open app.py
:
- nano app.py
Add a route /do/
at the end of the file:
. . .
@app.route('/<int:id>/do/', methods=('POST',))
def do(id):
conn = get_db_connection()
conn.execute('UPDATE items SET done = 1 WHERE id = ?', (id,))
conn.commit()
conn.close()
return redirect(url_for('index'))
This new route accepts only POST
requests. The do()
view function takes an id
argument—this is the ID of the item you want to mark as completed. Inside the function, you open a database connection, then you use an UPDATE
SQL statement to set the value of the done
column to 1
for the item to be marked as completed.
You use the ?
placeholder in the execute()
method and pass a tuple containing the ID to safely insert data into the database. Then you commit the transaction and close the connection and redirect to the index page.
After adding a route to mark items as completed, you need another route to undo this action and return the item to a non-completed status. Add the following route at the end of the file:
. . .
@app.route('/<int:id>/undo/', methods=('POST',))
def undo(id):
conn = get_db_connection()
conn.execute('UPDATE items SET done = 0 WHERE id = ?', (id,))
conn.commit()
conn.close()
return redirect(url_for('index'))
This route is similar to the /do/
route, and the undo()
view function is exactly the same as the do()
function except that you set the value of done
to 0
instead of 1
.
Save and close the app.py
file.
You now need a button to mark to-do items as completed or uncompleted depending on the state of the item, open the index.html
template file:
- nano templates/index.html
Change the contents of the inner for
loop inside the <ul>
element to look as follows:
{% block content %}
<h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
{% for list, items in lists.items() %}
<div class="card" style="width: 18rem; margin-bottom: 50px;">
<div class="card-header">
<h3>{{ list }}</h3>
</div>
<ul class="list-group list-group-flush">
{% for item in items %}
<li class="list-group-item"
{% if item['done'] %}
style="text-decoration: line-through;"
{% endif %}
>{{ item['content'] }}
{% if not item ['done'] %}
{% set URL = 'do' %}
{% set BUTTON = 'Do' %}
{% else %}
{% set URL = 'undo' %}
{% set BUTTON = 'Undo' %}
{% endif %}
<div class="row">
<div class="col-12 col-md-3">
<form action="{{ url_for(URL, id=item['id']) }}"
method="POST">
<input type="submit" value="{{ BUTTON }}"
class="btn btn-success btn-sm">
</form>
</div>
</div>
</li>
{% endfor %}
</ul>
</div>
{% endfor %}
{% endblock %}
In this for
loop, you use a line-through
CSS value for the text-decoration
property if the item is marked as completed, which you know from the value of item['done']
. You then use the Jinja syntax set
to declare two variables, URL
and BUTTON
. If the item is not marked as completed the button will have the value Do and the URL will direct to the /do/
route, and if the item was marked as completed, the button will have a value of Undo and will point to /undo/
. After, you use both these variables in an input
form that submits the proper request depending on the state of the item.
Run the server:
- flask run
You can now mark items as completed on the index page http://127.0.0.1:5000/
. Next you will add the ability to edit to-do items.
In this step, you will add a new page for editing items so you can modify the contents of each item and assign items to different lists.
You will add a new /edit/
route to the app.py
file, which will render a new edit.html
page in which a user can modify existing items. You will also update the index.html
file to add an Edit
button to each item.
First, open the app.py
file:
- nano app.py
Then add the following route at the end of the file:
. . .
@app.route('/<int:id>/edit/', methods=('GET', 'POST'))
def edit(id):
conn = get_db_connection()
todo = conn.execute('SELECT i.id, i.list_id, i.done, i.content, l.title \
FROM items i JOIN lists l \
ON i.list_id = l.id WHERE i.id = ?', (id,)).fetchone()
lists = conn.execute('SELECT title FROM lists;').fetchall()
if request.method == 'POST':
content = request.form['content']
list_title = request.form['list']
if not content:
flash('Content is required!')
return redirect(url_for('edit', id=id))
list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
(list_title,)).fetchone()['id']
conn.execute('UPDATE items SET content = ?, list_id = ?\
WHERE id = ?',
(content, list_id, id))
conn.commit()
conn.close()
return redirect(url_for('index'))
return render_template('edit.html', todo=todo, lists=lists)
In this new view function, you use the id
argument to fetch the ID of the to-do item you want to edit, the ID of the list it belongs to, the value of the done
column, the content of the item, and the list title using a SQL JOIN
. You save this data in the todo
variable. Then you get all of the to-do lists from the database and save them in the lists
variable.
If the request is a normal GET request, the condition if request.method == 'POST'
does not run, so the application executes the last render_template()
function, passing both todo
and lists
to an edit.html
file.
If however, a form was submitted, the condition request.method == 'POST'
becomes true
, in which case you extract the content and the list title the user submitted. If no content was submitted, you flash the message Content is required!
and redirect to the same edit page. Otherwise, you fetch the ID of the list the user submitted; this allows the user to move a to-do item from one list to another. Then, you use an UPDATE
SQL statement to set the content of the to-do item to the new content the user submitted. You do the same for the list ID. Finally, you commit the changes and close the connection, and redirect the user to the index page.
Save and close the file.
To use this new route, you need a new template file called edit.html
:
- nano templates/edit.html
Add the following contents to this new file:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Edit an Item {% endblock %}</h1>
<form method="post">
<div class="form-group">
<label for="content">Content</label>
<input type="text" name="content"
placeholder="Todo content" class="form-control"
value="{{ todo['content'] or request.form['content'] }}"></input>
</div>
<div class="form-group">
<label for="list">List</label>
<select class="form-control" name="list">
{% for list in lists %}
{% if list['title'] == request.form['list'] %}
<option value="{{ request.form['list'] }}" selected>
{{ request.form['list'] }}
</option>
{% elif list['title'] == todo['title'] %}
<option value="{{ todo['title'] }}" selected>
{{ todo['title'] }}
</option>
{% else %}
<option value="{{ list['title'] }}">
{{ list['title'] }}
</option>
{% endif %}
{% endfor %}
</select>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
{% endblock %}
You use the value {{ todo['content'] or request.form['content'] }}
for the content input. This signifies that the value will be either the current content of the to-do item or what the user has submitted in a failed attempt to submit the form.
For the list selection form, you loop through the lists
variable, and if the list title is the same as the one stored in the request.form
object (from a failed attempt), then set that list title as the selected value. Otherwise if the list title equals the one stored in the todo
variable, then set it as the selected value. This is the current list title of the to-do item before any modification; the rest of the options are then displayed without the selected
attribute.
Save and close the file.
Then, open index.html
to add an Edit
button:
- nano templates/index.html
Change the contents of the div
tag with the "row"
class to add another column as follows:
. . .
<div class="row">
<div class="col-12 col-md-3">
<form action="{{ url_for(URL, id=item['id']) }}"
method="POST">
<input type="submit" value="{{ BUTTON }}"
class="btn btn-success btn-sm">
</form>
</div>
<div class="col-12 col-md-3">
<a class="btn btn-warning btn-sm"
href="{{ url_for('edit', id=item['id']) }}">Edit</a>
</div>
</div>
Save and close the file.
This is a standard <a>
link tag that points to the relevant /edit/
route for each item.
Run the server if you haven’t already:
- flask run
You can now go to the index page http://127.0.0.1:5000/
and experiment with modifying to-do items. In the next step, you will add a button to delete items.
In this step, you will add the ability to delete specific to-do items.
You will first need to add a new /delete/
route, open app.py
:
- nano app.py
Then add the following route at the end of the file:
. . .
@app.route('/<int:id>/delete/', methods=('POST',))
def delete(id):
conn = get_db_connection()
conn.execute('DELETE FROM items WHERE id = ?', (id,))
conn.commit()
conn.close()
return redirect(url_for('index'))
Save and close the file.
The delete()
view function accepts an id
argument. When a POST
request gets sent, you use the DELETE
SQL statement to delete the item with the matching id
value, then you commit the transaction and close the database connection, and return to the index page.
Next, open templates/index.html
to add a Delete
button:
- nano templates/index.html
Add the following highlighted div
tag below the Edit
button:
<div class="row">
<div class="col-12 col-md-3">
<form action="{{ url_for(URL, id=item['id']) }}"
method="POST">
<input type="submit" value="{{ BUTTON }}"
class="btn btn-success btn-sm">
</form>
</div>
<div class="col-12 col-md-3">
<a class="btn btn-warning btn-sm"
href="{{ url_for('edit', id=item['id']) }}">Edit</a>
</div>
<div class="col-12 col-md-3">
<form action="{{ url_for('delete', id=item['id']) }}"
method="POST">
<input type="submit" value="Delete"
class="btn btn-danger btn-sm">
</form>
</div>
</div>
This new submit button sends a POST request to the /delete/
route for each item.
Save and close the file.
Then run the development server:
- flask run
Go to the index page and try out the new Delete
button—you can now delete any item you want.
Now that you have added the ability to delete existing to-do items, you will move on to add the ability to add new lists in the next step.
So far, lists can only be added directly from the database. In this step, you will add the ability to create new lists when the user adds a new item, instead of only choosing between the existing lists. You will incorporate a new option called New List
, which when chosen, the user can input the name of the new list they wish to create.
First, open app.py
:
- nano app.py
Then, modify the create()
view function by adding the following highlighted lines to the if request.method == 'POST'
condition:
. . .
@app.route('/create/', methods=('GET', 'POST'))
def create():
conn = get_db_connection()
if request.method == 'POST':
content = request.form['content']
list_title = request.form['list']
new_list = request.form['new_list']
# If a new list title is submitted, add it to the database
if list_title == 'New List' and new_list:
conn.execute('INSERT INTO lists (title) VALUES (?)',
(new_list,))
conn.commit()
# Update list_title to refer to the newly added list
list_title = new_list
if not content:
flash('Content is required!')
return redirect(url_for('index'))
list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
(list_title,)).fetchone()['id']
conn.execute('INSERT INTO items (content, list_id) VALUES (?, ?)',
(content, list_id))
conn.commit()
conn.close()
return redirect(url_for('index'))
lists = conn.execute('SELECT title FROM lists;').fetchall()
conn.close()
return render_template('create.html', lists=lists)
Save and close the file.
Here you save the value of a new form field called new_list
in a variable. You will add this field later to the create.html
file. Next, in the list_title == 'New List' and new_list
condition, you check whether the list_title
has the value 'New List'
, which indicates that the user wishes to create a new list. You also check that the value of the new_list
variable is not None
, if this condition is met, you use an INSERT INTO
SQL statement to add the newly submitted list title to the lists
table. You commit the transaction, then you update the value of the list_title
variable to match that of the newly added list for later use.
Next, open create.html
to add a new <option>
tag to let the user add a new list:
- nano templates/create.html
Modify the file by adding the highlighted tags in the following code:
<div class="form-group">
<label for="list">List</label>
<select class="form-control" name="list">
<option value="New List" selected>New List</option>
{% for list in lists %}
{% if list['title'] == request.form['list'] %}
<option value="{{ request.form['list'] }}" selected>
{{ request.form['list'] }}
</option>
{% else %}
<option value="{{ list['title'] }}">
{{ list['title'] }}
</option>
{% endif %}
{% endfor %}
</select>
</div>
<div class="form-group">
<label for="new_list">New List</label>
<input type="text" name="new_list"
placeholder="New list name" class="form-control"
value="{{ request.form['new_list'] }}"></input>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
Save and close the file.
You have added a new <option>
tag to refer to the New List
option, this will allow the user to specify that they want to create a new list. Then you add another <div>
with an input field named new_list
, this field is where the user will input the title of the new list they wish to create.
Finally, run the development server:
- flask run
Then visit the index page:
http://127.0.0.1:5000/
The application will now look as follows:
With the new additions to your application, users can now mark to-do items as complete or restore completed items to a non-completion state, edit and delete existing items, and create new lists for different kinds of to-do tasks.
You can browse the full source code of the application in the DigitalOcean Community Repository.
You now have a complete to-do application in which users can create new to-do items, mark an item as complete, and edit or delete existing items, in addition to the ability to create new lists. You have modified a Flask web application, added new features to it, and modified database items specifically in a One-to-Many relationship. You may develop this application further by learning How To Add Authentication to Your App with Flask-Login to add security to your Flask application.
]]>Flask is a framework for building web applications using the Python language, and SQLite is a database engine that can be used with Python to store application data. In this tutorial, you will use Flask with SQLite to create a to-do application where users can create lists of to-do items. You will learn how to use SQLite with Flask and how one-to-many database relationships work.
A one-to-many database relationship is a relationship between two database tables where a record in one table can reference several records in another table. For example, in a blogging application, a table for storing posts can have a one-to-many relationship with a table for storing comments. Each post can reference many comments, and each comment references a single post; therefore, one post has a relationship with many comments. The post table is a parent table, while the comments table is a child table—a record in the parent table can reference many records in the child table. This is important to be able to have access to related data in each table.
We’ll use SQLite because it is portable and does not need any additional set up to work with Python. It is also great for prototyping an application before moving to a larger database such as MySQL or Postgres. For more on how to choose the right database system read our SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems article.
Before you start following this guide, you will need:
flask_todo
.In this step, you will activate your programming environment, install Flask, create the SQLite database, and populate it with sample data. You’ll learn how to use foreign keys to create a one-to-many relationship between lists and items. A foreign key is a key used to associate a database table with another table, it is the link between the child table and its parent table.
If you haven’t already activated your programming environment, make sure you’re in your project directory (flask_todo
) and use this command to activate it:
- source env/bin/activate
Once your programming environment is activated, install Flask using the following command:
- pip install flask
Once the installation is complete, you can now create the database schema file that contains SQL commands to create the tables you need to store your to-do data. You will need two tables: a table called lists
to store to-do lists, and an items
table to store the items of each list.
Open a file called schema.sql
inside your flask_todo
directory:
- nano schema.sql
Type the following SQL commands inside this file:
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;
CREATE TABLE lists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
title TEXT NOT NULL
);
CREATE TABLE items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
list_id INTEGER NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
content TEXT NOT NULL,
FOREIGN KEY (list_id) REFERENCES lists (id)
);
Save and close the file.
The first two SQL command are DROP TABLE IF EXISTS lists;
and DROP TABLE IF EXISTS items;
, these delete any already existing tables named lists
and items
so you don’t see confusing behavior. Note that this will delete all of the content you have in the database whenever you use these SQL commands, so ensure you don’t write any important content in the web application until you finish this tutorial and experiment with the final result.
Next, you use CREATE TABLE lists
to create the lists
table that will store the to-do lists (such as a study list, work list, home list, and so on) with the following columns:
id
: An integer that represents a primary key, this will get assigned a unique value by the database for each entry (i.e. to-do list).created
: The time the to-do list was created at. NOT NULL
signifies that this column should not be empty and the DEFAULT
value is the CURRENT_TIMESTAMP
value, which is the time at which the list was added to the database. Just like id
, you don’t need to specify a value for this column, as it will be automatically filled in.title
: The list title.Then, you create a table called items
to store to-do items. This table has an ID, a list_id
integer column to identify which list an item belongs to, a creation date, and the item’s content. To link an item to a list in the database you use a foreign key constraint with the line FOREIGN KEY (list_id) REFERENCES lists (id)
. Here the lists
table is a parent table, which is the table that is being referenced by the foreign key constraint, this indicates a list can have multiple items. The items
table is a child table, which is the table the constraint applies to. This means items belong to a single list. The list_id
column references the id
column of the lists
parent table.
Since a list can have many items, and an item belongs to only one list, the relationship between the lists
and items
tables is a one-to-many relationship.
Next, you will use the schema.sql
file to create the database. Open a file named init_db.py
inside the flask_todo
directory:
- nano init_db.py
Then add the following code:
import sqlite3
connection = sqlite3.connect('database.db')
with open('schema.sql') as f:
connection.executescript(f.read())
cur = connection.cursor()
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Work',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Home',))
cur.execute("INSERT INTO lists (title) VALUES (?)", ('Study',))
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(1, 'Morning meeting')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Buy fruit')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(2, 'Cook dinner')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn Flask')
)
cur.execute("INSERT INTO items (list_id, content) VALUES (?, ?)",
(3, 'Learn SQLite')
)
connection.commit()
connection.close()
Save and close the file.
Here you connect to a file called database.db
that will be created once you execute this program. You then open the schema.sql
file and run it using the executescript()
method that executes multiple SQL statements at once.
Running schema.sql
will create the lists
and items
tables. Next, using a Cursor object, you execute a few INSERT
SQL statements to create three lists and five to-do items.
You use the list_id
column to link each item to a list via the list’s id
value. For example, the Work
list was the first insertion into the database, so it will have the ID 1
. This is how you can link the Morning meeting
to-do item to Work
—the same rule applies to the other lists and items.
Finally, you commit the changes and close the connection.
Run the program:
- python init_db.py
After execution, a new file called database.db
will appear in your flask_todo
directory.
You’ve activated your environment, installed Flask, and created the SQLite database. Next, you’ll retrieve the lists and items from the database and display them in the application’s homepage.
In this step, you will connect the database you created in the previous step to a Flask application that displays the to-do lists and the items of each list. You will learn how to use SQLite joins to query data from two tables and how to group to-do items by their lists.
First, you will create the application file. Open a file named app.py
inside the flask_todo
directory:
- nano app.py
And then add the following code to the file:
from itertools import groupby
import sqlite3
from flask import Flask, render_template, request, flash, redirect, url_for
def get_db_connection():
conn = sqlite3.connect('database.db')
conn.row_factory = sqlite3.Row
return conn
app = Flask(__name__)
app.config['SECRET_KEY'] = 'this should be a secret random string'
@app.route('/')
def index():
conn = get_db_connection()
todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)
conn.close()
return render_template('index.html', lists=lists)
Save and close the file.
The get_db_connection()
function opens a connection to the database.db
database file and then sets the row_factory
attribute to sqlite3.Row
. In this way you can have name-based access to columns; this means that the database connection will return rows that behave like regular Python dictionaries. Lastly, the function returns the conn
connection object you’ll be using to access the database.
In the index()
view function, you open a database connection and execute the following SQL query:
SELECT i.content, l.title FROM items i JOIN lists l ON i.list_id = l.id ORDER BY l.title;
You then retrieve its results by using the fetchall()
method and save the data in a variable called todos
.
In this query, you use SELECT
to get the content of the item and the title of the list it belongs to by joining both the items
and lists
tables (with the table aliases i
for items
and l
for lists
). With the join condition i.list_id = l.id
after the ON
keyword, you will get each row from the items
table with every row from the lists
table where the list_id
column of the items
table matches the id
of the lists
table. You then use ORDER BY
to order the results by list titles.
To understand this query better, open the Python REPL in your flask_todo
directory:
- python
To understand the SQL query, examine the contents of the todos
variable by running this small program:
- from app import get_db_connection
- conn = get_db_connection()
- todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
- ON i.list_id = l.id ORDER BY l.title;').fetchall()
- for todo in todos:
- print(todo['title'], ':', todo['content'])
You first import the get_db_connection
from the app.py
file then open a connection and execute the query (note that this is the same SQL query you have in your app.py
file). In the for
loop you print the title of the list and the content of each to-do item.
The output will be as follows:
OutputHome : Buy fruit
Home : Cook dinner
Study : Learn Flask
Study : Learn SQLite
Work : Morning meeting
Close the REPL using CTRL + D
.
Now that you understand how SQL joins work and what the query achieves, let’s return back to the index()
view function in your app.py
file. After declaring the todos
variable, you group the results using the following code:
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)
You first declare an empty dictionary called lists
, then use a for
loop to go through a grouping of the results in the todos
variable by the list’s title. You use the groupby()
function you imported from the itertools
standard library. This function will go through each item in the todos
variable and generate a group of results for each key in the for
loop.
k
represents list titles (that is, Home
, Study
, Work
), which are extracted using the function you pass to the key
parameter of the groupby()
function. In this case the function is lambda t: t['title']
that takes a to-do item and returns the title of the list (as you have done before with todo['title']
in the previous for loop). g
represents the group that contains the to-do items of each list title. For example, in the first iteration, k
will be 'Home'
, while g
is an iterable that will contain the items 'Buy fruit'
and 'Cook dinner'
.
This gives us a representation of the one-to-many relationship between lists and items, where each list title has several to-do items.
When running the app.py
file, and after the for
loop finishes execution, lists
will be as follows:
Output{'Home': [<sqlite3.Row object at 0x7f9f58460950>,
<sqlite3.Row object at 0x7f9f58460c30>],
'Study': [<sqlite3.Row object at 0x7f9f58460b70>,
<sqlite3.Row object at 0x7f9f58460b50>],
'Work': [<sqlite3.Row object at 0x7f9f58460890>]}
Each sqlite3.Row
object will contain the data you retrieved from the items
table using the SQL query in the index()
function. To represent this data better, let’s make a program that goes through the lists
dictionary and displays each list and its items.
Open a file called list_example.py
in your flask_todo
directory:
- nano list_example.py
Then add the following code:
from itertools import groupby
from app import get_db_connection
conn = get_db_connection()
todos = conn.execute('SELECT i.content, l.title FROM items i JOIN lists l \
ON i.list_id = l.id ORDER BY l.title;').fetchall()
lists = {}
for k, g in groupby(todos, key=lambda t: t['title']):
lists[k] = list(g)
for list_, items in lists.items():
print(list_)
for item in items:
print(' ', item['content'])
Save and close the file.
This is very similar to the content in your index()
view function. The last for
loop here illustrates how the lists
dictionary is structured. You first go through the dictionary’s items, print the list title (which is in the list_
variable), then go through each group of to-do items that belong to the list and print the content value of the item.
Run the list_example.py
program:
- python list_example.py
Here is the output of list_example.py
:
OutputHome
Buy fruit
Cook dinner
Study
Learn Flask
Learn SQLite
Work
Morning meeting
Now that you understand each part of the index()
function, let’s create a base template and create the index.html
file you rendered using the line return render_template('index.html', lists=lists)
.
In your flask_todo
directory, create a templates
directory and open a file called base.html
inside it:
- mkdir templates
- nano templates/base.html
Add the following code inside base.html
, note that you’re using Bootstrap here. If you are not familiar with HTML templates in Flask, see Step 3 of How To Make a Web Application Using Flask in Python 3:
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<title>{% block title %} {% endblock %}</title>
</head>
<body>
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item active">
<a class="nav-link" href="#">About</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% block content %} {% endblock %}
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>
</html>
Save and close the file.
Most of the code in the preceding block is standard HTML and code required for Bootstrap. The <meta>
tags provide information for the web browser, the <link>
tag links the Bootstrap CSS files, and the <script>
tags are links to JavaScript code that allows some additional Bootstrap features. Check out the Bootstrap documentation for more information.
Next, create the index.html
file that will extend this base.html
file:
- nano templates/index.html
Add the following code to index.html
:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Welcome to FlaskTodo {% endblock %}</h1>
{% for list, items in lists.items() %}
<div class="card" style="width: 18rem; margin-bottom: 50px;">
<div class="card-header">
<h3>{{ list }}</h3>
</div>
<ul class="list-group list-group-flush">
{% for item in items %}
<li class="list-group-item">{{ item['content'] }}</li>
{% endfor %}
</ul>
</div>
{% endfor %}
{% endblock %}
Here you use a for
loop to go through each item of the lists
dictionary, you display the list title as a card header inside an <h3>
tag, and then use a list group to display each to-do item that belongs to the list in an <li>
tag. This follows the same rules explained in the list_example.py
program.
You will now set the environment variables Flask needs and run the application using the following commands:
- export FLASK_APP=app
- export FLASK_ENV=development
- flask run
Once the development server is running, you can visit the URL http://127.0.0.1:5000/
in your browser. You will see a web page with the “Welcome to FlaskTodo” and your list items.
You can now type CTRL + C
to stop your development server.
You’ve created a Flask application that displays the to-do lists and the items of each list. In the next step, you will add a new page for creating new to-do items.
In this step, you will make a new route for creating to-do items, you will insert data into database tables, and associate items with the lists they belong to.
First, open the app.py
file:
- nano app.py
Then, add a new /create
route with a view function called create()
at the end of the file:
...
@app.route('/create/', methods=('GET', 'POST'))
def create():
conn = get_db_connection()
lists = conn.execute('SELECT title FROM lists;').fetchall()
conn.close()
return render_template('create.html', lists=lists)
Save and close the file.
Because you will use this route to insert new data to the database via a web form, you allow both GET and POST requests using methods=('GET', 'POST')
in the app.route()
decorator. In the create()
view function, you open a database connection then get all the list titles available in the database, close the connection, and render a create.html
template passing it the list titles.
Next, open a new template file called create.html
:
- nano templates/create.html
Add the following HTML code to create.html
:
{% extends 'base.html' %}
{% block content %}
<h1>{% block title %} Create a New Item {% endblock %}</h1>
<form method="post">
<div class="form-group">
<label for="content">Content</label>
<input type="text" name="content"
placeholder="Todo content" class="form-control"
value="{{ request.form['content'] }}"></input>
</div>
<div class="form-group">
<label for="list">List</label>
<select class="form-control" name="list">
{% for list in lists %}
{% if list['title'] == request.form['list'] %}
<option value="{{ request.form['list'] }}" selected>
{{ request.form['list'] }}
</option>
{% else %}
<option value="{{ list['title'] }}">
{{ list['title'] }}
</option>
{% endif %}
{% endfor %}
</select>
</div>
<div class="form-group">
<button type="submit" class="btn btn-primary">Submit</button>
</div>
</form>
{% endblock %}
Save and close the file.
You use request.form
to access the form data that is stored in case something goes wrong with your form submission (for example, if no to-do content was provided). In the <select>
element, you loop through the lists you retrieved from the database in the create()
function. If the list title is equal to what is stored in request.form
then the selected option is that list title, otherwise, you display the list title in a normal non-selected <option>
tag.
Now, in the terminal, run your Flask application:
- flask run
Then visit http://127.0.0.1:5000/create
in your browser, you will see a form for creating a new to-do item, note that the form doesn’t work yet because you have no code to handle POST requests that get sent by the browser when submitting the form.
Type CTRL + C
to stop your development server.
Next, let’s add the code for handling POST requests to the create()
function and make the form function properly, open app.py
:
- nano app.py
Then edit the create()
function to look like so:
...
@app.route('/create/', methods=('GET', 'POST'))
def create():
conn = get_db_connection()
if request.method == 'POST':
content = request.form['content']
list_title = request.form['list']
if not content:
flash('Content is required!')
return redirect(url_for('index'))
list_id = conn.execute('SELECT id FROM lists WHERE title = (?);',
(list_title,)).fetchone()['id']
conn.execute('INSERT INTO items (content, list_id) VALUES (?, ?)',
(content, list_id))
conn.commit()
conn.close()
return redirect(url_for('index'))
lists = conn.execute('SELECT title FROM lists;').fetchall()
conn.close()
return render_template('create.html', lists=lists)
Save and close the file.
Inside the request.method == 'POST'
condition you get the to-do item’s content and the list’s title from the form data. If no content was submitted, you send the user a message using the flash()
function and redirect to the index page. If this condition was not triggered, then you execute a SELECT
statement to get the list ID from the provided list title and save it in a variable called list_id
. You then execute an INSERT INTO
statement to insert the new to-do item into the items
table. You use the list_id
variable to link the item to the list it belongs to. Finally, you commit the transaction, close the connection, and redirect to the index page.
As a last step, you will add a link to /create
in the navigation bar and display flashed messages below it, to do this, open base.html
:
- nano templates/base.html
Edit the file by adding a new <li>
navigation item that links to the create()
view function. Then display the flashed messages using a for
loop above the content
block. These are available in the get_flashed_messages()
Flask function:
<nav class="navbar navbar-expand-md navbar-light bg-light">
<a class="navbar-brand" href="{{ url_for('index')}}">FlaskTodo</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarNav">
<ul class="navbar-nav">
<li class="nav-item active">
<a class="nav-link" href="{{ url_for('create') }}">New</a>
</li>
<li class="nav-item active">
<a class="nav-link" href="#">About</a>
</li>
</ul>
</div>
</nav>
<div class="container">
{% for message in get_flashed_messages() %}
<div class="alert alert-danger">{{ message }}</div>
{% endfor %}
{% block content %} {% endblock %}
</div>
Save and close the file.
Now, in the terminal, run your Flask application:
- flask run
A new link to /create
will appear in the navigation bar. If you navigate to this page and try to add a new to-do item with no content, you’ll receive a flashed message saying Content is required!. If you fill in the content form, a new to-do item will appear on the index page.
In this step, you have added the ability to create new to-do items and save them to the database.
You can find the source code for this project in this repository.
You now have an application to manage to-do lists and items. Each list has several to-do items and each to-do item belongs to a single list in a one-to-many relationship. You learned how to use Flask and SQLite to manage multiple related database tables, how to use foreign keys and how to retrieve and display related data from two tables in a web application using SQLite joins.
Furthermore, you grouped results using the groupby()
function, inserted new data to the database, and associated database table rows with the tables they are related to. You can learn more about foreign keys and database relationships from the SQLite documentation.
You can also read more of our Python Framework content. If you want to check out the sqlite3
Python module, read our tutorial on How To Use the sqlite3 Module in Python 3.
SQLite is a self-contained, file-based SQL database. SQLite comes bundled with Python and can be used in any of your Python applications without having to install any additional software.
In this tutorial, we’ll go through the sqlite3
module in Python 3. We’ll create a connection to a SQLite database, add a table to that database, insert data into that table, and read and modify data in that table.
For this tutorial, we’ll be working primarily with an inventory of fish that we need to modify as fish are added to or removed from a fictional aquarium.
To get the most out of this tutorial, it is recommended to have some familiarity with programming in Python and some basic background with SQL.
You can review these tutorials for the necessary background information:
When we connect to a SQLite database, we are accessing data that ultimately resides in a file on our computer. SQLite databases are fully featured SQL engines that can be used for many purposes. For now, we’ll consider a database that tracks the inventory of fish at a fictional aquarium.
We can connect to a SQLite database using the Python sqlite3
module:
import sqlite3
connection = sqlite3.connect("aquarium.db")
import sqlite3
gives our Python program access to the sqlite3
module. The sqlite3.connect()
function returns a Connection
object that we will use to interact with the SQLite database held in the file aquarium.db
. The aquarium.db
file is created automatically by sqlite3.connect()
if aquarium.db
does not already exist on our computer.
We can verify we successfully created our connection
object by running:
print(connection.total_changes)
If we run this Python code, we will see output like:
Output0
connection.total_changes
is the total number of database rows that have been changed by connection
. Since we have not executed any SQL commands yet, 0 total_changes
is correct.
If, at any time, we find we want to start this tutorial again, we can delete the aquarium.db
file from our computer.
Note: It is also possible to connect to a SQLite database that resides strictly in memory (and not in a file) by passing the special string ":memory:"
into sqlite3.connect()
. For example, sqlite3.connect(":memory:")
. A ":memory:"
SQLite database will disappear as soon as your Python program exits. This might be convenient if you want a temporary sandbox to try something out in SQLite, and don’t need to persist any data after your program exits.
Now that we have connected to the aquarium.db
SQLite database, we can start inserting and reading data from it.
In a SQL database, data is stored in tables. Tables define a set of columns, and contain 0 or more rows with data for each of the defined columns.
We will create a table named fish
that tracks the following data:
name | species | tank_number |
---|---|---|
Sammy | shark | 1 |
Jamie | cuttlefish | 7 |
The fish
table will track a value for name
, species
, and tank_number
for each fish at the aquarium. Two example fish
rows are listed: one row for a shark
named Sammy
, and one row for a cuttlefish
named Jamie
.
We can create this fish
table in SQLite using the connection
we made in Step 1:
cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")
connection.cursor()
returns a Cursor
object. Cursor
objects allow us to send SQL statements to a SQLite database using cursor.execute()
. The "CREATE TABLE fish ..."
string is a SQL statement that creates a table named fish
with the three columns described earlier: name
of type TEXT
, species of type TEXT
, and tank_number
of type INTEGER
.
Now that we have created a table, we can insert rows of data into it:
cursor.execute("INSERT INTO fish VALUES ('Sammy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")
We call cursor.execute()
two times: once to insert a row for the shark Sammy
in tank 1
, and once to insert a row for the cuttlefish Jamie
in tank 7
. "INSERT INTO fish VALUES ..."
is a SQL statement that allows us to add rows to a table.
In the next section, we will use a SQL SELECT
statement to inspect the rows we just inserted into our fish
table.
In Step 2, we added two rows to a SQLite table named fish
. We can retrieve those rows using a SELECT
SQL statement:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this code, we will see output like the following:
Output[('Sammy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]
The cursor.execute()
function runs a SELECT
statement to retrieve values for the name
, species
, and tank_number
columns in the fish
table. fetchall()
retrieves all the results of the SELECT
statement. When we print(rows)
we see a list of two tuples. Each tuple has three entries; one entry for each column we selected from the fish
table. The two tuples have the data we inserted in Step 2: one tuple for Sammy
the shark
, and one tuple for Jamie
the cuttlefish
.
If we wanted to retrieve rows in the fish
table that match a specific set of criteria, we can use a WHERE
clause:
target_fish_name = "Jamie"
rows = cursor.execute(
"SELECT name, species, tank_number FROM fish WHERE name = ?",
(target_fish_name,),
).fetchall()
print(rows)
If we run this, we will see output like the following:
Output[('Jamie', 'cuttlefish', 7)]
As with the previous example, cursor.execute(<SQL statement>).fetchall()
allows us to fetch all the results of a SELECT
statement. The WHERE
clause in the SELECT
statement filters for rows where the value of name
is target_fish_name
. Notice that we use ?
to substitute our target_fish_name
variable into the SELECT
statement. We expect to only match one row, and indeed we only see the row for Jamie
the cuttlefish
returned.
Warning: Never use Python string operations to dynamically create a SQL statement string. Using Python string operations to assemble a SQL statement string leaves you vulnerable to SQL injection attacks. SQL injection attacks can be used to steal, alter, or otherwise modify data stored in your database. Always use the ?
placeholder in your SQL statements to dynamically substitute values from your Python program. Pass a tuple of values as the second argument to Cursor.execute()
to bind your values to the SQL statement. This substitution pattern is demonstrated here and in other parts of this tutorial as well.
Rows in a SQLite database can be modified using UPDATE
and DELETE
SQL statements.
Let’s say, for example, that Sammy the shark was moved to tank number 2. We can change Sammy’s row in the fish
table to reflect this change:
new_tank_number = 2
moved_fish_name = "Sammy"
cursor.execute(
"UPDATE fish SET tank_number = ? WHERE name = ?",
(new_tank_number, moved_fish_name)
)
We issue an UPDATE
SQL statement to change the tank_number
of Sammy
to its new value of 2
. The WHERE
clause in the UPDATE
statement ensures we only change the value of tank_number
if a row has name = "Sammy"
.
If we run the following SELECT
statement, we can confirm our update was made correctly:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this, we will see output like the following:
Output[('Sammy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]
Notice that the row for Sammy
now has the value of 2
for its tank_number
column.
Let’s say that Sammy the shark was released into the wild and no longer held by the aquarium. Since Sammy no longer lives at the aquarium, it would make sense to remove the Sammy
row from the fish
table.
Issue a DELETE
SQL statement to remove a row:
released_fish_name = "Sammy"
cursor.execute(
"DELETE FROM fish WHERE name = ?",
(released_fish_name,)
)
We issue a DELETE
SQL statement to remove the row for Sammy
the shark
. The WHERE
clause in the DELETE
statement ensures we only delete a row if that row has name = "Sammy"
.
If we run the following SELECT
statement, we can confirm our deletion was made correctly:
rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)
If we run this code, we will see output like the following:
Output[('Jamie', 'cuttlefish', 7)]
Notice that the row for Sammy
the shark
is now gone, and only Jamie
the cuttlefish
remains.
with
Statements For Automatic CleanupIn this tutorial, we’ve used two primary objects to interact with the "aquarium.db"
SQLite database: a Connection
object named connection
, and a Cursor
object named cursor
.
In the same way that Python files should be closed when we are done working with them, Connection
and Cursor
objects should also be closed when they are no longer needed.
We can use a with
statement to help us automatically close Connection
and Cursor
objects:
from contextlib import closing
with closing(sqlite3.connect("aquarium.db")) as connection:
with closing(connection.cursor()) as cursor:
rows = cursor.execute("SELECT 1").fetchall()
print(rows)
closing
is a convenience function provided by the contextlib
module. When a with
statement exits, closing
ensures that close()
is called on whatever object is passed to it. The closing
function is used twice in this example. Once to ensure that the Connection
object returned by sqlite3.connect()
is automatically closed, and a second time to ensure that the Cursor
object returned by connection.cursor()
is automatically closed.
If we run this code, we will see output like the following:
Output[(1,)]
Since "SELECT 1"
is a SQL statement that always returns a single row with a single column with a value of 1
, it makes sense to see a single tuple with 1
as its only value returned by our code.
The sqlite3
module is a powerful part of the Python standard library; it lets us work with a fully featured on-disk SQL database without installing any additional software.
In this tutorial, we learned how to use the sqlite3
module to connect to a SQLite database, add data to that database, as well as read and modify data in that database. Along the way, we also learned about the risks of SQL injection attacks and how to use contextlib.closing
to automatically call close()
on Python objects in with
statements.
From here we can learn more about SQL databases in SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems.
]]>