Python - Database Connectivity

 Database:

--------

- The database is a collection of organized information that can easily be used,

   managed, update, and they are classified according to their organizational approach.

- Database is also called as Backend Tool(Store and  maintenance the data)

- Database is a collection of tables(Relation)

Note:

-----

-Table  : Collection of rows and columns

-Rows  : Record or tuples

-Column : Field or Attributes

Example for Database:

--------------------------

1. MySQL

2. SQLite

3. MS SQL

4. PostgreSQL

5. Informix

5. Sybase

6. Inter-base

7. Oracle etc..

Note:   The DB-API is the standard for Pythons database interface(ODBC Support).

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Python Database Connectivity:

---------------------------------------------

-Python can be used in database applications.

-One of the most popular database is MySQL.

Steps are include in Python+MySQL :

---------------------------------

1) Install MySQL Driver :Python needs a MySQL driver to access the MySQL database.

   C:\Users\ISS\AppData\Local\Programs\Python\Python37-32\Scripts\pip install mysql-connector

Via DOS Prompt

-------------

  Step1 : (Windows+R)Run->cmd

  Step2 : cd\

  Step3 : c:\>cd Users\\ISS\\AppData\\Local\\Programs\\Python\\Python37-32\\Scripts>pip install mysql-connector

  

2) import python database interface

----------------------------------

ex:

   import mysql.connector

  

3) Create connection

  --------------------

ex:

   conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')

  

4) Define a Cursor object

-------------------------

ex:

   cursor = conn.cursor()

   

5) Create a SQL Query

---------------------

ex:

   sql=""

   

6) Execute SQL Query

--------------------

ex:

   cursor.execute(sql)

7) Close Connection

--------------------

ex:

   cursor.close()

   conn.close()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Programs:

-----------

1) Create Database

2) Create Table

3) Insert Records

4) View All Records

5) View Specific Record

6) Delete Records

7) Update Records

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 1 : Create a Database

import mysql.connector

try:

# opening a database connection

    conn = mysql.connector.connect(user='root',password='',host='localhost')    

# define a cursor object

    cursor = conn.cursor()

    sql = "create database sample1"

# execute query

    cursor.execute(sql)

# close object

    cursor.close()

# close connection

    conn.close()

    print("Successfully Created...")

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 2 : Create a table

import mysql.connector

try:

# opening a database connection

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

# define a cursor object

    cursor = conn.cursor()

    sql = "create table register(roll int, name1 varchar(15),city varchar(10))"

# execute query

    cursor.execute(sql)

# close object

    cursor.close()

# close connection

    conn.close()

    print("Successfully Created...")

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 3 : Insert Records

import mysql.connector

try:

    a=int(input("Enter Your Roll :"))

    b=input("Enter Your Name :")

    c=input("Enter Your City :")

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

    cursor = conn.cursor()

    sql1 = ("insert into register(roll,name1,city) values(%s,%s,%s)")

    Values=[a,b,c]

    cursor.execute(sql1,Values)

    cursor.close()

    conn.close()

    print("Successfully Inserted...")

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 4 : View Records

import mysql.connector

try:

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

    cursor = conn.cursor()

    sql1 = ("select *from register")

    cursor.execute(sql1)

# fetch all of the rows from the query

    data=cursor.fetchall()

    print("roll\tName\tCity")

    print("----------------")

          

# print the rows

    for row in data :

        print (row[0], row[1], row[2])

    cursor.close()

    conn.close()

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 5 : View Particular Records

import mysql.connector

try:

    roll=int(input("Enter the roll :"))

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

    cursor = conn.cursor()

    sql1 = ("select *from register where roll=%s")

    values=[roll]

    cursor.execute(sql1,values)

# fetch all of the rows from the query

    data=cursor.fetchall()

    print("roll\tName\tCity")

    print("----------------")

          

# print the rows

    for row in data :

        print (row[0], row[1], row[2])

    cursor.close()

    conn.close()

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 6 : Delete Particular Records

import mysql.connector

try:

    roll=int(input("Enter the roll :"))

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

    cursor = conn.cursor()

    sql1 = ("delete from register where roll=%s")

    values=[roll]

    cursor.execute(sql1,values)

    print("Successfully Deleted...")

    cursor.close()

    conn.close()

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 7 : Update Records

import mysql.connector

try:

    roll=int(input("Enter the roll :"))

    name=input("Enter username : ")

    city=input("Enter City : ")

    

    conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

    cursor = conn.cursor()

    sql1 = ("update register set name1=%s,city=%s where roll=%s")

    values=[name,city,roll]

    cursor.execute(sql1,values)

    print("Successfully Updated...")

    cursor.close()

    conn.close()

