Python 3 Flask Project to Upload CSV File Data in MySQL Table Using Pandas Library in Browser

Welcome folks today in this blog post we will be uploading csv file data and importing it inside the mysql table in flask using pandas library. All the full source code of the application is shown below.

Get Started

In order to get started you need to install the below libraries using the pip command as shown below

pip install flask

pip install pandas

After this you need to see the directory structure of the flask app is shown below

First of all as you can see you need to create the uploads directory where we will be storing the uploaded files inside the browser. And also we need to create the templates folder and inside it we need to create the index.html file which will have the simple html5 form where user can upload csv files.

Now first of all you need to create the app.py file and copy paste the following code

app.py

from flask import Flask, render_template, request, redirect, url_for
import os

import pandas as pd
import mysql.connector

app = Flask(__name__)

# enable debugging mode
app.config["DEBUG"] = True

# Upload folder
UPLOAD_FOLDER = 'uploads'
app.config['UPLOAD_FOLDER'] =  UPLOAD_FOLDER


# Database
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database="exceldb"
)

mycursor = mydb.cursor()


if (__name__ == "__main__"):
     app.run(port = 5000)

from flask import Flask, render_template, request, redirect, url_for

import os

import pandas as pd

import mysql.connector

app = Flask(__name__)

# enable debugging mode

app.config["DEBUG"] = True

# Upload folder

UPLOAD_FOLDER = 'uploads'

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# Database

mydb = mysql.connector.connect(

host="localhost",

user="root",

password="",

database="exceldb"

)

mycursor = mydb.cursor()

if (__name__ == "__main__"):

app.run(port = 5000)

As you can see we are importing the flask library and also we are importing the mysql driver library for connecting to the database using the username and password and also the database name. And also we are setting the upload folder path. And lastly we are starting the flask app at the port number 5000.

Creating the Table

After you install this library you need to start the xammp control panel and start the apache server and mysql server as well

Now you need to create the table inside the phpmyadmin as shown below

As you can see we have the four fields inside the user table. ID is the primary key of the table and then we are storing the name,age and country of the user.

Now we will be defining the get route to load the index.html file as shown below

# Root URL
@app.route('/')
def index():
     # Set The upload HTML template '\templates\index.html'
    return render_template('index.html')

# Root URL

@app.route('/')

def index():

# Set The upload HTML template '\templates\index.html'

return render_template('index.html')

As you can see we are using the render_template() method to load the index.html file inside the browser.

Now we need to create the templates folder and inside it you need to create the index.html file as shown below

templates/index.html

<!doctype html>
<html>
  <head>
    <title>FLASK CSV File Upload</title>
  </head>
  <body>
    <h1>Upload your CSV file</h1>
    <form method="POST" action="" enctype="multipart/form-data">
      <p><input type="file" name="file"></p>
      <p><input type="submit" value="Submit"></p>
    </form>
  </body>
</html>

<!doctype html>

<html>

<head>

<title>FLASK CSV File Upload</title>

</head>

<body>

<h1>Upload your CSV file</h1>

</form>

</body>

</html>

As you can see we have the html5 form where we have the input field where we allow the user to upload the csv file and then we have the button to submit the form. Now we need to define the post route at the / path as shown below

@app.route("/", methods=['POST'])
def uploadFiles():
      # get the uploaded file
      uploaded_file = request.files['file']
      if uploaded_file.filename != '':
           file_path = os.path.join(app.config['UPLOAD_FOLDER'], uploaded_file.filename)
          # set the file path
           uploaded_file.save(file_path)
           parseCSV(file_path)
          # save the file
      return redirect(url_for('index'))

@app.route("/", methods=['POST'])

def uploadFiles():

# get the uploaded file

uploaded_file = request.files['file']

if uploaded_file.filename != '':

file_path = os.path.join(app.config['UPLOAD_FOLDER'], uploaded_file.filename)

# set the file path

uploaded_file.save(file_path)

parseCSV(file_path)

# save the file

return redirect(url_for('index'))

As you can see inside the post request we are getting the selected file by the user and then we are uploading that csv file to the uploads folder and then we are calling the parseCSV() method which will parse the csv file and insert to mysql table.

def parseCSV(filePath):
      # CVS Column Names
      col_names = ['Name','Age','Country']
      # Use Pandas to parse the CSV file
      csvData = pd.read_csv(filePath,names=col_names, header=None)
      # Loop through the Rows
      for i,row in csvData.iterrows():
             sql = "INSERT INTO users (Name,Age,Country) VALUES (%s, %s, %s)"
             value = (row['Name'],row['Age'],row['Country'])
             mycursor.execute(sql, value)
             mydb.commit()

def parseCSV(filePath):

# CVS Column Names

col_names = ['Name','Age','Country']

# Use Pandas to parse the CSV file

csvData = pd.read_csv(filePath,names=col_names, header=None)

# Loop through the Rows

for i,row in csvData.iterrows():

sql = "INSERT INTO users (Name,Age,Country) VALUES (%s, %s, %s)"

value = (row['Name'],row['Age'],row['Country'])

mycursor.execute(sql, value)

mydb.commit()

As you can see we are defining the column names inside an array. And after that we are using the read_csv() method to extract the data from the csv file and then storing it inside the array. And then we are using the for loop to insert each row to the mysql table using the INSERT statement.

Full Source Code

Wrapping it up this is the full source code of the app.py file as shown below