Assignment-2

Python
B.E. (IT) 5th Semester

Submitted To: Submitted by:

Miss Kritika Ma’am Prashant kumar Singh
UE238071
IT Section-2

DEPARTMENT OF INFORMATION
TECHNOLOGY
University Institute of Engineering & Technology
PANJAB UNIVERSITY, CHANDIGARH
Q1. Write a short note on Database connectivity using
Python.
Ans: The following diagram illustrates how a connection request
is sent to the MySQL connector Python, how it gets accepted
from the database, and how the cursor is executed with the result
data.

To create a connection between the MySQL database and Python,

use the connect() method of [Link] connector module is
used. We pass the database details like HostName, username, and
the password in the method call, and then the method returns the
connection object. Steps to Connect SQL with Python involve:

1. Install MySQL Database

Download and install the MySQL database on your system.

2. Open Command Prompt and navigate to the location of PIP

After installing the MySQL database, open your Command Prompt and run
the commands given below to download and install "MySQL Connector".
Here [Link] connector statement will help you to communicate with
the MySQL database. Click here to see how to install PIP
pip install mysql-connector-python

3. Test MySQL Connector

To check if the installation was successful or if you already installed

"MySQL Connector," go to your IDE and run the code below:
import [Link]

If the above code gets executed with no errors, "MySQL Connector" is

ready to be used.

4. Create Connection

Now, to connect SQL with Python, run the code given below in your IDE.

●​ mysql. connector allows Python programs to access MySQL

databases.

●​ connect() method of the MySQL Connector class with the

arguments will connect to MySQL and would return a

 MySQLConnection object if the connection is established

successfully.

●​ host = "localhost": The host is the server where your MySQL

database is running. In most cases, for local development, you

can set it to "localhost". If the MySQL server is hosted on a

different machine, you would replace "localhost" with the

appropriate IP address or hostname of that machine.

●​ user = "yourusername": here "yourusername" should be the

same username as you set during MySQL installation.

●​ password = "your_password": here "your_password" should be

the same password as you set during MySQL installation.

import [Link]
mydb = [Link](
host = "localhost",
user = "yourusername",
password = "your_password"
)
print(mydb)

Output:
Q2. i) Write a code to create a table-Employee,
database-Company having the following columns: Name,
Age, Designation, and Salary
Ans:
import sqlite3
import os

# --- Configuration ---

DATABASE_NAME = "[Link]"
TABLE_NAME = "Employee"

