Article Outline

Recently, I started working on a Power BI project where I am using Python for data preparation and database transformations. This is a completely new experience for me, and I’m really excited to explore this space. Through this project, I’ve already started learning how data flows from a database to analytics tools like Power BI, and how backend processing plays a crucial role in building reliable dashboards.

In this blog, I’ll walk you through the end-to-end setup, including prerequisites, database creation, and Python integration with MySQL.

Project Prerequisite

Install MYSQL

Install Python

Install Visual Studio Code

Database Creation

Create Database

CREATE DATABASE codewithtechelliptica;

Create Table

CREATE TABLE students(

id SERIAL PRIMARY KEY,

name VARCHAR(1OO) NOT NULL,

phone INT (10) NOT NULL);

Python Package - MYSQL-connector-python

In order to connect with Database, Python need MYSQL-connector-python package

pip install MYSQL-connector-python

if install : show install successfully ! if you does not put path in environment variable. otherwise you will give Error

Python Code - Database connection

Open Visual Studio

create file mysql_connect_python.py with py extension

Note: extension: py (it is very important otherwise program can not run)

import mysql.connector

from mysql.connector import Error

#Create connection python with MySQL

try:

connection=mysql.connector.connect(

host='localhost',

user='root',

password='@1234',

database='codewithtechelliptica')

if connection.isconnect():

print("Connection create Successfully!")

#Fetch Data using cursor (pointer)

my_cursor=connection.cursor()

my_cursor.execute("SELECT DATABASE();")

#Select all database we used (Fetchone / Fetchall)

db=my_cursor.fetchone()

print(f"Connect to database:{db[0]}")

# Fetch all records from students table

my_cursor.execute("SELECT * FROM students")

rows = my_cursor.fetchall()

# Print all rows

for row in rows:

print(row)

#The program check the first condition.

#if it is satisfied the program stop there.

#if the condition fails its gives an error and goes to except

except Error as e:

print(f"Error:{e}")

#finally:

#The finally block always runs

#It executes whether an error occurs or not

finally:

if connection.is_connected():

connection.close()

print("Connection closed")

Key Points

fetchone() - returns only one row

fetchall() - returns all rows as a list of tuples

Using Pandas DataFrame with MySQL for Power BI

When working on my project with Power BI, I realized that just using SQL queries is not always enough. For better data cleaning and transformation, I started using Pandas in Python.

Why we use Pandas

Convert database data into a table format (DataFrame)

Clean and filter data easily

Handle large datasets

Prepare data for Power BI

Installing Python Package - Pandas-connector-python

In order to work with MySQL data and prepare it for Power BI, Python needs the Pandas package.

pip install pandas

Note: install : show install successfully ! if you does not put path in environment variable. otherwise you will give Error

Import Pandas in Python File

After installing Pandas, Imported the required libraries in Python.

import mysql.connector

import pandas as pd

Key Points

mysql.connector - to connect Python with MySQL

pandas - to work with data in table format (DataFrame)

Read Data from File – Pandas

If you have MYSQL database file and want to read it in Python using Pandas, you can do.

import pandas as pd

# SQL query

query = "SELECT * FROM students"

# Load data into pandas DataFrame

df = pd.read_sql(query, connection)

# Show data

print(df)

# Close connection

connection.close()

Key Points

df - Contains all the data from the file in table format.

pd.read_sql() - pandas function used to directly fetch data from a SQL database into a DataFrame.

connection - MYSQL connection object

Complete Python Code

First, I connected Python to MySQL using mysql-connector-python. Then, I got the data from the database and put it into a Pandas DataFrame. This made it very easy to read, clean, and prepare the data for analysis or for visualization tools like Power BI.

create file mysql_connect_python.py with py extension

# Import required libraries

import mysql.connector

from mysql.connector import Error

import pandas as pd

# Try block to handle errors

try:

connection=mysql.connector.connect(

host='localhost', #database host

user='root', #database user

password='pass@123', #database password

database='codewithvijay', #database name

)

if connection.is_connected():

print("Connected to MYSQL Successfully!") # confirmation message

# Create cursor to execute MySQL queries

my_cursor=connection.cursor()

# Check which database is connected

my_cursor.execute(f"SELECT DATABASE();")

db=my_cursor.fetchone()

print(f"Connected to database:{db[0]}") # show database name

# SQL query

query = "SELECT * FROM students"

# Read sql file into Pandas DataFrame

df = pd.read_sql(query, connection)

# Show data

print(df)

# Close connection

connection.close()

except Error as e:

print(f"Error while connecting to MYSQL:{e}")

finally:

if connection.is_connected():

my_cursor.close()

connection.close()

print("MYSQL connection is closed!")

Well using this project i learn many new thing like how mysql-connector and pandas libraries help us in data cleaning and setting up transformation rules. and i create one Github Repository .

Kindly Refer my Github Repository link :

https://github.com/aavijaywx/CSV-PANDAS-MYSQL-INTEGRATION.git

Promise in Javascript - A Comprehensive Guide

Callback function in JavaScript

Stop Writing Syntax: Welcome to the Era of AI-Driven Development (AIDD)

Handling HTML5 Native Validation Messages in Playwright

Python - Constant is a myth. We all are adults here

Understanding Python Collection Symbols: A Simple Memory Technique

Decorator in Python

Meet the Computer 13,000× Faster Than Today’s Machines — Google’s Willow Chip Runs OTOC Quantum Echoes

Test Report Metrics - Real Face of QA Process

𝗦𝗲𝘁𝘂𝗽 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗿𝗲𝗱𝗲𝗻𝘁𝗶𝗮𝗹𝘀 𝗢𝗔𝘂𝘁𝗵 𝟮.𝟬 𝗶𝗻 𝗼𝗿𝗱𝗲𝗿 𝘁𝗼 𝗮𝗰𝗰𝗲𝘀𝘀 𝗚𝗼𝗼𝗴𝗹𝗲 𝗦𝗵𝗲𝗲𝘁𝘀 𝗔𝗣𝗜𝘀