Query MSSQL Database with Python GUI
10-11-2017
This is an example program for using Python with MS SQL. In the example, we select one of several database server names. The server will then be queried to get a computer name based on the ID in the database. Or you can grab the ID based on the computer name given. A simple little GUI is provided using Tkinter.
Language or Platform: Python
LICENSE
Code:
"""
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
IDLock
Copyright (c) Josh M
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Connect to and query Microsoft SQL database using Python
Includes a Tkinter GUI
"""
import os
import pypyodbc
import tkinter
from tkinter import ttk
from tkinter import messagebox
class Lkup(ttk.Frame):
def __init__(self, parent, *args, **kwargs):
ttk.Frame.__init__(self, parent, *args, **kwargs)
self.root = parent
self.init_gui()
def on_help(self):
answer = messagebox.showinfo("IDLock Help", "Help message here.")
def on_quit(self):
"""Exits program."""
root.quit()
def calculate(self):
if len(self.workstation1_entry.get()) == 0:
serverName = str(self.selectedRegion.get())
num2 = int(self.localid2_entry.get())
connstr = '''Driver={{SQL Server}};Server={};Database=MyDatabaseNAME;Trusted_Connection=yes;'''.format(serverName)
try:
connection = pypyodbc.connect(connstr)
except pypyodbc.Error as ex:
sqlstate = ex.args[0]
if sqlstate == '28000':
self.answer_label['text'] = "You do not have access."
cursor = connection.cursor()
SQLCommand = ("SELECT Name, CompID "
"FROM dbo.MyTableNAME "
"with (nolock)"
"WHERE CompID = ?")
Values = [num2]
cursor.execute(SQLCommand,Values)
results = cursor.fetchone()
if results:
self.answer_label['text'] = (results[0]) # displays answer
connection.close()
else:
self.answer_label['text'] = "Number does not exist."
elif len(self.localid2_entry.get()) == 0:
serverName = str(self.selectedRegion.get())
word1 = str(self.workstation1_entry.get())
connstr = '''Driver={{SQL Server}};Server={};Database=MyDatabaseNAME;Trusted_Connection=yes;'''.format(serverName)
try:
connection = pypyodbc.connect(connstr)
except pypyodbc.Error as ex:
sqlstate = ex.args[0]
if sqlstate == '28000':
self.answer_label['text'] = "You do not have access."
cursor = connection.cursor()
SQLCommand = ("SELECT Name, CompID "
"FROM dbo.MyTableNAME "
"with (nolock)"
"WHERE CompName = ?")
Values = [word1]
cursor.execute(SQLCommand,Values)
results = cursor.fetchone()
if results:
self.answer_label['text'] = str(results[1]) # displays answer
connection.close()
else:
self.answer_label['text'] = "Name does not exist."
else:
self.answer_label['text'] = "Not valid."
def init_gui(self):
"""Builds GUI."""
self.root.title('IDL')
self.root.option_add('*tearOff', 'FALSE')
self.grid(column=0, row=0, sticky='nsew') # this starts the entire form
self.menubar = tkinter.Menu(self.root)
self.menu_file = tkinter.Menu(self.menubar)
self.menu_file.add_command(label='Help', command=self.on_help)
self.menu_file.add_command(label='Exit', command=self.on_quit)
self.menu_edit = tkinter.Menu(self.menubar)
self.menubar.add_cascade(menu=self.menu_file, label='File')
self.root.config(menu=self.menubar)
self.workstation1_entry = ttk.Entry(self, width=10) # width of first input box
self.workstation1_entry.grid(sticky='e', column=0, row = 2) # column and row it is placed on # sticky='w' justifies or aligns to left
# YOU WILL HAVE TO ADJUST THESE
self.localid2_entry = ttk.Entry(self, width=8) # width of second input box
self.localid2_entry.grid(sticky='e', column=0, row=4) # column and row it is placed on
self.reset_button = ttk.Button(self, text='Reset', command=self.reset) # button
self.reset_button.grid(column=0, row=5, columnspan=4) # column and row it is placed on
self.reset_button.bind('<Return>', lambda e: self.reset())
self.calc_button = ttk.Button(self, text='Search', command=self.calculate) # button
self.calc_button.grid(column=0, row=6, columnspan=4) # column and row it is placed on
self.calc_button.bind('<Return>', lambda e: self.calculate())
self.answer_frame = ttk.LabelFrame(self, text='Answer', height=100) # answer box
self.answer_frame.grid(column=0, row=7, columnspan=4, sticky='nesw')
self.answer_label = ttk.Label(self.answer_frame, text='', wraplength=110) # wraplength keeps the label from grow when large text it outputed
self.answer_label.grid(column=0, row=0)
self.selectedRegion = tkinter.StringVar()
self.selectedRegion.set('greenserver') # make it where the top radio button is selected by default
# Each Value is a different database server name
self.b1 = ttk.Radiobutton(self, text='server name 1 will go here', value='greenserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=8, columnspan=1) # sticky W to align everything to left
self.b2 = ttk.Radiobutton(self, text='server name 2', value='blueserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=9, columnspan=1)
self.b3 = ttk.Radiobutton(self, text='server name 3', value='blackserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=10, columnspan=1)
self.b4 = ttk.Radiobutton(self, text='server name 4', value='redserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=11, columnspan=1)
self.b5 = ttk.Radiobutton(self, text='server name 5', value='purpleserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=12, columnspan=1)
self.b6 = ttk.Radiobutton(self, text='server name 6', value='yellowserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=13, columnspan=1)
self.b7 = ttk.Radiobutton(self, text='server name 7', value='pinkserver', variable=self.selectedRegion).grid(sticky='W', column=0,row=14, columnspan=1)
self.selectedRegion.get()
# Labels that remain constant throughout execution.
self.subtitle = ttk.Label(self, text='Computer ID Finder')
self.subtitle.grid(column=0, row=0, columnspan=4)
self.name = ttk.Label(self, text='Name')
self.name.grid(column=0, row=2, sticky='w')
self.spacer = ttk.Label(self, text='OR')
self.spacer.grid(column=0, row=3)
self.localid = ttk.Label(self, text='Computer ID')
self.localid.grid(column=0, row=4, sticky='w')
ttk.Separator(self, orient='horizontal').grid(column=0, # line under title.
row=1, columnspan=4, sticky='ew')
for child in self.winfo_children(): # padx 10 adds horizontal padding on the out edge of window
child.grid_configure(padx=10, pady=4)
def reset(self):
self.workstation1_entry.delete(0, 'end')
self.localid2_entry.delete(0, 'end')
if __name__ == '__main__':
root = tkinter.Tk()
Lkup(root)
root.resizable(width=False, height=False) # locks window from being resized
root.mainloop()