Building a Flask Application with Google Cloud SQL, User Analytics, and Ethnicity Insights

Creating a full-stack web application that leverages Flask, Google Cloud SQL, and insightful analytics is a rewarding journey. In this blog, we’ll explore how to build a personality test web application that stores user details, tracks usage, and displays analytics, all hosted on Google Cloud Platform (GCP).

Overview of the Application

Our application performs the following:

Collects user input through a personality test form.
Stores user email, ethnicity, and personality type in Google Cloud SQL.
Tracks the number of times each user accesses the application.
Displays the total number of unique users and their ethnicity distributions.
Deploys the application on Google Cloud Run for scalability.

Technologies Used

Backend Framework: Flask (Python)
Database: Google Cloud SQL (MySQL)
Cloud Hosting: Google Cloud Run
Frontend: HTML, CSS, and embedded Looker Studio for analytics.
Environment Management: Google Cloud SDK
CI/CD Automation: Google Cloud Build with GitHub integration

Step 1: Cloning the Repository and Installing Dependencies

To get started, clone the project repository and set up your environment:

Clone the Repository

git clone https://github.com/Datartic/personality_test.git
cd personality_test

Install Python Dependencies

pip install -r requirements.txt

Download and Install Google Cloud SDK

Visit the Google Cloud SDK installation guide.
Follow the instructions for your operating system to install the SDK.
Once installed, authenticate with your Google account:bashCopy codegcloud auth login
Set the active project:bashCopy codegcloud config set project [PROJECT_ID]

Step 2: Setting Up the Flask Application

Start with the basic structure of a Flask application:

app.py

from flask import Flask, render_template, request
import pymysql
from google.cloud.sql.connector import Connector

app = Flask(__name__)

# Database configuration
INSTANCE_CONNECTION_NAME = "<your-instance-connection-name>"
DB_NAME = "personality_db"
DB_USER = "personality_user"
DB_PASS = "<your-db-password>"

# Initialize the Connector
connector = Connector()

def get_connection():
    try:
        conn = connector.connect(
            INSTANCE_CONNECTION_NAME,
            "pymysql",
            user=DB_USER,
            password=DB_PASS,
            db=DB_NAME
        )
        return conn
    except Exception as e:
        print("Failed to connect to the database:", e)
        exit(1)

# Test the connection early
get_connection()

@app.route("/", methods=["GET", "POST"])
def index():
    if request.method == "POST":
        email = request.form.get("email")
        ethnicity = request.form.get("ethnicity")
        answers = request.form.to_dict()

        # Analyze personality type (simplified for brevity)
        dominant_type = "Sanguine"  # Example logic

        # Store data in database
        conn = get_connection()
        cursor = conn.cursor()
        query = "INSERT INTO users (email, ethnicity, dominant_type) VALUES (%s, %s, %s)"
        cursor.execute(query, (email, ethnicity, dominant_type))
        conn.commit()
        cursor.close()
        conn.close()

        return render_template("result.html", type=dominant_type)

    return render_template("index.html", total_users=get_user_count())

# Function to fetch total user count
def get_user_count():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(DISTINCT email) FROM users")
    count = cursor.fetchone()[0]
    cursor.close()
    conn.close()
    return count

if __name__ == "__main__":
    app.run(debug=True)

Step 2: Designing the Frontend

The frontend is implemented using HTML and CSS. Ensure it is visually appealing and includes a dropdown for ethnicity selection:

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="/static/style.css">
    <title>Personality Test</title>
</head>
<body>
    <div class="container">
        <h1>Welcome to the Personality Test</h1>
        <form method="POST">
            <label for="email">Email:</label>
            <input type="email" id="email" name="email" required>

            <label for="ethnicity">Ethnicity:</label>
            <select id="ethnicity" name="ethnicity" required>
                <option value="African">African</option>
                <option value="Asian">Asian</option>
                <option value="Caucasian">Caucasian</option>
                <option value="Hispanic">Hispanic</option>
                <option value="Other">Other</option>
            </select>

            <!-- Add test questions here -->

            <button type="submit">Submit</button>
        </form>

        <p>People who have used this application so far: <strong>{{ total_users }}</strong></p>
    </div>
</body>
</html>

CSS (style.css)

body {
    font-family: Arial, sans-serif;
    background-color: #f4f4f9;
    color: #333;
}
.container {
    max-width: 600px;
    margin: 50px auto;
    padding: 20px;
    background: white;
    border-radius: 8px;
    box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
}
h1 {
    color: #4CAF50;
    text-align: center;
}
button {
    background: #4CAF50;
    color: white;
    border: none;
    padding: 10px;
    border-radius: 5px;
    cursor: pointer;
}
button:hover {
    background: #45a049;
}

Step 3: Creating the Database in Google Cloud SQL

Create a MySQL Instance on Google Cloud SQL.
Set Up the Database Schema

CREATE DATABASE personality_db;

USE personality_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    ethnicity VARCHAR(255),
    dominant_type VARCHAR(50),
    usage_count INT DEFAULT 1
);

Step 4: Deployment with Google Cloud Build and Run

To automate the build and deployment process, integrate Google Cloud Build with your GitHub repository.

cloudbuild.yaml

steps:
- name: 'gcr.io/cloud-builders/docker'
  args: ['build', '-t', 'gcr.io/$PROJECT_ID/flask-app', '.']

- name: 'gcr.io/cloud-builders/docker'
  args: ['push', 'gcr.io/$PROJECT_ID/flask-app']

- name: 'gcr.io/cloud-builders/gcloud'
  args:
    [
      'run', 'deploy', 'flask-app',
      '--image', 'gcr.io/$PROJECT_ID/flask-app',
      '--platform', 'managed',
      '--region', 'us-central1'
    ]

images:
- 'gcr.io/$PROJECT_ID/flask-app'

options:
  logging: CLOUD_LOGGING_ONLY

Dockerfile

FROM python:3.9-slim
WORKDIR /app
COPY . /app
RUN pip install -r requirements.txt
CMD ["python", "app.py"]

Steps to Enable CI/CD:

Link GitHub Repository: Go to Google Cloud Console → Cloud Build → Triggers and link your GitHub repository.
Create a Trigger: Define a trigger to run cloudbuild.yaml on every push to the repository.
Verify Deployment: On pushing new code to GitHub, Cloud Build automatically builds the container and deploys it to Google Cloud Run.

Step 5: Adding Analytics with Looker Studio

Connect Looker Studio to Google Cloud SQL: Use a MySQL connector.
Build Dashboards: Create visualizations like a bar chart for “Ethnicities vs. Personality Types”.
Embed Dashboard: Use an <iframe> to embed the Looker Studio dashboard in your HTML.

<iframe src="<your-looker-studio-dashboard-url>" width="600" height="400"></iframe>

Conclusion

This application combines the power of Flask, Google Cloud SQL, and Looker Studio to create a scalable and insightful web app. By integrating Google Cloud Build for CI/CD, you ensure an automated and efficient deployment process. Happy coding!