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()