Connecting a Live MySQL Northwind Database with Python Pandas Using MySQL Connector : Part 1

Categories: Python Pandas Tutorial

Tags:

Connecting a Live MySQL Northwind Database with Python Pandas Using MySQL Connector : Part 1

By Ankit Srivastava – Slidescope Institute

Introduction

Data analytics begins with one essential step—connecting to a reliable data source. While CSV and Excel files are excellent for learning, real-world businesses store their information inside relational database management systems such as MySQL. Sales records, customer information, inventory, employee data, and order history are continuously updated inside these databases.

In this tutorial, Ankit demonstrates how to establish a live connection between a MySQL database and Python using the mysql-connector-python library. Once connected, you’ll use Pandas to retrieve data directly from MySQL, perform quick exploratory analysis, and prepare the dataset for visualization.

For this tutorial, we’ll use the popular Northwind Database, a sample business database that contains customers, products, suppliers, orders, order details, employees, and shipping information. It is widely used for learning SQL, Business Intelligence, Power BI, and Data Analytics.

Instead of exporting CSV files every time the database changes, you’ll work with live data, making your analysis faster, more accurate, and suitable for real-world projects.

Prerequisites

Before writing any code, install the following software.

Python 3.x
MySQL Server
MySQL Workbench
VS Code or Jupyter Notebook
Northwind Database

Required Python Libraries

Open Command Prompt and install the required packages.

pip install pandas
pip install mysql-connector-python
pip install matplotlib
pip install seaborn

Or install everything together.

pip install pandas mysql-connector-python matplotlib seaborn

Import Required Libraries

import pandas as pd
import mysql.connector

Understanding mysql.connector

The mysql.connector library is the official MySQL driver for Python.

It allows Python applications to

Connect with MySQL Server
Execute SQL queries
Read tables
Insert new records
Update existing data
Delete records
Perform transactions

Pandas then converts SQL query results into DataFrames, making analysis extremely simple.

Database Connection Details

Assume your MySQL server contains the following database.

Parameter	Value
Host	localhost
Port	3306
Username	root
Password	your_password
Database	northwind

Replace the username and password with your own credentials.

Creating the Connection

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="northwind"
)

print("Database Connected Successfully")

Output

Database Connected Successfully

Checking Connection Status

if connection.is_connected():
    print("Connected Successfully")
else:
    print("Connection Failed")

Loading Data into Pandas

Now let’s retrieve the Customers table.

import pandas as pd

query = "SELECT * FROM customers"

customers = pd.read_sql(query, connection)

print(customers.head())

Sample Output

CustomerID CompanyName ContactName Country

ALFKI Alfreds Futterkiste Maria Anders Germany
ANATR Ana Trujillo Emparedados Ana Trujillo Mexico
ANTON Antonio Moreno Antonio Moreno Mexico
AROUT Around the Horn Thomas Hardy UK
BERGS Berglunds snabbkop Christina Berglund Sweden

Notice that the SQL query directly returns a Pandas DataFrame.

Viewing Dataset Information

customers.info()

Output

RangeIndex: 91 entries

Columns: 11

CustomerID
CompanyName
ContactName
City
Country
Phone

Display First 10 Records

customers.head(10)

Display Last Records

customers.tail()

View Column Names

customers.columns

Count Rows and Columns

customers.shape

Example Output

(91,11)

Meaning

91 Rows
11 Columns

Read Selected Columns Only

Instead of loading the complete table, retrieve only important fields.

query = """
SELECT
CustomerID,
CompanyName,
Country
FROM customers
"""

customers = pd.read_sql(query, connection)

customers.head()

Read Employee Table

employees = pd.read_sql(
    "SELECT * FROM employees",
    connection
)

employees.head()

Read Products Table

products = pd.read_sql(
    "SELECT * FROM products",
    connection
)

products.head()

Read Orders Table

orders = pd.read_sql(
    "SELECT * FROM orders",
    connection
)

orders.head()

Executing SQL with Filters

Retrieve only customers from Germany.

query = """
SELECT *
FROM customers
WHERE Country='Germany'
"""

germany = pd.read_sql(query, connection)

germany.head()

Sorting Records

query = """
SELECT *
FROM products
ORDER BY UnitPrice DESC
"""

products = pd.read_sql(query, connection)

products.head()

Limiting Records

query = """
SELECT *
FROM orders
LIMIT 20
"""

orders = pd.read_sql(query, connection)

Aggregate Query

Count customers by country.

query = """
SELECT
Country,
COUNT(*) AS TotalCustomers
FROM customers
GROUP BY Country
ORDER BY TotalCustomers DESC
"""

country = pd.read_sql(query, connection)

print(country)

Example Output

USA          13
Germany      11
France       11
Brazil        9
UK            7

Joining Multiple Tables

One of the biggest advantages of SQL is joining related tables.

query = """
SELECT

o.OrderID,
c.CompanyName,
o.OrderDate

FROM orders o

INNER JOIN customers c

ON o.CustomerID = c.CustomerID

LIMIT 15
"""

orders = pd.read_sql(query, connection)

orders.head()

Loading Order Summary

query = """

SELECT

o.OrderID,
c.CompanyName,
e.FirstName,
e.LastName,
o.OrderDate,
o.ShipCountry

FROM orders o

JOIN customers c
ON o.CustomerID=c.CustomerID

JOIN employees e
ON o.EmployeeID=e.EmployeeID

"""

orders = pd.read_sql(query, connection)

orders.head()

This single SQL query combines three different tables into one analytical dataset.

Basic Data Exploration

Total Customers

len(customers)

Unique Countries

customers["Country"].nunique()

Country Names

customers["Country"].unique()

Count Missing Values

customers.isnull().sum()

Closing Database Connection

Always close the connection once your work is completed.

connection.close()

print("Connection Closed")

Complete Program

import pandas as pd
import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="northwind"
)

query = """
SELECT
CustomerID,
CompanyName,
Country
FROM customers
"""

customers = pd.read_sql(query, connection)

print(customers.head())

print(customers.shape)

print(customers.info())

connection.close()

Part 1 Summary

In this tutorial, you learned how to connect Python to a live MySQL database using the official MySQL Connector library. You explored the Northwind database, executed SQL queries, loaded results into Pandas DataFrames, filtered records, joined multiple tables, and performed basic exploratory analysis. This workflow forms the foundation for modern data analytics and business intelligence projects.

In Part 2 we will focus on transforming this live database into meaningful visualizations using Matplotlib and Seaborn, where you’ll build bar charts, pie charts, line charts, histograms, scatter plots, and business dashboards directly from MySQL data.