MYSQL PYTHON CONNECTION CODE

CODE BELOW ADD , RETRIVE AND DELETE RECORDS FROM SQL TABEL

import pandas as pd
import mysql.connector as sqLtor
from sqlalchemy import create_engine

dbengine = create_engine("mysql+pymysql://root:1234@localhost/RISHABH")

connection1 = dbengine.connect()

connection2 = sqLtor.connect(
    host = 'localhost' ,
    user = 'root' , 
    passwd = '1234' , 
    database = "RISHABH"
)

def insert_query(rol , fname , lname , phone):
    mycursor = connection2.cursor()
    query = "insert into students values (%s , %s , %s , %s)"
    values = (rol , fname , lname , phone)
    mycursor.execute(query, values)
    connection2.commit()
    print(mycursor.rowcount, "record inserted.")

def delete_query(primfield , primkey , tbname):
    mycursor = connection2.cursor()
    query = f"delete from {tbname} where {primfield} = {primkey}"
    mycursor.execute(query)
    connection2.commit()
    print(mycursor.rowcount, "record deleted.")

try:
    options = ["INSERT" , "RETRIVE" , "DELETE"]
    print("Options are as follow :-")
    for option in options :
        print(option)
    inp = input("Enter choice : ").upper()
    if inp in options:
        if inp == options[0]:
            roll = int(input("Enter rollno : "))
            fname = input("Enter fname : ")
            lname = input("Enter lname : ")
            phone = input("Enter phoneno : ")
            insert_query(roll , fname , lname , phone)
        if inp == options[1]:
            pass
        if inp == options[2]:
            tbname = input("Enter table name : ")
            primfield = input("Enter unique field name : ")
            primkey = input("Enter field id : ")
            delete_query(primfield , primkey , tbname)
    else : 
        print("Wrong input")

    df1 = pd.read_sql("select * from students ;" , connection1)
    print(df1)
        
except Exception as e:
     print(e)

connection2.close()

import pandas as pd
import mysql.connector as sqLtor
from sqlalchemy import create_engine

dbengine = create_engine("mysql+pymysql://root:1234@localhost/RISHABH")

connection1 = dbengine.connect()

connection2 = sqLtor.connect(
    host = 'localhost' ,
    user = 'root' , 
    passwd = '1234' , 
    database = "RISHABH"
)

lst_of_record = []

def insert_query():
    global lst_of_record
    global use_str
    tup_of_records = tuple(lst_of_record)
    mycursor = connection2.cursor()
    query = f"insert into students values {use_str}"
    mycursor.execute(query, tup_of_records)
    connection2.commit()
    print(f"-----------\n{mycursor.rowcount} record inserted.\n-----------")

def delete_query(primfield , primkey , tbname):
    mycursor = connection2.cursor()
    query = f"delete from {tbname} where {primfield} = {primkey}"
    mycursor.execute(query)
    connection2.commit()
    print(f"-----------\n{mycursor.rowcount } record deleted.\n-----------")

try:
    df1 = pd.read_sql("select * from students ;" , connection1)
    column_of_df1 = df1.columns
    lst_of_column = list(column_of_df1)
    strnglst = []
    
    while True:
        dic_of_choice = {1 : "Insert", 2 : "Revert", 3 : "Delete"}
        print("---------------------")
        for choices in dic_of_choice:
            print(f"{choices} - {dic_of_choice[choices]}")
        print("4 - close")
        choice = int(input("Enter choice : "))
        print("---------------------")
        match choice:
            case 1 :
                for field in lst_of_column:
                    records = input(f"Enter {field} : ")
                    lst_of_record.append(records)
                    strnglst.append("%s")
                tup = tuple(strnglst)
                strg = ",".join(tup)
                use_str = f"({strg})"
                print(use_str ,"0 " , tup , "0" , strg , "0 ",strnglst)
                insert_query()
            case 2 :
                pass
            case 3 : 
                tbname = input("Enter table name : ")
                primfield = input("Enter unique field name : ")
                primkey = input("Enter field id : ")
                delete_query(primfield , primkey , tbname)
            case 4 :
                print("Loop Exit")
                break
            case _ :
                print("Wrong Input")
        print(df1)
except Exception as e :
    print(e)

connection1.close()
connection2.close()

Fixed Some Error

import pandas as pd
import mysql.connector as sqLtor
from sqlalchemy import create_engine


def insert_query():
    global lst_of_record
    global use_str
    tup_of_records = tuple(lst_of_record)
    mycursor = connection2.cursor()
    query = f"insert into students values {use_str}"
    mycursor.execute(query, tup_of_records)
    connection2.commit()
    print(f"-----------\n{mycursor.rowcount} record inserted.\n-----------")

def delete_query(primfield , primkey , tbname):
    mycursor = connection2.cursor()
    query = f"delete from {tbname} where {primfield} = {primkey}"
    mycursor.execute(query)
    connection2.commit()
    print(f"-----------\n{mycursor.rowcount } record deleted.\n-----------")

try:
    dbengine = create_engine("mysql+pymysql://root:1234@localhost/RISHABH")

    connection1 = dbengine.connect()

    connection2 = sqLtor.connect(
        host = 'localhost' ,
        user = 'root' , 
        passwd = '1234' , 
        database = "RISHABH"
    )

    lst_of_record = []
    strnglst = []

    df1 = pd.read_sql("select * from students ;" , connection1)
    column_of_df1 = df1.columns
    lst_of_column = list(column_of_df1)
    print(df1)
    
    while True:
        dic_of_choice = {1 : "Insert", 2 : "Revert", 3 : "Delete"}
        print("---------------------")
        for choices in dic_of_choice:
            print(f"{choices} - {dic_of_choice[choices]}")
        print("4 - close")
        choice = int(input("Enter choice : "))
        print("---------------------")
        match choice:
            case 1 :
                for field in lst_of_column:
                    records = input(f"Enter {field} : ")
                    lst_of_record.append(records)
                    strnglst.append("%s")
                tup = tuple(strnglst)
                strg = ",".join(tup)
                use_str = f"({strg})"
                insert_query()
            case 2 :
                pass
            case 3 : 
                tbname = input("Enter table name : ")
                primfield = input("Enter unique field name : ")
                primkey = input("Enter field id : ")
                delete_query(primfield , primkey , tbname)
            case 4 :
                print("Loop Exit")
                break
            case _ :
                print("Wrong Input")

        dbengine1 = create_engine("mysql+pymysql://root:1234@localhost/RISHABH")

        connection2a= dbengine.connect()
        df2 = pd.read_sql("select * from students" , connection2a)
        print(df2)
except Exception as e :
    print(e)

connection1.close()
connection2.close()