Python Assignment Unit 6
Database Connectivity (DBMS & Python)
Q1. Discuss advantages of DBMS over Files.
A Database Management System (DBMS) offers several significant advantages over traditional flat-file
systems:
# Advantage Description
1 Data Redundancy Control DBMS minimises duplicate data through normalisation; files store the same data in multiple places.
2 Data Consistency Updates in one place reflect everywhere, eliminating inconsistencies common in file systems.
3 Data Sharing Multiple users/applications can access data simultaneously with proper concurrency control.
4 Data Security Role-based access control restricts unauthorised access; files have limited security.
5 Data Integrity Constraints (PRIMARY KEY, NOT NULL, FOREIGN KEY) enforce correct data automatically.
6 Backup & Recovery DBMS provides transaction logs and rollback mechanisms; file recovery is manual.
7 Query Language SQL allows complex data retrieval in a single statement; file searching requires custom code.
8 Data Independence Logical and physical independence means schema changes don't break applications.
Q2. Write a Python program to retrieve and display all rows from the table.
The program connects to a MySQL database using the mysql-connector-python library, executes a
SELECT query, and prints every row returned.
import [Link]
# Establish connection
conn = [Link](
host="localhost",
user="root",
password="your_password",
database="company"
)
cursor = [Link]()
# Execute SELECT query
[Link]("SELECT * FROM emptab")
# Fetch and display all rows
rows = [Link]()
print("All rows in emptab:")
for row in rows:
print(row)
# Close connection
[Link]()
[Link]()
Output (sample):
All rows in emptab:
(101, 'Alice', 'HR', 55000.0)
(102, 'Bob', 'Finance', 62000.0)
(103, 'Charlie','IT', 70000.0)
Q3. Write a Python program to retrieve all rows from the table and display the
column values in tabular form.
We use Python's built-in string formatting (or the tabulate library) to align column values neatly. The
column names are read from [Link].
import [Link]
conn = [Link](
host="localhost", user="root",
password="your_password", database="company"
)
cursor = [Link]()
[Link]("SELECT * FROM emptab")
# Get column names from cursor description
columns = [desc[0] for desc in [Link]]
rows = [Link]()
# Print header
header = f"{'EmpNo':<8}{'EmpName':<15}{'Dept':<12}{'Salary':>10}"
print(header)
print("-" * len(header))
# Print each row
for row in rows:
print(f"{row[0]:<8}{row[1]:<15}{row[2]:<12}{row[3]:>10.2f}")
[Link]()
[Link]()
Output (sample):
EmpNo EmpName Dept Salary
------------------------------------------
101 Alice HR 55000.00
102 Bob Finance 62000.00
103 Charlie IT 70000.00
Q4. Write a Python program to insert several rows into a table from the keyboard.
The user is asked how many rows to insert. For each row, values are entered at the keyboard and saved
using executemany() with parameterised queries to prevent SQL injection.
import [Link]
conn = [Link](
host="localhost", user="root",
password="your_password", database="company"
)
cursor = [Link]()
n = int(input("How many rows do you want to insert? "))
rows = []
for i in range(n):
print(f"\nEnter details for row {i+1}:")
empno = int(input(" Employee No : "))
empname = input(" Employee Name : ")
dept = input(" Department : ")
salary = float(input(" Salary : "))
[Link]((empno, empname, dept, salary))
sql = "INSERT INTO emptab (empno, empname, dept, salary) VALUES (%s, %s, %s, %s)"
[Link](sql, rows)
[Link]()
print(f"\n{[Link]} row(s) inserted successfully.")
[Link]()
[Link]()
Sample Interaction:
How many rows do you want to insert? 2
Enter details for row 1:
Employee No : 104
Employee Name : Diana
Department : Marketing
Salary : 58000
Enter details for row 2:
Employee No : 105
Employee Name : Eve
Department : IT
Salary : 72000
2 row(s) inserted successfully.
Q5. Write a Python program to delete a row from emptab by accepting the
employee number.
The employee number is accepted from the keyboard. A parameterised DELETE query removes the
matching record, and rowcount confirms whether a row was actually deleted.
import [Link]
conn = [Link](
host="localhost", user="root",
password="your_password", database="company"
)
cursor = [Link]()
empno = int(input("Enter Employee Number to delete: "))
sql = "DELETE FROM emptab WHERE empno = %s"
[Link](sql, (empno,))
[Link]()
if [Link] > 0:
print(f"Employee {empno} deleted successfully.")
else:
print(f"No employee found with empno = {empno}.")
[Link]()
[Link]()
Sample Interaction:
Enter Employee Number to delete: 103
Employee 103 deleted successfully.
Q6. Write a Python program to increase the salary of an employee by accepting
the employee number from the keyboard.
The program accepts the employee number and the increment amount, then runs an UPDATE query. It
displays the old salary, the increment, and the new salary for confirmation.
import [Link]
conn = [Link](
host="localhost", user="root",
password="your_password", database="company"
)
cursor = [Link]()
empno = int(input("Enter Employee Number : "))
increment = float(input("Enter Salary Increment Amount: "))
# Fetch current salary for display
[Link]("SELECT empname, salary FROM emptab WHERE empno = %s", (empno,))
result = [Link]()
if result:
empname, old_salary = result
new_salary = old_salary + increment
sql = "UPDATE emptab SET salary = %s WHERE empno = %s"
[Link](sql, (new_salary, empno))
[Link]()
print(f"\nEmployee : {empname}")
print(f"Old Salary: {old_salary:.2f}")
print(f"Increment : {increment:.2f}")
print(f"New Salary: {new_salary:.2f}")
print("Salary updated successfully!")
else:
print(f"No employee found with empno = {empno}.")
[Link]()
[Link]()
Sample Interaction:
Enter Employee Number : 101
Enter Salary Increment Amount: 5000
Employee : Alice
Old Salary: 55000.00
Increment : 5000.00
New Salary: 60000.00
Salary updated successfully!
Note: All programs use mysql-connector-python (install via pip install mysql-connector-python). Create the emptab
table with columns: empno INT PRIMARY KEY, empname VARCHAR(50), dept VARCHAR(30), salary FLOAT.
Reference: R Nageswara Rao, Core Python Programming, 2nd Ed., Dreamtech Press, Chapter 24.