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. This is exactly how real apps like your school's result portal or a shop's billing software work! š¾
> [!TIP]
> **How to use these notes:** Focus on **`read_csv()`** and **`to_csv()`** first ā they're the most tested in board exams. Then move to MySQL sections. Every code example here is meant to be typed and run ā don't just read! šÆ
---
## 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)**.
```mermaid
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.
```mermaid
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
```
::: grid
::: card š¾ | Persistence | Data survives even after closing the program | Like saving a Word document ā it doesn't disappear!
::: card š | CSV File | Simple text file storing data in rows/columns | Like a basic Excel sheet, but plain text
::: card šļø | MySQL Database | Powerful, organised storage system | Like a bank's data storage ā highly reliable
::: card š | Import/Export | Moving data IN and OUT of Python | Import = bring data in, Export = save data out
:::
> **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!
```mermaid
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
```
```python
# 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()`:**
| Parameter | What it does | Example |
| :--- | :--- | :--- |
| `filepath` | Path/name of the CSV file (required!) | `"students.csv"` |
| `sep` | Character separating values (default is comma) | `sep=';'` for semicolon files |
| `header` | Which row is the column header | `header=0` (first row), `header=None` (no header) |
| `names` | Give your own column names | `names=['ID','Name','Marks']` |
| `index_col` | Which column to use as row labels | `index_col='RollNo'` |
---
**Case 1: CSV file WITHOUT a header row**
```
1,Amit,85
2,Neha,90
3,Raj,78
```
```python
# 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
```
> [!WARNING]
> **Forgetting `header=None` is a common mistake!**
> If your CSV has NO header row and you don't specify `header=None`, Pandas wrongly treats the FIRST DATA ROW as column names! You'd lose that row of actual data. Always check your CSV file first!
---
**Case 2: Using a column as the row Index**
```python
# 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:
```python
# 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
```
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a statement to read a CSV file 'marks.csv' which has no header row, and assign column names as RollNo, Name, Marks." ā **2-mark** question, very common!
> Answer:
> ```python
> 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!
```python
# 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! ā**
```python
# to_csv_clean.py
df.to_csv("output.csv", index=False)
```
**Now the file contains (clean!):**
```
Name,Marks
Amit,85
Neha,90
Raj,78
```
> [!WARNING]
> **`index=False` is the #1 most tested parameter!**
> Without it, your saved CSV file has an ugly extra column of row numbers (0,1,2...). ALWAYS add `index=False` unless you specifically need those numbers saved!
---
**Important Parameters of `to_csv()`:**
| Parameter | What it does | Example |
| :--- | :--- | :--- |
| `path_or_buf` | File name to save as (required!) | `"result.csv"` |
| `sep` | Separator character to use | `sep=';'` |
| `index` | Whether to save row numbers | `index=False` (recommended!) |
| `header` | Whether to save column names | `header=True` (default) |
| `na_rep` | What text to show instead of NaN | `na_rep='Absent'` |
**Handling Missing Values while Saving:**
```python
# 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
Neha,Absent
Raj,78
```
> Instead of a blank or confusing "NaN" in the file, it now clearly says "Absent"!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write code to save a DataFrame df to 'result.csv' without the index column." ā **2-mark** question, asked EVERY year!
> Answer: `df.to_csv("result.csv", index=False)`
---
## 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:**
```python
# required_imports.py
import pandas as pd
import mysql.connector
```
---
### 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:**
```mermaid
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
```
```python
# 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:**
```python
# 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:**
| Parameter | What it means |
| :--- | :--- |
| `sql` | The SQL query string to run |
| `con` | The connection object (from mysql.connector) |
| `index_col` | Optional ā column to use as DataFrame's index |
> [!IMPORTANT]
> **Board Exam Tip**
> "Write code to fetch all records from the 'Student' table into a DataFrame." ā **3-mark** question!
> Answer:
> ```python
> 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!
```python
# 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:**
```python
# 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)
```
> [!NOTE]
> **Why is this called "Flexible"? š§ **
> Because the SAME piece of code can answer DIFFERENT questions just by changing what the user types! Without user input, you'd need to rewrite the SQL query every single time you want different results. This makes real applications interactive!
> [!WARNING]
> **A Word of Caution ā SQL Injection!**
> Directly inserting user input into a query string (like above) can be risky in real-world applications ā a clever user could type something harmful instead of a normal value. For CBSE exams, this f-string method is accepted and expected. In professional coding, parameterised queries (using `%s`) are safer!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write a program to accept a minimum marks value from the user and display matching student records from MySQL." ā **4-mark** question!
> Key idea: Take input ā Build query string with the variable ā `pd.read_sql(query, conn)`
---
## 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.
```python
# 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:// username : password @ host / database_name
ā ā ā ā ā
Driver type root yourpassword localhost SchoolDB
```
---
**Complete Example ā Exporting a DataFrame to MySQL:**
```python
# 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:**
| Parameter | What it means | Common Values |
| :--- | :--- | :--- |
| `name` | Name of the table to write to | `'Student'` |
| `con` | The connection engine | SQLAlchemy engine |
| `if_exists` | What to do if the table already exists | `'fail'`, `'replace'`, `'append'` |
| `index` | Whether to include DataFrame index as a column | `index=False` (recommended!) |
---
**Understanding `if_exists` ā The Most Important Parameter! ā ļø**
::: grid
::: card ā | if_exists='fail' | Stops with an error if table already exists | Default ā safest but least useful
::: card š£ | if_exists='replace' | DELETES the old table completely, creates a NEW one | Use carefully ā you LOSE old data!
::: card ā
| if_exists='append' | ADDS new rows to the existing table (keeps old data) | Most commonly used ā safest for adding data!
:::
```mermaid
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
```
> [!WARNING]
> **`if_exists='replace'` is DANGEROUS!**
> This DELETES the entire existing table (including all old data!) and creates a fresh one with only your new DataFrame's data. In almost all real situations, you want `'append'` instead ā which safely ADDS your new rows without losing anything!
> [!IMPORTANT]
> **Board Exam Tip**
> "Write code to export a DataFrame df to a MySQL table named 'Result' without deleting existing data." ā **3-mark** question!
> Answer:
> ```python
> from sqlalchemy import create_engine
> engine = create_engine("mysql+mysqlconnector://root:pass@localhost/SchoolDB")
> df.to_sql('Result', engine, if_exists='append', index=False)
> ```
> Key point: use `if_exists='append'` to NOT delete existing data!
---
## š Complete Data Flow ā The Big Picture
```mermaid
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!**
| Function | Direction | Purpose |
| :--- | :--- | :--- |
| `pd.read_csv()` | CSV ā DataFrame | Load data FROM a CSV file |
| `df.to_csv()` | DataFrame ā CSV | Save data TO a CSV file |
| `pd.read_sql()` | MySQL ā DataFrame | Load data FROM a MySQL table |
| `df.to_sql()` | DataFrame ā MySQL | Save data TO a MySQL table |
---
## š CSV vs MySQL ā When to Use Which?
| Feature | CSV File | MySQL Database |
| :--- | :--- | :--- |
| **Setup** | Very simple ā just a text file | Needs a server running |
| **Speed for small data** | Fast | Slightly slower (network/connection overhead) |
| **Multiple users at once** | ā Difficult (file conflicts) | ā
Designed for this |
| **Filtering large data** | Loads everything, then filters in Python | Filters BEFORE loading (`WHERE` clause) ā much faster! |
| **Best for** | Quick exports, backups, small projects | Real applications, large data, multi-user systems |
> [!NOTE]
> **Why filter in SQL, not in Python? š§ **
> `pd.read_sql("SELECT * FROM Student WHERE Marks > 80", conn)` only fetches the matching rows from the database ā saving memory and time. Compare this to `pd.read_csv()` which loads the ENTIRE file first, then you filter afterwards in Python. For huge datasets, SQL filtering is much more efficient!
---
## ā ļø Common Errors and Misconceptions
| Mistake | What Goes Wrong | Fix |
| :--- | :--- | :--- |
| ā Forgetting `index=False` in `to_csv()` | Extra unnamed column of row numbers in the file | ā
Always add `index=False` |
| ā Not using `header=None` for header-less CSV | First data row becomes column names, data is lost | ā
Use `header=None, names=[...]` |
| ā `FileNotFoundError` | Wrong file name/path, or file in a different folder | ā
Check spelling and file location |
| ā Using `if_exists='replace'` casually | Deletes ALL existing data in that table! | ā
Use `'append'` unless you truly want to reset the table |
| ā Forgetting to import `mysql.connector` | NameError: name not defined | ā
Always `import mysql.connector` at the top |
| ā Wrong MySQL password/host in connection | `InterfaceError` or Access Denied | ā
Double-check credentials |
| ā Not closing the MySQL connection | Wastes server resources | ā
Always call `conn.close()` at the end |
---
## š Quick Revision ā Exam Ready!
**CSV Functions:**
```python
# 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
```
**MySQL Functions:**
```python
# 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)
```
**The Golden Rules:**
- **CSV Reading:** No header row? ā Add `header=None, names=[...]`
- **CSV Saving:** Always add `index=False` unless you need row numbers saved
- **MySQL Reading:** Use `mysql.connector` + `pd.read_sql()`
- **MySQL Saving:** Use `sqlalchemy` + `df.to_sql()`, always prefer `if_exists='append'`
---
## šÆ 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.**
```python
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".**
```python
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.**
```python
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.
Back to List
Calculating...
UNIT 1 : CH 4
Dec 14, 2025
š Importing/Exporting Data (CSV & MySQL)
Learning Support
Need Help With This Chapter?
Save key topics for exam revision, ask questions to teachers, or submit content corrections.
Verified Doubts & Teacher Answers
Loading resolved questions for this note...