📘 Importing/Exporting Data (CSV & MySQL)

So far, all our data has lived only inside Python — the moment you close your program, everything vanishes! This chapter teaches you how to save data permanently (to CSV files or MySQL databases) and load it back whenever you need it. Real applications such as a school's result portal or a shop's billing software work the same way.

4.1 🌟 Introduction

Imagine you spend 2 hours in Python creating a DataFrame of 100 students' marks. You close your laptop. Next morning — all that data is GONE! Why? Because Python variables only exist while the program is running — this is called temporary memory (RAM).

graph LR
    PY["🐍 Python Program\nRunning..."]
    RAM["💭 Data in RAM\n(Temporary Memory)"]
    CLOSE["❌ Program Closes"]
    GONE["😱 Data LOST!"]

    PY --> RAM --> CLOSE --> GONE

    style RAM fill:#FF9800,color:#fff
    style GONE fill:#F44336,color:#fff

The Solution: Persistence! We save data to permanent storage — either a simple CSV file or a MySQL database — so it survives even after you close Python.

graph LR
    PY["🐍 Python\nDataFrame"]
    CSV["📄 CSV File\n(Permanent!)"]
    SQL["🗄️ MySQL Database\n(Permanent!)"]

    PY -->|"to_csv()"| CSV
    CSV -->|"read_csv()"| PY
    PY -->|"to_sql()"| SQL
    SQL -->|"read_sql()"| PY

    style CSV fill:#4CAF50,color:#fff
    style SQL fill:#2196F3,color:#fff

Real-World Analogy: Writing on a whiteboard is like storing data in RAM — erase the board (close Python), everything is gone. Writing in your notebook is like saving to a CSV file or MySQL database — it stays there forever, even after you close your bag!

4.2 📄 Transferring Data between .csv Files and DataFrames

What is a CSV file? 🤔

CSV = Comma Separated Values. It's the SIMPLEST way to store table data — just plain text where:

• Each line = one row
• Commas separate the columns

RollNo,Name,Marks
1,Amit,85
2,Neha,90
3,Raj,78

That's it! No fancy formatting — just text with commas. You can even open a CSV file in Notepad!

graph LR
    EXCEL["📊 Looks like Excel\nwhen opened in\nExcel/Sheets"]
    NOTEPAD["📝 Looks like plain text\nwhen opened in\nNotepad"]
    CSV["📄 SAME .csv FILE"]

    CSV --> EXCEL
    CSV --> NOTEPAD

    style CSV fill:#FF9800,color:#fff

4.2.1 Loading Data from CSV to DataFrames 📥

The function pd.read_csv() reads a CSV file and instantly converts it into a Pandas DataFrame!

Sample file: students.csv

RollNo,Name,Marks,City
1,Amit,85,Delhi
2,Neha,90,Mumbai
3,Raj,78,Delhi
4,Priya,92,Pune

# read_csv_basic.py
import pandas as pd

df = pd.read_csv("students.csv")
print(df)

Output:

   RollNo   Name  Marks    City
0       1   Amit     85   Delhi
1       2   Neha     90  Mumbai
2       3    Raj     78   Delhi
3       4  Priya     92    Pune

That's it — ONE line of code, and your entire CSV file is now a working DataFrame!

Important Parameters of read_csv():

Case 1: CSV file WITHOUT a header row

1,Amit,85
2,Neha,90
3,Raj,78

# no_header_csv.py
import pandas as pd

df = pd.read_csv("data.csv", header=None,
                  names=['RollNo', 'Name', 'Marks'])
print(df)

Output:

   RollNo   Name  Marks
0       1   Amit     85
1       2   Neha     90
2       3    Raj     78

What happens if you forget header=None?

# forgot_header.py
df = pd.read_csv("data.csv")      # header=None missing!
print(df)

Output:

   1  Amit  85
0  2  Neha  90
1  3   Raj  78

The first student's data (1, Amit, 85) has become the column names, and only 2 rows of data are left.

Case 2: Using a column as the row Index

# index_col_demo.py
import pandas as pd

df = pd.read_csv("students.csv", index_col='RollNo')
print(df)

Output:

         Name  Marks    City
RollNo
1        Amit     85   Delhi
2        Neha     90  Mumbai
3         Raj     78   Delhi
4       Priya     92    Pune

Instead of 0,1,2,3 as row labels, now RollNo itself becomes the index! Very useful for cleaner data access.

Case 3: Different separator (not comma)

Some files use semicolons ; or tabs \t instead of commas:

# custom_separator.py
import pandas as pd

df = pd.read_csv("data.txt", sep=';')     # Semicolon-separated file
df2 = pd.read_csv("data.tsv", sep='\t')   # Tab-separated file

df = pd.read_csv("marks.csv", header=None, names=['RollNo', 'Name', 'Marks'])

4.2.2 Storing DataFrame's Data to CSV File 📤

The function df.to_csv() does the OPPOSITE — it takes your DataFrame and saves it as a permanent CSV file!

# to_csv_basic.py
import pandas as pd

data = {
    'Name':  ['Amit', 'Neha', 'Raj'],
    'Marks': [85, 90, 78]
}
df = pd.DataFrame(data)

df.to_csv("output.csv")
print("File saved!")

The file output.csv will contain:

,Name,Marks
0,Amit,85
1,Neha,90
2,Raj,78

Notice the extra unnamed column with 0,1,2? That's the DataFrame's index being saved too — usually we DON'T want that!

The index=False Fix — MOST Important Parameter! ⭐

# to_csv_clean.py
df.to_csv("output.csv", index=False)

Now the file contains (clean!):

Name,Marks
Amit,85
Neha,90
Raj,78

Important Parameters of to_csv():

Handling Missing Values while Saving:

# na_rep_demo.py
import pandas as pd
import numpy as np

data = {
    'Name':  ['Amit', 'Neha', 'Raj'],
    'Marks': [85, np.nan, 78]      # Neha was absent!
}
df = pd.DataFrame(data)

df.to_csv("attendance.csv", index=False, na_rep='Absent')

File content:

Name,Marks
Amit,85.0
Neha,Absent
Raj,78.0

Instead of a blank or confusing "NaN" in the file, it now clearly says "Absent"!

Why 85.0 and not 85? As soon as a column contains a missing value (NaN), Pandas stores the whole column as decimal numbers, so the other marks are written as 85.0 and 78.0.

4.3 🗄️ Transferring Data between DataFrames and MySQL

Now let's connect our DataFrame directly to a MySQL database — the more powerful, professional way to store data (used by real companies!).

What you need before starting:

# required_imports.py
import pandas as pd
import mysql.connector

If these libraries are not installed yet, install them once from the terminal (sqlalchemy is needed in Section 4.4):

pip install pandas mysql-connector-python sqlalchemy

An engine also works for reading — pd.read_sql("SELECT * FROM Student", engine) — but a mysql.connector connection does not work for to_sql().

4.3.1 Bringing Data from MySQL Database into a DataFrame 📥

The function pd.read_sql() runs an SQL query and puts the results directly into a DataFrame!

Step-by-step process:

graph LR
    S1["Step 1\nConnect to MySQL\nmysql.connector.connect()"]
    S2["Step 2\nWrite SQL Query\n'SELECT * FROM table'"]
    S3["Step 3\npd.read_sql(query, conn)"]
    S4["Step 4\nDataFrame Ready!"]

    S1 --> S2 --> S3 --> S4

    style S3 fill:#FF9800,color:#fff
    style S4 fill:#4CAF50,color:#fff

# read_sql_basic.py
import pandas as pd
import mysql.connector

# Step 1: Connect to MySQL
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="SchoolDB"
)

# Step 2 & 3: Run query directly into a DataFrame
df = pd.read_sql("SELECT * FROM Student", conn)
print(df)

conn.close()

Output (assuming Student table has this data):

   RollNo   Name  Marks
0       1   Amit     85
1       2   Neha     90
2       3    Raj     78

Look how simple that is! Just ONE line (pd.read_sql()) does the job of connecting your database to a DataFrame — no need to manually fetch rows and build the DataFrame yourself!

Reading with a Filtered Query:

# read_sql_filtered.py
import pandas as pd
import mysql.connector

conn = mysql.connector.connect(
    host="localhost", user="root",
    password="yourpassword", database="SchoolDB"
)

# Only fetch students with Marks > 80:
df = pd.read_sql("SELECT * FROM Student WHERE Marks > 80", conn)
print(df)

conn.close()

read_sql() Parameters:

import pandas as pd
import mysql.connector

conn = mysql.connector.connect(host="localhost", user="root",
                                password="pass", database="SchoolDB")
