When building local Flask projects, developers often face challenges when attempting to dynamically load data into a chart or table on a webpage using JavaScript. Cross-Origin Resource Sharing (CORS) issues can arise if a webpage fetches a .csv file from a Flask server using JavaScript, especially if the frontend and backend are treated as separate origins (e.g., different ports or domains).
However, these problems can be avoided by converting the .csv data to an SQLite database and serving the data via a JSON API. This approach not only resolves CORS concerns but also offers better data management and flexibility.
In this article, we’ll explore the differences and provide a step-by-step guide.
Setting Up the Environment
First, set up a Python virtual environment to isolate dependencies.
For Windows:
python -m venv venv
venv\Scripts\activate
pip install flask flask-cors pandas numpyFor Linux/Mac:
python3 -m venv venv
source venv/bin/activate
pip install flask flask-cors pandas numpyGenerate a Sample .csv File
Here’s a script to generate a .csv file with 100 records. Save this script as generate_csv.py and run it.
import pandas as pd
import numpy as np
import random
# Set random seed for reproducibility
np.random.seed(42)
# Define product details
products_info = {
"Lumina Elite": {"sell_price": 29.99, "investment_price": 8.42},
"AeroChill XT": {"sell_price": 49.99, "investment_price": 19.02},
}
# Define sales channels
channels = ["Online", "Social Media", "Outlet Store", "Normal Store"]
# Generate random data
sales_data = []
for i in range(100):
product_name, details = random.choice(list(products_info.items()))
channel = random.choice(channels)
quantity = random.randint(10, 100)
sell_price = details["sell_price"]
investment_price = details["investment_price"]
total_sales = quantity * sell_price
total_investment = quantity * (investment_price + random.uniform(1, 10))
sales_data.append({
"Date": pd.Timestamp('2022-01-01') + pd.to_timedelta(i, unit='D'),
"ProductName": product_name,
"Channel": channel,
"QuantitySold": quantity,
"TotalSales": total_sales,
"TotalInvestment": total_investment,
"ROI": (total_sales - total_investment) / total_investment,
})
# Save to CSV
sales_df = pd.DataFrame(sales_data)
sales_df.to_csv("sales_data.csv", index=False)
print("CSV file generated: sales_data.csv")The Problem: CORS Issues with a CSV
Flask Code to Serve the CSV
Create a basic Flask app to serve the .csv file. Save this as app.py:
from flask import Flask, send_file
app = Flask(__name__)
@app.route('/data', methods=['GET'])
def get_data():
return send_file('sales_data.csv', mimetype='text/csv')
if __name__ == '__main__':
app.run(debug=True)This will serve the .csv file at http://127.0.0.1:5000/data.
Frontend Fetching CSV via JavaScript
Create an HTML file (index.html) to fetch the .csv dynamically:
<!DOCTYPE html>
<html>
<head>
<title>CSV Loader</title>
<script>
async function loadCSV() {
try {
const response = await fetch('http://127.0.0.1:5000/data');
if (!response.ok) throw new Error('Network response was not ok');
const csvData = await response.text();
console.log(csvData);
} catch (error) {
console.error('Error loading CSV:', error);
}
}
document.addEventListener('DOMContentLoaded', loadCSV);
</script>
</head>
<body>
<h1>CSV Loader</h1>
<p>Check the console for CSV data or errors.</p>
</body>
</html>Serve the HTML file using a different port (e.g., 8000) with Python’s HTTP server:
python -m http.server 8000Now, open http://127.0.0.1:8000 in your browser. The JavaScript fetch request will try to load the CSV from http://127.0.0.1:5000/data.
Expected Error
In the browser console, you’ll see an error like this:
Access to fetch at 'http://127.0.0.1:5000/data' from origin 'http://127.0.0.1:8000' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.
GET http://127.0.0.1:5000/data net::ERR_CONNECTION_REFUSED
Error loading CSV: TypeError: Failed to fetch at HTMLDocument.loadCSV ((index):8:40)This occurs because the frontend (http://127.0.0.1:8000) is making a cross-origin request to the backend (http://127.0.0.1:5000).
Solution: Convert CSV to SQLite
Step 1: Load CSV into SQLite
Create a script (csv_to_sqlite.py) to convert the .csv file into an SQLite database:
import sqlite3
import pandas as pd
def load_csv_to_sqlite(csv_file, db_name):
conn = sqlite3.connect(db_name)
df = pd.read_csv(csv_file)
df.to_sql('sales_data', conn, if_exists='replace', index=False)
conn.close()
print(f"Data from {csv_file} loaded into {db_name}")
load_csv_to_sqlite('sales_data.csv', 'sales_data.db')Run the script to create sales_data.db.
Step 2: Create a JSON API
Update the Flask app (app.py) to serve the SQLite data as JSON:
from flask import Flask, jsonify
import sqlite3
from flask_cors import CORS
app = Flask(__name__)
CORS(app) # Enable CORS
@app.route('/api/data', methods=['GET'])
def get_data():
conn = sqlite3.connect('sales_data.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM sales_data LIMIT 10")
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
conn.close()
return jsonify([dict(zip(columns, row)) for row in rows])
if __name__ == '__main__':
app.run(debug=True)Relaunch the app and the server.
In one terminal, launch app.py
python app.pyIn another terminal, launch the server on port 8000.
python -m http.server 8000Open http://127.0.0.1:8000 and you should see a data table in the browser.
Data Table
Below is the data fetched from the Flask API:
Date ProductName Channel QuantitySold TotalSales TotalInvestment ROI
Social Media 2022-01-01 Lumina Elite 40 0.9135117236107236 626.9101909323035 1199.6
Social Media 2022-01-02 AeroChill XT 83 1.237398457520096 1854.4618130285503 4149.17
Normal Store 2022-01-03 AeroChill XT 40 0.9785893692512332 1010.6189950655188 1999.6
Online 2022-01-04 AeroChill XT 67 0.8835446803942443 1778.2057600560622 3349.33
Outlet Store 2022-01-05 AeroChill XT 69 1.0807901317460271 1657.6924060600115 3449.31
Social Media 2022-01-06 AeroChill XT 48 1.4665063315413471 972.8416137900256 2399.52
Outlet Store 2022-01-07 AeroChill XT 76 0.772637867512562 2143.2691186560564 3799.24
Normal Store 2022-01-08 Lumina Elite 99 1.8332246383246196 1047.9260838828773 2969.01
Outlet Store 2022-01-09 AeroChill XT 24 0.8474131410455673 649.4270141009067 1199.76
Outlet Store 2022-01-10 AeroChill XT 25 0.9899013891845456 628.0461970591131 1249.75CSV Files vs. APIs
Why the CSV Approach Fails:
Static File Limitations:
- A
.csvis a static file, and Flask serves it as a simple file resource (e.g., through the/dataendpoint). - If your frontend and backend are served from different origins (e.g., different ports), JavaScript cannot fetch the file due to CORS restrictions.
Unstructured Access:
- A
.csvfile doesn’t support structured queries (e.g., selecting specific rows or filtering data). - JavaScript has to fetch the entire file and parse it manually, which can be inefficient for large datasets.
- CORS Problems:
- When fetching the
.csvfile dynamically with JavaScript from a different origin, the browser enforces the same-origin policy and blocks the request unless the server explicitly allows it with CORS headers.
Why SQLite Works:
API-First Approach:
- When you use SQLite with Flask, the backend queries the database and serves the data as JSON through an API endpoint (e.g.,
/api/data). - JSON is the preferred format for web applications, making it easier to integrate with frontend frameworks and libraries.
No CORS Issues:
- When you enable CORS on the Flask app, cross-origin requests are allowed, and the browser does not block the API calls.
Dynamic Queries:
- SQLite supports SQL queries, allowing you to dynamically filter, sort, or aggregate data before sending it to the client. This is far more efficient than processing a raw
.csvfile on the client side.
Thank you for reading this article. I hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials, please feel free to reach out. Your feedback and suggestions are always welcome!
Happy coding!
C. C. Python Programming
You can also find this article at Medium.com
