UNIT – V – ANSWER KEY

Data types and objects, loading packages, namespaces, Reading and writing data, Simple
plotting, Control flow, Debugging, Code profiling, Acquiring Data with Python:Loading from
CSV files, Accessing SQL databases, Cleansing Data with Python:Stripping out extraneous
information, normalizing data, Formatting data.

Q. Questions Mark
No s
.
1 What are CSV Files? 2

CSV (Comma Separated Values) is a simple file format used to store

tabular data, such as a spreadsheet or database in plain text. Each line
of the file is a data record. Each record consists of one or more fields,
separated by commas.

The use of the comma as a field separator is the source of the name
for this file format.For working CSV files in python, there is an
inbuilt module called csv.

SN Name City

1 Michael New Jersey

2 Jack California

3 Donald Texas

We can represent the above table in CSV as :

SN, Name, City
1, Michael, New Jersey
2, Jack, California
3, Donald, Texas

2 What are namespaces? 2

● A namespace is a simple system to control the names in a

program. It ensures that names are unique and won't lead to
any conflict.
● A namespace is a system that has a unique name for each and
every object in Python. An object might be a variable or a
 method.
● A namespace is a collection of currently defined symbolic
names along with information about the object that each name
references. You can think of a namespace as a dictionary in
which the keys are the object names and the values are the
objects themselves

3 Define Profiling in Python. 2

Python provides many excellent modules to measure the statistics of

a program. This makes us know where the program is spending too
much time and what to do inorder to optimize it. It is better to
optimize the code inorder to increase the efficiency of a program. So,
perform some standard tests to ensure optimization and we can
improve the program inorder to increase the efficiency.

Using line_profiler module

Python provides a built-in module to measure execution time and the

module name is LineProfiler.It gives detailed report on time
consumed by a program.

Example:
# importing line_profiler module

from line_profiler import LineProfiler

  
def geek(rk):
    print(rk)
  
rk ="geeks"
profile = LineProfiler(geek(rk))
profile.print_stats()

Output:

Timer unit: 4.27198e-10 s

4 Write the syntax in python to establish database connectivity. 2

#Importing the module

 importMySQLdb

#Open database connection

conn
object=MySQLdb.connect("host","username","password","database"
)

5 How to insert a row into a table? Give its syntax. 2

INSERT INTO TABLE_NAME (column1,

column2,column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Following query inserts a record into the table named EMPLOYEE.

INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE,
SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000);

6 How to read and write CSV files in Python? 5

How to read a CSV File in Python?

Python has inbuilt module i.e CSV, which is used for working with
CSV files. In python, we use csv.reader module to read a CSV file.
Example : Reading a CSV file using csv.reader

Here we are going to show how you can read a people.csv file, that
we created in above example.

import csv

withopen('people.csv','r')as csvFile:
reader =csv.reader(csvFile)
for row in reader:
  print(row)

Once we run it on the python console, it will show following result:

['SN', 'Name', 'City']

['1','Michael', 'New Jersey']
['2','Jack', 'California']
['3','Donald', 'Texas']
 In above program, First, we open our CSV file as csvFile.

Then, we used the csv.reader() method to extract the data into

the reader object which we can iterate over to get each line of our
data.

How to write in a CSV File in Python?

In python, we use csv.Writer module to write in a csv file.

Example: Writing in a CSV file using csv.writer

Here we are going to show how you can write to in a csv file. We
will output some data stored in a python list into a csv file.
import csv

csvData = [['Name of Person','Age'],['Peter','22'],['Jasmine','21'],

['Sam','24']]
withopen('person.csv','w')as csvFile:
writer =csv.writer(csvFile)
writer.writerows(csvData)

When we run the above program, person.csv will contain the

following data
Name of Person, Age
Peter, 22
Jasmine, 21
Sam, 24

7 What is Python Matplotlib? Briefly tell about different types of 5

plotting defined in the library.

Matplotlib is arguably the most popular graphing and data

visualization library for Python.
● Mat−plotlib (plotting library)
● Matplotlib is a python library used to create 2D graphs and
plots by using python scripts.
● It has a module named pyplot which makes things easy for
plotting by providing feature to control line styles, font
properties, formatting axes etc.
● It supports a very wide variety of graphs and plots namely -
histogram, bar charts, power spectra, error charts etc.
Installation

Easiest way to install matplotlib is to use pip. Type following

command in terminal:

pip install matplotlib

C:\Users\lifei>pip install matplotlib

Some plots it will let us build are:

1. Plots (Linechart) : line chart can be created using

the Matplotlib plot() function. While we can just plot a line,
we are not limited to that. We can explicitly define the grid, the
x and y axis scale and labels, title and display options.

Syntax : object.plot(parameters)

2. Histograms :The histogram represents the frequency of

occurrence of specific phenomena which lie within a specific
range of values and arranged in consecutive and fixed intervals