df = pd.read_sql("SELECT * FROM Student", conn)
print(df)

4.3.2 Framing Flexible SQL Queries with User Data 🎯

Instead of hardcoding values in your SQL, you can build the query using variables (like user input) — making your program interactive!

# flexible_query.py
import pandas as pd
import mysql.connector

conn = mysql.connector.connect(
    host="localhost", user="root",
    password="yourpassword", database="SchoolDB"
)

# Take input from the user:
min_marks = int(input("Enter minimum marks to search: "))

# Build query using the variable (f-string):
query = f"SELECT * FROM Student WHERE Marks >= {min_marks}"

df = pd.read_sql(query, conn)
print(f"\nStudents scoring {min_marks} or above:")
print(df)

conn.close()

Sample Run:

Enter minimum marks to search: 80

Students scoring 80 or above:
   RollNo   Name  Marks
0       1   Amit     85
1       2   Neha     90

Another example — Filter by City entered by user (for a Student table that also has a City column):

# flexible_query_city.py
city = input("Enter city name: ")
query = f"SELECT * FROM Student WHERE City = '{city}'"
df = pd.read_sql(query, conn)
print(df)

How the query is built — step by step:

graph LR
    IN["⌨️ User types\nDelhi"]
    VAR["city = 'Delhi'"]
    FS["The f-string puts\nthe value of city\ninto the query text"]
    OUT["SELECT * FROM Student\nWHERE City = 'Delhi'"]
    SQL["pd.read_sql()\nruns this query"]

    IN --> VAR --> FS --> OUT --> SQL

    style FS fill:#FFE0B2,stroke:#FF9800
    style OUT fill:#C8E6C9,stroke:#4CAF50

Numbers vs text inside the query:

• A number goes in as it is: Marks >= {min_marks} becomes Marks >= 80.
• Text must be wrapped in single quotes inside the string: City = '{city}' becomes City = 'Delhi'. Without the quotes, MySQL thinks Delhi is a column name and gives an error like Unknown column 'Delhi' in 'where clause'.

4.4 📤 Exporting a DataFrame's Data as a Table in MySQL Database

The function df.to_sql() does the reverse of read_sql() — it takes your DataFrame and creates/updates a table in MySQL!

Important: For to_sql(), we typically use SQLAlchemy (a more powerful connection tool) instead of plain mysql.connector, because it handles data types automatically.

# to_sql_setup.py
import pandas as pd
from sqlalchemy import create_engine

# Create an "engine" — SQLAlchemy's special connection object:
engine = create_engine("mysql+mysqlconnector://root:yourpassword@localhost/SchoolDB")

Breaking down the connection string "mysql+mysqlconnector://root:yourpassword@localhost/SchoolDB":

Read it like a sentence: "use MySQL through mysql-connector, log in as root with password yourpassword, on localhost, and open SchoolDB".

Complete Example — Exporting a DataFrame to MySQL:

# to_sql_complete.py
import pandas as pd
from sqlalchemy import create_engine

# Step 1: Create the DataFrame
data = {
    'RollNo': [5, 6, 7],
    'Name':   ['Sita', 'Karan', 'Meera'],
    'Marks':  [88, 72, 95]
}
df = pd.DataFrame(data)

# Step 2: Create connection engine
engine = create_engine("mysql+mysqlconnector://root:yourpassword@localhost/SchoolDB")

# Step 3: Export DataFrame to MySQL table
df.to_sql('Student', engine, if_exists='append', index=False)

print("Data exported to MySQL successfully!")

to_sql() Parameters:

Understanding if_exists ⚠️

graph TD
    START["df.to_sql('Student', engine, if_exists=?)"]
    FAIL["'fail'\n❌ ERROR if\ntable exists"]
    REPLACE["'replace'\n💣 Deletes old table,\nmakes new one"]
    APPEND["'append'\n✅ Adds new rows,\nkeeps old data"]

    START --> FAIL
    START --> REPLACE
    START --> APPEND

    style FAIL fill:#9E9E9E,color:#fff
    style REPLACE fill:#F44336,color:#fff
    style APPEND fill:#4CAF50,color:#fff

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:pass@localhost/SchoolDB")
df.to_sql('Result', engine, if_exists='append', index=False)

📐 Complete Data Flow — The Big Picture

