Problem Displaying MS Sql Query in python

user_12139025 0
Viewed 97

Hello I am Trying to Display a Simple Query from my Database in python tkinter my problem is the data wont sit in the correct form (Mostly Because of extra spaces) how Can i Remove Spaces in a Query stored in a list my code :

# Importing Required to Create GUI
import pyodbc 
#Import tkinter but as tk (Shortetning the name that we use to call it)
import tkinter as tk
from tkinter import *
from tkinter import ttk
from tkinter.messagebox import showinfo
#Connect to DataBase
connection = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'Server=POORIA-PC;'
                      'Database=BooksDataBase;'
                      'Trusted_Connection=yes;')
#Define our Cursor
cursor = connection.cursor()
# Define Main Window

Mainwin =  tk.Tk()

#Title for Main Window(ROOT)
Mainwin.title('Hello Dear User ^_^')

#Define Main Window Height and Width

Main_Window_Width = 800
Main_Window_Height = 600

#Return the Screens current dimensions (MONITOR DIMENSIONS)

Screen_Width =  Mainwin.winfo_screenwidth()
Screen_Height =  Mainwin.winfo_screenheight()

#Calculate the Center of the Screen

Center_X = int(Screen_Width/2 - Main_Window_Width/2)
Center_Y =  int(Screen_Height/2 -  Main_Window_Height/2)
#NOW DEFINE THE GEOMETRY OF THE MAIN WINDOW BY PASSING IT THE ABOVE VARIABLES

Mainwin.geometry(f'{Main_Window_Width}x{Main_Window_Height}+{Center_X}+{Center_Y}')
#Lock the Dimensions so user cant change it
Mainwin.resizable(False , False)

#Define Transparency of the Main Window
Mainwin.attributes('-alpha',1.0)
Mainwin.iconbitmap('./Icons/MainWinIcon.ico')
#Retrieve a List from SQL Database
Data = cursor.execute("SELECT * FROM BooksMain")
for i in Data:
    print(i)

# define columns
columns = ('UID', 'Name', 'Category', 'QTY', 'Price')

tree = ttk.Treeview(Mainwin, columns=columns, show='headings')

# define headings
tree.heading('UID', text='UID')
tree.heading('Name', text='Name')
tree.heading('Category', text='Category')
tree.heading('QTY', text='QTY')
tree.heading('Price', text='Price')

for j in Data:
    tree.insert('', tk.END, values=Data)


def item_selected(event):
    for selected_item in tree.selection():
        item = tree.item(selected_item)
        record = item['values']
        # show a message
        showinfo(title='Information', message=','.join(record))


tree.bind('<<TreeviewSelect>>', item_selected)

tree.grid(row=0, column=0, sticky='nsew')

# add a scrollbar
scrollbar = ttk.Scrollbar(Mainwin, orient=tk.VERTICAL, command=tree.yview)
tree.configure(yscroll=scrollbar.set)
scrollbar.grid(row=0, column=1, sticky='ns')


#Call the Main Window
Mainwin.mainloop()

I Created a Treeview and the Result was Names in wrong Columns because of the spaces so now im trying to print it to see if i can stored without any extra spaces

(1, 'Harry Potter and the Goblet of Fire', 'Magic', 'J. K. Rowling', 2, '5000')
(2, 'Harry potter and the goblet of fire', 'Magic', 'J. K. Rowling', 10, '10000')
(3, 'Harry potter and the order of the phoenix', 'Magic', 'J. K. Rowling', 1, '5000')
(4, 'Harry potter and the Deathly Hallows Part I', 'Magic', 'J. K. Rowling', 1, '5000')
(5, 'Lord of the Rings', 'Magic', 'J. K. Rowling', 85, '20000')

this is the result I get after a query

pyodbc tkinter sql python
1 Answers

You missed to include the column 'Author' in the treeview.

columns = ('UID', 'Name', 'Category', 'Author', 'QTY', 'Price')

tree = ttk.Treeview(Mainwin, columns=columns, show='headings')

# define headings
tree.heading('UID', text='UID')
tree.heading('Name', text='Name')
tree.heading('Category', text='Category')
tree.heading('Author', text='Author')
tree.heading('QTY', text='QTY')
tree.heading('Price', text='Price')

for j in Data:
    tree.insert('', tk.END, values=j) # Insert j and not Data into the treeview

And if you don't want to see the column 'Author', then you must alter your SQL query likewise.

Data = cursor.execute("SELECT 'UID', 'Name', 'Category', 'QTY', 'Price' FROM BooksMain")

Also keep a note that the right and the conventional way is to use fetchall() method to fetch the data and store it, rather than using the object directly.

Data = cursor.execute("SELECT * FROM BooksMain").fetchall()

Also something to note is, your window is non-resizable due to Mainwin.resizable(False , False), so if the width of the treeview is bigger than the specified window width, then you wont be able to see the full treeview, so its best to remove it unless you're sure the treeview will fit perfectly inside the window.

Again something to note(last thing, I swear :P) is that inside item_selected function, you are using record = item['values'] but that will return a list with the data types retained ~ meaning UID will be int, but join cannot work properly when int is stored inside the iterable(a list, in this case), so you will have to convert each item to a str.

record = item['values']
record = map(str, record) # Apply str() to each item in the record
user_133820000
Related