Syntax : object.hist(parameters).

3. Bar charts : The bar graphs are used in data comparison where
we can measure the changes over a period of time. It can be
represented horizontally or vertically. Longer the bar it has the
greater the value it contains.

Syntax : object.bar(parameters).

4. Scatter Plots :Scatterplots show many points plotted in the

Cartesian plane. Each point represents the values of two
variables. One variable is chosen in the horizontal axis and
another in the vertical axis

Syntax : object.scatter(parameters).

5. Pie Chart : A pie chart is a circular graph which is divided into

segments or slices of pie. It is used to represent the percentage
or proportional data where each slice of pie represents a
category.
 Syntax : object.pie(parameters).
8 Create a table name student with fields reg no, name, marks and 5
grade and insert 2 rows in it and display it.

create table student (reg no int, name varchar(255),mark int, grade

varchar(5))")
insert into student values(191611101,”ashok”,76,”b”)
insert into student values(191611102,”ashwin”,84,”a”)

9 What are the various database operations? Explain with an example. 5

Refer Question No 18.

10 Briefly explain with an example , how to insert records into a table. 5

Program

importMySQLdb
# Open database connection

db=MySQLdb.connect("localhost","testuser","test123","TESTDB")
# prepare a cursor object using cursor() method
cursor =db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql="""INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:

# Execute the SQL command

cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback() # disconnect from server
db.close()

11 Explain with an example working with CSV files in Python. 10

CSV (Comma Separated Values) is a simple file format used to store

tabular data, such as a spreadsheet or database in plain text. Each line
of the file is a data record. Each record consists of one or more fields,
separated by commas.

The use of the comma as a field separator is the source of the name
for this file format.For working CSV files in python, there is an
inbuilt module called csv.

SN Name City

1 Michael New Jersey

2 Jack California

3 Donald Texas
We can represent the above table in CSV as :
SN, Name, City
1, Michael, New Jersey
2, Jack, California
3, Donald, Texas

Read in a CSV File in Python

Python has inbuilt module i.e CSV, which is used for working with
CSV files. In python, we use csv.reader module to read a CSV file.
Example : Reading a CSV file using csv.reader

Here we are going to show how you can read a people.csv file, that
we created in above example.

import csv

withopen('people.csv','r')as csvFile:
reader =csv.reader(csvFile)
for row in reader:
  print(row)

Once we run it on the python console, it will show following result:

['SN', 'Name', 'City']

['1','Michael', 'New Jersey']
['2','Jack', 'California']
['3','Donald', 'Texas']
 In above program,

First, we open our CSV file as csvFile.

Then, we used the csv.reader() method to extract the data into
the reader object which we can iterate over to get each line of our
data.

Write in a CSV File in Python

In python, we use csv.Writer module to write in a csv file.

Example: Writing in a CSV file using csv.writer

Here we are going to show how you can write to in a csv file. We
will output some data stored in a python list into a csv file.
import csv

csvData =[['Name of Person','Age'],['Peter','22'],['Jasmine','21'],

['Sam','24']]

withopen('person.csv','w')as csvFile:
writer =csv.writer(csvFile)
writer.writerows(csvData)

When we run the above program, person.csv will contain the

following data
Name of Person, Age
Peter, 22
Jasmine, 21
Sam, 24

12 Write a python program to plot a graph for the following values: - 10

Time (x axis) = [1,2,3,4]
Position (y axis) = [0,100,200,300].

Give label to x axis and y axis.

Program

# importing the required module 
import matplotlib.pyplot as plt 
  
# x axis values 
x = [1,2,3,4] 

# corresponding y axis values 
y = [0,100,200,300] 
  
# plotting the points  
plt.plot(x, y) 
  
# naming the x axis 
plt.xlabel('Time ') 

# naming the y axis 
plt.ylabel('Position ') 
  
# giving a title to my graph 
plt.title('Visualizing Time and Position Graph!') 
  
# function to show the plot 
plt.show() 

Output :
13 Plot a bar graph for the following rainfall data. Give appropriate 10
name for x- axis and y axis. Give a title to the graph.

District Rainfall (in cms}

Chennai 50cms
Thiruvallur 45 cms Kanchipurram 55cms Arakkonam 32 cms

Program

import matplotlib.pyplot as plt
 
 # x-coordinates of left sides of bars  
left = [1, 2, 3, 4] 
  
# heights of bars 
height = [50, 45, 55, 32] 
  
# labels for bars 
tick_label = ['chennai', 'thiruvallur', 'kanchipuram', 'Arakonnam'] 
  
# plotting a bar chart 
plt.bar(left, height, tick_label = tick_label, width = 0.8, color = ['re
d', 'green']) 

# naming the x-axis 
plt.xlabel('District') 

# naming the y-axis 
plt.ylabel('Rainfall in cms') 

# plot title 
plt.title('District- Rainfall!') 
  
# function to show the plot 
plt.show() 
 Output:

14 Plot and draw a scatter plot using python program. 10

Program

import matplotlib.pyplot as plt 

# x-axis values 
x = [1,2,3,4,5,6,7,8,9,10] 

# y-axis values 
y = [2,4,5,7,6,8,9,11,12,12] 
  
# plotting points as a scatter plot 
plt.scatter(x, y, color= "red", marker= "*", s=30) 
  
# x-axis label 
plt.xlabel('x - axis chart') 

# frequency label 
plt.ylabel('y - axis chart') 
 # plot title 
plt.title('My scatter plot visualization!') 

# showing legend 
plt.legend() 
  
# function to show the plot 
plt.show()

Output

15 Explain the various steps for database connection in Python using 10

an example.

Python MySQL - Create Table

Basically, to store information in the MySQL database there is a

need to create the tables.
It is also required to select our database first and then create
tables inside that database.
At the time of creating the connection, you can also specify the
name of your database, like given below:

import mysql.connector
 db = mysql.connector.connect(
host = "localhost",
user = "yourusername",
password = "yourpassword",
database = "studytonight"
)
If the above code is executed without any errors then it means
you have successfully connected to the database
named studytonight.
SQL Query to Create Table

To create a table in the selected database the following statement

will be used. Let us see the syntax:

CREATE TABLE table_name;

Let us create a table named students in the specified database, that

is studytonight

In the table students we will have the following

fields: name, rollno, branch, and address.

# we will import mysql.connector as mysql

import mysql.connector as mysql

db = mysql.connect(
host = "localhost",
user = "yourusername",
passwd = "yourpassword",
database="studytonight"
)
cursor = db.cursor()

cursor.execute("CREATE TABLE students (name

VARCHAR(255), rollno INTEGER(100), branch
VARCHAR(255), address VARCHAR(255))")

If this code executes without any error then it means the table has

been created successfully.

16 With the help of python program, create a student table consists of 10

name, reg no. and grade. Insert a new row to the table.
Creating a Database

To create a database in MySQL, use the "CREATE DATABASE"

statement:

Example

create a database named "student":

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE student")

Creating a Table

To create a table in MySQL, use the "CREATE TABLE"

statement.
Make sure you define the name of the database when you create
the connection
Example
Create a table named "stud":
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="student"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE stud (name
VARCHAR(255), regno int, grade VARCHAR(5))")

Insert Into Table

To fill a table in MySQL, use the "INSERT INTO" statement.

Example
Insert a record in the "customers" table:
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="student"
)

mycursor = mydb.cursor()

sql = "INSERT INTO stud (name, regno,grade) VALUES (%s,%d,

%s)"
val = ("kumaran", 191911101, S")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Select From a Table

To select from a table in MySQL, use the "SELECT" statement:

Example
Select all records from the "customers" table, and display the
result:
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="student"
)
 mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM stud")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

17 Draw a pie chart by representing the daily activities of the person

like sleep, work, play, walk, and mediate with following time slot 10
15, 30, 20, 12, 13 by using all the parameters in it.

Program

import matplotlib.pyplot as plt 
  
# defining labels 
activities = ['eat', 'sleep', 'work', 'play' ,'mediate'] 
  
# portion covered by each label 
slices = [15, 30, 20, 12, 13] 
 
# color for each label 
colors = ['r', 'y', 'g', 'b','m'] 

# plotting the pie chart 
plt.pie(slices, labels = activities, colors=colors, startangle=90, shad
ow = True,radius = 1.2) 
  
# plotting legend 
plt.legend() 
  
# showing the plot 
plt.show() 
 Output

18 Develop a python program to create database connectivity with

MYSQL and create a table for maintaining student database with 10
student‟s details.

Creating a Database

To create a database in MySQL, use the "CREATE DATABASE"

statement:
Example
create a database named "mydatabase":
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

Creating a Table

To create a table in MySQL, use the "CREATE TABLE"

statement.
Make sure you define the name of the database when you create
the connection
Example
Create a table named "customers":
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE TABLE customers (name

VARCHAR(255), address VARCHAR(255))")

Insert Into Table

To fill a table in MySQL, use the "INSERT INTO" statement.

Example
Insert a record in the "customers" table:
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s,

%s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

Select From a Table

To select from a table in MySQL, use the "SELECT" statement:

Example
Select all records from the "customers" table, and display the
result:
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output :

Update Table
You can update existing records in a table by using the "UPDATE"
statement:
Example
Overwrite the address column from "Valley 345" to "Canyoun
123":
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE

address = 'Valley 345'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

Output

Delete Record

You can delete records from an existing table by using the

"DELETE FROM" statement:
Example
Delete any record where the address is "Mountain 21":
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = 'Mountain

21'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Output