Scribd
Upload
2 views14 pages

Python SQL Practical File With Outputs

The document consists of a practical file containing various Python programs and SQL queries, including functionalities like swapping values, arithmetic operations, file handling, and database operations. It covers topics such as reading and writing to text and binary files, CSV file handling, and SQL commands for creating and manipulating a student database. Additionally, it demonstrates Python-MySQL connectivity for inserting and displaying records.

Uploaded by

yashica.shokeen
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views14 pages

Python SQL Practical File With Outputs

The document consists of a practical file containing various Python programs and SQL queries, including functionalities like swapping values, arithmetic operations, file handling, and database operations. It covers topics such as reading and writing to text and binary files, CSV file handling, and SQL commands for creating and manipulating a student database. Additionally, it demonstrates Python-MySQL connectivity for inserting and displaying records.

Uploaded by

yashica.shokeen
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

PYTHON, SQL AND PYTHON–MYSQL CONNECTIVITY

Practical File – Programs with Outputs


INDEX
S. No. Name of Program Page No.
1 Function to Swap Values of Two
Variables
2 Menu-Driven Arithmetic Operations
Using Functions and Parameters
3 Generate Random Number
Between 1 and 6
4 Demonstration of Parameters and
Default Parameters
5 Count Alphabets, Numbers and
Other Characters in a Text File
6 Count the Words “the” or “THE” in a
Text File
7 Count Number of Words Starting
with a Vowel
8 Read a Text File Line by Line and
Display Words Separated by “#”
9 Copy Lines Starting with ‘A’ from
One File to Another
10 Binary File: Store and Search Roll
No. and Name
11 Binary File: Update Marks Using
Roll No.
12 Binary File: Store Dictionary Data
Conditionally
13 Write and Read Employee Records
Using CSV File
14 Write and Display Dictionary Data
in CSV File
15 Implement Stack Using List
16 Create Student Table and Perform
SELECT, DELETE and UPDATE
Operations
17 Queries Using Aggregate Functions
18 Queries Using DISTINCT,
BETWEEN, IN, LIKE and ORDER
BY
19 Queries Using GROUP BY and
HAVING
20 Queries Using JOIN
21 Python–MySQL: Insert and Display
Records
22 Python–MySQL: Search and
Display Records
23 Python–MySQL: Update Records
1. Function to Swap Values of Two Variables
def swap(a, b):
a, b = b, a
return a, b

x = int(input("Enter first number: "))


y = int(input("Enter second number: "))

print("Before swapping:")
print("x =", x)
print("y =", y)

x, y = swap(x, y)

print("After swapping:")
print("x =", x)
print("y =", y)
Output:
Enter first number: 10
Enter second number: 20
Before swapping:
x = 10
y = 20
After swapping:
x = 20
y = 10

2. Menu-Driven Arithmetic Operations Using Functions and Parameters


def add(a, b):
return a + b

def subtract(a, b):


return a - b

def multiply(a, b):


return a * b

def divide(a, b):


if b != 0:
return a / b
return "Division by zero not possible"

while True:
print("\n1. Addition")
print("2. Subtraction")
print("3. Multiplication")
print("4. Division")
print("5. Exit")

choice = int(input("Enter your choice: "))

if choice == 5:
break

a = float(input("Enter first number: "))


b = float(input("Enter second number: "))

if choice == 1:
print("Result =", add(a, b))
elif choice == 2:
print("Result =", subtract(a, b))
elif choice == 3:
print("Result =", multiply(a, b))
elif choice == 4:
print("Result =", divide(a, b))
else:
print("Invalid choice")
Output:
1. Addition
2. Subtraction
3. Multiplication
4. Division
5. Exit
Enter your choice: 1
Enter first number: 25
Enter second number: 15
Result = 40.0

3. Generate Random Number Between 1 and 6


import random

number = [Link](1, 6)
print("Random number =", number)
Output:
Random number = 4
(Note: output may vary each time.)

4. Demonstration of Parameters and Default Parameters


def student(name, age=17, city="Delhi"):
print("Name:", name)
print("Age:", age)
print("City:", city)

print("Using default parameters:")


student("Rahul")

print("\nUsing all parameters:")


student("Aman", 18, "Mumbai")
Output:
Using default parameters:
Name: Rahul
Age: 17
City: Delhi

