Python Assignment – Unit 6
Python Database Connectivity (MySQL)
Reference: R Nageswara Rao, Core Python Programming, 2nd Edition, Chapter 24
Note: All programs below assume MySQL is installed and a database named 'company' exists with a
table 'emptab' having columns: empno (INT, PRIMARY KEY), ename (VARCHAR), salary (FLOAT).
The mysql-connector-python library is used for connectivity.
# Common setup for all programs:
# pip install mysql-connector-python
import [Link]
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
Q1. Advantages of DBMS over Files
A Database Management System (DBMS) overcomes the limitations of traditional file-based systems.
The main advantages are:
• 1. Data Independence: Changes to data structure do not affect the application programs.
Physical and logical data independence is maintained.
• 2. Reduced Data Redundancy: DBMS uses normalization to minimize data duplication, whereas
file systems often store the same data in multiple places.
• 3. Data Consistency: Since data is stored in one place, updates are automatically reflected
everywhere, ensuring consistency.
• 4. Improved Data Security: DBMS provides authentication, authorization, and access control to
restrict unauthorized access. File systems have limited security features.
• 5. Data Integrity: DBMS enforces constraints such as primary keys, foreign keys, and check
constraints to ensure data accuracy.
• 6. Concurrent Access: DBMS supports multiple users accessing data simultaneously using
locking and transaction management. File systems lack proper concurrency control.
• 7. Backup and Recovery: DBMS provides built-in mechanisms for backing up data and
recovering it after failures. File systems require manual backups.
• 8. Query Language Support: DBMS supports SQL for easy data retrieval and manipulation. File
systems require custom programs to search and process data.
• 9. Data Sharing: Multiple applications and users can share the same data stored in a DBMS
simultaneously.
• 10. Reduced Application Development Time: With DBMS, developers don't need to write data
storage and retrieval logic from scratch.
Q2. Retrieve and Display All Rows from the Table
This program connects to the MySQL database and fetches all rows from the emptab table using
fetchall().
import [Link]
# Establish connection
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
# Execute SELECT query
[Link]('SELECT * FROM emptab')
# Fetch all rows
rows = [Link]()
# Display each row
print('All rows from emptab:')
for row in rows:
print(row)
# Close connection
[Link]()
[Link]()
# Sample Output:
# All rows from emptab:
# (101, 'Rahul', 45000.0)
# (102, 'Priya', 52000.0)
# (103, 'Amit', 38000.0)
Q3. Retrieve All Rows and Display in Tabular Form
This program retrieves all rows and formats the output neatly using column headers.
import [Link]
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
[Link]('SELECT * FROM emptab')
rows = [Link]()
# Get column names from cursor description
columns = [desc[0] for desc in [Link]]
# Print header
print('-' * 40)
print(f'{columns[0]:<10} {columns[1]:<15} {columns[2]:<10}')
print('-' * 40)
# Print each row in tabular form
for row in rows:
print(f'{row[0]:<10} {row[1]:<15} {row[2]:<10.2f}')
print('-' * 40)
print(f'Total records: {len(rows)}')
[Link]()
[Link]()
# Sample Output:
# ----------------------------------------
# empno ename salary
# ----------------------------------------
# 101 Rahul 45000.00
# 102 Priya 52000.00
# 103 Amit 38000.00
# ----------------------------------------
# Total records: 3
Q4. Insert Several Rows into a Table from Keyboard
This program accepts employee details from the user (keyboard) in a loop and inserts them into the
emptab table.
import [Link]
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
print('Enter employee details (type \'done\' as empno to stop):')
while True:
empno = input('Employee No: ')
if [Link]() == 'done':
break
ename = input('Employee Name: ')
salary = float(input('Salary: '))
sql = 'INSERT INTO emptab (empno, ename, salary) VALUES (%s, %s, %s)'
values = (int(empno), ename, salary)
try:
[Link](sql, values)
[Link]()
print(f'Record inserted for {ename} successfully!')
except [Link] as e:
print(f'Error: {e}')
[Link]()
print('All records inserted.')
[Link]()
[Link]()
# Sample Interaction:
# Enter employee details (type 'done' as empno to stop):
# Employee No: 104
# Employee Name: Sneha
# Salary: 47000
# Record inserted for Sneha successfully!
# Employee No: done
# All records inserted.
Q5. Delete a Row from emptab by Employee Number
This program accepts the employee number from the keyboard and deletes the corresponding record
from emptab.
import [Link]
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
# Accept employee number from keyboard
empno = int(input('Enter Employee Number to delete: '))
# Check if employee exists
[Link]('SELECT * FROM emptab WHERE empno = %s', (empno,))
record = [Link]()
if record:
confirm = input(f'Delete record for {record[1]}? (yes/no): ')
if [Link]() == 'yes':
[Link]('DELETE FROM emptab WHERE empno = %s', (empno,))
[Link]()
print(f'Employee {empno} deleted successfully!')
else:
print('Deletion cancelled.')
else:
print(f'No employee found with empno {empno}')
[Link]()
[Link]()
# Sample Output:
# Enter Employee Number to delete: 103
# Delete record for Amit? (yes/no): yes
# Employee 103 deleted successfully!
Q6. Increase Salary of an Employee by Employee Number
This program accepts the employee number from the keyboard and increases their salary by a given
amount or percentage.
import [Link]
con = [Link](
host='localhost',
user='root',
password='your_password',
database='company'
)
cursor = [Link]()
# Accept employee number
empno = int(input('Enter Employee Number: '))
# Fetch current salary
[Link]('SELECT ename, salary FROM emptab WHERE empno = %s', (empno,))
record = [Link]()
if record:
ename, current_salary = record
print(f'Employee: {ename}')
print(f'Current Salary: {current_salary:.2f}')
# Accept increment amount
increment = float(input('Enter increment amount: '))
new_salary = current_salary + increment
# Update salary in database
[Link](
'UPDATE emptab SET salary = %s WHERE empno = %s',
(new_salary, empno)
)
[Link]()
print(f'Salary updated successfully!')
print(f'New Salary for {ename}: {new_salary:.2f}')
else:
print(f'No employee found with empno {empno}')
[Link]()
[Link]()
# Sample Output:
# Enter Employee Number: 101
# Employee: Rahul
# Current Salary: 45000.00
# Enter increment amount: 5000
# Salary updated successfully!
# New Salary for Rahul: 50000.00