Skip to content

VNSGU BCA Sem 2: Desktop Publishing (205_02) Practical Solutions - Set F

Paper Details

  • Subject: Desktop Publishing (DRA) / Python SQLite
  • Subject Code: 205_02
  • Set: F
  • Semester: 2
  • Month/Year: April 2025
  • Max Marks: 25
  • Time Recommendation: 45 Minutes
  • Paper: View Paper | Download PDF

Questions & Solutions

All questions are compulsory

Q1: Student Information System

Max Marks: 20

  1. Create CSV file students.csv containing the following columns: ID, Name, Address, and Grade. The file contains data of minimum 10 students.
  2. Create a table named students with appropriate data types.
  3. Write a Python script that Imports data from the CSV file into the students table.
  4. Write a Python script to display student records.

1. Source CSV Generation

Define the initial dataset for the migration.

Hint

A simple CSV format uses commas between values. Ensure no commas are used inside the data itself (like in the Address) unless they are enclosed in quotes.

View Solution & Output

students.csv (Content): 

ID,Name,Address,Grade
201,Aarav Shah,Adajan Surat,A+
202,Diya Patel,Varachha Surat,B
203,Krish Gupta,Rustompura Surat,A
204,Mira Joshi,Vesu Surat,A+
205,Aryan Mehta,Katargam Surat,C
206,Sia Parekh,Althan Surat,B+
207,Rohan Desai,Citylight Surat,A
208,Isha Rana,Navsari,B
209,Karan Jain,Piplod Surat,A+
210,Nehal Mistry,Udhna Surat,B

Step-by-Step Explanation: 1. Initialization: Identify the required column headers for student data. 2. Logic Flow: List at least 10 student records with their details in CSV format. 3. Completion: Save the content as 'students.csv' for future database import.

2. SQLite Schema Creation

Prepare the database table to store student records.

flowchart TD
start[Start]
db[Connect students.db]
tbl[Create Table: students]
done[Close]

start --> db
db --> tbl
tbl --> done
View Solution & Output 
import sqlite3

# [2] Schema Setup
conn = sqlite3.connect('students.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        ID INTEGER PRIMARY KEY,
        Name TEXT NOT NULL,
        Address TEXT,
        Grade TEXT
    )
''')

print("Table students created successfully.")
conn.close()

Step-by-Step Explanation: 1. Initialization: Import sqlite3 and connect to 'students.db'. 2. Logic Flow: Execute a CREATE TABLE query for the students table with a primary key. 3. Completion: Print a success message and close the database connection.

3. Data Import Logic

Bridge the CSV file and the SQLite database.

Hint

Iterate through each row of the CSV and execute an INSERT command. Use conn.commit() at the end of the loop to save all changes.

View Solution & Output 
import sqlite3
import csv

# [3] Import Script
conn = sqlite3.connect('students.db')
cursor = conn.cursor()

with open('students.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader) # Skip Header row

    for row in reader:
        cursor.execute('INSERT INTO students VALUES (?, ?, ?, ?)', row)

conn.commit()
print("CSV data successfully imported into table.")
conn.close()

Step-by-Step Explanation: 1. Initialization: Import sqlite3 and csv and open 'students.csv' for reading. 2. Logic Flow: Use next(reader) to skip the header and iterate through each row to execute an INSERT statement. 3. Completion: Commit the changes to the database and close the connection.

4. Records Display

Retrieve and print the imported data for verification.

View Solution & Output 
import sqlite3

# [4] Display Script
conn = sqlite3.connect('students.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM students')
rows = cursor.fetchall()

print(f"{'ID':<5} {'Name':<15} {'Address':<20} {'Grade':<5}")
print("-" * 50)
for s in rows:
    print(f"{s[0]:<5} {s[1]:<15} {s[2]:<20} {s[3]:<5}")

conn.close()

Step-by-Step Explanation: 1. Initialization: Connect to 'students.db' and initialize a cursor object. 2. Logic Flow: Execute a SELECT * query to fetch all student records using fetchall(). 3. Completion: Format and print the results in a tabular view before closing the connection.

Concept Deep Dive: CSV vs SQL for Small Projects

For small datasets, CSVs are excellent for human readability and easy editing. However, as data grows or requires complex relationships (like linking students to their marks or teachers), SQL becomes essential because it allows indexing and relational joins which CSV files cannot natively support.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: What is the purpose of commit() in this script?
  2. A: It writes the transaction to the database file. Without it, the data would only exist in memory and be lost when the script ends.
  3. Q: How can you update a student's grade in the database?
  4. A: Use UPDATE students SET Grade = 'A' WHERE ID = 201.
  5. Q: What module is used for CSV operations in Python?
  6. A: The built-in csv module.
  7. Q: How do you format output into columns in Python?
  8. A: Using f-string alignment markers like :<15 (left-aligned, 15 chars wide).
  9. Q: How can you delete all records from the students table?
  10. A: Use the query DELETE FROM students.
  11. Q: What is the difference between csv.reader() and csv.DictReader()?
  12. A: csv.reader() returns rows as lists, while csv.DictReader() returns them as dictionaries using headers as keys.

Common Pitfalls

  • Duplicate IDs: If you run the import script twice without clearing the table, you will get a UNIQUE constraint failed error because ID is the primary key.
  • File Path: Ensure the students.csv file is in the same directory as your Python script.

Quick Navigation

Set Link
Set A Solutions
Set B Solutions
Set C Solutions
Set D Solutions
Set E Solutions
Set F Current Page

Last Updated: April 2025