Explore topic-wise InterviewSolutions in .

The difference between Comma-Separated Values (CSV) and eXceL Sheets(XLS) file formats is

Excel:

1. Excel is a binary file that holds information about all the worksheets in a file, including both content and formatting

2. XLS files can only be read by applications that have been especially written to read their format, and can only be written in the same way.

3. Excel is a spreadsheet that saves files into its own proprietary format viz. xls or xlsx

4. Excel consumes more memory while importing data

CSV: 

1. CSV format is a plain text format with a series of values separated by commas.

2. CSV can be opened with any text editor in Windows like notepad, MS Excel, OpenOffice, etc.

3. CSV is a format for saving tabular information into a delimited text file with extension .csv

4. Importing CSV files can be much faster, and it also consumes less memory

Rules to be followed to format data in a CSV file:

(i) Each record (row of data) is to be located on a separate line, delimited by a line break by pressing enter key.

For example:

xxx,yyy

denotes enter Key to be pressed

(ii) The last record in the file may or may not have an ending line break.

For example:

PPP, qqq

yyy,xxx

(iii) There may be an optional header line appearing as the first line of the file with the same format as normal record lines. The header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file.

For example: field_ name 1,field_name 2,field_name_3 aaa,bbb,ccc zzz,yyy,xxx CRFF( Carriage Return and Line feed)

(iv) Within the header and each record, there may be one or more fields, separated by commas. Spaces are considered part of a field and should not be ignored. The last field in the record must not be followed by a comma.

For example: Red, Blue

(v) Each field may or may not be enclosed in double quotes. If fields are not enclosed with double quotes, then double quotes may not appear inside the fields. 

For example: “Red” , ”Blue” , ”Green” #Field data with double quotes

Black,White,Yellow #Field data without double quotes

(vi) Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

For example: Red, “;:Blue CRLF #comma itself is a field value, so it is enclosed with double quotes Red, Blue, Green

(vii) If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be preceded with another double quote.

For example: “Red, ” “Blue1: “Green” , #since double quotes is a field value it is enclosed with another double quotes,, White

(d) both a & b

To sort by more than one column you can use itemgetter with multiple indices: operator .itemgetter (1,2).

#using operator module for sorting multiple columns 

sortedlist = sorted (data, key=operator.itemgetter(1))

(b) Excel files can be opened with any text editor

csv files cannot be opened with html

CSV File with quote characters

You can write the CSV file with custom quote characters, by registering new dialects using 

csv.register_dialect( ) class of csv module,

import csv

csvData = [[‘SNO’ ,Items’], [‘l’ , ’Pen’], [‘2′, ’Book’], [‘3′, ’Pencil’]]

csv.register_dialect(‘myDialect’ , delimiter = ‘|’ ,quotechar = “” ,

quoting=csv.QUOTE_ALL)

with open(‘c:\\pyprg\ \chl3\\quote.csv’ , ‘w’) as csvFile:

writer= csv.writer(csvFile, dialect-myDialect’)

writer, write rows(csvData)

print(“writing completed”)

csvFile.close( )

When you open the “quote.csv” file in notepad, we get following output:

In the above program, myDialect uses pipe (|) as delimiter and quotechar as doublequote “” to write inside the file.

python file

F=open(inFile;’r’)

# reading the File with the help of csv.reader( ) reader = csv.reader(F)

# skipping the first row(heading) next( reader)

# declaring a list array Value = [ ]

a= int(input (“Enter the column number 1 to 3:-“))

# sorting a particular column-cost for row in reader: 

array Value. append(row[a])

array Value.sorif) for row in array Value: print (row) 

F.close( )

OUTPUT

Enter the column number 1 to 3:-

2

50

12

10

r + b opens the file for read and write in binary mode.

In text mode, while reading from the file the data would be in the format of strings

1. handle

2. text

Insert is used to populate the records in the table

# skipping the first row(heading)

Example: next( reader)

The default mode when you open a file is r

CSV file with default delimiter comma(,) The following program read a file called “sample l.csv” with default delimiter cpmma(,) and print row by row.

#importing csv

import csv

#opening the csv fde which is in different location 

with read mode with open(‘c:\ \pyprg\\sample l.csv’ , V) as F:

#other way to open the file is f = (‘c:\\pyprg\\sample l.csv’ , ‘r’)

