To analyze data fetched from an SQL query, the data needs to be imported in a way that is easy to read and analyze. In this article, we illustrate 2 methods used in exporting data from MySQL queries to an Excel sheet.

Method 1. Exporting data from MySQL queries using a Python script.

This method involves creating a Python script that will fetch the data and export it as an Excel file. See the commands below:

import pandas as pd
import pymysql

# MySQL connection parameters
mysql_host = 'localhost'
mysql_user = 'myuser'
mysql_password = 'mypassword'
mysql_db = 'mydatabase'
query = 'SELECT * FROM table WHERE condition;'

# Establish MySQL connection
conn = pymysql.connect(
    host=mysql_host,
    user=mysql_user,
    password=mysql_password,
    db=mysql_db
)

# Execute the query and fetch data into a DataFrame
df = pd.read_sql_query(query, conn)

# Close the database connection
conn.close()

# Save the DataFrame to an Excel file
excel_filename = '/home/data.xlsx'
df.to_excel(excel_filename, index=False)

print(f'Data exported to {excel_filename}')

Create a Python file named get_data.py with the above lines of code. Adjust the server connection details, the query, the path to save the data, and the resulting file name. Create this file on the server where the database is accessed.

Update and prepare the system for required package installations. Run the following commands:

sudo apt update    #update package repositories
sudo apt install python3 python3-venv python3-pip   #install python3, pip and virtualenv
python3 -m venv env    #create a virtual environment named env
source env/bin/activate    #activate the virtualenv

Once the above has been installed, create another file; name it requirements.txt and add the following dependencies.

cryptography==41.0.4
openpyxl==3.1.2
pandas==2.1.1
PyMySQL==1.1.0

To install the above dependencies, run the command:

pip install -r requirements.txt   #To install the above dependancies

Now that you have installed this, it’s time to execute the script.

python3 get_data.py   #This script will query the database with the given query, then save the output in the given path. Then from here, copy or download the file and proceed with the analysis.

Method 2: Save the data in a CSV file

To export the result of a MySQL query to an Excel file using the command line, you can use the SELECT INTO OUTFILE syntax in MySQL. However, this will export the data to a CSV file rather than directly to an Excel file. You can then open the CSV file in Excel and save it as an Excel file. To use this method, first access mysql command line. See the code:

mysql -u myuser -p -e "SELECT * FROM user WHERE condition" mydatabase > output.csv

In this case, the system saves the query results into a file named “output.csv”. Ensure to adjust the database username, query, and database name as needed.

Download the CSV file into your laptop.
Open Excel.
Go to the “Data” tab and select “Get Data” or “Import External Data”, then choose “Select Data Source” and proceed to select the CSV file.
Follow the Excel wizard to load the data, specify the delimiter, and import the data into Excel.
You can manipulate and analyze the data as needed once you load it into Excel
Finally, save the file as an Excel workbook (.xlsx) using Excel’s “Save As” feature and proceed to analyze the data as needed.