For the most common web applications, it is generally recommended that you use a Flask extension such as flask-sqlalchemy. To install the package for working with SQLAlchemy from Flask, just execute the following command:
pip install flask-sqlalchemy
Once we have our Flask application created, to integrate it with sqlalchemy, we would have to create a configuration file with the database path, from which the SQLAlchemy object is created to manage the database.
In this example, we will use a SQLite database to simplify the configuration without having a database server.
We can add the database configuration in the config.py file inside the flask_sqlalchemy folder:
#!/usr/local/bin/python3
import os
DEBUG = True
SQLALCHEMY_DATABASE_URI = 'sqlite:///'+ os.path.join(os.path.dirname(__file__), 'books_database.db')
SECRET_KEY = 'SECRET_KEY'
SQLALCHEMY_DATABASE_URI is required by the Flask-SQLAlchemy extension and represents the local address to our database file. We also need to define the SECRET_KEY for working with Flask-forms.
You can find the following code in the books.py file inside the flask_sqlalchemy folder on the GitHub repository at https://github.com/PacktPublishing/Learning-Python-Networking-Second-Edition:
#!/usr/local/bin/python3
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
import json
from flask_wtf import FlaskForm
from wtforms import StringField
from wtforms import TextAreaField
from wtforms.validators import DataRequired
from datetime import date
# Flask application and config
app = Flask(__name__)
app.config.from_object('config')
db = SQLAlchemy(app)
...
In the previous code block, we defined the Flask application and configuration from the config.py file. The Book class is our model that represents a book entity, while the CreateBookForm class represents our form object. In the following code block, we define our methods for threat application requests.The index method will show the index.html from the templates folder, and the new_book method will receive book information with the POST method using the request.form syntax. To save book information in the database, we will use the session.add() and session.commit() methods from the db object:
@app.route('/new_book', methods=['POST'])
def new_book():
form = CreateBookForm()
if request.method == 'POST':
post = Book(request.form['title'], request.form['author'], request.form['description'])
db.session.add(post)
db.session.commit()
# validate the received values
if request.form['title'] and request.form['author']:
return json.dumps({'html':'<span>New book saved in database</span>'})
return render_template('index.html',form = form,conf = app.config)
@app.route('/', methods=['GET'])
def index():
form = CreateBookForm()
return render_template('index.html',form = form,conf = app.config)
if __name__ == '__main__':
app.run()
db.create_all()
Finally, the following is the content of index.html. It contains the form for sending book information:
<html>
<body>
<form method="post" action="/new_book">
<dl>
{{ form.csrf_token }}
{{ form.title.label }} {{ form.title(style="width:100%") }}
{% for error in form.title.errors %} {{ error }} {% endfor %}
<br />
{{ form.author.label }} {{ form.author(style="width:100%") }}
{% for error in form.author.errors %} {{ error }} {% endfor %}
<br />
{{ form.description.label }} {{form.description(style="height:100px;width:100%") }}
{% for error in form.description.errors %} {{ error }} {% endfor %}
</dl>
<p><input type="submit" value="submit">
</form>
</body>
</html>
In the previous form object, we also added a CSRF token with the {{ form.csrf_token }} instruction to avoid some security attacks like cross-site scripting and cross-site request forgery.
In the following screenshot, we can see the HTML form for saving book information in the database:
When you submit the form, you will get a message indicating that the book has been saved in the SQLite database:
In this section, we have worked with Flask and SQLAlchemy to persist data in an SQLite database. We used the flask-sqlalchemy and flask_wtf packages for working with forms in an easy way.