How to connect Python and Flask to a database.

(In continuation with the previous blog How To Make a Web Application Using Flask in Python, In this latest blog, we are going to learn how to connect our Python Flask Application with Databases.

Before we start, first let’s have a glimpse of what a database is and some of its essential commands.

Database

A database is a systematic collection of data stored in the row-column form. Where a single row represents a record and each column represents some attribute ( Ex. Name) that is associated with the record value.

Example: User Database

MySql Database

For login MySQL database, we need to run the command

mysql -u db_user -p

Create a new Database:

CREATE DATABASE db_name;

View All MySQL Databases:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
||
+--------------------+
1 rows in set (0.00 sec)

Select a Mysql database:

USE database_name;
outupt
Database changed

Now move further,

Initiating Flask-SQLAlchemy

Flask-SQLAlchemy is Python SQL is a Python package that allows us to integrate our Python application with the SQL-based Databases and helps in performing DB operations such as creating connections, establishing sessions, and perform data operations.

pip install Flask
pip install Flask-SQLAlchemy

let’s create a __init__.py file for Flask applications using Flask-SQLAlchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


def init_app():
    """Construct the core application."""
    app = Flask(__name__, instance_relative_config=False)
    app.config.from_object('config.Config')

    db.init_app(app)

    with app.app_context():
        from . import routes  # Import routes
        db.create_all()  # Create sql tables for our data models

        return app

Creating Database Models

Database Models: A Model is a Python class that represents the database table and its attributes map to the column of the table.

Example:

__tablename__ = 'posts'
    id = db.Column(db.Integer(), primary_key=True)
    title = db.Column(db.String(255), nullable=False)

Create a models.py file in our application directory. Here we'll import the DB an object that we created in __init__.py. Now we can create database models by defining classes in this file.

"""Data models."""
from . import db


class User(db.Model):
    """Data model for user accounts."""

    __tablename__ = 'users'
    id = db.Column(
        db.Integer,
        primary_key=True
    )
    username = db.Column(
        db.String(64),
        index=False,
        unique=True,
        nullable=False
    )
    email = db.Column(
        db.String(80),
        index=True,
        unique=True,
        nullable=False
    )
   

    def __repr__(self):
        return '<User {}>'.format(self.username)

Creating Users

now have what we need to create users in our database on the fly. Here we’ll createroutes.py and make a simple route to create a user.

( If you want to know more about Routes. Click Here https://medium.com/uneritx/how-to-make-a-web-application-using-flask-in-python-aec2f99a7c3a )

from flask import request, render_template, make_response
from flask import current_app as app
from .models import db, User


@app.route('/', methods=['GET'])
def records():
    """Create a user via query string parameters."""
    username = request.args.get('user')
    email = request.args.get('email')
    if username and email:
        existing_user = User.query.filter(
            User.username == username or User.email == email
        ).first()
        if existing_user:
            return make_response(
                f'{username} ({email}) already created!'
            )
        new_user = User(
            username=username,
            email=email,
        ) 
       # Create an instance of the User class
        db.session.add(new_user)  # Adds new User record to database
        db.session.commit()  # Commits all changes
        redirect(url_for('user_records'))
    return render_template(
        'index.jinja2',
        users=User.query.all(),
        title="Show Users"
    )

Render Data into the HTML Templates

Here we need to add some code routes.py to adding records into a template.

@app.route('/', methods=['GET'])
def create_user():
    """Create a user."""
  
    return render_template(
        'users.jinja2',
        users=User.query.all(),
        title="Show Users"
    )

The statement User.query.all() will return all instances User in our database, and We pass all records into our template with users=users. Here's our Jinja template:

we need to create a file index.jinja2 in the template folder.

<!DOCTYPE html> 
2<html lang="en"> 
3<head> 
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=<<for>>, initial-scale=1.0">
<title>Users</title>
</head>
<body>
<div class="container">
  
    {% for user in users %}
    <h1>Welcome to{{user.username}}!</h1>    
     <ul id="user.username">
      <li>
        <span class="field-name">User:</span>
        <span class="field-value">{{ user.username }}</span>
      </li>
      <li>
        <span class="field-name">Email:</span>
        <span class="field-value">{{ user.email }}</span>
      </li>
     
    </ul>
    {% endfor %}
  </div>
</body>
</html>

In the upcoming blog, we will be discussing How to connect Python & Flask with Yaml and CRUD operations database.

Hope you have enjoyed reading it. Thank you!