In this Flask Tutorial we are going to learn about Working with Flask SQLAlchemy, we are going to learn how you can use SQLAlchemy and how you can connect that with MySQL database.
What is SQLAlchemy ?
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.
What is Flask-SQLAlchemy ?
Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks. See the SQLAlchemy documentation to learn how to work with the ORM in depth.
Installation
You can simply install Flask-SQLAlchemy using pip command.
|
1 |
pip install Flask-SQLAlchemy |
Because we are using MySQL database, you need to install mysqlclient.
|
1 |
pip install mysqlclient |
So now after installation you need to create database, we are using Mysql database. for this you need to download and install Wamp Server. create a database in the Wamp Server, iam going to name it myflask for the database name, but you can give the name according to your choice. right now we don’t have any table in our database, we will create that using Flask SQLAlchemy.
You can add this code for SQLAlchemy configuration in your app.py file, you can see that we are using MySQL database, you need to give your database type, username, password and database name, in our case it is myflask
|
1 2 |
app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:''@localhost/myflask' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False |
Using this code you can create your model or we can say our database table.
|
1 2 3 4 5 6 7 8 |
class UserInfo(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) def __init__(self, username, password): self.username = username self.password = password |
This is the complete code for app.py file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
from flask import Flask, render_template, redirect,\ url_for, request, flash from form import LoginForm from flask_sqlalchemy import SQLAlchemy #create the object of Flask app = Flask(__name__) app.config['SECRET_KEY'] = 'hardsecretkey' #SqlAlchemy Database Configuration With Mysql app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:''@localhost/myflask' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) # our model class UserInfo(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(100), unique=True) password = db.Column(db.String(100)) def __init__(self, username, password): self.username = username self.password = password #creating our routes @app.route('/') def Index(): name = 'Geekscoders.com' return render_template('index.html', data = name) @app.route('/login', methods = ['GET', 'POST']) def Login(): form = LoginForm() if form.validate_on_submit(): if request.form['username'] != 'geekscoders' \ or request.form['password'] != '12345': flash("Invalid Credentials, Please Try Again") else: return redirect(url_for('Index')) return render_template('login.html', form=form) @app.route('/contact') def Contact(): return render_template('contact.html') @app.route('/about') def About(): return render_template('about.html') @app.errorhandler(404) def page_not_found(e): return render_template('404.html') @app.errorhandler(500) def internal_server_error(e): return render_template('500.html') #run flask app if __name__ == "__main__": app.run(debug=True) |
Now open your Python terminal and write these commands for creating the table in your database.
|
1 2 |
>>> from app import db >>> db.create_all() |
Check your Wamp Server and now we have our user_info table in our database.
Also you can add data to your table using this command.
|
1 2 3 4 |
>>> from app import UserInfo >>> u = UserInfo('parwiz', '123456') >>> db.session.add(u) >>> db.session.commit() |
Rest of the files for the tutorial.
form.py
|
1 2 3 4 5 6 7 8 9 |
from flask_wtf import FlaskForm from wtforms import StringField, PasswordField from wtforms.validators import InputRequired class LoginForm(FlaskForm): username = StringField('Username', validators=[InputRequired()]) password = PasswordField('Password', validators=[InputRequired()]) |
templates/base.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>{% block title %}{% endblock %}</title> <!-- CSS only --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/css/bootstrap.min.css" rel="stylesheet" > <!-- Add Icon --> <link rel="icon" href="{{url_for('static', filename = 'py.png')}}"/> </head> <body> <nav class="navbar navbar-expand-lg navbar navbar-dark bg-dark"> <div class="container-fluid"> <a class="navbar-brand" href="{{url_for('Index')}}">GeeksCoders</a> <div class="collapse navbar-collapse" id="navbarSupportedContent"> <ul class="navbar-nav me-auto mb-2 mb-lg-0"> <li class="nav-item"> <a class="nav-link active" aria-current="page" href="{{url_for('Index')}}">Home</a> </li> <li class="nav-item"> <a class="nav-link" href="{{url_for('Contact')}}">Contact</a> </li> <li class="nav-item"> <a class="nav-link" href="{{url_for('About')}}">About</a> </li> </ul> <form class="d-flex"> <input class="form-control me-2" type="search" placeholder="Search" aria-label="Search"> <button class="btn btn-outline-success" type="submit">Search</button> </form> </div> </div> <a href="{{url_for('Login')}}"> <button class="btn btn-success navbar-btn">Login</button> </a> </nav> {% block body %} {% endblock %} <!-- JavaScript Bundle with Popper --> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.1/dist/js/bootstrap.bundle.min.js" > </script> </body> </html> |
templates/index.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
{% extends 'base.html' %} {% block title %}Home{% endblock %} {% block body %} <div class="container"> <h1>Welcome to our home page</h1> <h3>Welcome to . {{data}}</h3> </div> {% endblock %} |
templates/contact.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{% extends 'base.html' %} {% block title %}Contact{% endblock %} {% block body %} <div class="container"> <h1>This is contact page</h1> </div> {% endblock %} |
templates/about.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{% extends 'base.html' %} {% block title %}About{% endblock %} {% block body %} <div class="container"> <h1>This is our about page</h1> </div> {% endblock %} |
templates/500.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{% extends 'base.html' %} {% block title %}Server Error {% endblock %} {% block body %} <div class="container"> <h1>Internal Server Error </h1> </div> {% endblock %} |
templates/404.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
{% extends 'base.html' %} {% block title %}Page Not Found {% endblock %} {% block body %} <div class="container"> <h1>Page Not Found</h1> </div> {% endblock %} |