Program18:
MySQL CONNECTIVITY
Write a program to create a SQL table Using python and insert the records,
display the structure of the table and to drop one attribute and add a new
attribute in the table.
Aim:
To Write a program to create a SQL table using python and insert the records,
display the structure of the table and to drop one attribute and add a new
attribute in the table.
Program:
import mysql.connector
con = mysql.connector.connect(
host='localhost',
user='root',
password='vijin',
database='bethany'
)
mycursor = con.cursor()
mycursor.execute("CREATE TABLE STUDENT(ROLLNO INT
PRIMARY KEY, SNAME CHAR(25), AGE INT, GENDER
CHAR(10))")
con.commit()
print('## Structure of the table ##')
mycursor.execute('DESC STUDENT')
data = mycursor.fetchall()
for row in data:
print(row)
print('## After Altering ##')
mycursor.execute('ALTER TABLE STUDENT DROP COLUMN
GENDER')
mycursor.execute('DESC STUDENT')
data = mycursor.fetchall()
for row in data:
print(row)
print('## After adding a new Column ##')
mycursor.execute('ALTER TABLE STUDENT ADD COLUMN
BLOODGROUP VARCHAR(10)')
mycursor.execute('DESC STUDENT')
data = mycursor.fetchall()
for row in data:
print(row)
con.commit()
con.close()
Result:
Thus, the program to create a SQL table using Python, alter the records,
display the structure of the table, and drop one attribute was successfully
completed.
Program19:
MySQL CONNECTIVITY
Write a program to create a SQL table Using python and insert the records, and
then demonstrate the ability to alter the table structure, update records, and again
display the data in the table.
Aim:
To create a MySQL database named BETHANY, create a STUDENT table
within that database, insert records into the table, and then demonstrate the
ability to alter the table structure, update records, and display the data in a
specific order using Python.
Program:
import mysql.connector
con = mysql.connector.connect(
host='localhost',
user='root',
password='vijin',
)
mycursor = con.cursor()
mycursor.execute("CREATE DATABASE IF NOT EXISTS
BETHANY")
mycursor.execute("USE BETHANY")
mycursor.execute("DROP TABLE IF EXISTS STUDENT")
mycursor.execute("""
CREATE TABLE STUDENT(
ROLLNO INT PRIMARY KEY,
SNAME CHAR(25),
AGE INT,
GENDER CHAR(10),
TOTALMARK INT
)
""")
mycursor.execute("""
INSERT INTO
STUDENT(ROLLNO,SNAME,AGE,GENDER,TOTALMARK)VALUE
S
(101,'ANU',16,'FEMALE',450),
(102,'MANU',16,'MALE',422),
(103,'ASHA',15,'FEMALE',462),
(104,'ADAM',16,'MALE',499),
(105,'BIJOY',15,'MALE',455)
""")
print('*** To display the student details ***')
mycursor.execute('SELECT * FROM STUDENT')
data=mycursor.fetchall()
for row in data:
print(row)
print('*** Altering the student table ***')
mycursor.execute('ALTER TABLE STUDENT ADD CLASS
VARCHAR(10)')
print('*** Update the student table ***')
mycursor.execute("UPDATE STUDENT SET CLASS='XII_LILY'")
print('*** To display the student details in descending order by totalmark
***')
mycursor.execute('SELECT * FROM STUDENT ORDER BY
TOTALMARK DESC')
data=mycursor.fetchall()
for row in data:
print(row)
con.commit()
con.close()
Result:
Thus, the program to create a SQL table using Python, insert and update
records, alter the table structure, and display the records in a specific
order was successfully completed.
Program 20:
MySQL CONNECTIVITY
Write a program to search employee ID and display employee record from SQL
table using python.
Aim:
To write a program to search employee ID and display employee record from
SQL table using Python.
Program:
import mysql.connector
# Connect to MySQL server
con = mysql.connector.connect(
host='localhost',
user='root',
password='vijin',
)
mycursor = con.cursor()
# Create and use the database
mycursor.execute("CREATE DATABASE IF NOT EXISTS
BETHANYEMP")
mycursor.execute("USE BETHANYEMP")
# Create the EMPLOYEE table
mycursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
mycursor.execute("""
CREATE TABLE EMPLOYEE(
EMPID VARCHAR(20) PRIMARY KEY,
EMPNAME CHAR(25),
AGE INT,
GENDER CHAR(10),
SALARY FLOAT
)
""")
# Insert data into the EMPLOYEE table
mycursor.execute("""
INSERT INTO EMPLOYEE (EMPID, EMPNAME, AGE, GENDER,
SALARY) VALUES
('E101', 'ANU', 19, 'FEMALE', 45000),
('E102', 'MANU', 21, 'MALE', 42200),
('E103', 'ASHA', 19, 'FEMALE', 46200),
('E104', 'ADAM', 19, 'MALE', 49900),
('E105', 'BIJOY', 20, 'MALE', 45500)
""")
print('*** Employee Searching Form ***')
ans = 'y'
while ans.lower() == 'y':
empno = input("Enter the EMPID for searching: ")
query = "SELECT * FROM EMPLOYEE WHERE EMPID =
'{}'".format(empno)
mycursor.execute(query)
data = mycursor.fetchall()
if data:
for row in data:
print(row)
else:
print("Sorry! EMPID not found.")
ans = input('Search again? (y/n) to exit: ')
con.commit()
con.close()
Result:
Thus, the program to search employee ID and display employee record
from SQL table using python was successfully completed.
Program 21:
MySQL CONNECTIVITY
Write a program to create a SQL table using Python and display records using
aggregate functions.
Aim:
To write a program to create a SQL table using Python and display records
using aggregate functions.
Program:
import mysql.connector
# Establishing connection to the database
con = mysql.connector.connect(
host="localhost",
user="root",
password="vijin",
database="bethanyemp"
)
mycursor = con.cursor()
# Creating a table if it doesn't exist
mycursor.execute("""
CREATE TABLE IF NOT EXISTS Sales(
sales_id INT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT,
price FLOAT
)
""")
# Inserting data into the table
mycursor.execute("""
INSERT INTO Sales (sales_id,product_name, quantity, price)
VALUES
(01,'Laptop', 5, 70000.00),
(02,'Mobile', 10, 15000.00),
(03,'Tablet', 7, 30000.00),
(04,'TV', 7, 51000.00),
(05,'bike', 7, 150000.00)
""")
con.commit()
# Demonstrating aggregate functions
mycursor.execute("SELECT SUM(quantity) FROM Sales")
total_quantity = mycursor.fetchone()[0]
mycursor.execute("SELECT AVG(price) FROM Sales")
average_price = mycursor.fetchone()[0]
mycursor.execute("SELECT MIN(price) FROM Sales")
min_price = mycursor.fetchone()[0]
mycursor.execute("SELECT MAX(price) FROM Sales")
max_price = mycursor.fetchone()[0]
mycursor.execute("SELECT COUNT(*) FROM Sales")
total_records = mycursor.fetchone()[0]
# Displaying the results
print(f"Total Quantity Sold: {total_quantity}")
print(f"Average Price of Products: {average_price}")
print(f"Minimum Price of a Product: {min_price}")
print(f"Maximum Price of a Product: {max_price}")
print(f"Total Number of Records: {total_records}")
# Closing the connection
con.close()
Result:
Thus, the program to create a SQL table using Python, insert data, and
display aggregate information was successfully completed.
