react-python

Full Stack React With Python Flask Series – Database

By Rasyue | On April 19, 2020

Welcome to the second tutorial of the React With Python Flask Series. In case if you have not read the first tutorial, you may find it here.

In this tutorial, we are going to look at how we can configure our Flask App so we can connect our Python script with our database. For this tutorial we are going to be using MySQL database. You are free to choose any database you like but the python package might be different.

Following the previous tutorial, I have added a bit of styling to the React App to make it a bit more satisfying to look at.

Before that, make sure your directory is as follow.

react-flask-project-directory

Now, let’s style our React App a bit.

Run the following command in your command terminal

npm install --save bootstrap
npm install --save reactstrap 

Import Bootstrap CSS in the src/index.js file:

import 'bootstrap/dist/css/bootstrap.min.css';

Go to your Navigation.js and paste the following code

import React from 'react';
import { Nav, NavLink} from 'reactstrap';

 
const Navigation = () => {
    return (
       //<div>
         // <NavLink to="/">Home</NavLink>       
       //</div>
       <Nav>
        <NavLink href="/">Home</NavLink> 
        <NavLink href="/login">Login</NavLink> 
      </Nav>


    );
}
 
export default Navigation;

Paste the following code in your Home.js

import React, { Component } from 'react';


export default class Home extends Component {
    constructor(props) {
        super(props);
        this.state = {
            output : []
        };
      }
    
    componentDidMount() {
        fetch('http://localhost:5000/').then(res => res.json()).then(data => {
             console.log(data)         
            this.setState({               
                output: data
              });
            
          });
      }
    
    render() {
       
        return (
        <div>
             <h1>This is the Homepage</h1>
              <p>{this.state.output.hello}</p>
              <p>{this.state.output.results}</p>
        </div>
        );
      }
}

Paste the following in your Login.js file, we will look at how to create a login system on the next tutorial

import React, { Component } from 'react';
import { Col, Button, Form, FormGroup, Label, Input, FormText } from 'reactstrap';

export default class Login extends Component {
    constructor(props) {
        super(props);
        this.state = {
          username: '',
          email: '',
          password: '',
          output : []
        };
        this.handleChange = this.handleChange.bind(this);
        this.handleSubmit = this.handleSubmit.bind(this);
      }
    
      handleChange(e) {
        
        this.setState({
          [e.target.name]: e.target.value
        })
      }
    
      handleSubmit(event) {        
        console.log(this.state.username)
        console.log(this.state.email)
        console.log(this.state.password)
        event.preventDefault();
      }
    
    render() {
       
        return (
          <Form onSubmit={this.handleSubmit}>
          <FormGroup row>
            <Label for="username" sm={2}>Username</Label>
            <Col sm={4}>
              <Input type="text" name="username" id="username" placeholder="with a placeholder" value={this.state.username} onChange={this.handleChange} />
            </Col>
          </FormGroup>
          <FormGroup row>
            <Label for="email" sm={2}>Email</Label>
            <Col sm={4}>
              <Input type="email" name="email" id="email" placeholder="with a placeholder" value={this.state.email} onChange={this.handleChange} />
            </Col>
          </FormGroup>
          <FormGroup row>
            <Label for="password" sm={2}>Password</Label>
            <Col sm={4}>
              <Input type="password" name="password" id="password" placeholder="password placeholder" value={this.state.password} onChange={this.handleChange} />
            </Col>
          </FormGroup>
          <FormGroup row>
           
            <Col sm={4}>
              <input type="submit" value="Submit" />
            </Col>
          </FormGroup>
        </Form>
        );
      }
}

Now, your Home page should look a bit more decent then the previous tutorial. We will add more styling in the next tutorial.

Connecting to Database

Now, go to your api folder and create a new python script (.py) and name it db.py. Before we can connect to db, we need to install the necessary packages. Open up the terminal line and go to your api directory and run the code.

pip install pymysql

Now, before we can start writing the python code to connect to our db and try to fetch records from our database to see if our script is working. We need to have a working database with a table.

