How to Export MySQL to a CSV (Dashboard+Python)

5/5 - (9 votes)

This article shows you how to export a Database Table to a CSV file using MySQL.

ℹī¸ SQL, an acronym for Structured Query Language, is the language of the Database! This standardized language is used to manage databases, tables and the data they hold. A must-have in the coding world.

This language allows coders to select a database, a table, query data, export data and much more! The possibilities are endless! This language can be called within phpMyAdmin or directly from a Python script.

📖 Story: Let’s say you have taken over for Sven, the IT Manager at Ignite: a used car dealership in Medford, Oregon. Ignite wants the details of all cars in their inventory. They have asked you to export this data from their server into a CSV file. How can you accomplish this? Follow along with our article to discover how!

This article assumes you have phpMyAdmin installed and a database and table to work with.


  1. Overview
  2. Use phpMyAdmin and Export Tab
  3. Use phpMyAdmin and the SQL Tab
  4. Use mysql.connector
  5. Use pymysql

Overview

Before you export the data, let’s look at how and where this data is stored. As you can see from the image below, this data is being held, for this example, in the inventory table of the ignite_db database in phpMyAdmin.

ℹī¸ phpMyAdmin is a 3rd party tool that manages databases, tables and the data within. It is used in administering MySQL across the web, is free to use, and can be installed locally. Upon installation, keep the URL and login credentials handy, as they will be needed later.

If you have phpMyAdmin installed, navigate to and log in. From the home page, click to select the ignite_db database.

With ignite_db selected, click the Browse hyperlink on the right-hand side.

This action opens and populates the grid to display rows of data from the inventory table. These results will be paginated if the table contains more than 25 rows.

💡 Note: By default, the number of rows/page is 25. Click the Number of rows dropdown to modify.


Use phpMyAdmin and Export Tab

The data from the inventory table can be exported into a CSV file directly from phpMyAdmin using the Export Tab. You can’t see it from the interface; however, this uses MySQL to extract the data from the inventory table.

From phpMyAdmin, click the Export Tab.

Click the Format dropdown box to display a list of available file types. For this example, select CSV to export the data as a CSV file.

Click the Go button to execute. This action displays the Save As dialogue box.

Select a location and file name from this dialogue box, or accept the default values. Click the Save button to complete this task.

For this example, this file was saved as inventory.csv to the desktop. When opened, the data from the inventory table displays (snippet of actual file).


Use phpMyAdmin and the SQL Tab

What happens if you want to use phpMyAdmin and export a filtered result? You can accomplish this by using the SQL Tab and running a MySQL query.

From phpMyAdmin, click the SQL Tab. By default, a query is already started for you. Clear the contents.

Enter the query shown below, which asks the question…
Give me all records in the inventory table where the car’s MPG is greater than 40.

SELECT * FROM inventory WHERE MPG > 40

When creating a query, to select a Column name, double-click the desired name in the Columns list or manually enter it. Once the query is complete, click the Go button to execute.

This query results in the following output of nine (9) records.

To export these results, scroll down the page until you see the Export hyperlink. Click this hyperlink.

This action forwards you to the Export Tab (outlined earlier). Complete the same steps above to export the filtered data to a CSV file.

If successful, the exported CSV file, inventory_filtered.csv, should now contain the data shown below.


Use mysql.connector

This section shows you how to filter and export data from a MySQL database to a CSV file using mysql.connector and Python.

Before we start, two (2) libraries require installation:

  • The mysql.connector library which allows access to the database and table created earlier.
  • The pandas library which allows access to the CSV writer.

Navigate to an IDE and run the following code:

pip install mysql.connector
pip install pandas

Next, copy and paste the code below into a blank Python file, modify the credentials, and save.

import mysql.connector
from mysql.connector import errorcode
import csv

mpg_hdr = ['Cars', 'MPG', 'Cylinders']

try:
    con = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='ignite_db')
except mysql.connector.Error as err:
    print(err)
    exit()

