Database-

    • – Mainly there are two ways by which user can store the information and these are-

 

    • 1) File System –

      Unstructured hence the data processing becomes complex.

 

    • 2) DBMS –

      DBMS stands for “Database Management System”.

    • – In DBMS data stored in the form of structured data.
    • – In DBMS processing is very easy as comparative to file system.

 

  • MySQL:-
  • https://dev.mysql.com/downloads/installer/
  • module – mysql.connector

Queries-

  • create database sab101;
  • show databases;
  • use sab101;
  • drop database sab101;
  • show tables;
  • create table sab101(no int, name varchar(20));
  • select * from sab101;
  • select no,name from sab101;
  • insert into sab101 values(1, ‘Python1’);
  • delete from sab101;
  • delete from sab101 where no=3;
  • update sab101 set name=’Sachin’ where no=2;
  • desc sab101;

Ex-

import mysql.connector
class DB:
    con = None
    def main():
        try:
            DB.con=mysql.connector.connect(user="root", password="root", host="127.0.0.1", database="sab103")
            cur = DB.con.cursor() # We are creating the cursor object
            #query = "delete from tab103 where no=1"
            query = "update tab103 set name='Python7' where no=2"
            cur.execute(query)
            print("Query executed")
            DB.con.commit()

        except Exception as ex:
            print("Error-", ex)
        finally:
            if DB.con != None:
                DB.con.close()
                print("DB closed")
DB.main()
Output-
Query executed
DB closed

Ex-

import mysql.connector
class DB:
    con = None
    def main():
        try:
            DB.con=mysql.connector.connect(user="root", password="root", host="127.0.0.1", database="sab103")
            print("DB connected")
            cur = DB.con.cursor() # We are creating the cursor object
            query = "select * from tab103"
            cur.execute(query)
            record = cur.fetchall()
            #record = cur.fetchone()
            print(record)
            print("***********")
            for recordData in record:
                print(recordData)
            #print(record[1])
        except Exception as ex:
            print("Error-", ex)
        finally:
            if DB.con != None:
                DB.con.close()
                print("DB closed")
DB.main()

Output-
DB connected
[(2, 'Python7'), (2, 'Python7'), (3, 'Sachin'), (1, 'Python1'), (4, 'Python4'), (17, 'python7')]
***********
(2, 'Python7')
(2, 'Python7')
(3, 'Sachin')
(1, 'Python1')
(4, 'Python4')
(17, 'python7')
DB closed

Ex-

import mysql.connector
class DB:
    con = None
    def main():
        try:
            DB.con=mysql.connector.connect(user="root", password="root", host="127.0.0.1", database="sab103")
            print("DB connected")
            cur = DB.con.cursor() # We are creating the cursor object
            #query = "insert into tab103 values(4, 'Python4')"
            no = int(input("Enter no-"))
            name = input("Enter name-")
            query = "insert into tab103 values(%s, %s)"
            val = (no, name)
            cur.execute(query, val)
            DB.con.commit()

        except Exception as ex:
            print("Error-", ex)
        finally:
            if DB.con != None:
                DB.con.close()
                print("DB closed")
DB.main()

Output-
DB connected
Enter no-17
Enter name-Python
DB closed
    • – Cursor object is required to execute the query.
    • – execute() method of a cursor object is used to sent a query to the Database application to process the data.

 

  • Static And Dynamic Query –

  • Static Query –

  • query = “insert into tab101 values(3, ‘Python3’)”

Dynamic Query –

	    no = int(input("Enter no "))
            name = input("Enter Name ")
            query = "insert into tab101 values(%s, %s)"
            val=(no, name)
            cur.execute(query, val)
Back
Next
Menu