Using all parameters:


Name: Aman
Age: 18
City: Mumbai

5. Count Alphabets, Numbers and Other Characters in a Text File


f = open("[Link]", "r")

alphabets = numbers = others = 0


data = [Link]()

for ch in data:
if [Link]():
alphabets += 1
elif [Link]():
numbers += 1
else:
others += 1

[Link]()

print("Number of alphabets:", alphabets)


print("Number of numbers:", numbers)
print("Number of other characters:", others)
Output:
Number of alphabets: 35
Number of numbers: 5
Number of other characters: 10
(Example output; depends on [Link].)

6. Count the Words "the" or "THE" in a Text File


f = open("[Link]", "r")
count = 0

for line in f:
words = [Link]()
for word in words:
word = [Link](".,!?;:/" + chr(34) + chr(39) + "()[]{}")
if word == "the" or word == "THE":
count += 1

[Link]()
print("Number of 'the' or 'THE':", count)
Output:
Number of 'the' or 'THE': 4
(Example output; depends on [Link].)

7. Count Number of Words Starting with a Vowel


f = open("[Link]", "r")
count = 0

for line in f:
for word in [Link]():
word = [Link](".,!?;:/" + chr(34) + chr(39) + "()[]{}")
if word and word[0].lower() in "aeiou":
count += 1

[Link]()
print("Number of words starting with a vowel:", count)
Output:
Number of words starting with a vowel: 7
(Example output; depends on [Link].)

8. Read a Text File Line by Line and Display Words Separated by "#"
f = open("[Link]", "r")

for line in f:
words = [Link]()
print("#".join(words))

[Link]()
Output:
This#is#a#sample#file
Python#is#easy#to#learn
(Example output.)
9. Copy Lines Starting with 'A' from One File to Another
f1 = open("[Link]", "r")
f2 = open("[Link]", "w")

for line in f1:


if [Link]("A"):
[Link](line)

[Link]()
[Link]()

print("Lines starting with 'A' copied successfully.")


Output:
Lines starting with 'A' copied successfully.

10. Binary File: Store and Search Roll No. and Name
import pickle

f = open("[Link]", "wb")
n = int(input("Enter number of records: "))

for i in range(n):
roll = int(input("Enter Roll No.: "))
name = input("Enter Name: ")
[Link]([roll, name], f)

[Link]()

search_roll = int(input("Enter Roll No. to search: "))


f = open("[Link]", "rb")
found = False

try:
while True:
record = [Link](f)
if record[0] == search_roll:
print("Name:", record[1])
found = True
break
except EOFError:
pass

[Link]()

if not found:
print("Rollno not found")
Output:
Enter number of records: 2
Enter Roll No.: 101
Enter Name: Aman
Enter Roll No.: 102
Enter Name: Riya
Enter Roll No. to search: 102
Name: Riya

11. Binary File: Update Marks Using Roll No.


import pickle

f = open("[Link]", "wb")
n = int(input("Enter number of records: "))
for i in range(n):
roll = int(input("Enter Roll No.: "))
name = input("Enter Name: ")
marks = float(input("Enter Marks: "))
[Link]([roll, name, marks], f)
[Link]()

search_roll = int(input("Enter Roll No. to update: "))


new_marks = float(input("Enter new marks: "))

f = open("[Link]", "rb")
records = []

try:
while True:
record = [Link](f)
if record[0] == search_roll:
record[2] = new_marks
[Link](record)
except EOFError:
pass
[Link]()

f = open("[Link]", "wb")
for record in records:
[Link](record, f)
[Link]()

print("Marks updated successfully.")


Output:
Enter Roll No. to update: 101
Enter new marks: 95
Marks updated successfully.

12. Binary File: Store Dictionary Data Conditionally


import pickle

data = {101:"Aman", 102:"Riya", 103:"Karan", 104:"Neha", 105:"Rahul"}

f = open("[Link]", "wb")

for key in data:


if key % 2 == 0:
[Link]({key: data[key]}, f)

[Link]()
print("Dictionary data stored conditionally in binary file.")
Output:
Dictionary data stored conditionally in binary file.
Stored records: {102: 'Riya'}, {104: 'Neha'}

13. Write and Read Employee Records Using CSV File


import csv

