ℹ️ 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.
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 > 40When 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.connectorlibrary 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
pymysqllibrary 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
