Spread the love

Introduction:
Handling large datasets can be a challenge. When tasked with exporting thousands of rows from a MySQL database using Laravel, I found traditional methods lacking. This article showcases a solution that marries Laravel, Python, and Pandas for efficient data export.

Why Python with Pandas?
Python’s Pandas module is a powerhouse for data manipulation. Its prowess in swiftly exporting large datasets to Excel makes it an attractive choice for this challenge.

Integrating Symfony/Process in Laravel:
The symfony/process extension in PHP is our bridge to the Python world. It facilitates the execution of shell commands, in this case, running our Python script.

Laravel Export Function:
This function is the core of our solution. Here’s a breakdown:

$result = DB::table('full_report');
// ... [Database querying based on request parameters]
$sql = $result->toSql();
$bindings = $result->getBindings();
$filledSql = vsprintf(str_replace('?', "'%s'", $sql), $bindings);
$filename = 'FullReport-' . Carbon::now()->timestamp . '.xlsx';
$process = new Process(['/opt/homebrew/bin/python3', 'export.py', $filledSql, $filename]);
$process->run();
if (!$process->isSuccessful()) {
    throw new ProcessFailedException($process);
}
return response()->json(['success' => 'Export started successfully!']);

Abstract:
The function begins by querying the database based on request parameters. It then generates the SQL query and its bindings. Finally, using the Symfony Process, it runs the Python script, passing the SQL query and filename as arguments.

AJAX Request:
The AJAX request serves as the trigger for our export function.

$.ajax({
    // ... [AJAX setup and data parameters]
    success: function(response) {
        if (response.success) {
            console.log("Success:", response.success);
        }
    },
    error: function(error) {
        console.error("Error:", error);
    }
});

Abstract:
This asynchronous request sends data parameters to the server. Upon successful data export, it provides feedback to the user. In case of errors, they are logged for debugging.

Python Script (export.py):
This script is responsible for fetching and exporting the data.

import pandas as pd
import mysql.connector
import sys
import os
# ... [Database connection setup]
query = sys.argv[1]
filename = sys.argv[2]
df = pd.read_sql(query, cnx);
storage_path = os.path.join(os.path.dirname(__file__), '..', 'storage', 'app', 'public');
os.makedirs(storage_path, exist_ok=True);
df.to_excel(os.path.join(storage_path, filename), index=False);
cnx.close();

Abstract:
The script connects to the MySQL database using mysql.connector. It then fetches the data using Pandas based on the SQL query passed from Laravel. The data is saved as an Excel file in Laravel’s storage directory.

Conclusion:
By combining Laravel, Python, and Pandas, we achieve:

  • Speed: Rapid export of large datasets.
  • Flexibility: Harnessing the strengths of both PHP and Python.
  • Background Processing: Ensuring system responsiveness.

In web development, such integrative solutions can lead to enhanced efficiency and user satisfaction.

Pin