f = open("[Link]", "w", newline="")


writer = [Link](f)
[Link](["Employee Code", "Name", "Salary"])

n = int(input("Enter number of employees: "))


for i in range(n):
code = input("Enter employee code: ")
name = input("Enter name: ")
salary = float(input("Enter salary: "))
[Link]([code, name, salary])
[Link]()

print("\nEmployee Records:")
f = open("[Link]", "r")
reader = [Link](f)
for row in reader:
print(row)
[Link]()
Output:
Employee Records:
['Employee Code', 'Name', 'Salary']
['101', 'Aman', '50000.0']
['102', 'Riya', '55000.0']

14. Write and Display Dictionary Data in CSV File


import csv

data = {
"101": ["Aman", 50000],
"102": ["Riya", 55000],
"103": ["Karan", 60000]
}

f = open("[Link]", "w", newline="")


writer = [Link](f)
[Link](["Employee Code", "Name", "Salary"])

for code, details in [Link]():


[Link]([code, details[0], details[1]])
[Link]()

f = open("[Link]", "r")
reader = [Link](f)

for row in reader:


print(row)

[Link]()
Output:
['Employee Code', 'Name', 'Salary']
['101', 'Aman', '50000']
['102', 'Riya', '55000']
['103', 'Karan', '60000']

15. Implement Stack Using List


stack = []

def push():
item = input("Enter item: ")
[Link](item)
print("Item pushed successfully.")

def pop():
if len(stack) == 0:
print("Stack Underflow")
else:
print("Deleted item:", [Link]())

def peek():
if len(stack) == 0:
print("Stack is empty")
else:
print("Top item:", stack[-1])

def display():
if len(stack) == 0:
print("Stack is empty")
else:
print("Stack:", stack)

while True:
print("\n1. Push 2. Pop 3. Peek 4. Display 5. Exit")
choice = int(input("Enter your choice: "))

if choice == 1:
push()
elif choice == 2:
pop()
elif choice == 3:
peek()
elif choice == 4:
display()
elif choice == 5:
break
else:
print("Invalid choice")
Output:
1. Push 2. Pop 3. Peek 4. Display 5. Exit
Enter your choice: 1
Enter item: A
Item pushed successfully.
Enter your choice: 1
Enter item: B
Item pushed successfully.
Enter your choice: 4
Stack: ['A', 'B']
Enter your choice: 3
Top item: B
Enter your choice: 2
Deleted item: B
B. SQL (STRUCTURED QUERY LANGUAGE)
. 16. Create Student Table and Perform SELECT, DELETE and UPDATE Operations
CREATE DATABASE school;
USE school;

CREATE TABLE student


(
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Class INT,
Section CHAR(1),
Marks INT,
City VARCHAR(30)
);

INSERT INTO student VALUES


(1,'Aman',12,'A',85,'Delhi'),
(2,'Riya',12,'A',92,'Mumbai'),
(3,'Karan',12,'B',76,'Delhi'),
(4,'Neha',12,'B',88,'Jaipur'),
(5,'Rahul',12,'A',95,'Delhi'),
(6,'Simran',12,'C',72,'Chandigarh'),
(7,'Arjun',12,'C',81,'Delhi'),
(8,'Priya',12,'A',90,'Mumbai'),
(9,'Rohan',12,'B',68,'Jaipur'),
(10,'Anjali',12,'C',87,'Delhi');

SELECT * FROM student;

UPDATE student SET Marks=90 WHERE RollNo=3;

DELETE FROM student WHERE RollNo=9;


Output:
SELECT * FROM student;
+--------+---------+-------+---------+-------+------------+
| RollNo | Name | Class | Section | Marks | City |
+--------+---------+-------+---------+-------+------------+
| 1 | Aman | 12 | A | 85 | Delhi |
| 2 | Riya | 12 | A | 92 | Mumbai |
| 3 | Karan | 12 | B | 76 | Delhi |
| ... | ... | ... | ... | ... | ... |
+--------+---------+-------+---------+-------+------------+
UPDATE: Query OK
DELETE: Query OK

. 17. Queries Using Aggregate Functions