except Exception as e1:

    print (e1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----------------------------------------------

GUI Registration Form

-----------------------------------------------

#Program 1: Create a Registration Form

from tkinter import *

import mysql.connector

import tkinter

from tkinter import messagebox

window=Tk()

window.minsize(300,200)

window.geometry("320x200")

def register():

    try:

        a=int(a1.get())

        b=a2.get()

        c=a3.get()

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("insert into register(roll,name1,city) values(%s,%s,%s)")

        Values=[a,b,c]

        cursor.execute(sql1,Values)

        cursor.close()

        conn.close()

        messagebox.showinfo("Information","Success")

    except Exception as e1:

        print (e1)

def clear():

    a1.set("")

    a2.set("")

    a3.set("")

    

a1=StringVar()   #a1=IntVar()

a2=StringVar()   #a2=IntVar()

a3=StringVar()   #a3=IntVar()

l1=Label(window, text='Roll')

l1.pack()

t1=Entry(window,textvariable=a1)

t1.focus_set()

t1.pack()

l2=Label(window, text='Name')

l2.pack()

t2=Entry(window,textvariable=a2)

t2.pack()

l3=Label(window, text='City')

l3.pack()

t3=Entry(window,textvariable=a3)

t3.pack()

b1=Button(window,text="Register",command=register,width=20)

b1.pack(pady=10)

b2=Button(window,text="Clear",command=clear,width=20)

b2.pack()

window.mainloop()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----------------------------------------------

GUI Login Form

-----------------------------------------------

#Program 1 : Design Login Form

from tkinter import *

import mysql.connector

import tkinter

from tkinter import messagebox

import os

window=Tk()

window.minsize(300,200)

window.geometry("320x200")

def login():

    try:

        a=a1.get()

        b=a2.get()

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("select *from register1 where username=%s and password=%s")

        Values=[a,b]

        cursor.execute(sql1,Values)

        # fetch all of the rows from the query

        data=cursor.fetchall()

#print the rows

        for row in data :

            messagebox.showinfo("Information","Login Success")

            os.system(“python user.py”)

            break

        else:

            messagebox.showerror("Error", "Invalid Login")

        cursor.close()

        conn.close()

    except Exception as e1:

        print (e1)

def clear():

    a1.set("")

    a2.set("")

       

a1=StringVar()   #a1=IntVar()

a2=StringVar()   #a2=IntVar()

l1=Label(window, text='username')

l1.pack()

t1=Entry(window,textvariable=a1)

t1.focus_set()

t1.pack()

l2=Label(window, text='password')

l2.pack()

t2=Entry(window,textvariable=a2,show='*')

t2.pack()

b1=Button(window,text="Login",command=login,width=20)

b1.pack(pady=10)

b2=Button(window,text="Clear",command=clear,width=20)

b2.pack()

window.mainloop()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

#Program 2 : Design Login Form using Grid

from tkinter import *

import mysql.connector

import tkinter

from tkinter import messagebox

import os

window=Tk()

window.minsize(300,200)

window.geometry("320x200")

def login():

    try:

        a=a1.get()

        b=a2.get()

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("select *from register1 where username=%s and password=%s")

        Values=[a,b]

        cursor.execute(sql1,Values)

        # fetch all of the rows from the query

        data=cursor.fetchall()

#print the rows

        for row in data :

            messagebox.showinfo("Information","Login Success")

            os.system(“python user.py”)

            break

        else:

            messagebox.showerror("Error", "Invalid Login")

        cursor.close()

        conn.close()

    except Exception as e1:

        print (e1)

def clear():

    a1.set("")

    a2.set("")

       

a1=StringVar()   #a1=IntVar()

a2=StringVar()   #a2=IntVar()

l1=Label(window, text='username').grid(row=0,column = 0)

t1=Entry(window,textvariable=a1).grid(row=0,column = 1)

l2=Label(window, text='password').grid(row=1,column = 0)

t2=Entry(window,textvariable=a2,show='*').grid(row=1,column = 1)

b1=Button(window,text="Login",command=login,width=20).grid(row=2,column = 0)

b2=Button(window,text="Clear",command=clear,width=20).grid(row=2,column = 1)

window.mainloop()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-----------------------------------------------

GUI View,Update & Delete Form

-----------------------------------------------

#Program 1 : Design a View,Update & Delete Form

from tkinter import *

import mysql.connector

import tkinter

from tkinter import messagebox

window=Tk()

window.minsize(300,200)

window.geometry("320x200")

#View Data

def view():

    try:

        a=int(a1.get())

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("select *from register where roll=%s")

        Values=[a]

        cursor.execute(sql1,Values)

        # fetch all of the rows from the query

        data=cursor.fetchall()

#print the rows

        for row in data :

            a2.set(row[1])

            a3.set(row[2])

            break

        else:

            messagebox.showerror("Error", "Invalid Roll Number..")

        cursor.close()

        conn.close()

    except Exception as e1:

        print (e1)

#Clear

def clear():

    a1.set("")

    a2.set("")

    a3.set("")

#Update Data

def update():

    try:

        a=int(a1.get())

        b=a2.get()

        c=a3.get()

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("update register set name1=%s,city=%s where roll=%s")

        Values=[b,c,a]

        cursor.execute(sql1,Values)

        cursor.close()

        conn.close()

        messagebox.showinfo("Information","Successfully Updated...!")

    except Exception as e1:

        print (e1)

        

#Delete Data

def delete():

    try:

        a=int(a1.get())

        conn = mysql.connector.connect(user='root',password='',host='localhost',database='sample')    

        cursor = conn.cursor()

        sql1 = ("delete from register where roll=%s")

        Values=[a]

        cursor.execute(sql1,Values)

        cursor.close()

        conn.close()

        messagebox.showinfo("Information","Successfully Deleted...!")

    except Exception as e1:

        print (e1)

        

a1=StringVar()   #a1=IntVar()

a2=StringVar()   #a2=IntVar()

a3=StringVar()   #a3=IntVar()

l1=Label(window, text='Roll')

l1.pack()

t1=Entry(window,textvariable=a1)

t1.focus_set()

t1.pack()

b1=Button(window,text="View",command=view,width=20)

b1.pack(pady=10)

l2=Label(window, text='Name')

l2.pack()

t2=Entry(window,textvariable=a2)

t2.pack()

l3=Label(window, text='City')

l3.pack()

t3=Entry(window,textvariable=a3)

t3.pack()

b2=Button(window,text="Update",command=update,width=20)

b2.pack()

b2.pack(pady=10)

b3=Button(window,text="Delete",command=delete,width=20)

b3.pack()

b3.pack(pady=10)

b4=Button(window,text="Clear",command=clear,width=20)

b4.pack()

window.mainloop()

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~