def create_database_and_table():
"""
Connects to the specified SQLite database and creates the Employee
table
if it doesn't already exist.
"""
conn = None # Initialize connection outside try block
try:
# 1. Establish Connection (Creates the database file if it
doesn't exist)
print(f"Attempting to connect to the database:
{DATABASE_NAME}")
conn = [Link](DATABASE_NAME)
cursor = [Link]()
print("Database connection established successfully.")

# 2. Define the SQL command to create the table

# We use IF NOT EXISTS to prevent errors if the script is run
multiple times.
create_table_sql = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
Name TEXT NOT NULL,
Age INTEGER,
Designation TEXT,
Salary REAL
);
"""

# 3. Execute the SQL command

[Link](create_table_sql)
 print(f"\nSuccessfully created table '{TABLE_NAME}' with the
following columns:")
print(" - Name (TEXT)")
print(" - Age (INTEGER)")
print(" - Designation (TEXT)")
print(" - Salary (REAL)")

# 4. Commit the changes to make the table creation permanent

[Link]()
print("\nChanges committed to the database.")

except [Link] as e:
print(f"An error occurred: {e}")
# Optional: Rollback if an error occurs
if conn:
[Link]()

finally:
# 5. Close the cursor and connection
if conn:
[Link]()
print("Database connection closed.")
print(f"\nVerification: A file named '{DATABASE_NAME}'
should now exist in this directory.")

if __name__ == "__main__":
create_database_and_table()
Q)2
ii) Insert five rows into the table Employee.
Ans:
import sqlite3
import os

# --- Configuration ---

DATABASE_NAME = "[Link]"
TABLE_NAME = "Employee"

def create_table(conn):
"""Creates the Employee table (Name, Age, Designation, Salary) if
it doesn't exist."""
cursor = [Link]()
# 2. Define the SQL command to create the table
# Columns: Name (TEXT NOT NULL), Age (INTEGER), Designation (TEXT),
Salary (REAL)
create_table_sql = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
Name TEXT NOT NULL,
Age INTEGER,
Designation TEXT,
Salary REAL
);
"""
# 3. Execute the SQL command
[Link](create_table_sql)
print(f"\nSuccessfully ensured table '{TABLE_NAME}' exists with the
defined columns.")

def insert_employee_data(conn):
"""Inserts five rows of sample data into the Employee table if the
table is currently empty."""
cursor = [Link]()

# Check if the table already has data to prevent duplicate entries

on repeated runs
[Link](f"SELECT COUNT(*) FROM {TABLE_NAME};")
count = [Link]()[0]

if count > 0:
 print(f"Table '{TABLE_NAME}' already contains {count} rows.
Skipping insertion.")
return False # Return False if insertion was skipped

# Data to be inserted (5 rows as requested)

employees_data = [
("Prashant Singh", 29, "Software Engineer", 72000.00),
("Shubh ", 33, "Product Manager", 98000.00),
("Raghav ", 26, "QA Engineer", 61000.00),
("Priyanshu ", 24, "Data Scientist", 85000.00),
("Tanuja ", 38, "HR Manager", 70000.00),
]

# SQL command using placeholders (?) for parameterized insertion -

a best practice for security
insert_sql = f"INSERT INTO {TABLE_NAME} (Name, Age, Designation,
Salary) VALUES (?, ?, ?, ?);"

# Use executemany for efficient insertion of multiple rows

[Link](insert_sql, employees_data)

print(f"Successfully inserted {[Link]} new rows into the

'{TABLE_NAME}' table.")
return True # Return True if insertion was successful

def select_and_display_data(conn):
"""Selects all data from the Employee table and prints it in a
formatted way."""
cursor = [Link]()
[Link](f"SELECT Name, Age, Designation, Salary FROM
{TABLE_NAME};")

# Fetch all results

rows = [Link]()

print(f"\n--- Current Data in {TABLE_NAME} Table ({len(rows)} total

rows) ---")

# Print header with alignment

header = ["Name", "Age", "Designation", "Salary"]
print(f"{header[0]:<20} | {header[1]:<4} | {header[2]:<20} |
{header[3]:<10}")
print("-" * 62)
 # Print rows with alignment
for row in rows:
# Name is row[0], Age is row[1], Designation is row[2], Salary
is row[3]
print(f"{row[0]:<20} | {row[1]:<4} | {row[2]:<20} |
{row[3]:<10.2f}")
print("-" * 62)

def main():
"""Main function to setup the database, insert data, and display
results."""
conn = None
try:
# 1. Establish Connection
print(f"Attempting to connect to the database:
{DATABASE_NAME}")
conn = [Link](DATABASE_NAME)
print("Database connection established successfully.")

# Table Creation
create_table(conn)

# Data Insertion (Only runs if table is empty)

insertion_successful = insert_employee_data(conn)

# 4. Commit the changes (only if an insertion or creation

happened)
if insertion_successful:
[Link]()
print("\nChanges committed to the database.")

# Data Verification (Always display current data)

select_and_display_data(conn)

except [Link] as e:
print(f"An error occurred: {e}")
# Optional: Rollback if an error occurs
if conn:
[Link]()
print("Transaction rolled back due to error.")
 finally:
# 5. Close the cursor and connection
if conn:
[Link]()
print("\nDatabase connection closed.")

if __name__ == "__main__":
main()
Q2)
iii) Find and display the row(s) having your name in it?
Ans:
import sqlite3
import os

# --- Configuration ---

DATABASE_NAME = "[Link]"
TABLE_NAME = "Employee"

def create_table(conn):
"""Creates the Employee table (Name, Age, Designation, Salary) if
it doesn't exist."""
cursor = [Link]()
# 2. Define the SQL command to create the table
# Columns: Name (TEXT NOT NULL), Age (INTEGER), Designation (TEXT),
Salary (REAL)
create_table_sql = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
Name TEXT NOT NULL,
Age INTEGER,
Designation TEXT,
Salary REAL
);
"""
# 3. Execute the SQL command
[Link](create_table_sql)
print(f"\nSuccessfully ensured table '{TABLE_NAME}' exists with the
defined columns.")

def insert_employee_data(conn):
"""
Replaces the five rows of sample data with the new list provided by
the user.
It deletes all existing non-AI Assistant rows before insertion to
ensure only the new data is present.
"""
cursor = [Link]()
 # 1. DELETE existing non-AI Assistant data to ensure a clean slate
for the replacement
delete_sql = f"DELETE FROM {TABLE_NAME} WHERE Designation != 'AI
Assistant';"
[Link](delete_sql)
print(f"\n[Data Replacement] Deleted all previous employee data
(non-AI Assistant rows) to prepare for replacement.")

# New Data provided by the user (5 rows)

employees_data = [
("Prashant Singh", 29, "Software Engineer", 72000.00),
("Shubh", 33, "Product Manager", 98000.00),
("Raghav", 26, "QA Engineer", 61000.00),
("Priyanshu", 24, "Data Scientist", 85000.00),
("Tanuja", 38, "HR Manager", 70000.00),
]

# SQL command using placeholders (?) for parameterized insertion -

a best practice for security
insert_sql = f"INSERT INTO {TABLE_NAME} (Name, Age, Designation,
Salary) VALUES (?, ?, ?, ?);"

# Use executemany for efficient insertion of multiple rows

[Link](insert_sql, employees_data)

print(f"Successfully inserted {[Link]} new rows into the

'{TABLE_NAME}' table.")
return True # Return True as insertion was successful

def insert_my_info(conn):
"""Inserts a row for the AI Assistant (Gemini) if it doesn't
exist."""
cursor = [Link]()
my_name = "Gemini Assistant"

# Check if 'Gemini Assistant' is already in the table

[Link](f"SELECT Name FROM {TABLE_NAME} WHERE Name = ?;",
(my_name,))
if [Link]() is None:
# Insert the row if it doesn't exist
print(f"\n[System] Inserting special row for '{my_name}' to
answer the query...")
 insert_sql = f"INSERT INTO {TABLE_NAME} (Name, Age,
Designation, Salary) VALUES (?, ?, ?, ?);"
# Age 0 and high salary as a conceptual representation
[Link](insert_sql, (my_name, 0, "AI Assistant",
999999.99))
[Link]()
print(f"Row for '{my_name}' inserted and committed.")
return True
return False

def find_and_display_employee(conn, name):

"""Finds and displays the row for the employee with the given
name."""
cursor = [Link]()

print(f"\n--- Running Query: SELECT row(s) WHERE Name = '{name}'

([Link]) ---")

# Execute the SELECT query using a placeholder for safety

select_sql = f"SELECT Name, Age, Designation, Salary FROM
{TABLE_NAME} WHERE Name = ?;"
[Link](select_sql, (name,))

# Fetch all results

rows = [Link]()

if rows:
print(f"Query successful. Found {len(rows)} matching row(s):")
# Print header with alignment
header = ["Name", "Age", "Designation", "Salary"]
print(f"{header[0]:<20} | {header[1]:<4} | {header[2]:<20} |
{header[3]:<10}")
print("-" * 62)

# Print rows
for row in rows:
# row[3] is Salary, formatted to 2 decimal places
print(f"{row[0]:<20} | {row[1]:<4} | {row[2]:<20} |
{row[3]:<10.2f}")
print("-" * 62)
else:
print(f"No row found with the Name '{name}'.")
def select_and_display_data(conn):
"""Selects all data from the Employee table and prints it in a
formatted way."""
cursor = [Link]()
[Link](f"SELECT Name, Age, Designation, Salary FROM
{TABLE_NAME};")

# Fetch all results

rows = [Link]()

print(f"\n--- Current Data in {TABLE_NAME} Table ({len(rows)} total

rows) ---")

# Print header with alignment

header = ["Name", "Age", "Designation", "Salary"]
print(f"{header[0]:<20} | {header[1]:<4} | {header[2]:<20} |
{header[3]:<10}")
print("-" * 62)

# Print rows with alignment

for row in rows:
# Name is row[0], Age is row[1], Designation is row[2], Salary
is row[3]
print(f"{row[0]:<20} | {row[1]:<4} | {row[2]:<20} |
{row[3]:<10.2f}")
print("-" * 62)

def main():
"""Main function to setup the database, insert data, and display
results."""
conn = None
try:
# 1. Establish Connection
print(f"Attempting to connect to the database:
{DATABASE_NAME}")
conn = [Link](DATABASE_NAME)
print("Database connection established successfully.")

# Table Creation
create_table(conn)

# Data Insertion (Replaces previous 5 rows with the new 5 rows)

 insertion_successful = insert_employee_data(conn)

# 4. Commit the changes

if insertion_successful:
[Link]()
print("\nNew employee data committed to the database.")

# Q2(iii) - Insert and Find the AI Assistant (Ensures this row

is still present)
insert_my_info(conn)
find_and_display_employee(conn, "Gemini Assistant")

# Final Data Verification (Display the full table, now

including the new 5 rows + AI Assistant)
select_and_display_data(conn)

except [Link] as e:
print(f"An error occurred: {e}")
# Optional: Rollback if an error occurs
if conn:
[Link]()
print("Transaction rolled back due to error.")

finally:
# 5. Close the cursor and connection
if conn:
[Link]()
print("\nDatabase connection closed.")

if __name__ == "__main__":
main()
Q3.
i) Create a program to design a GUI window with two
textboxes and one button.
Ans:

"""
Simple tkinter GUI with two textboxes and one button.
Run: python a:\\VS\\gui_two_textboxes.py
"""
import tkinter as tk
from tkinter import messagebox

def on_submit():
first = [Link]()
second = [Link]()
# Show the collected values in a message box
[Link]("Entries", f"First: {first}\nSecond: {second}")

if __name__ == "__main__":
root = [Link]()
[Link]("Two Textboxes Example")
 [Link]("360x160")

frm = [Link](root, padx=12, pady=12)

[Link](fill=[Link], expand=True)

[Link](frm, text="First input:").grid(row=0, column=0,

sticky="w")
entry1 = [Link](frm, width=40)
[Link](row=1, column=0, columnspan=2, pady=(0, 8))

[Link](frm, text="Second input:").grid(row=2, column=0,

sticky="w")
entry2 = [Link](frm, width=40)
[Link](row=3, column=0, columnspan=2, pady=(0, 8))

btn = [Link](frm, text="Submit", width=12, command=on_submit)

[Link](row=4, column=0, pady=(6, 0))

# Allow Enter to trigger the button

[Link]('<Return>', lambda event: on_submit())

[Link]()
Q3)
ii) Fetch the rows from the table-Employee created in Q
no. 2 where Age >25 and Salary > 300000. (Enter age and
salary in the textboxes and fetch the rows on clicking the
button.)
Ans:

import tkinter as tk
from tkinter import ttk, scrolledtext, messagebox
import sqlite3

# --- Configuration for Database ---

DATABASE_NAME = "[Link]"
TABLE_NAME = "Employee"

def setup_database():
"""Ensures the database and table exist with the required initial
data."""
conn = None
try:
conn = [Link](DATABASE_NAME)
cursor = [Link]()
 # Create Table (Logic copied from create_employee_table.py)
create_table_sql = f"""
CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
Name TEXT NOT NULL,
Age INTEGER,
Designation TEXT,
Salary REAL
);
"""
[Link](create_table_sql)

# Ensure initial data is present (Logic adapted from

create_employee_table.py)
# Check if the table already has initial data (excluding the AI
Assistant)
[Link](f"SELECT COUNT(*) FROM {TABLE_NAME} WHERE
Designation != 'AI Assistant';")
count = [Link]()[0]

employees_data = [
("Prashant Singh", 29, "Software Engineer", 72000.00),
("Shubh", 33, "Product Manager", 98000.00),
("Raghav", 26, "QA Engineer", 61000.00),
("Priyanshu", 24, "Data Scientist", 85000.00),
("Tanuja", 38, "HR Manager", 70000.00),
]

# If existing employee rows don't match the required 5, delete

and re-insert the provided list
if count != len(employees_data):
# Delete existing employee data (non-AI Assistant rows)
[Link](f"DELETE FROM {TABLE_NAME} WHERE Designation
!= 'AI Assistant';")
# Insert the new data
insert_sql = f"INSERT INTO {TABLE_NAME} (Name, Age,
Designation, Salary) VALUES (?, ?, ?, ?);"
[Link](insert_sql, employees_data)

# Ensure Gemini Assistant row is present

my_name = "Gemini Assistant"
[Link](f"SELECT Name FROM {TABLE_NAME} WHERE Name =
?;", (my_name,))
 if [Link]() is None:
[Link](f"INSERT INTO {TABLE_NAME} (Name, Age,
Designation, Salary) VALUES (?, ?, ?, ?);",
(my_name, 0, "AI Assistant", 999999.99))

[Link]()
print(f"Database setup complete. Table '{TABLE_NAME}' ready
with data.")

except [Link] as e:
print(f"Database setup error: {e}")
if conn:
[Link]()
finally:
if conn:
[Link]()

class EmployeeSearchApp([Link]):
def __init__(self):
super().__init__()
[Link]("Employee Search (Age > X AND Salary > Y)")
[Link]("600x450")

# Configure grid system

[Link](0, weight=1)
[Link](1, weight=1)
[Link](2, weight=1)

self.create_widgets()

def create_widgets(self):
# --- Input Frame ---
input_frame = [Link](self, padding="10")
input_frame.grid(row=0, column=0, columnspan=3, sticky="ew")
input_frame.columnconfigure(0, weight=1)
input_frame.columnconfigure(1, weight=1)
input_frame.columnconfigure(2, weight=1)
input_frame.columnconfigure(3, weight=1)

# 1. Age Input
[Link](input_frame, text="Minimum Age (>):").grid(row=0,
column=0, padx=5, pady=5, sticky="w")
 self.age_entry = [Link](input_frame, width=10)
self.age_entry.grid(row=0, column=1, padx=5, pady=5,
sticky="ew")
self.age_entry.insert(0, "25") # Default value

# 2. Salary Input
[Link](input_frame, text="Minimum Salary (>):").grid(row=0,
column=2, padx=5, pady=5, sticky="w")
self.salary_entry = [Link](input_frame, width=15)
self.salary_entry.grid(row=0, column=3, padx=5, pady=5,
sticky="ew")
self.salary_entry.insert(0, "300000.00") # Default value (as
requested)

# 3. Button
self.search_button = [Link](self, text="Fetch Matching
Employees", command=self.fetch_employees)
self.search_button.grid(row=1, column=0, columnspan=3, pady=10)

# --- Results Area ---

[Link](self, text="Query Results:").grid(row=2, column=0,
columnspan=3, sticky="w", padx=10)
self.results_text = [Link](self,
wrap=[Link], width=70, height=15, font=('Consolas', 10))
self.results_text.grid(row=3, column=0, columnspan=3, padx=10,
pady=5, sticky="nsew")
[Link](3, weight=1)

# Display initial prompt

self.results_text.insert([Link], "Enter age and salary
thresholds and click 'Fetch Matching Employees'.")

def fetch_employees(self):
"""Fetches employee rows from the database based on age and
salary criteria."""
self.results_text.delete('1.0', [Link]) # Clear previous
results
conn = None

try:
# 1. Validate and get user input
min_age = int(self.age_entry.get())
 min_salary = float(self.salary_entry.get())

# 2. Establish Connection
conn = [Link](DATABASE_NAME)
cursor = [Link]()

# 3. Execute the SELECT query with parameterized inputs

select_sql = f"""
SELECT Name, Age, Designation, Salary
FROM {TABLE_NAME}
WHERE Age > ? AND Salary > ?;
"""

[Link](select_sql, (min_age, min_salary))

rows = [Link]()

# 4. Display Results
header = f"Query: Employees where Age > {min_age} AND
Salary > {min_salary:.2f}\n"
self.results_text.insert([Link], header)
self.results_text.insert([Link], "=" * len(header) +
"\n\n")

if rows:
output = f"{'Name':<25} | {'Age':<5} |
{'Designation':<20} | {'Salary':<10}\n"
output += "-" * 67 + "\n"

for row in rows:

name, age, designation, salary = row
output += f"{name:<25} | {age:<5} |
{designation:<20} | {salary:<10.2f}\n"

self.results_text.insert([Link], output)
else:
self.results_text.insert([Link], "No employees found
matching the criteria.")

except ValueError:
[Link]("Input Error", "Please ensure Age is a
whole number and Salary is a valid number.")
except [Link] as e:
 [Link]("Database Error", f"An error occurred
during query execution: {e}")
except Exception as e:
[Link]("An unexpected error occurred",
str(e))
finally:
if conn:
[Link]()

if __name__ == "__main__":
# Ensure the database file and table are created and populated
before starting the GUI
setup_database()

app = EmployeeSearchApp()
[Link]()