Mastering Database Management

0 of 78 lessons complete (0%)

Project: MySQL with Python using Flask

MySQL with Python and Flask

You don’t have access to this lesson

Please register or sign in to access the course content.

Here we are creating a project to demonstrate the use of MySQL with the Python and Flask. Flask is the python library for the web interface. This project has 5 files which can be download from the GITHUB.

Install some libraries before moving to this project. Install the VS Code

pip install python
pip install flask
pip install flask_mysqldb

File: app.py

from flask import Flask, render_template, request, redirect, url_for, session
from flask_mysqldb import MySQL
import re

app = Flask(__name__)

app.secret_key = 'qwerty'

# Required
app.config["MYSQL_USER"] = "root"
app.config["MYSQL_PASSWORD"] = ""
app.config["MYSQL_DB"] = "pylogin"

# Extra configs, optional:
app.config["MYSQL_CURSORCLASS"] = "DictCursor"
app.config["MYSQL_CUSTOM_OPTIONS"] = {"ssl": {"ca": "/path/to/ca-file"}}  # https://mysqlclient.readthedocs.io/user_guide.html#functions-and-attributes

mysql = MySQL(app)
    
    
@app.route('/')
@app.route('/login', methods =['GET', 'POST'])
def login():
    mesage = ''
    if request.method == 'POST' and 'email' in request.form and 'password' in request.form:
        email = request.form['email']
        password = request.form['password']
        
        cursor = mysql.connection.cursor()
        cursor.execute('SELECT * FROM user WHERE email = % s AND password = % s', (email, password, ))

        user = cursor.fetchone()
        if user:
            session['loggedin'] = True
            session['userid'] = user['userid']
            session['name'] = user['name']
            session['email'] = user['email']
            mesage = 'Logged in successfully !'
            return render_template('user.html', mesage = mesage)
        else:
            mesage = 'Please enter correct email / password !'
    return render_template('login.html', mesage = mesage)
  
@app.route('/logout')
def logout():
    session.pop('loggedin', None)
    session.pop('userid', None)
    session.pop('email', None)
    return redirect(url_for('login'))
  
@app.route('/register', methods =['GET', 'POST'])
def register():
    mesage = ''
    if request.method == 'POST' and 'name' in request.form and 'password' in request.form and 'email' in request.form :
        userName = request.form['name']
        password = request.form['password']
        email = request.form['email']
        cursor = mysql.connection.cursor()
        cursor.execute('SELECT * FROM user WHERE email = % s', (email, ))
        account = cursor.fetchone()
        if account:
            mesage = 'Account already exists !'
        elif not re.match(r'[^@]+@[^@]+\.[^@]+', email):
            mesage = 'Invalid email address !'
        elif not userName or not password or not email:
            mesage = 'Please fill out the form !'
        else:
            cursor.execute('INSERT INTO user VALUES (NULL, % s, % s, % s)', (userName, email, password, ))
            mysql.connection.commit()
            mesage = 'You have successfully registered !'
    elif request.method == 'POST':
        mesage = 'Please fill out the form !'
    return render_template('register.html', mesage = mesage)
    
if __name__ == "__main__":
    app.run()  

Now let’s create different views,

Create a folder named templates, now we will create three files in the index.html, register.html and user.html

File: templates/index.html

<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>User Login Form</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">
</head>
<body>	
<div class="container">
	<h2>User Login</h2>
	<form action="{{ url_for('login') }}" method="post">
	    {% if mesage is defined and mesage %}
			<div class="alert alert-warning">{{ mesage }}</div>
		{% endif %}
		<div class="form-group">
			<label for="email">Email:</label>
			<input type="email" class="form-control" id="email" name="email" placeholder="Enter email" name="email">
		</div>
		<div class="form-group">
			<label for="pwd">Password:</label>
			<input type="password" class="form-control" id="password" name="password" placeholder="Enter password" name="pswd">
		</div>    
		<button type="submit" class="btn btn-primary">Login</button>
		<p class="bottom">Dont't have an account?  <a class="bottom" href="{{url_for('register')}}"> Register here</a></p>
	</form>
</div>
</body>
</html>

File: templates/register.html

<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>User Registeration Form</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">              
</head>
<body>
<div class="container">
	<h2>User Registration</h2>
	<form action="{{ url_for('register') }}" method="post">
        {% if mesage is defined and mesage %}
			<div class="alert alert-warning">{{ mesage }}</div>
		{% endif %}
		<div class="form-group">
			<label for="name">Name:</label>
			<input type="text" class="form-control" id="name" name="name" placeholder="Enter name" name="name">
		</div>
		<div class="form-group">
			<label for="email">Email:</label>
			<input type="email" class="form-control" id="email" name="email" placeholder="Enter email" name="email">
		</div>
		<div class="form-group">
			<label for="pwd">Password:</label>
			<input type="password" class="form-control" id="password" name="password" placeholder="Enter password" name="pswd">
		</div>    
		<button type="submit" class="btn btn-primary">Register</button>
		<p class="bottom">Already have an account?  <a class="bottom" href="{{url_for('login')}}"> Login here</a></p>
	</form>
</div>        
</body>
</html>

File: templates/user.html

<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>User Account</title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">              
</head>
<body>
<div class="container">
	<div class="row">	
		<h1>User Profile</h1>
	</div>
	<br>
	<div class="row">	
		Logged in : <strong>{{session.name}} | <a href="{{ url_for('logout') }}"> Logout</a>
	</div>
	<br><br>
	<div class="row">
	    
		<h2>Welcome to the user profile page...</h2> 
	</div>		
</div>
</body>
</html>