← Back to Past Papers

VNSGU BCA Sem 3: Database Handling (303) Practical Solutions - April 2025 Set A

Paper Information

Attribute	Value
Subject	Database Handling Using Python
Subject Code	303
Set	A
Semester	3
Month/Year	April 2025
Max Marks	25
Paper	View Paper | Download PDF

---

Questions & Solutions

Q1: Bank Customer Database Script

Max Marks: 20

Write a python script to create database "Bank.db" and perform the following tasks: A. Create table Customer(c_id, name, act_type, balance). B. Insert 10 records. C. Read data into a Pandas DataFrame. D. Display customer id and balance whose name starts with 'A'. E. Display the last 3 records.

1. Concept Explanation

What is SQLite in Python?

SQLite is a C-language library that provides a lightweight, disk-based database that doesn't require a separate server process. Python has built-in support for SQLite via the sqlite3 module. It is widely used for prototyping, embedded systems, and educational lab practicals.

What is a Pandas DataFrame?

A Pandas DataFrame is a 2-dimensional labeled data structure with columns of potentially different types, similar to a spreadsheet or SQL table. Reading SQL records directly into a DataFrame using pandas.read_sql_query() makes data manipulation, filtering, and analytics extremely simple and efficient.

---

2. Algorithm & Step-by-Step Logic

1. Import Modules: Import sqlite3 for database connectivity and pandas for DataFrame manipulation.
2. Connect & Create Database: Establish a connection to Bank.db. This will automatically create the database file if it does not exist.
3. Create Table: Write a SQL CREATE TABLE query defining columns c_id (Primary Key), name (text), act_type (text), and balance (real/numeric).
4. Insert Records: Populate the table with 10 sample customer records. We will use parameterized queries (? placeholders) for secure inserts.
5. Load into Pandas: Establish a connection query and use pd.read_sql_query() to pull the entire table into a DataFrame.
6. Apply Filters:
   • Filter the DataFrame to show customers whose name begins with the letter 'A'.
   • Print the last 3 records using Pandas' .tail(3) method.
7. Clean Up: Close the database connection to prevent resource leaks.

---

3. Implementation Code & Output

View Solution & Output

# 🏦 VNSGU BCA Sem 3 - Database Handling Using Python (Practical 303)
# 🚀 Action: Setup SQLite3 Bank Database, load into Pandas, filter and slice records.

import sqlite3
import pandas as pd

def setup_database():
    # 1. Connect to Bank.db (automatically creates the file if it doesn't exist)
    conn = sqlite3.connect("Bank.db")
    cursor = conn.cursor()
    
    # Drop table if it already exists to start fresh on subsequent runs
    cursor.execute("DROP TABLE IF EXISTS Customer;")
    
    # 2. Create the Customer table
    cursor.execute("""
    CREATE TABLE Customer (
        c_id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        act_type TEXT NOT NULL,
        balance REAL NOT NULL
    );
    """)
    print("✓ Customer table created successfully in Bank.db.")
    
    # 3. List of 10 sample customer records to insert
    # Include names starting with 'A' to test filtering conditions
    customers = [
        (101, "Amit Patel", "Savings", 25000.0),
        (102, "Brijesh Shah", "Current", 50000.0),
        (103, "Anjali Sharma", "Savings", 15000.0),
        (104, "Deepa Vyas", "Savings", 8000.0),
        (105, "Arvind Mehta", "Current", 120000.0),
        (106, "Gaurav Joshi", "Savings", 35000.0),
        (107, "Aarti Trivedi", "Savings", 4500.0),
        (108, "Hardik Pandya", "Current", 95000.0),
        (109, "Ishita Sen", "Savings", 62000.0),
        (110, "Karan Malhotra", "Savings", 18500.0)
    ]
    
    # Insert records securely using executemany
    cursor.executemany("""
    INSERT INTO Customer (c_id, name, act_type, balance)
    VALUES (?, ?, ?, ?);
    """, customers)
    
    # Commit changes and close cursor
    conn.commit()
    cursor.close()
    conn.close()
    print("✓ 10 customer records inserted successfully.\n")

def analyze_data():
    # Re-connect to database for pandas read operation
    conn = sqlite3.connect("Bank.db")
    
    # 4. Read SQL table data directly into a Pandas DataFrame
    df = pd.read_sql_query("SELECT * FROM Customer", conn)
    
    print("--- FULL PANDAS DATAFRAME ---")
    print(df.to_string(index=False))
    print("-" * 30 + "\n")
    
    # 5. Display customer id and balance whose name starts with 'A'
    # We use .str.startswith('A') to apply Boolean indexing
    print("--- CUSTOMERS WHOSE NAMES START WITH 'A' ---")
    customers_with_a = df[df['name'].str.startswith('A')]
    print(customers_with_a[['c_id', 'balance']].to_string(index=False))
    print("-" * 30 + "\n")
    
    # 6. Display the last 3 records using .tail(3)
    print("--- LAST 3 RECORDS IN THE DATABASE ---")
    last_three = df.tail(3)
    print(last_three.to_string(index=False))
    print("-" * 30 + "\n")
    
    # Close connection
    conn.close()

if __name__ == "__main__":
    setup_database()
    analyze_data()

Expected Terminal Output:

✓ Customer table created successfully in Bank.db.
✓ 10 customer records inserted successfully.

--- FULL PANDAS DATAFRAME ---
 c_id           name act_type  balance
  101     Amit Patel  Savings  25000.0
  102   Brijesh Shah  Current  50000.0
  103  Anjali Sharma  Savings  15000.0
  104     Deepa Vyas  Savings   8000.0
  105   Arvind Mehta  Current 120000.0
  106   Gaurav Joshi  Savings  35000.0
  107  Aarti Trivedi  Savings   4500.0
  108  Hardik Pandya  Current  95000.0
  109     Ishita Sen  Savings  62000.0
  110 Karan Malhotra  Savings  18500.0
------------------------------

--- CUSTOMERS WHOSE NAMES START WITH 'A' ---
 c_id  balance
  101  25000.0
  103  15000.0
  105 120000.0
  107   4500.0
------------------------------

--- LAST 3 RECORDS IN THE DATABASE ---
 c_id           name act_type  balance
  108  Hardik Pandya  Current  95000.0
  109     Ishita Sen  Savings  62000.0
  110 Karan Malhotra  Savings  18500.0
------------------------------

---

💡 Common Examination Mistakes & Tips

Key Pitfalls to Avoid

1. Forget to Commit: In SQLite, changes like INSERT or CREATE are not saved permanently unless you call conn.commit(). Always commit before closing or querying with Pandas.
2. Missing sqlite3 Connection: pd.read_sql_query requires an active connection object (conn) as its second parameter. Do not close the connection before executing the query.
3. Hardcoding Database Paths: Keep the database path local (e.g. "Bank.db") so that the examiner can run it easily without path modification errors.
4. Library Dependency: Pandas is not standard with Python. Students must run pip install pandas or conda install pandas on their lab systems.