CRUD OPERATION WITH PYTHON AND MYSQL

Here we are going to perform CRUD  (CREATE, READ, UPDATE, and DELETE) operations in python using MYSQL.
for this we are going to install  MYSQL connector .For MYSQL we have used  Visual studio Code for python .To perform
CRUD operations with python and MYSQL, you need to perform this four steps

• Setting up MYSQL Database
• Installing MYSQL Connector
• Connecting to Database
• Performing CRUD operations
  

   CRUD operations

  creating Database

  To perform CRUD operations firstly we need to create the Database . now we are going to create an employee database .we will give name as employee_db and table named tblemployee
  

• Syntax for creating the Database
• CREATE DATABASE
  <DATABASE_NAME>

  import mysql.connector 
  db = mysql.connector.connect(
       host="localhost",
       user="root",
       passwd="password"
  )
  c = db.cursor()
  c.execute("CREATE DATABASE employee_db")
  c.execute("SHOW DATABASES")
  for i in c:
       print(i)
  c = db.cursor()
  db.close()

  output:

  ('information_schema',)
  ('employee_db',)
  ('mysql',)
  ('performance_schema',)

  Creating Table

  now we want to create the table, we need to follow the syntax
  CREATE TABLE
  (
  column1 column1_data_type,
  column2 column2_data_type,
  column3
  column3_data_type…
  );

  import mysql.connector
  db = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="password",
  database="employee_db"
  
  = db.cursor()
  employeetbl_create
  c.execute(employeetbl_create)
  c = db.cursor()
  c.execute("desc tblemployee")
  for i in c:
        print(i)
  db.close()

  output:
  ('empid','int(10)','NO','PRI',None,'auto_increment')
  ('empname','varchar(40)','YES','',None,'')
  ('department',varchar(40)',YES', '', None, '')
  ('salary', 'int(10)', 'YES', '', None,'')

  Inserting Data

  now we will inserting the data into tables .For that we need to follow this syntax

  INSERT INTO   <TABLE_NAME>
  (column1, column2,column3..)
  VALUES (data1, data2,data3..);

  import mysql.connector
   db = mysql.connector.connect(
   host="localhost", 
   user="root",
   passwd="password", 
   database="employee_db" 
  ) 
  c = db.cursor()
   employeetbl_insert = """ INSERT INTO
   empname,
   department,
   salary)
   VALUES (%s, $s, $s)""" 
  data = [("Renu","HR", "115000"), 
          ("Saahus","Accounts", "60000"),
          ("Oliva","Sales","250000"), 
          ("Revanth","Marketing","40000")]
   c.executemany(employeetbl_insert, data) 
  db.commit()
  db.close()
  

  output:
  empid           empname                 department                 salary
  1                Renu                     HR                       100000
  2                Saahus                   Accounts                 60000
  3                Oliva                    Sales                    25000
  4                Revanth                  Marketing                40000

• Reading

  for reading the data we need to follow this command

• SELECT * FROM <TABLE_NAME>
  this command is used to fetch columns from table
  

  import mysql.connector 
  db = mysql.connector.connect(
       host="localhost", 
       user="root",
       passwd="password", 
       database="employee_db"
   )
   c = db.cursor()
   employeetbl_select = """SELECT * FROM tblemployee'""
   c.execute(employeetbl_select)
   employee_data = c.fetchall()
   for e in employee_data:
   print(e)
   db.close

  output
  (1,'Renu', 'HR', 100000)
  (2,'Saahus', 'Accounts', 600000)
  (3,' Oliva', 'Sales', 25000)
  (4,'Revanth,'Marketing', 40000)

  Update

  Update means simply making the changes in existing data. For example its like editing a document or updating your contact information on your phone. firstly you want to identify the data, which you need to change or update .we will run this updated query by using syntax

• UPDATE <TABLE_NAME> SET
  <COLUMN_NAME> = <VALUE> WHERE
  <PRIMARY KEY NAME> =<PRIMARY KEY VALUE>

  import mysql.connector 
  db = mysql.connector.connect( 
       host="localhost",
       user="root",
       passwd="password",
       database="employee_db",
      
       b.cursor()
       yeetbl_update = "UPDATE tblemployee\alary = 200000 WHERE empid = 1"
       c.execute(employeetb1_update)
       db.close()

  output

  empid           empname            department             salary 
  1                Renu                 HR                   115000
  2                Saahus              Accounts              60000
  3                Oliva               Sales                 25000
  4                Revanth             Marketing             40000
  
  

  Deleting Data

• Removing  existing data from the database, for that firstly you want to specify which data you want to remove  from database
  this is usually done by providing some criteria ,like an ID or name. After you identifying the data you will send a command to
  database to remove that particular data.
• DELETE FROM <TABLE_NAME> WHERE
  <PRIMARY KEY NAME> = <PRIMARY KEY VALUE>

  import mysql.connector
  db = mysql.connector.connect (
       host="localhost",
       user="root",
       passwd="password",
       database="employee_db"
  )
  c = db.cursor()
  employeetbl_delete = "DELETE FROM tblemployee WHERE empid = 1"
  c.execute(employeetbl_delete)
  db.commit()
  db.close()

  output
  empid         empname       department       salary
  2             Saahus        Accounts         60000
  3             Oliva         Sales            25000
  4             Revanth       Marketing        40000