Managing Database-SQL Server with Python

This article specifically focuses on python interacting with SQL Database.

First of all this module is an application of python concepts to interact with SQL Database.

This is an application of python concepts.

There are some bare minimum steps we need to be aware of to interact with database.

Below are the steps required to connect to the database.

1) Install pyodbc on your machine.

1.a) Go to the path were python is installed. 

C:\Users\user\AppData\Local\Programs\Python\Python38-32

Ideally the directory structure should looks like this. It should have Scripts folder in it.

1.b) Go to the script folder

There is a file called as pip.exe. This pip.exe is used for downloading the pyodbc package from the internet.

2) Below sample code is a DAL to perform CRUD operation.

import pyodbc

class DAL: #data access layer

    def insertdata(self,strquery):

        c=self.getcursor()

        c.execute(strquery)

        c.commit()

        return c

    def updatedata(self,strquery):

        c=self.getcursor()

        c.execute(strquery)

        c.commit()

        return c

    def selectdata(self,strquery):

        c=self.getcursor()

        c.execute(strquery)

        return c

    def getcursor(self):#purely for db connection only.

        #Code to connect the database

        #1 connection string

        strconnectionstring= """Driver={SQL Server Native Client 11.0}; 

                              Server=DELL\\SQLEXPRESS;

                              Trusted_Connection=yes;

                              Database=EmployeeDB"""

        #2 connection object

        cursor=None

        try:

            conn=pyodbc.connect(strconnectionstring)

        # cursor Object  This is responsbile to execute the query

            cursor=conn.cursor()

        except Exception as e:

            print(e) # logging 

        return cursor

3) Below sample code is client code which imports the DAL to perform the CRUD operation.

#Creating DataEntry Form

import tkinter as tk

from tkinter import messagebox

import LearningDatabase2 as dal

errormessage=".."

IU="I"

form = tk.Tk() 

form.geometry("300x200")

form.title('Learning Threads') 

# select EmpId, Name, Age, Salary, Department from employee where EmpId=123

def getemployeedatabyid():

    global IU

    objdal=dal.DAL()

    strquery="select EmpId, Name, Age, Salary, Department from employee where EmpId="+str(txtemployeeid.get());

    c=objdal.selectdata(strquery)

    print(type(c))

    lstrow=[]

    for row in c:

        lstrow.append(row)

        print(row)

    

    if lstrow.__len__()>0:

        tpl=lstrow[0]

        empname=tpl[1]

        age=tpl[2]

        salary=tpl[3]

        dept=tpl[4]

        txtName.insert(0,empname)

        txtage.insert(0,age)

        txtsalary.insert(0,salary)

        txtdepartment.insert(0,dept)

        IU="U"

        

    else:

        errormessage="No data found for employee id "+txtemployeeid.get()

        messagebox.showinfo("Info",errormessage)

    print(lstrow)

# def insertemployeedata():

#     if IU=="I":

#         strquery="insert into employee (EmpId, Name, Age, Salary, Department) values("+txtemployeeid.get()+",'"+txtName.get()+"',"+txtage.get()+","+txtsalary.get()+",'"+txtdepartment.get()+"')"

#             #insert into employee(EmpId, Name, Age, Salary, Department) values(1,'Sheetal',37,100000,'IT')

#         objdal=dal.DAL()

#         objdal.insertdata(strquery)

#     elif IU=="U":

#         updateemployeedata()

    

# def updateemployeedata():

#     strquery="update employee set Name='"+txtName.get()+"', Age="+txtage.get()+", Salary="+txtsalary.get()+", Department='"+txtdepartment.get()+"' where empid=" +txtemployeeid.get();

    

#     objdal=dal.DAL()

#     # objdal.insertdata(strquery)

#     print(strquery) 

def IUData():

    if IU=="I":

        strquery="insert into employee (EmpId, Name, Age, Salary, Department) values("+txtemployeeid.get()+",'"+txtName.get()+"',"+txtage.get()+","+txtsalary.get()+",'"+txtdepartment.get()+"')"

    elif IU=="U":

        strquery="update employee set Name='"+txtName.get()+"', Age="+txtage.get()+", Salary="+txtsalary.get()+", Department='"+txtdepartment.get()+"' where empid=" +txtemployeeid.get();

    

    objdal=dal.DAL()

    objdal.insertdata(strquery)

    

lblHeader=tk.Label(form,text="Employee Data")

lblHeader.grid(row=0,columnspan=4)

lblName=tk.Label(form,text="Name")

lblName.grid(row=1,column=0)

txtName=tk.Entry(form)

txtName.grid(row=1,column=1)

lblage=tk.Label(form,text="Age")

lblage.grid(row=2,column=0)

txtage=tk.Entry(form)

txtage.grid(row=2,column=1)

lblsalary=tk.Label(form,text="Salary")