Now, we are going to use MySQL in this tutorial, if you are a beginner and have no idea how to get your MySQL database running, I would recommend that you download XAMPP. Why XAMPP you asked? Well, mainly because XAMPP comes with MySQL and Apache, of course we are not going to use Apache but we are going to use MySQL.

After you have installed XAMPP, open up the control panel and start the MySQL service. Now, you either go to localhost/phpmyadmin on your browser or you can use HeidiSQL. Both will work fine for beginner as it provides a UI for you to be able to understand better and work with creating your database and tables.

In this tutorial we will be using HeidiSQL, take note that if you plan on accessing your database through phpmyadmin, make sure you start both Apache service and MySQL service. After that open up your browser and go to localhost/phpmyadmin.

After you have successfully installed HeidiSQL, open it up and fill up your database credentials as follow, the Mysql from xampp usually have a username of root and no password, so you can leave the password input blank.

heidisql-interface

Then, click Open to open the connection to your database.

Now, on your left side, there is a navbar, click on the connection and create a new databse, we are going to name it reactflask. Then click on the database you have created and create a table with the name users

react-flask-user-table

Now we have a table users, let’s create a sample data inside the table. Just fill in some sample data that we can fetch later from our Flask App.

react-flask-user-table-sampledata

Of course, for real data, the password will actually be a hash string.

Now, let’s go back to our api.py and write some codes to connect our Flask App to the database.

from flask import Flask, jsonify

from flask_cors import cross_origin
import pymysql
import json
import os
from dotenv import load_dotenv
import logging




APP_ROOT = os.path.join(os.path.dirname(__file__), '..')   # refers to application_top
dotenv_path = os.path.join(APP_ROOT, '.env')
load_dotenv(dotenv_path)

mysql_host = os.getenv("DB_HOST")
mysql_port = os.getenv("DB_PORT")
mysql_dbname = os.getenv("DB_DATABASE")
mysql_user = os.getenv("DB_USERNAME")
mysql_password = os.getenv("DB_PASSWORD")

connection = None
CONNECTION_TIMEOUT = 5000

print(mysql_host)
print(mysql_port)
print(mysql_dbname)
print(mysql_user)

def connect_db():


  connection = pymysql.connect(host=mysql_host, user=mysql_user, port=int(mysql_port), password=mysql_password, database=mysql_dbname, connect_timeout=CONNECTION_TIMEOUT)
  print("connected to db")
  return connection

  
def get_cursor(connection): 
  connection.ping(reconnect=True)
  return connection.cursor()

app = Flask(__name__)

@app.route('/')
@cross_origin()
def hello_world():
    connection = connect_db()
    cur = connection.cursor()
    getUser = "SELECT * FROM users"
    cur.execute(getUser)
    results = cur.fetchone()
    jsonResp = {'hello': 'world', 'results': results}
    return jsonify(jsonResp)
    #return jsonResp

if __name__ == '__main__':
    app.run(debug=False)

    

Hold up! We are not done yet, you need to create a .env file on your root folder.

DB_CONNECTION=mysql
DB_HOST= 127.0.0.1
DB_PORT= 3306
DB_DATABASE= reactflask         
DB_USERNAME= root    
DB_PASSWORD=

The .env file is the file that is usually used to store credentials, be it your database credentials, your API credentials and many more. So when you push your source code to your git repo, make sure not to push your .env file as well. Using .env file helps you to manage your credentials better so you would not have to input your credentials randomly in your source code and helps you to secure it better.

Now, if everything has been done correctly, start your React App and your Flask App. You should have something similar to this.

We have successfully connected our database to our Flask App. Doing so, we can now proceed to do any CRUD process.

Tips to help you debug in case you encounter any error.

  1. Make sure to start your Flask App. You first open the command line and run the venv\Scripts\activate and then run python api.py on your command line. For React, just run npm start
  2. Make sure your db credentials in the .env file is correct, the database name is correct and the table name is correct. If you decided to use different name then make sure you properly change it.
  3. In case your React App encounter errors, check the library is properly installed and included. Check that the components are properly created.

In the next tutorial, we are going to create a simple login system and we are going to see how to sanitize input before saving it into our database. Also, we are going to look at how we should save the passwords in a correct way.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*
*