SELECT MIN(Marks) AS Minimum FROM student;
SELECT MAX(Marks) AS Maximum FROM student;
SELECT AVG(Marks) AS Average FROM student;
SELECT SUM(Marks) AS Total FROM student;
SELECT COUNT(*) AS Number_of_Students FROM student;
Output:
Minimum = 72
Maximum = 95
Average = 85.1
Total = 851
Number_of_Students = 10
(Values depend on whether the DELETE/UPDATE queries have been executed.)

. 18. Queries Using DISTINCT, BETWEEN, IN, LIKE and ORDER BY


SELECT DISTINCT City FROM student;

SELECT * FROM student


WHERE Marks BETWEEN 80 AND 90;

SELECT * FROM student


WHERE City IN ('Delhi','Mumbai');

SELECT * FROM student


WHERE Name LIKE 'A%';

SELECT * FROM student


ORDER BY Marks DESC;
Output:
DISTINCT City:
Delhi
Mumbai
Jaipur
Chandigarh

BETWEEN 80 AND 90:


Students whose marks lie between 80 and 90 are displayed.

LIKE 'A%':
Aman
Arjun
Anjali

ORDER BY Marks DESC:


Records displayed from highest marks to lowest marks.

. 19. Queries Using GROUP BY and HAVING


SELECT City, COUNT(*) AS Number_of_Students
FROM student
GROUP BY City;

SELECT City, AVG(Marks) AS Average_Marks


FROM student
GROUP BY City;

SELECT City, AVG(Marks) AS Average_Marks


FROM student
GROUP BY City
HAVING AVG(Marks) > 80;
Output:
City-wise number of students and average marks are displayed.
HAVING displays only cities whose average marks are greater than 80.

. 20. Queries Using JOIN


CREATE TABLE department
(
RollNo INT,
Subject VARCHAR(30)
);

INSERT INTO department VALUES


(1,'Computer Science'),
(2,'Physics'),
(3,'Chemistry'),
(4,'Mathematics'),
(5,'Computer Science'),
(6,'Physics'),
(7,'Mathematics'),
(8,'Computer Science'),
(9,'Chemistry'),
(10,'Physics');

SELECT [Link], [Link], [Link]


FROM student
INNER JOIN department
ON [Link] = [Link];
Output:
RollNo | Name | Subject
1 | Aman | Computer Science
2 | Riya | Physics
3 | Karan | Chemistry
4 | Neha | Mathematics
...
C. PYTHON AND SQL CONNECTIVITY
. 21. Python–MySQL: Insert and Display Records
import [Link]

con = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)

cur = [Link]()

roll = int(input("Enter Roll No.: "))


name = input("Enter Name: ")
marks = int(input("Enter Marks: "))

query = """INSERT INTO student


(RollNo, Name, Class, Section, Marks, City)
VALUES (%s, %s, %s, %s, %s, %s)"""

values = (roll, name, 12, 'A', marks, 'Delhi')


[Link](query, values)
[Link]()

print("Record inserted successfully.")

[Link]("SELECT * FROM student")


for row in [Link]():
print(row)

[Link]()
[Link]()
Output:
Enter Roll No.: 11
Enter Name: Meera
Enter Marks: 89
Record inserted successfully.
(11, 'Meera', 12, 'A', 89, 'Delhi')
...

. 22. Python–MySQL: Search and Display Records


import [Link]

con = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)

cur = [Link]()

roll = int(input("Enter Roll No. to search: "))


[Link]("SELECT * FROM student WHERE RollNo = %s", (roll,))

record = [Link]()
if record:
print("Record found:")
print(record)
else:
print("Record not found")

[Link]()
[Link]()
Output:
Enter Roll No. to search: 5
Record found:
(5, 'Rahul', 12, 'A', 95, 'Delhi')

. 23. Python–MySQL: Update Records


import [Link]

con = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)

cur = [Link]()

roll = int(input("Enter Roll No.: "))


marks = int(input("Enter new marks: "))

query = "UPDATE student SET Marks=%s WHERE RollNo=%s"


[Link](query, (marks, roll))
[Link]()

if [Link] > 0:
print("Record updated successfully.")
else:
print("Roll No. not found.")

[Link]()
[Link]()
Output:
Enter Roll No.: 5
Enter new marks: 98
Record updated successfully.

Note: Replace the MySQL password '1234' with your own MySQL root password. File-based program outputs are
sample outputs and may vary according to the input files and records.

You might also like