Python can be used in database applications. One of the most popular databases is MySQL.

Let's understand how to insert, upadate & delete data from MySQL using python.

Creation of Database

---

A database is a systematic collection of interrelated data stored together in the form of multiple tables with rows and columns so that it can be easily accessed and managed.

The 'CREATE DATABASE' statement is used to create a new SQL database.

CREATE DATABASE databasename;
                

copy

create database myfirstdatabase;

         
                         

Use Database

---

The USE statement of MySQL helps you to select/use a database

Creation Of Tables

---

A table is a logically organised format of rows and columns, each row represent a unique record and each column represent a field in the record.

Tables are created with 'create table' command. Each table must have at least one column

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
);
            

Create a table named 'emp' with columns as code, name, salary

Insertion

---

To fill a table in MySQL, use the "INSERT INTO" statement.

copy

import mysql.connector as c
con = c.connect(host="localhost",
                user="root",
                password="purnima",
                database="myfirstdatabase")
if con.is_connected():
    print("Successfully connected...")
cur = con.cursor()
while True:
    code = int(input("Enter code"))
    name = input("Enter name")
    salary = int(input("Enter salary"))
    query = "insert into emp values ({},'{}',{})".format(code,name,salary)
    cur.execute(query)
    con.commit()
    print("Data inserted successfully...")
    x = int(input("1: Enter more \n 2: Exit \n Enter your choice"))
    if x == 2:
            break

         
                         

Note : the statement: con.commit(). It is required to make the changes, otherwise no changes are made to the table.

Output :



Data is inserted in the table named "emp" as shown below

Updation

---

You can update existing records in a table by using the "UPDATE" statement:

copy

import mysql.connector as c
con = c.connect(host="localhost",
                user="root",
                password="purnima",
                database="myfirstdatabase")
if con.is_connected():
    print("Successfully connected...")
cur = con.cursor()
code = int(input("Enter Employee code:"))
salary = int(input("Enter Employee salary:"))
query = "update emp set salary = {} where code = {}".format(salary,code)
cur.execute(query)
con.commit()
if cur.rowcount>0:
    print("Data upadated succesfully")
else:
    print("Data not found")


 
          
                          

Note : The WHERE clause specifies which record/records should be updated. If you'll not include WHERE clause, all records will be updated

Output :



Data is upadated in the table named "emp" as shown below



When user enters some value which is not there in the table then the output will be as shown below :

Deletion

---

You can delete records from an existing table by using the "DELETE FROM" statement:

copy

import mysql.connector as c
con = c.connect(host="localhost",
                user="root",
                password="purnima",
                database="myfirstdatabase")
if con.is_connected():
    print("Successfully connected...")
cur = con.cursor()
code = int(input("Enter Employee code:"))
query = "delete from emp where  code = {}".format(code)
cur.execute(query)
con.commit()
if cur.rowcount>0:
    print("Record deleted succesfully")
else:
    print("Record not found")


 
 
  
           
                           

Note : The WHERE clause specifies which record/records should be deleted. If you'll not include WHERE clause, all records will be deleted

Output :



Data is upadated in the table named "emp" as shown below



When user enters some value which is not there in the table then the output will be as shown below :

Extracting data from MySQL Table

---

To extract data from the table there are 3 function provided by the python :

• fetchone()


• fetchmany()


• fetchall()

1. fetchone(): Fetches one record at a time.

copy

import mysql.connector as c
con = c.connect(host="localhost",
                user="root",
                password="purnima",
                database="myfirstdatabase")
if con.is_connected():
    print("Successfully connected...")
cur = con.cursor()
cur.execute("select * from emp")
data = cur.fetchone()
print(data)
print("Total no. of rows = " , cur.rowcount)


 
          
                          


                         

Output :



SQL Table Structure :

2. fetchmany() : Fetches given number of records.

copy

import mysql.connector as c
con = c.connect(host="localhost",
                user="root",
                password="purnima",
                database="myfirstdatabase")
if con.is_connected():
    print("Successfully connected...")
cur = con.cursor()
cur.execute("select * from emp")
data = cur.fetchmany(3)
for i in data:
    print(i)
print("Total no. of rows = " , cur.rowcount)
   
   
    
             
                             
   
    
    
     
              
                              
    
    
                             

Output :



SQL Table Structure :

3. fetchall() : Fetches all records from the table.

copy

    import mysql.connector as c
    con = c.connect(host="localhost",
                    user="root",
                    password="purnima",
                    database="myfirstdatabase")
    if con.is_connected():
        print("Successfully connected...")
    cur = con.cursor()
    cur.execute("select * from emp")
    data = cur.fetchmany(3)
    for i in data:
        print(i)
    print("Total no. of rows = " , cur.rowcount)
       
       
        
                 
                                 
       
        
        
         
                  
                                  
        
        
                                 

Output :



SQL Table Structure :