graph TD
    CSV["📄 CSV File"]
    DF["🐼 Pandas DataFrame\n(In Python Memory)"]
    SQL["🗄️ MySQL Database Table"]

    CSV -->|"pd.read_csv()"| DF
    DF -->|"df.to_csv()"| CSV
    SQL -->|"pd.read_sql()"| DF
    DF -->|"df.to_sql()"| SQL

    style DF fill:#FF9800,color:#fff
    style CSV fill:#4CAF50,color:#fff
    style SQL fill:#2196F3,color:#fff

The Four Key Functions — Master These!

📊 CSV vs MySQL — When to Use Which?

⚠️ Common Errors and Misconceptions

# Read CSV → DataFrame:
df = pd.read_csv("file.csv")
df = pd.read_csv("file.csv", header=None, names=['A','B','C'])   # No header
df = pd.read_csv("file.csv", index_col='ID')                     # Custom index

# Save DataFrame → CSV:
df.to_csv("file.csv", index=False)                    # Clean, no index column
df.to_csv("file.csv", index=False, na_rep='Absent')   # Replace NaN with text

# Read MySQL → DataFrame:
import mysql.connector
conn = mysql.connector.connect(host="localhost", user="root",
                                password="pass", database="SchoolDB")
df = pd.read_sql("SELECT * FROM Student", conn)

# Save DataFrame → MySQL:
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:pass@localhost/SchoolDB")
df.to_sql('TableName', engine, if_exists='append', index=False)

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) Which function is used to read a CSV file into a DataFrame? → pd.read_csv()

b) Which parameter of to_csv() prevents the extra index column from being saved? → index=False

c) What does if_exists='append' do in to_sql()? → Adds new rows to the existing table without deleting old data.

d) What does CSV stand for? → Comma Separated Values

e) Which function reads data from a MySQL table into a DataFrame? → pd.read_sql()

Q2: Short Answer [2 marks]

Q: Differentiate between if_exists='replace' and if_exists='append' in to_sql().

if_exists='replace' deletes the existing table completely and creates a brand new one containing only the current DataFrame's data — all previous data is lost. if_exists='append' adds the new DataFrame's rows to the existing table, keeping all the previous data intact. This is the safer and more commonly used option.

Q3: Program Writing [3 marks]

Q: Write a program to read data from "marks.csv" (with columns RollNo, Name, Marks) and display only students scoring above 75.

import pandas as pd

df = pd.read_csv("marks.csv")
print("Students scoring above 75:")
print(df[df['Marks'] > 75])

Q4: Program Writing [4 marks]

Q: Write a program to create a DataFrame of 3 students and save it to "students.csv" without the index column. Also handle any missing marks by writing "NA".

import pandas as pd
import numpy as np

data = {
    'Name':  ['Aman', 'Kavya', 'Rohan'],
    'Marks': [88, np.nan, 92]
}
df = pd.DataFrame(data)

df.to_csv("students.csv", index=False, na_rep='NA')
print("File saved successfully!")

Q5: Program Writing [4 marks]

Q: Write a program that connects to a MySQL database 'SchoolDB', reads data from the 'Student' table where Marks are greater than a user-given value, and displays it.

import pandas as pd
import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="SchoolDB"
)

marks = int(input("Enter minimum marks: "))
query = f"SELECT * FROM Student WHERE Marks > {marks}"

df = pd.read_sql(query, conn)
print(df)

conn.close()

✏️ Practice Problems

1. Write code to read "products.csv" and display the top 5 rows using head().

2. Create a DataFrame with 4 employees (Name, Department, Salary) and save it as "employees.csv" without the index column.

3. A CSV file "raw_data.csv" has no header row and contains ID, Item, Price. Write code to read it correctly with proper column names.

4. Write a program to read data from a MySQL table 'Products' where Price is less than a value entered by the user.

5. Write code to export a DataFrame df to a MySQL table called 'Inventory', replacing any existing table completely.

6. Explain the difference between read_csv() and read_sql(). When would you prefer one over the other?

7. Write a program that reads "attendance.csv", replaces missing values (NaN) with the text "Absent" while displaying, and saves an updated version.

8. Create a DataFrame and append its data safely to an existing MySQL table called 'Sales', ensuring old records are not deleted.

9. What happens if you use to_csv() without the index=False parameter? Show the difference with and without it using a short example.

10. Write a flexible program that asks the user for a city name and displays all matching student records from a MySQL 'Student' table.