在python中显示MS Sql查询问题

Question

你好，我正试图显示一个简单的查询从我的数据库在python tkinter我的问题是数据不会坐在正确的形式(主要是因为额外的空间)我如何在一个查询中删除存储在列表中的空格我的代码:

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

我创建了一个树视图，结果是名字在错误的列，因为空间，所以现在我试图打印它，看看我是否可以存储没有任何额外的空间

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

这是我在查询

后得到的结果

Answer 1

您没有在树视图中包含" Author "列。

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

如果你不想看到'Author'列，那么你必须同样修改你的SQL查询。

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

---

还要注意右和传统的方法是使用fetchall()方法获取数据并存储，而不是直接使用对象。

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

还需要注意的是，由于Mainwin.resizable(False , False)，您的窗口是不可调整大小的，所以如果树视图的宽度大于指定的窗口宽度，那么您将无法看到完整的树视图，所以最好删除它，除非您确定树视图将完全适合在窗口内。

再次需要注意的是(最后一件事，我发誓:p)是在item_selected函数中，您使用的是record = item['values']，但这将返回一个保留数据类型的列表~这意味着UID将是int，但是join不能正常工作，当int存储在可迭代对象中(在这种情况下是一个列表)，所以您必须将每个项目转换为str。

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