Exploring MySQL Database Management in Python: Day 32 Insights

Introduction :

Welcome back to my Python journey! Yesterday, I laid the foundation with mysql database in python.

Today, I dove into something more on mysql database, Let's explore what I learned!

#Program for Linux:

from MySQLdb import *

conobj =connect()

curobj = conobj.cursor()

#create a new db

#curobj.execute('create database ONLINE_BATCH;')

#show list all DB

'''

curobj.execute('show databases;')

list_db =curobj.fetchall()

for name in list_db:

print ( name )

'''

curobj.execute('use ONLINE_BATCH;')

#curobj.execute ('create table STUDENT (RegNO int, Name varchar(30), Course_Name varchar(20), Duration varchar(20), Fees int); ')

'''

curobj.execute('show tables;')

list_table=curobj.fetchall()

print (list_table)

'''

#insert record

#curobj.execute ('insert into STUDENT values (1, "rajani","python", "90 days", 5000);')

#curobj.execute ('insert into STUDENT(RegNo, Name, Course_Name, Fees) values (2, "rajani kant" , "C", 4500 ); ')

#curobj.execute ('insert into STUDENT(RegNo, Name, Course_Name, Duration) values (3, "amit","java","60 days");')

#update query

curobj.execute ('update STUDENT set Duration = "45 days" where RegNO = 2 ;')

curobj.execute ('update STUDENT set Fees = 4000 where RegNo =3;')

#delete record :

curobj.execute ('delete from STUDENT where RegNO = 2;')

curobj.execute ('select * from STUDENT;')

record = curobj.fetchall()

print (record)

for RegNo, Name, Cource_Name, Duration, Fees in record :

print (RegNo, Name, Cource_Name, Duration, Fees)

curobj.close()

conobj.close()

Program for window:

#mysql connector driver

import mysql.connector as mysql

conobj =mysql.connect(host ="localhost",user ="",password ="")

curobj = conobj.cursor()

curobj.execute("show databases")

data = curobj.fetchall() print (data)

curobj.close()

conobj.close()

\===========================================

#for pymysql

import pymysql

conobj =pymysql.connect(host ="localhost",user ="root",password ="")

curobj = conobj.cursor()

curobj.execute("show databases")

curobj.close()

conobj.close()

import pymysql

conobj =pymysql.connect (host='localhost',user ='root',password ='', port = 3306 )

curobj = conobj.cursor ()

#curobj.execute ("create database JULBATCH;")

curobj.execute ('use JULBATCH;')

#curobj.execute ('create table STUDENT (Regno int , Name varchar (30), Course varchar (20), Fee int , Duration varchar (15));')

choice = input ("I for Insert Record\nD for Display Record\nDel for Delete record\nUfor Update Record\nS for search Record\nE for Exit ")

if choice == 'I':

no = int (input ("Enter number of record want to insert"))

for i in range (1, no +1):

regno = int (input ("Enter Reg no"))

name = input ("Enter Student name ")

course = input ("Enter course name")

fee = float (input ("Enter Course Fee"))

duration = input ("Enter course Duration")

r = 'insert into STUDENT values ({R},"{N}","{C}",{F},"{D}");'

r1 = r.format (R= regno, N = name , C = course, F = fee , D = duration )

#print (r1)

curobj.execute (r1)

conobj.commit ()

elif choice =='D':

curobj.execute ('select * from STUDENT;')

record =curobj .fetchall ()

for a, b , c , d , e in record : print (a,"\t", b,"\t",c,"\t",d,"\t",e )

elif choice == 'Del':

R = int (input ("Enter reg no which want to delete "))

d = 'delete from STUDENT where Regno = {r}';

d1= d.format (r= R )

#print (d1)

curobj.execute (d1)

conobj.commit ()

elif choice == 'U':

R = int (input ("Enter reg no where want to update "))

C = input ("Which column value want to update")

V = input ("Enter which value want to update ")

u = 'update STUDENT set {c}="{v}" where Regno = {r};'

u1= u.format (c= C, v= V, r= R )

#print (u1)

curobj.execute (u1)

conobj.commit ()

elif choice =='S':

R = int (input ("Enter reg to search details "))

s= 'select * from STUDENT where Regno = {r};'

s1= s.format (r= R)

curobj.execute (s1)

record1= curobj.fetchall ()

for i in record1 :

print (i)

elif choice =='E':

print ("Thank you")

exit (0)

else :

print ("Soory your choice is invalid!!!")

exit (0)

curobj.close ()

conobj.close ()

Challenges :

1. Understanding python databases.

2. Handling errors.

Resources :

1. Official Python Documentation: python databases

2. W3Schools' Python Tutorial: python databases

3. Scaler's Python Courses : python databases

Goals for Tomorrow :

1. Explore something more on mysql database and MongoDB.

Conclusion :

Day 32’s a success!

What are your favorite Python resources? Share in the comments below.

Connect with me :

GitHub: [ https://github.com/p-archana1 ]

LinkedIn : [ linkedin.com/in/archana-prusty-4aa0b827a ]

twitter : [ https://x.com/_archana77 ]

Join the conversation :

Share your own learning experiences or ask questions in the comments.

HAPPY LEARNING!!

THANK YOU!!