Class 12 Informatics Practices 065 Ch 4 Importing Exporting Data Between CSV Files MySQL and Pandas Sumita Arora Book Exercise Solution

Type C: Programming Practice/Knowledge-based Questions

---

1. Write a program to read details such as Items, and Sales made in a DataFrame and then store this data in a CSV file.

Ans:

#Que – 1

import pandas as pd

Cars = {‘Item’:[‘Alto’, ‘Zen’, ‘Kia’, ‘Breeza’],

        ‘Sales_Made’ : [30000, 12000, 25000, 1500]}

df = pd.DataFrame(Cars)

print(df)

df.to_csv(“D:\\PythonProg\\Class 12 IP\\Item.csv”)

2. Write a program to read data from a CSV file where separator character is ‘@’. Make sure that:

• the top row is used as data, not as column headers.
• only 20 rows are read into dataframe.

Ans:

#Que – 2

import pandas as pd

df = pd.read_csv(“D:\\PythonProg\\Class 12 IP\\two.csv”, header = None, sep=’@’)

print(df)

3. Write a program to get the following data in two DataFrame:

dfl		df2
Roll no	Name	Roll no	Marks 1	Marks 2	Marks 3
1	ABC	1	70	80	75
2	DEF	2	60	65	70
.		.
.		.

Store these DataFrames as two separate tables in the same database.

Ans:

#Que 3

import pandas as pd

import pymysql

from sqlalchemy import create_engine

stud = {‘RollNo’ : [1, 2, 3],

        ‘Name’ : [‘ABC’, ‘DEF’, ‘GHI’]}

marks = {‘RollNo’: [1, 2, 3],

         ‘Marks1’: [70, 60, 50],

         ‘Marks2’: [45, 65, 85],

         ‘Marks3’: [75, 85, 80]}

df1 = pd.DataFrame(stud)

df2 = pd.DataFrame(marks)

engine = create_engine(‘mysql+pymysql://root:MyPass@localhost/School’)

mycon = engine.connect()

df1.to_sql(‘Student’, mycon, if_exists = ‘replace’, index = False)

df1.to_sql(‘Marks’, mycon, if_exists = ‘replace’, index = False)

4. You have a database on MySQL namely school having three tables in it — Student, Subject, Teacher. Write a program to store these tables in three dataframes.

Ans:

5. The Dataframe SDF stores the sales records of 100 salesmen. Write a program to store this as a table in the database namely “company” on MySQL.

Ans:

6. Consider the SDF dataframe storing the sales records of 100 salesmen, write a program that stores only the first 25 rows of the dataframe in a table on MySQL database.

Ans:

7. The sales table of the company database of MySQL stores the sales records of 100 salesmen. Write a program to load only those records in the dataframe which have made sales more than Rs 50000/-.

Ans: