CRUD Operation in MySQL using Python

Run both command in jupyter to install pymysql and mysqlclient

https://dev.mysql.com/downloads/mysql/

#Installing MySQL on Python :
!pip install pymysql
!pip install mysqlclient

1) Database Connection :

import mysql.connector
# Establishing connection with the SQL  
  
dataBase = mysql.connector.connect( 
  host ="localhost", 
  user ="root", 
  password ="sahil123",
  auth_plugin='mysql_native_password',
  port=3306
) 
# Cursor to the database 
cursor = dataBase.cursor() 
  
cursor.execute("CREATE DATABASE Student") 
print("Student Data base is created") 

Student Data base is created

2) Creating Database Table :

import mysql.connector   
  
# connecting to the database  
dataBase = mysql.connector.connect( 
                     host = "localhost", 
                     user = "root", 
                     passwd = "sahil123", 
                     database = "Student" ) 

# preparing a cursor object  
cursorObject = dataBase.cursor()  
  
# creating table   
studentRecord = """CREATE TABLE NEWSTUDENT (  
                   NAME  VARCHAR(20) NOT NULL,  
                   ROLL INT NOT NULL
                   )"""
  
# table created 
cursorObject.execute(studentRecord)  

3) INSERT Operation :

sql = "INSERT INTO newstudent (NAME, ROLL) VALUES (%s, %s)"
val = ("Ram", "85") 
  
cursorObject.execute(sql, val) 
dataBase.commit() 
  
print(cursorObject.rowcount, "details inserted")

1 details inserted

4) INSERT Multiple Operations:

sql = "INSERT INTO newstudent (NAME, ROLL) VALUES (%s, %s)"
val = [("Akash", "98"), 
       ("Neel", "23"), 
       ("Rohan", "43"), 
       ("Amit", "87"), 
       ("Anil", "45"),  
       ("Megha", "55"),  
       ("Sita", "95")] 
  
cursorObject.executemany(sql, val) 
dataBase.commit() 
  
print(cursorObject.rowcount, "details inserted") 

7 details inserted

5) SELECT QUERY:

print("Displaying NAME and ROLL columns from the STUDENT table:") 
  
# selecting query 
query = "SELECT NAME, ROLL FROM NEWSTUDENT"
cursorObject.execute(query) 
  
myresult = cursorObject.fetchall() 
  
for x in myresult: 
    print(x) 

Displaying NAME and ROLL columns from the STUDENT table:

('Ram', 85)

('Ram', 85)

('Akash', 98)

('Neel', 23)

('Rohan', 43)

('Amit', 87)

('Anil', 45)

('Megha', 55)

('Sita', 95)

6) Update Operation :

# drop clause 
statement ="UPDATE NEWSTUDENT SET ROLL = 23 WHERE Name ='Rohan'"
  
cursorObject.execute(statement) 
dataBase.commit() 

Example -2:

# drop clause 
statement ="UPDATE NEWSTUDENT SET NAME = 'ROSHAN' WHERE Name ='Rohan'"
  
cursorObject.execute(statement) 
dataBase.commit() 

7) DELETE OPERATION :

query = "DELETE FROM NEWSTUDENT WHERE ROLL = 55"
cursorObject.execute(query) 
  
dataBase.commit() 

8) DROP TABLE:

statement = "Drop Table newstudent"
  
cursorObject.execute(statement)
  
dataBase.commit() 
dataBase.close()