cursor = con.cursor()
mpg_query = ('SELECT Car, MPG, Cylinders FROM inventory WHERE MPG > 40')
cursor.execute(mpg_query)

with open('filtered.csv', 'w', newline='') as fp:
    writer = csv.writer(fp)
    writer.writerow(mpg_hdr)

    for (Car, MPG, Cylinders) in cursor:
        writer.writerow([Car, MPG, Cylinders])

con.close()

Import Libraries

The first three (3) lines in the above code snippet import the libraries and functions required to successfully run this code.

Declare Variable

This line creates a list containing the three (3) column names to be used as the header row for the CSV file. The results save to the variable mpg_hdr.

Try/Except

This section uses the try/except statement to attempt to connect to ignite_db using the credentials passed. If an error occurs, this code falls into the except section where a message outputs to the terminal indicating the error, and the script ends.

1044 (42000): Access denied for user ''@'localhost' to database 'ignite_db'

If successful, the script continues.

Create a Cursor

The first line in this section creates a cursor() class. This allows the code to execute queries against the ignite_db database and/or the inventory table. The following line creates this query, saves it to mpg_query, and executes it on the next line.

Write Results to CSV

This section opens a file in write (w) mode and saves this as a File Object, fp. The File Object allows access to the filtered.csv file. If fp was output to the terminal, an Object similar to the one below displays.

<_io.TextIOWrapper name='filtered.csv' mode='w' encoding='utf8'>

Next, the header columns for the CSV file are written to this file using the mpg_hdr variable. Then a for loop is instantiated to iterate through each query result, format the row data, and append it as a list to the CSV file.

Once complete, the connection is closed and the script ends.

Output

If successful, the filtered.csv file resides in the current working directory containing the following query results.


Use pymysql

This section shows you how to filter and export data from a MySQL database to a CSV file using pymysql and Python.

Before we start, one (1) library requires installation:

  • The pymysql library which allows access to the database and table created earlier.

Navigate to an IDE and run the following code:

pip install pymysql

Next, copy and paste the code below into a blank Python file and save.

Run this code to connect and export the inventory table as a CSV file to the current working directory. Change the details as needed.

import pymysql
import csv

conn_details = {'user':'rot', 'password':'', 'host':'127.0.0.1', 'database':'ignite_db'}
conn = pymysql.connect(**conn_details)
cur = conn.cursor()

all_cars = 'SELECT * from inventory'
cur.execute(all_cars)
rows = cur.fetchall()

with open('inventory.csv', 'w', newline='') as fp:
    myFile = csv.writer(fp)
    myFile.writerows(rows)
conn.close()

Import Libraries

The first two (2) lines in the above code snippet import the libraries required to successfully run this code.

Connect & Create Cursor

The first line in this section saves the credentials needed to connect to ignite_db. The result saves to conn_details. The following line uses these credentials to connect. If successful, the result saves to conn. The following line creates a cursor() class allowing the code to execute queries against the ignite_db database and/or the inventory table.

Execute Query

The first line in this section declares the query, all_cars. The following line executes this query. Then, the next line fetches the query results and saves them to the variable rows.

Write Results to CSV

This section opens a file (inventory.csv) in write (w) mode and saves this as a File Object, fp. The File Object allows access to the inventory.csv file. If fp was output to the terminal, an Object similar to the one below displays.

<_io.TextIOWrapper name='inventory.csv' mode='w' encoding='cp1252'>

The following two (2) lines write the query results to inventory.csv, and the script ends.

Output

If successful, the inventory.csv file resides in the current working directory containing the following query results.


Summary

This article has shown four (4) ways to export MySQL to a CSV to select the best fit for your coding requirements.

Good Luck & Happy Coding!


Programmer Humor – Blockchain

“Blockchains are like grappling hooks, in that it’s extremely cool when you encounter a problem for which they’re the right solution, but it happens way too rarely in real life.” source xkcd