MYSQL DATABASE CONNECTIVITY IN PYTHON

MySQL Database Connectivity : 

• First you need to setup Apache, phpMyAdmin and MySQL. 
• To know the setup procedure click here.
• As a example here I am using Users for table name and Id, Name, Age, City for field names.
• The below program shows CRUD operation in MySQL using python.
• C - Create (INSERT)
• R - Read (SELECT)
• U - Update (UPDATE)
• D - Delete (DELETE)

Program : 

# MySQL DATABASE CONNECTIVITY
import mysql.connector

# presenting output in tabular view
from tabulate import tabulate

# connecting the database file to MySQL
connection = mysql.connector.connect(host="localhost", user="enter username", password="enter password", database="enter database file name")
cursor = connection.cursor()

# function to INSERT data
def insert_data(name, age, city):
    # INSERT query
    query = "INSERT INTO Users (name, age, city) values (%s, %s, %s)"
    # to execute the query
    cursor.execute(query, (name, age, city))
    # to save the executed query
    connection.commit()
    print("Data inserted successfully")

# function to READ data
def select_data():
    # SELECT query
    query = "SELECT * FROM Users"
    cursor.execute(query)
    """
        result = cursor.fetchone() -> returns only the top most row
        result = cursor.fetchmany(2) -> returns no of rows that you passed as a argument
    """
    result = cursor.fetchall()
    print(tabulate(result, headers=["ID", "NAME", "AGE", "CITY"]))

# function to UPDATE data
def update_data(id, name, age, city):
    # UPDATE query
    query = "UPDATE Users SET name = %s, age = %s, city = %s WHERE id = %s"
    cursor.execute(query, (name, age, city, id))
    connection.commit()
    print("Data updated successfully")

# function to DELETE data
def delete_data(id):
    # DELETE query
    query = "DELETE FROM Users WHERE id = %s"
    data = id,
    cursor.execute(query, data)
    connection.commit()
    print("Data deleted successfully")

while True:

    print("""
        1. INSERT
        2. SELECT
        3. UPDATE
        4. DELETE
        5. EXIT
    """)
    
    choice = int(input("Enter your choice : "))
    
    if choice == 1:  # INSERT
        print("You have chosen INSERT command")
        
        name = input("Enter name : ")
        age = int(input("Enter age : "))
        city = input("Enter city : ")
        
        insert_data(name, age, city)
        
    elif choice == 2:  # SELECT
        print("You have chosen SELECT command")
        
        select_data()
        
    elif choice == 3:  # UPDATE
        print("You have chosen UPDATE command")
        
        id = int(input("Enter ID : "))
        name = input("Enter name : ")
        age = int(input("Enter age : "))
        city = input("Enter city : ")
        
        update_data(id, name, age, city)
        
    elif choice == 4:  # DELETE
        print("You have chosen DELETE command")
        
        id = int(input("Enter ID : "))
        
        delete_data(id)
        
    elif choice == 5:  # EXIT
        print("Thank You")
        quit()

MySQL Database Connectivity using OOPs concept : 

# MySQL DATABASE CONNECTIVITY USING OOPS CONCEPT
import mysql.connector
# To present output in tabular view
import tabulate

class Db_oops:

    def __init__(self):
        # To connect the database
        self.connection = mysql.connector.connect(host="localhost", user="root", password="gowtham", database="db_2")
        self.cursor = self.connection.cursor()

        # Query to create table
        query = """
            CREATE TABLE IF NOT EXISTS Users(
                Id INT PRIMARY KEY AUTO_INCREMENT,
                Name VARCHAR(20) NOT NULL,
                Age INT NOT NULL,
                City VARCHAR(50) NOT NULL
            );
        """
        self.cursor.execute(query)
        self.connection.commit()

    # INSERT Query
    def insert(self, name, age, city):
        query = "INSERT INTO Users(Name, Age, City) VALUES(%s, %s, %s);"
        self.cursor.execute(query, (name, age, city))
        self.connection.commit()
        print("Inserted Successfully")
        
    # SELECT Query
    def select(self):
        query = "SELECT * FROM Users"
        self.cursor.execute(query)
        result = self.cursor.fetchall()
        # To print the fetched data
        print(tabulate.tabulate(result, headers=["ID", "NAME", "AGE", "CITY"]))

    # UPDATE Query
    def update(self, id, name, age, city):
        query = "UPDATE Users SET Name=%s, Age=%s, City=%s WHERE Id=%s"
        self.cursor.execute(query, (name, age, city, id))
        self.connection.commit()
        print("Updated Successfully")
    
    # DELETE Query
    def delete(self, id):
        query = "DELETE FROM Users WHERE Id = %s"
        self.cursor.execute(query, [id])
        self.connection.commit()
        print("Deleted Successfully")

obj = Db_oops()
while True:

    print("""
        1. INSERT
        2. SELECT
        3. UPDATE
        4. DELETE
        5. EXIT
    """)
    choice = int(input("Enter the choice : "))

    if choice == 1: # INSERT
        name = input("Enter name : ")
        age = input("Enter age : ")
        city = input("Enter city : ")
        obj.insert(name, age, city)

    elif choice == 2: # SELECT
        obj.select()
    
    elif choice == 3: # UPDATE
        id = input("Enter ID : ")
        name = input("Enter name : ")
        age = input("Enter age : ")
        city = input("Enter city : ")
        obj.update(id, name, age, city)

    elif choice == 4: # DELETE
        id = int(input("Enter ID : "))
        obj.delete(id)
    
    elif choice == 5: # EXIT
        print("Thank You")
        quit()

Output : 

INSERT

SELECT

UPDATE

DELETE

Also Refer : SQLite database connectivity in python