Scribd
Upload
8 views5 pages

MySQL Python Project Operations Guide

The document contains class notes for a XII Informatics Practices course, focusing on MySQL operations using Python. It includes code snippets for connecting to a MySQL database, creating and modifying tables, and performing CRUD operations through a menu-driven program. Additionally, it demonstrates how to visualize data using a bar graph with Matplotlib.

Uploaded by

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

MySQL Python Project Operations Guide

The document contains class notes for a XII Informatics Practices course, focusing on MySQL operations using Python. It includes code snippets for connecting to a MySQL database, creating and modifying tables, and performing CRUD operations through a menu-driven program. Additionally, it demonstrates how to visualize data using a bar graph with Matplotlib.

Uploaded by

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

Class Notes

Class: XII Date: 21-12-2020

Subject: Topic: Additional Notes on Project


Informatics Practices

Project Contents:

#To check for all the databases, present in MySQL using Python
import [Link]

mydb = [Link](host="localhost",user="root",passwd="opjs")

mycursor = [Link]()

[Link]("SHOW DATABASES")

for x in mycursor:

print(x)

#To Create a Database Table:


import [Link]

mydb = [Link](host='localhost',user='root',passwd='opjs', database='school')

mycursor = [Link]()

[Link]("CREATE table students1(rollno int(2), name varchar(10), age int(2), marks decimal(5,2), city
varchar(20))")

#To modify table student (adding a new column) in


#MySQL using Python Interface

import [Link]

mydb = [Link](host="localhost",\

user="root",\

passwd="opjs",\

database="ajay")

mycursor = [Link]()

[Link]("Alter table students add(marks2 decimal(5,2))")

#To view the modified structure of table student in


#MySQL using Python Interface
import [Link]

mydb = [Link](host="localhost",\

user="root",\

passwd="opjs",\

database="school")

mycursor = [Link]()

[Link]("Desc students1")

for x in mycursor:

print(x)

#Menu-driven program to demonstrate FIVE major operations


#performed on a table through MySQL-Python connectivity

def menu():

c='y'

while (c=='y'):

print ("1. add record")

print ("2. update record ")

print ("3. delete record")

print("4. display records")

print ("5. display graph")

print("6. Exiting")

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

if choice == 1:

adddata()

elif choice== 2:

updatedata()

elif choice== 3:

deldata()

elif choice== 4:

fetchdata()
elif choice==5:

graph()

elif choice == 6:

print("Exiting")

break

else:

print("wrong input")

c=input("Do you want to continue or not: ")

def fetchdata():

import [Link]

try:

mydb = [Link](host="localhost",user="root",passwd="opjs",database="ajay")

mycursor = [Link]()

[Link]("Select * from students")

myrecords = [Link]()

for x in myrecords:

print(x)

except:

print ("Error: unable to fetch data")

def adddata():

try:

import [Link]

mydb = [Link](host="localhost",user="root",passwd="opjs",database="ajay")

mycursor = [Link]()

[Link]("INSERT INTO students VALUES(2,'Pooja',21, 'VI','A', 'Pending',390,320)")

[Link]("INSERT INTO students VALUES(3,'Radhika',18, 'VII','B','Evaluated',388,450)")

[Link]("INSERT INTO students VALUES(4,'Sonia',24,'X','D', 'Pending',300,544)")

[Link]("INSERT INTO students VALUES(5,'Vinay',25,'XI','C','Evaluated',410,345)")

[Link]("INSERT INTO students VALUES(10,'Shaurya',15,'X','C','Evaluated',345,560)")


[Link]()

except Exception as e:

print(e)

def deldata():

try:

import [Link]

mydb = [Link](host="localhost",user="root",passwd="opjs",database="ajay")

mycursor = [Link]()

rno= int(input("Input the rollno to delete the record:"))

qry="DELETE FROM students where Rollno = %s;" %(rno,)

[Link](qry)

[Link]()

print([Link],"Record (s) Deleted")

except Exception as e:

print(e)

def updatedata():

try:

import [Link]

mydb = [Link](host="localhost",user="root",passwd="opjs",database="ajay")

mycursor = [Link]()

mks=float(input("Input the marks to update:"))

nm=input("Input the name for marks will be changed")

qry="UPDATE students set marks1 = %s where Name = '%s';"%(mks,nm)

[Link](qry)

[Link]()

print([Link],"Record (s) Updated")

except Exception as e:

print(e)
def graph():

