社区!
在熊猫身上使用脓杆菌时,我发现了一些奇怪的东西,至少对我来说是这样。当运行该程序时,一切都按预期进行,但我似乎缺少一行生成的DataFrame。
我在SSMS中运行了完全相同的查询,结果应该显示2行。将DataFrame打印到控制台,我看到它只显示结果行中的后一行。
我没看到什么?pd.set_option
中的任何设置-部分我做错了?到目前为止,我一直试图改变这些,但没有成功。
import tkinter as tk
from tkinter import Tk, W, E
from tkinter.ttk import Frame, Button, Entry, Style, Radiobutton, Label
from tkinter import filedialog as fd
import os
class Application(Frame):
def __init__(self):
super().__init__()
self.initUI()
def initUI(self):
#------------------------------#
# Functions #
#------------------------------#
def SQL_Query(query_string):
import pyodbc as p # MIT Lisence. OK
import itertools
import pandas as pd # BSD Lisence. OK
pd.set_option("display.max_rows", None)
pd.set_option("display.max_columns", None)
pd.set_option("display.width", 1000)
databaseName = '***'
username = '***'
password = '***'
server = '***'
driver = '***'
CONNECTION_STRING = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+databaseName+';UID='+username+';PWD='+ password
conn = p.connect(CONNECTION_STRING)
cursor = conn.cursor()
cursor.execute(query_string)
row = cursor.fetchone()
desc = cursor.description
column_names = [col[0] for col in desc]
data = [dict(zip(column_names, row))
for row in cursor.fetchall()]
conn.close()
df = pd.DataFrame(data)
if df.empty == False:
qty_total = str(df['InitialQuantity'].sum())
qty_RT = str(df['RT'].sum())
print('Number of units found with criteria: ' + qty_total)
print('Numbe rof units with RT: ' + qty_RT + 'n')
print(df)
def btnRun():
line = var.get()
mat = varMaterial.get()
if line == "('1', '6', '8', '18')":
query = f"""QueryStringIsHere"""
SQL_Query(query)
elif line == "('3', '10')":
query = f"""QueryStringIsHere"""
SQL_Query(query)
#------------------------------#
# Program title and attributes #
#------------------------------#
self.master.title("Production Orders")
Style().configure("TButton", padding=(0, 5, 0, 5),
font='serif 10')
self.columnconfigure(0, pad=3)
self.columnconfigure(1, pad=3)
self.columnconfigure(2, pad=3)
self.columnconfigure(3, pad=3)
self.rowconfigure(0, pad=3)
self.rowconfigure(1, pad=3)
#------------------------------#
# UI Elements #
#------------------------------#
var = tk.StringVar() #variable to hold radio button values
varMaterial = tk.StringVar()
rad12 = Radiobutton(self,text="x", variable=var, value = "('1', '6', '8', '18')").grid(row=0, column=0, sticky=W)
rad36 = Radiobutton(self,text="y", variable=var, value = "('3', '10')").grid(row=1, column=0, sticky=W)
entry = Entry(self, textvariable=varMaterial).grid(row=2, column=1, columnspan=4, sticky=W+E)
lblEntry = Label(self, text="Material Number:").grid(row=2, column=0,columnspan=1, sticky=W+E)
#------------------------------#
# Command buttons #
#------------------------------#
btnSelect = Button(self, text="Run Query", command = btnRun).grid(row=3, column=0)
btnClear = Button(self, text="Clear text", command = '').grid(row=3, column=1)
btnQuit = Button(self, text="Quit", command = self.master.destroy).grid(row=3, column=4)
#------------------------------#
# Packing #
#------------------------------#
self.pack()
def main():
root = Tk()
app = Application()
root.mainloop()
if __name__ == '__main__':
main()
这个问题的解决方案是在原始帖子评论的指导下找到的。由于我先做.fetchone()
,然后用.fetchall()
填充DataFrame
,所以data
不包括第一个结果行。
import tkinter as tk
from tkinter import Tk, W, E
from tkinter.ttk import Frame, Button, Entry, Style, Radiobutton, Label
from tkinter import filedialog as fd
import os
class Application(Frame):
def __init__(self):
super().__init__()
self.initUI()
def initUI(self):
#------------------------------#
# Functions #
#------------------------------#
def SQL_Query(query_string):
import pyodbc as p # MIT Lisence. OK
import itertools
import pandas as pd # BSD Lisence. OK
pd.set_option("display.max_rows", None)
pd.set_option("display.max_columns", None)
pd.set_option("display.width", 1000)
databaseName = '***'
username = '***'
password = '***'
server = '***'
driver = '***'
CONNECTION_STRING = 'DRIVER='+driver+';SERVER='+server+';DATABASE='+databaseName+';UID='+username+';PWD='+ password
conn = p.connect(CONNECTION_STRING)
cursor = conn.cursor()
cursor.execute(query_string)
desc = cursor.description
column_names = [col[0] for col in desc]
data = [dict(zip(column_names, row))
for row in cursor.fetchall()]
conn.close()
df = pd.DataFrame(data)
if df.empty == False:
qty_total = str(df['InitialQuantity'].sum())
qty_RT = str(df['RT'].sum())
print('Number of units found with criteria: ' + qty_total)
print('Numbe rof units with RT: ' + qty_RT + 'n')
print(df)
def btnRun():
line = var.get()
mat = varMaterial.get()
if line == "('1', '6', '8', '18')":
query = f"""QueryStringIsHere"""
SQL_Query(query)
elif line == "('3', '10')":
query = f"""QueryStringIsHere"""
SQL_Query(query)
#------------------------------#
# Program title and attributes #
#------------------------------#
self.master.title("Production Orders")
Style().configure("TButton", padding=(0, 5, 0, 5),
font='serif 10')
self.columnconfigure(0, pad=3)
self.columnconfigure(1, pad=3)
self.columnconfigure(2, pad=3)
self.columnconfigure(3, pad=3)
self.rowconfigure(0, pad=3)
self.rowconfigure(1, pad=3)
#------------------------------#
# UI Elements #
#------------------------------#
var = tk.StringVar() #variable to hold radio button values
varMaterial = tk.StringVar()
rad12 = Radiobutton(self,text="x", variable=var, value = "('1', '6', '8', '18')").grid(row=0, column=0, sticky=W)
rad36 = Radiobutton(self,text="y", variable=var, value = "('3', '10')").grid(row=1, column=0, sticky=W)
entry = Entry(self, textvariable=varMaterial).grid(row=2, column=1, columnspan=4, sticky=W+E)
lblEntry = Label(self, text="Material Number:").grid(row=2, column=0,columnspan=1, sticky=W+E)
#------------------------------#
# Command buttons #
#------------------------------#
btnSelect = Button(self, text="Run Query", command = btnRun).grid(row=3, column=0)
btnClear = Button(self, text="Clear text", command = '').grid(row=3, column=1)
btnQuit = Button(self, text="Quit", command = self.master.destroy).grid(row=3, column=4)
#------------------------------#
# Packing #
#------------------------------#
self.pack()
def main():
root = Tk()
app = Application()
root.mainloop()
if __name__ == '__main__':
main()