Skip to content

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

Paper Details

  • Subject: Desktop Publishing (DRA) / Python SQLite
  • Subject Code: 205_02
  • Set: D
  • 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: Library Data Migration

Max Marks: 20

  1. Create CSV file Library.csv containing the following columns: Book_No, Book_Title, Subject, Author, Price. The file contains data of minimum 10 Books.
  2. Create a table named Books with appropriate data types.
  3. Write a Python script that Imports data from the CSV file into the Books table.

1. CSV Data Generation

Create the source file with at least 10 book records.

Hint

You can create this file manually in Excel/Notepad or using a simple Python script as shown below.

View Solution & Output

Library.csv (Content): 

Book_No,Book_Title,Subject,Author,Price
101,C Programming,Programming,Dennis Ritchie,450.0
102,Python Basics,Programming,Guido van Rossum,550.0
103,Modern RDBMS,Database,C.J. Date,750.0
104,Operating Systems,Systems,Silberschatz,800.0
105,Networking,Networking,Tanenbaum,600.0
106,Data Structures,Algorithms,Sartaj Sahni,500.0
107,AI Fundamentals,AI,Stuart Russell,950.0
108,Web Design,Web,Jon Duckett,400.0
109,Java complete ref,Programming,Herbert Schildt,700.0
110,Software Engg,Management,Pressman,650.0

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

2. Database Table Creation

Define the Books table structure in SQLite.

flowchart TD
A["Books Table"]
B["Book_No (INT PK)"]
C["Book_Title (TEXT)"]
D["Subject (TEXT)"]
E["Author (TEXT)"]
F["Price (REAL)"]

A --> B
A --> C
A --> D
A --> E
A --> F
View Solution & Output 
import sqlite3

conn = sqlite3.connect('library.db')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS Books (
        Book_No INTEGER PRIMARY KEY,
        Book_Title TEXT NOT NULL,
        Subject TEXT,
        Author TEXT,
        Price REAL
    )
''')

print("Books table created.")
conn.close()

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

3. Data Import Script

Write the logic to read the CSV and insert it into the database.

Hint

Use csv.DictReader to map column headers to dictionary keys, making the insertion logic cleaner.

flowchart TD
csv[Read Library.csv]
loop[Loop through Rows]
ins[Execute INSERT]
comm[Commit Changes]

csv --> loop
loop --> ins
ins --> comm
View Solution & Output 
import sqlite3
import csv

# [1] Setup Database
conn = sqlite3.connect('library.db')
cursor = conn.cursor()

# [2] Read CSV and Import
try:
    with open('Library.csv', 'r') as f:
        reader = csv.reader(f)
        next(reader) # Skip header row

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

    conn.commit()
    print("Data imported successfully from CSV to SQLite.")

except FileNotFoundError:
    print("Error: Library.csv not found!")
finally:
    conn.close()

Step-by-Step Explanation: 1. Initialization: Import sqlite3 and csv and open 'Library.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.

Concept Deep Dive: Data Migration

In real-world applications, data is often moved from legacy systems (like CSV/Excel) to relational databases (like SQLite/MySQL). This process involves reading row-by-row, validating data types, and using transactions (commit) to ensure data integrity.

Q2: Viva Preparation

Max Marks: 5

Potential Viva Questions
  1. Q: Why use next(reader)?
  2. A: It skips the header row of the CSV so you don't try to insert the column names as actual data records.
  3. Q: What happens if Book_No is a duplicate?
  4. A: Since Book_No is the PRIMARY KEY, SQLite will raise an IntegrityError if you try to insert a duplicate.
  5. Q: How can you check if the import was successful?
  6. A: By running a SELECT count(*) FROM Books query or SELECT * FROM Books to view the data.
  7. Q: What is the purpose of the try-except-finally block?
  8. A: It provides robust error handling, ensuring the database connection is closed even if an error occurs during file reading.
  9. Q: Can you import a CSV without the csv module?
  10. A: Yes, by reading the file line-by-line and using split(','), but the csv module is safer as it handles quotes and delimiters better.
  11. Q: Why is Price stored as REAL?
  12. A: To allow for currency values with decimal points.

Common Pitfalls

  • Encoding Issues: If your CSV contains special characters, specify encoding='utf-8' in the open() function.
  • Header Mismatch: Ensure the number of columns in your CSV exactly matches the columns in your INSERT statement.

Quick Navigation

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

Last Updated: April 2025