try:

import [Link]

import pandas as pd

import [Link] as plt

mydb = [Link](host="localhost",user="root",passwd="opjs",database="ajay")

qry="Select name,marks1 from students;"

df=pd.read_sql(qry, mydb)

print(df)

[Link](df['name'],df['marks1'])

[Link]()

except Exception as e:

print(e)

menu()

Common questions

Powered by AI

A menu-driven program in Python to perform operations on a MySQL table includes a loop displaying options like 'add record', 'update record', 'delete record', 'display records', etc. A user inputs a choice which triggers corresponding functions like 'adddata()', 'updatedata()', 'deldata()', 'fetchdata()', or others for specific operations. Each function performs database operations such as insertion, updating, deletion, or fetching data, and includes error handling to manage exceptions. This design enables dynamic database manipulation through user interaction .

To fetch and display records from a MySQL table in Python, import 'mysql.connector' and connect to the database using 'mysql.connector.connect()'. Create a cursor object via 'mydb.cursor()' and execute a 'SELECT * FROM table_name' command using 'mycursor.execute()'. Retrieve the records using 'mycursor.fetchall()' and iterate over 'myrecords' to print each record. Ensure error handling is implemented to manage any issues during data retrieval .

To update specific records in a MySQL database using Python, first connect to the database and create a cursor. Execute an 'UPDATE table_name SET column_name = value WHERE condition' command, for example, updating marks using 'UPDATE students SET marks1 = %s WHERE Name = %s'. Surround these operations with a 'try' block and implement an 'except' block to catch potential exceptions. Finally, commit changes with 'mydb.commit()' to apply the updates to the database .

Inserting multiple records into a MySQL table in Python involves creating a connection using 'mysql.connector.connect()', followed by creating a cursor with 'mydb.cursor()'. Execute 'INSERT INTO table_name VALUES (value1, value2, ...)' statements for each record using 'mycursor.execute()'. After all insertions, call 'mydb.commit()' to save changes to the database. This method accommodates adding multiple records in a single transaction and ensures data integrity .

To alter a table in MySQL using Python to add a new column, import 'mysql.connector' and connect to the database using 'mysql.connector.connect()' with the appropriate parameters. Create a cursor object using 'mydb.cursor()'. Execute the SQL 'ALTER TABLE' statement to add the new column, for instance, 'ALTER TABLE students ADD(marks2 DECIMAL(5,2))'. This will modify the table structure and add the specified column .

To create a new table in MySQL using Python, import 'mysql.connector' and establish a connection to the database using 'mysql.connector.connect()' with specified parameters. Create a cursor object using 'mydb.cursor()'. Execute the SQL command to create the table, specifying the table name and the data types for each column, e.g., 'CREATE TABLE students1(rollno int(2), name varchar(10), age int(2), marks decimal(5,2), city varchar(20))'. Finally, commit the transaction if necessary .

To display all databases in MySQL using Python, follow these steps: First, import 'mysql.connector'. Next, establish a connection using 'mysql.connector.connect()' with parameters 'host', 'user', and 'passwd'. Then create a cursor object using 'mydb.cursor()'. Execute the SQL command 'SHOW DATABASES' with 'mycursor.execute()'. Finally, iterate through 'mycursor' to print each database name .

A Python script handles user input for record deletion by prompting the user to input details like 'rollno' for the record they wish to delete. The input is obtained via 'input()' and converted to the appropriate type if necessary. Execute the delete SQL command using the cursor, formatted with the user's input, e.g., 'DELETE FROM students WHERE Rollno = %s'. After executing the command, call 'mydb.commit()' to save changes, and provide feedback on the number of records deleted. This interaction facilitates precise control over database modifications by the user .

Errors during database operations in Python can include connection errors, SQL syntax errors, or operational errors like accessing non-existent tables. These can be handled using exception handling in Python. Surround database operations with a 'try' block and catch exceptions using an 'except' block. Log or print error messages to identify issues. For instance, if data fetching fails, output 'Error: unable to fetch data' to notify about the failure .

Incorporating data visualization in a Python-MySQL program involves using libraries like 'pandas' and 'matplotlib'. After fetching data from a MySQL table using a query, convert the result into a DataFrame using 'pandas.read_sql()'. Utilize 'matplotlib' to plot graphs, such as bar graphs, by specifying DataFrame columns for axes. This allows for intuitive visual representation of database content, aiding in better data analysis and presentation .

You might also like