A quick run through for web development using the python-flask framework and the SQLite database
Contents
- SQLite Overview
- SQLAlchemy Overview
- Models
- Querying
- Flask Overview
- Flask Starter Code
- Routing
- URL Building
- HTTP Methods
- Rendering Templates
- Accessing Requests
- Redirects
- APIs with JSON
- Bringing it all Together
- Initial Setup
- Practice Example
*Knowledge of Python and CRUD is necessary for better comprehension*
SQLite Overview
SQLite is a software library that provides a RDMS, relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resources.
SQLite has the following noticeable features: self-contained, serverless, zero-configuration, transactional.
Normally, an RDBMS such as MySQL, PostgreSQL, etc., requires a separate server process to operate. The applications that want to access the database server use TCP/IP protocol to send and receive requests. This is called client/server architecture.
The following diagram illustrates the RDBMS client/server architecture:
SQLite does NOT work this way. SQLite doesn't require a server to run.
SQLite database is integrated with the application that accesses the database. The applications interact with the SQLite database read and write directly from the database files stored on disk.
The following diagram illustrates the SQLite server-less architecture:
SQLite is self-contained meaning it requires minimal support from the operating system or external library. This makes SQLite usable in any environment especially in embedded devices like iPhones, Android phones, game consoles, handheld media players, etc.
Because of the serverless architecture, you don’t need to “install” SQLite before using it. There is no server process that needs to be configured, started, and stopped
SQL Alchemy Overview
SQLAlchemy is the abstraction or interface layer between our web application and our database.
Just like most database abstraction layers, SQLAlchemy is an ORM, Object-Relational Mapper.
An ORM is a package or library that abstracts database calls into an object-oriented programming construct. In our case, that means that each database table becomes an object with a set of database methods attached to it (for SELECT, INSERT, DELETE, etc.).
The ORM translates Python classes to tables for relational databases and automatically converts Pythonic SQLAlchemy Expression Language to SQL statements. This conversion allows developers to write SQL queries with Python syntax.
SQLAlchemy also abstracts database connections and provides connection maintenance automatically. Together these features make SQLAlchemy a great package for loading and querying databases.
Models
Defining a Model
class contact(db.Model):
id = db.Column('id', db.Integer, primary_key=True)
time = db.Column('time', db.Integer, default=time.time())
user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'))
associated_user = db.relationship('user', lazy='select')
def __init__(self, full_name, user_id):
self.full_name = full_name
self.user_id = user_idQueries
Making Queries
# Print out user 1’s contact names
contactList = contact.query.filter(contact.user_id == 1)
for eachContact in contactList:
print(eachContact.full_name)
# Print the names, sorted by added date
contactList2 = contactList.order_by(contact.time)
for eachContact in contactList2:
print(eachContact.full_name)
# Change contactList from a db iterable into a real list
contactList = contactList.all()Inserting
Example of how to insert
# contact model: contact(full_name, user_id)
newContact = contact('John Doe', 1)
db.session.add(newContact)
db.session.commit()Updating
theContact = contact.query.filter(contact.id == 1).first()
if theContact is not None:
theContact.full_name = 'Jane Doe'
db.session.commit()
Deleting
theContact = contact.query.filter(contact.id == 1).first()
if theContact is not None:
db.session.delete(theContact)
db.session.commit()Flask Overview
Flask is a microframework that allows you to build web apps in Python. Flask is easy to get started with as a beginner because there is little boilerplate code for getting a simple app up and running.
For our main web application, we use the Python Flask web framework. Flask doesn’t need to use SQLAlchemy, but the two are closely linked so it's better to work with it.
Flask Starter Code:
from flask import Flask
app = Flask(__name__)
@app.route("/")
def hello_world():
return "<p>Hello, World!</p>"To run the application, use the flask command or python-mflask. You need to tell the Flask where your application is with the --app option.
Routing with Flask:
@app.route('/')
def index():
return 'Index Page'
@app.route('/hello')
def hello():
return 'Hello, World'URL Building:
To build a URL to a specific function, use the url_for() function. It accepts the name of the function as its first argument and any number of keyword arguments, each corresponding to a variable part of the URL rule. Unknown variable parts are appended to the URL as query parameters
Reversing is often more descriptive than hard-coding the URLs.
You can change your URLs in one go instead of needing to remember to manually change hard-coded URLs.
URL building handles escaping of special characters transparently.
The generated paths are always absolute, avoiding unexpected behavior of relative paths in browsers.
If your application is placed outside the URL root, for example, in /myapplication instead of /, url_for() properly handles that for you.
from flask import url_for
@app.route('/login')
def login():
return 'login'
@app.route('/user/<username>')
def profile(username):
return f'{username}\'s profile'
with app.test_request_context():
print(url_for('login'))
print(url_for('login', next='/'))
print(url_for('profile', username='John Doe'))
HTTP Methods:
Web applications use different HTTP methods when accessing URLs. By default, a route only answers to GET requests. You can use the methods argument of the route() decorator to handle different HTTP methods.
from flask import request
//Using the methods argument
@app.route('/login', methods=['GET', 'POST'])
def login():
if request.method == 'POST':
return do_the_login()
else:
return show_the_login_form()
//Shortcut using get(), post()
@app.get('/login')
def login_get():
return show_the_login_form()
@app.post('/login')
def login_post():
return do_the_login()
Rendering Templates:
To render a template you can use the render_template() method. All you have to do is provide the name of the template and the variables you want to pass to the template engine as keyword arguments. Here’s a simple example of how to render a template:
from flask import render_template
@app.route('/hello/')
@app.route('/hello/<name>')
def hello(name=None):
return render_template('hello.html', name=name)Accessing Requests:
The current request method is available by using the method attribute. To access form data (data transmitted in a POST or PUT request) you can use the form attribute. Here is a full example of the two attributes mentioned above:
@app.route('/login', methods=['POST', 'GET'])
def login():
error = None
if request.method == 'POST':
if valid_login(request.form['username'],
request.form['password']):
return log_the_user_in(request.form['username'])
else:
error = 'Invalid username/password'
# the code below is executed if the request method
# was GET or the credentials were invalid
return render_template('login.html', error=error)Redirects:
To redirect a user to another endpoint, use the redirect() function; to abort a request early with an error code, use the abort() function:
from flask import abort, redirect, url_for
#Redirect
@app.route('/')
def index():
return redirect(url_for('login'))
#Abort
@app.route('/login')
def login():
abort(401)
this_is_never_executed()APIs with JSON:
A common response format when writing an API is JSON. If you return a dict or list from a view, it will be converted to a JSON response.
@app.route("/me")
def me_api():
user = get_current_user()
return {
"username": user.username,
"theme": user.theme,
"image": url_for("user_image", filename=user.image),
}
@app.route("/users")
def users_api():
users = get_all_users()
return [user.to_json() for user in users]Bringing it All Together
Initial Setup
Install Flask-SQLAlchemy extension.
pip install flask-sqlalchemy
You need to import SQLAlchemy class from this module.
from flask_sqlalchemy import SQLAlchemyNow create a Flask application object and set URI for the database to be used.
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'Practice Example
Create an object of SQLAlchemy class with application object as the parameter. This object contains helper functions for ORM operations. It also provides a parent Model class using which user defined models are declared. In the snippet below, a students model is created.
db = SQLAlchemy(app)
class students(db.Model):
id = db.Column('student_id', db.Integer, primary_key = True)
name = db.Column(db.String(100))
city = db.Column(db.String(50))
address = db.Column(db.String(200))
def __init__(self, name, city, address):
self.name = name
self.city = city
self.address = addressTo create / use database mentioned in URI, run the create_all() method.
db.create_all()The Session object of SQLAlchemy manages all persistence operations of ORM object.
The following session methods perform CRUD operations −
db.session.add(model object) − inserts a record into mapped table
db.session.delete(model object) − deletes record from table
model.query.all() − retrieves all records from table (corresponding to SELECT query).
You can apply a filter to the retrieved record set by using the filter attribute. For instance, in order to retrieve records with city = ’Hyderabad’ in students table, use following statement −
Students.query.filter_by(city = 'Hyderabad').all()
With this much of background, now we shall provide view functions for our application to add a student data.
The entry point of the application is show_all() function bound to ‘/’ URL. The Record set of students table is sent as parameter to the HTML template. The server side code in the template renders the records in HTML table form.
@app.route('/')
def show_all():
return render_template('show_all.html', students = students.query.all())The HTML script of the template (‘show_all.html’) is like this −
<!DOCTYPE html><html lang = "en"><head></head><body><h3><a href = "{{ url_for('show_all') }}">Comments - Flask
SQLAlchemy example</a></h3><hr/>
{%- for message in get_flashed_messages() %}
{{ message }}
{%- endfor %}
<h3>Students (<a href = "{{ url_for('new') }}">Add Student
</a>)</h3><table><thead><tr><th>Name</th><th>City</th><th>Address</th><th>Pin</th></tr></thead><tbody>
{% for student in students %}
<tr><td>{{ student.name }}</td><td>{{ student.city }}</td><td>{{ student.addr }}</td><td>{{ student.pin }}</td></tr>
{% endfor %}
</tbody></table></body></html>The above page contains a hyperlink to ‘/new’ URL mapping new() function. When clicked, it opens a Student Information form. The data is posted to the same URL in POST method.
new.html
<!DOCTYPE html>
<html>
<body>
<h3>Students - Flask SQLAlchemy example</h3>
<hr/>
{%- for category, message in get_flashed_messages(with_categories = true) %}
<div class = "alert alert-danger">
{{ message }}
</div>
{%- endfor %}
<form action = "{{ request.path }}" method = "post">
<label for = "name">Name</label>
<br>
<input type = "text" name = "name" placeholder = "Name" />
<br>
<label for = "email">City</label>
<br>
<input type = "text" name = "city" placeholder = "city" />
<br>
<label for = "address">Address</label>
<br>
<textarea name = "address" placeholder = "address"></textarea>
<br>
<input type = "submit" value = "Submit" />
</form>
</body>
</html>When the http method is detected as POST, the form data is added in the students table and the application returns to homepage showing the added data.
@app.route('/new', methods = ['GET', 'POST'])
def new():
if request.method == 'POST':
if not request.form['name'] or not request.form['city'] or not request.form['addr']:
flash('Please enter all the fields', 'error')
else:
student = students(
request.form['name'],
request.form['city'],
request.form['address']
)
db.session.add(student)
db.session.commit()
flash('Record was successfully added')
return redirect(url_for('show_all'))
return render_template('new.html')Given below is the complete code of application app.py.
from flask import Flask, request, flash, url_for, redirect, render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///students.sqlite3'
app.config['SECRET_KEY'] = "random string"
db = SQLAlchemy(app)
class students(db.Model):
id = db.Column('student_id', db.Integer, primary_key = True)
name = db.Column(db.String(100))
city = db.Column(db.String(50))
address = db.Column(db.String(200))
def __init__(self, name, city, address):
self.name = name
self.city = city
self.address = address
@app.route('/new', methods = ['GET', 'POST'])
def new():
if request.method == 'POST':
if not request.form['name'] or not request.form['city'] or not request.form['addr']:
flash('Please enter all the fields', 'error')
else:
student = students(
request.form['name'],
request.form['city'],
request.form['address']
)
db.session.add(student)
db.session.commit()
flash('Record was successfully added')
return redirect(url_for('show_all'))
return render_template('new.html')
if __name__ == '__main__':
db.create_all()
app.run(debug = True)Run the script from Python shell and enter http://localhost:5000/ in the browser.
Comments