reader = csv.reader(F)

Sprinting each line of the Data row by row print(row) 

F.close( )

OUTPUT

[‘SNO’ , ‘NAME’ , ‘CITY’]

[12101’ , ’RAM’ , ‘CHENNAI’]

[‘12102′, ’LAVANYA’ , ’TIRUCHY’]

[‘12103′, ’LAKSHMAN’ , ’MADURA’]

By default, csv files open automatically in Excel.

Answer:  False

In SQL, the where clause is used to extract only those records that fulfill a specified condition.

The distinct clause is helpful when there is need of avoiding the duplicate values present in any specific columns/table. When we use distinct keyword only the unique values are fetched.

(c) WHERE cannot be used with NOT

“Select” is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a table in a database. The syntax for using this statement is “Select*from table_name” and all the table data can be fetched in an object in the form of list of lists.

(c) Python script

If a column of a table is declared to be an INTEGER PRIMARY KEY, then whenever a NULL . will be used as an input for this column, the NULL will be automatically converted into an integer which will one larger than the highest value so far used in that column. If the table is empty, the value 1 will be used.

1. SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer.

2. It is designed to be embedded in applications, instead of using a separate database server program such as MySQLor Oracle.

3. SQLite is fast, rigorously tested, and flexible, making it easier to work. Python has a native library for SQLite.

SQL provides various clauses that can be used in the SELECT statements.

1. DISTINCT 

2. WHERE 

3. GROUP BY 

4. ORDER BY 

5. HAVING

(a) connect( )

, is used to print all elements separated by space

The most commonly used statement in SQL is select

Database is an organized collection of data,

All the commands will be executed using cursor object only.

Answer is (b) 1

.db is the extension for database file

(a) sqlite master

(b) Relational Database system

Answer is (d) Many

(i) OR, 

(ii) MAX, 

(iii) NULL

The SQL commands have to be defined with triple quotes.

Integer primary key

Answer is (d) a & c

A cursor in SQL and databases is a control structure to traverse over the records in a database. So it’s used for the fetching of the results.

Cursor is used for performing all SQL commands. The cursor object is created by calling the cursor( ) method of connection. The cursor is used to traverse the records from the result set.

\n is new line character

1 values are returned from the aggregate functions

import sqlite3

connection= sqlite3.connect(“Academy.db”)

cursor = connection.cursor( ) 

cursor.execute(“SELECT gender,count(gender) FROM student Group BY gender”)

result = cursor. fetchall( )

print(*result,sep= ”\n”)

OUTPUT

(‘F’, 2)

(‘M’ , 5)

The function that returns the largest value of the selected column is MAX( )

SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer. It is designed to be embedded in applications, instead of using a separate database server program such as MySQLor Oracle. SQLite is fast, rigorously tested, and flexible, making it easier to work. Python has a native library for SQLite. To use SQLite,

Step 1 import sqliteS

Step 2 create a connection using connect ( ) method and pass the name of the database File 

Step 3 Set the cursor object cursor = connection.cursor( )

1. Connecting to a database in step2 means passing the name of the database to be accessed. If the database already exists the connection will open the same. Otherwise, Python will open a new database file with the specified name.

2. Cursor in step 3: is a control structure used to traverse and fetch the records of the database.

3. Cursor has a major role in working with Python. All the commands will be executed using cursor object only.

To create a table in the database, create an object and write the SQL command in it.

Example:- sql_comm = “SQL statement” For executing the command use the cursor method and pass the required sql command as a parameter. Many number of commands can be stored in the sql comm and can be executed one after other. Any changes made in the values of the record should be saved by the command “Commit” before closing the “Table connection”.

There are 2 types of sorting

Answer is (c) (4)

The WHERE clause is used to extract only .those records that fulfill a specified condition. In this example we are going to display the different grades scored by male students from “student table” import sqlite3

connection = sqlite3.connect(“Academy.db”)

cursor = connection.cursor( ) 

cursor.execute(“SELECT DISTINCT (Grade) FROM

student where gender= ’M'”)

result = cursor. fetchall( )

print(*result,sep= ”\n”)

OUTPUT

(‘B’ ,)

(‘A’ ,)

(‘C’ ,)

(‘D’ ,)

(c) group by clause is used to filter data