Uploading and downloading file to SQL Server using flask api
Contents
Uploading and downloading file to SQL Server using flask api#
Goal#
In this flask application, the goal is to upload data from an excel file into a SQL server table called ZIP_CODE. The excel contains many columns, but the requirement is to only upload five columns (ZIP, Latitude, Longitude, City, State, County Names, and Time zone). We will be building a flask application which allow users to pass the file path and name in URL as query parameters. The program will read the file and upload required filed into the database. The second part of the program allow users to downlaod the content of the table into a excel file.
Flask#
Flask is a web framework. It provides tools, libraries, and technologies to build a web application. Flask is part of the categories of micro-framework. Micro-framework is a framework with little or no dependencies on external libraries. That makes flaks lightweight, and there is little dependency on updating and watching for security bugs.
SQLalchemy#
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides a full suite of well-known enterprise-level persistence patterns designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language. More details can be found at:- https://www.sqlalchemy.org/
Steps#
Read the file and create a data frame.
Drop unwanted columns from the data frame.
Do required data preparation/cleaning (in this example we will be modifying ZIP columns, making it a string and padding it will additional zeros to make it fixed 5 characters. )
Create database connection (using sqlalchecy)
Upload the content to the table /Download the content to a file
# Import required libraries
from sqlalchemy import create_engine
import pandas as pd
import os
from flask import Flask, request, jsonify, make_response
app = Flask(__name__)
# Method to be called for uploading the exls file to the database.
# exmple of URL need to be passed to invoke this method
# http://127.0.0.1:5000/upload?filepath=S:\kkeshav\Zip\&filename=uszips.xlsx
@app.route("/upload", methods=['GET'])
def file_upload():
try:
#Capture the File path from the query string
file_path = request.args.get('filepath')
# Capture the file name from the query string
file_name = request.args.get('filename')
file_location = file_path + file_name
# Create a datframe from the data in the file mentioned in the query string
df = pd.read_excel(file_location)
# data preparation
# There can be several steps of data cleaning and prepration
# In this example we will be removing unwanted columns and make zip column as string with fixed length of 5 charecters
# Converting zip into string and padding required 0 to make it of 5 digits
df['zip'] = df['zip'].astype(str)
df['zip'] = df['zip'].str.zfill(5)
# Drop all columns which are not required for the table.
df = df.drop(['state_name', 'zcta', 'parent_zcta', 'population', 'density', 'county_fips','county_fips_all', 'county_weights', 'county_names_all', 'imprecise', 'military'], axis=1)
# Rename the column name of the data frame to match the tables column name.
df.rename(columns = {'lat':'Latitude', 'lng':'Longitude', 'city':'City', 'state_id':'StateId', 'county_name':'CountyNames', 'timezone':'TimeZone'}, inplace = True)
# get the datbase connection using the method database_connection defined under main
engine = database_connection()
#Upload the data from xlsx file to the database table
df.to_sql('ZIP_CODE',engine, if_exists='replace')
# IF all good return a sucess message to the client else send the failure message
return " Data sucessfully inserted !..."
except:
return ("Error inserting data ! ...")
# Method to be called for downloading the content of the databse table to the file.
# exmple of URL need to be passed to invoke this method
# http://127.0.0.1:5000/download?filepath=S:\kkeshav\Zip\&filename=downloadedfile.xlsx
@app.route("/download", methods=['GET'])
def file_download():
try:
# get the datbase connection using the method database_connection defined under main
engine = database_connection()
# Create datframe using selected data from the table
df_table= pd.read_sql('select * from ZIP_CODE', engine)
# Get the File path and File name from the query string passed in the request
file_path = request.args.get('filepath')
file_name = request.args.get('filename')
file_location = file_path + file_name
# Created directry as the file path provided in the query string
os.makedirs(file_path, exist_ok=True)
# Create the file as passed in query string and Write dataframe to the file
df_table.to_excel(file_location)
# If all good return sucess response to user with file path and file name
return ("The file ... " + file_name + " is available at ... " + file_path )
except:
return ("Error downloading the data ! ...")
if __name__ == '__main__':
# Common function to create datbase connection. The function returns enging.
def database_connection():
# Database configs (host, user_id, passowrd, Database name )
svr = 'essql1.walton.uark.edu'
uid = 'uszip_user'
pwd = 'GohogsUA2'
db = 'USZIPCODE'
# create connection to the database as engine
engine = create_engine(f"mssql+pyodbc://{uid}:{pwd}@{svr}/{db}?driver=ODBC+Driver+17+for+SQL+Server&TrustServerCertificate=yes", fast_executemany=True)
return (engine)
app.run()
URL template#
Upload - http://127.0.0.1:5000/upload?filepath=S:\kkeshav\Zip&filename=uszips.xlsx
Download - http://127.0.0.1:5000/download?filepath=S:\kkeshav\Zip&filename=downloadedfile.xlsx