lblsalary.grid(row=3,column=0)

txtsalary=tk.Entry(form)

txtsalary.grid(row=3,column=1)

lbldepartment=tk.Label(form,text="Deparment")

lbldepartment.grid(row=4,column=0)

txtdepartment=tk.Entry(form)

txtdepartment.grid(row=4,column=1)

lblemployeeid=tk.Label(form,text="Emp ID")

lblemployeeid.grid(row=5,column=0)

txtemployeeid=tk.Entry(form)

txtemployeeid.grid(row=5,column=1)

btnsave=tk.Button(form,text="Clear")

btnsave.grid(row=6,column=0)

btnclear=tk.Button(form,text="Save",command=IUData)

btnclear.grid(row=6,column=1)

btnsearch=tk.Button(form,text="Search",command=getemployeedatabyid)

btnsearch.grid(row=6,column=2)

lblmessage=tk.Label(form,textvariable=errormessage)

lblmessage.grid(row=7,columnspan=3)

form.mainloop() 

Multithreading

Considering M1(),M2(),M3() are the tasks that needs to be executed.

This can be achieved in two ways

1)       Sequentially:

Note: Length of the Rectangle denote the time of execution.

When button is clicked, main method is executed and hence m1,m2,m3 are execute in sequence. Let say M1() takes 10 secs, M2() takes 25 secs ,M3() takes 15 secs , so total time to complete the process will be 50 secs. So during 50 sec, all the method are executing in sequence, window gets stuck and shows as not responding.

After 50 seconds, the window resume back to normal.

Sample code

import threading

import time

def main():

    

    print("M0 Thread Executed  in thread id::",threading.get_native_id())

    m1()

    m2()

    m3()

    print("M0 Ends after 10 seconds")

