Python DataFrames

Use Python to export data from a MySQL database to a CSV using a Pandas DataFrame

This guide provides instructions on how to use Python to connect to a MySQL database, convert a set of database table entries to a Pandas DataFrame and finally, export the results to a CSV file.

Prerequisites

Python 3- the latest version of Python

pip- a package installer for Python

Access to a MySQL database

Installing Python

Follow the steps outlined in Python’s official release notes to download and install the latest release for your operating system:

Mac- https://www.python.org/downloads/macos/

Windows- https://www.python.org/downloads/windows/

Linux- https://www.python.org/downloads/source/

Other- https://www.python.org/download/other/

Note: If you already have Python installed, ensure you are running version 3.7 or later at a minimum. At the time of writing the latest version of Python was 3.11.0.

Installing pip

pip is installed with the official version of Python. If you require a standalone installation, follow the instructions outlined in the official documentation that can be found here: https://pip.pypa.io/en/stable/installation/

Check which version of pip is installed by running the command:

pip --version

Note: At the time of writing the latest version of pip was 22.3.1.

Dependencies

The following libraries will need to be installed in order to complete the task:

Pandas- a popular Python library used to analyse data
MySQLConnector- a self-contained Python driver used to communicate with MySQL databases

Use pip to install both libraries by running these two commands:

pip install pandas

pip install mysql.connector

Running the script

The Python script below will complete the following steps:

Connect to a local MySQL database named Users, authenticating with a defined username and password.
Run a query to access all records from the userdetails table located in the Users database once a connection has been established.
Create a Pandas DataFrame from the query results in step 2.
Print an error if there are any issues connecting to the database.
Export the Pandas DataFrame results to a CSV file saved to local disk with a filename of usersexport.csv

The following variables are defined in the script and should be modified to suit your environment as required:

Detail	Description	Code
Host	Hostname of the MySQL server. Can be defined using hostname or IP address	`localhost`
Database	Name of the database to connect to	`Users`
User	Username of account to access the database	`admin`
Password	Password of the account used to access the database	`admin`
Table name	Name of database table containing data to be exported	`userdetails`
CSV Filename	File name of the exported CSV file saved to your local disk	`usersexport.csv`

import mysql.connector as connector 
import pandas as pd 
try: 
  mydb = connector.connect(host="localhost", database = 'Users',user="admin",passwd="admin") 
  query = "Select * from userdetails;" 
  result_dataFrame = pd.read_sql(query,mydb) 
  mydb.close() 

except Exception as e: 
  mydb.close() 
  print(str(e))

result_dataFrame.to_csv('usersexport.csv')

Kevin Moran