def m1():

    

    print("M1 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(10)#This methods takes 5 seconds to complete the task

    print("M1 Ends after 10 seconds")

    

def m2():

    

    print("M2 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(25)#This methods takes 4 seconds to complete the task

    print("M2 Ends after 25 seconds")

def m3():

    

    print("M3 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(15)#This methods takes 5 seconds to complete the task

    print("M3 Ends after 15 seconds")

    

print("Main Thread Executed  in thread id::",threading.current_thread().ident)

import tkinter as tk 

r = tk.Tk() 

r.geometry("500x100")

r.title('Learning Threads') 

button = tk.Button(r, text='Run Main Method', width=25, command=main) 

button.pack() 

r.mainloop() 

print("Main thread Ends")

2)       Multitasking:

In multitasking we can schedule to execute M1() to run on different thread,

Similarly M2, M3 can be executed in different thread.

When button is clicked, Main method creates 3 threads which are mapped to 3 methods. All the threads trigger execute the method in parallel.

Imp: The client window is also NOT hung while executing the three methods.

So the total time taken to complete the process will be the task which takes the maximum time here in this case its M2 i.e 25 secs.

Sample Code

import threading

import time

def main():

    

    print("Main method Thread Executed  in thread id::",threading.get_native_id())

    t1=threading.Thread(target=m1)

    t2=threading.Thread(target=m2)

    t3=threading.Thread(target=m3)

    time.sleep(0.1)

    t1.start()

    time.sleep(0.1)

    t2.start()

    time.sleep(0.1)

    t3.start()

    print("Main method Ends after 10 seconds")

def m1():

    

    print("M1 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(10)#This methods takes 5 seconds to complete the task

    print("M1 Ends after 10 seconds")

    

def m2():

    

    print("M2 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(25)#This methods takes 4 seconds to complete the task

    print("M2 Ends after 25 seconds")

    

    

def m3():

    

    print("M3 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(15)#This methods takes 5 seconds to complete the task

    print("M3 Ends after 15 seconds")

    

print("Main Program Thread Executed  in thread id::",threading.current_thread().ident)

import tkinter as tk 

r = tk.Tk() 

r.geometry("500x100")

r.title('Learning Threads') 

button = tk.Button(r, text='Run Main Method', width=25, command=main) 

button.pack() 

r.mainloop() 

print("Main thread Ends")

What is Threading

1)       A thread is an entity within a process that can be scheduled for execution.

2)       It is the smallest unit of processing that can be performed in an OS.

3)       It is a sequence of such instructions within a program that can be executed independently of other code.

4)       A thread is simply a subset of a process.

Joining of Thread

The dependent thread cannot be trigger unless self thread execution is completed.

In the below diagram the 3 threads are triggered from main thread.

If T1 is joined, then Main thread,T2, T3 are threads are blocked, unless T1 is completed.

When the button is clicked, the main thread is blocked and hence the window hungs till the T1 execution is completed. Same happens when T2 is joined, T3 is joined.

Sample Code:

import threading

import time

def main():

    

    print("Main method Thread Executed  in thread id::",threading.get_native_id())

    t1=threading.Thread(target=m1)

    t1.start()

    

    t2=threading.Thread(target=m2)

    t3=threading.Thread(target=m3)

    time.sleep(0.1)

    

    time.sleep(0.1)

    t1.join()

    t2.start()

    time.sleep(0.1)

    t3.start()

    print("Main method Ends after 10 seconds")

def m1():

    print("M1 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(10)#This methods takes 5 seconds to complete the task

    print("M1 Ends after 10 seconds")

    

def m2():

    

    print("M2 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(25)#This methods takes 4 seconds to complete the task

    print("M2 Ends after 25 seconds")

    

    

def m3():

    

    print("M3 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(15)#This methods takes 5 seconds to complete the task

    print("M3 Ends after 15 seconds")

    

print("Main Program Thread Executed  in thread id::",threading.current_thread().ident)

import tkinter as tk 

r = tk.Tk() 

r.geometry("500x100")

r.title('Learning Threads') 

button = tk.Button(r, text='Run Main Method', width=25, command=main) 

button.pack() 

r.mainloop() 

print("Main thread Ends")

Argument: The above scenario is as good as running m1,m2,m3 sequentially.

In the both the cases the main window hungs.Then whats the point of joining the thread.

To avoid this problem, create new threads t2,t3 from thread t1. And if t2 is joined then main thread is not going blocked and hence the window. Same happens when you join T3.

Sample Code:

import threading

import time

def main():

    

    print("Main method Thread Executed  in thread id::",threading.get_native_id())

    t1=threading.Thread(target=m1)

    t1.start()

    print("Main method Ends after 10 seconds")

def m1():

    t2=threading.Thread(target=m2)

    t3=threading.Thread(target=m3)

    

    time.sleep(0.1)

    t2.start()

    t2.join()

    time.sleep(0.1)

    t3.start()

    time.sleep(0.1)

    print("M1 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(10)#This methods takes 5 seconds to complete the task

    print("M1 Ends after 10 seconds")

    

def m2():

    

    print("M2 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(25)#This methods takes 4 seconds to complete the task

    print("M2 Ends after 25 seconds")

    

    

def m3():

    

    print("M3 Thread Executed  in thread id::",threading.current_thread().ident)

    time.sleep(15)#This methods takes 5 seconds to complete the task

    print("M3 Ends after 15 seconds")

    

print("Main Program Thread Executed  in thread id::",threading.current_thread().ident)

import tkinter as tk 

r = tk.Tk() 

r.geometry("500x100")

r.title('Learning Threads') 

button = tk.Button(r, text='Run Main Method', width=25, command=main) 

button.pack() 

r.mainloop() 

print("Main thread Ends")

Thread Synchronization.

 When there are two or more threads within a program, there may be a situation when multiple threads try to access the same resource and finally they can produce unforeseen result due to concurrency issues. For example, if multiple threads try to write within a same file then they may corrupt the data because one of the threads can override data or while one thread is opening the same file at the same time another thread might be closing the same file.

So there is a need to synchronize the action of multiple threads and make sure that only one thread can access the resource at a given point in time

	m2()  25 secs
		t3
		m3()  15 secs
t1
m1()  10 sec
Shared Data( Critical Section)

In the above diagram, threads t1,t2,t3 are trying to access the shared data, or trying to enter into critical section. This could lead to problems as described above.

 This can be achieved by synchronizing threads, by telling all threads to enter the critical section one by one.

In the below diagram, multiple threads are trying to enter the critical section, but are not allowed to access it, because of Lock object. Lock object ensure only one thread is allowed to enter in to the critical section, once its operation is done, and the lock is released, then only other thread is allowed to enter into the critical section. There is no sequence, it can be distinguished, that which thread will be entering into lock object. It is first come first server basis, however if there are two thread invoked at the same time, then it is os which determine the priority.

Example

import threading

import time

lock=threading.Lock() #This is lock object

def btn1click():

    t1=threading.Thread(target=updatedata)

    t1.start()

def btn2click():

    t2=threading.Thread(target=updatedata)

    t2.start()

    

def updatedata():   

    #At this point i need to acquire lock

    lock.acquire()

    for i in range(1,10):

        time.sleep(1)

        lstbox.insert(i,"Value of i "+ str(i)+" on thread id "+str(threading.get_native_id()))

    lock.release()

import tkinter as tk

form=tk.Tk()

form.geometry("500x500")

form.title("Learning Threads")

button1=tk.Button(form,text="Update Textbox",width=25,command=btn1click)# creating  windows button with command

button1.pack()

button2=tk.Button(form,text="Update Textbox",width=25,command=btn2click)# creating  windows button with command

button2.pack()

lstbox=tk.Listbox(form,height=200,width=50)

lstbox.pack()

form.mainloop()