Right now I'm trying to write code that will pull a value from a text box, and insert it into my SQL table. I'm pretty sure I have the code written correctly, but I think I need to define a few things, but I'm unsure of the format or how to go about it. I am getting the error:
missing 3 required positional arguments: 'db', 'query', and 'parameters'
Additionally, I am having trouble returning the selected value from wx.ListCtrl. I can have it detect the correct selection, but all I've been able to do so far is return the selection number (e.g. 0,1,2,3, etc) and not the actual string. How can I get it to read properly?
Here is a minimal example of code, below.
import sys
import wx
from six.moves import urllib
import wx.adv
import datetime
from datetime import date
import sqlalchemy
from sqlalchemy import *
import sqlalchemy_utils
import pandas as pd
from datetime import datetime
import _strptime
import os
#Reads the SQL Column to use for the dropdown menu
params = urllib.parse.quote_plus("Driver={ODBC Driver 18 for SQL Server};"
"Server=SERVER;"
"Database=DATABASE;"
"Trusted_Connection=yes;"
"TrustServerCertificate=yes"
)
engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
engine.connect()
#Gets list of Part Numbers for the menubox as well as matching to SQL DF
sqlread = pd.read_sql_query('''SELECT * FROM TABLE''',engine)
pndf = pd.DataFrame(sqlread, columns = ['PN'])
pndf = pndf[pndf.PN != None]
dflist = pndf.values.tolist()
class compwin(wx.Frame):
def __init__(self):
wx.Frame.__init__(self,None,-1,'Test',
size=(400,600))
panel = wx.Panel(self,-1)
#Prompts for Update Field
wx.TextCtrl(panel,value = "________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________",size=(1300,20),pos=(-1,275),style=wx.TE_READONLY|wx.BORDER_NONE)
wx.TextCtrl(panel,value = "Update What Part?",size=(100,20),pos=(25,325),style=wx.TE_READONLY|wx.BORDER_NONE)
#Text Boxes for Update Field
self.ComboUp = wx.ListCtrl(panel, wx.ID_ANY, size=(150,125),pos=(20,350),style=wx.LC_REPORT)
self.ComboUp.InsertColumn(0,"Select a Part Number:",format=wx.LIST_FORMAT_RIGHT,width=150)
for Part in dflist:
self.ComboUp.Append(Part)
#update field bindings
#Prompts for add field
wx.TextCtrl(panel,value = "Part Number",size=(100,20),pos=(75,100),style=wx.TE_READONLY|wx.BORDER_NONE)
self.PNAdd = wx.TextCtrl(panel,value = "",size=(125,20),pos=(60,125))
#define buttons
self.sysexit = wx.Button(panel,label="Exit", name="1",pos=(100,500))
self.add = wx.Button(panel,label="Add Record", name="2",pos=(300,125))
#bindings for panel
self.Bind(wx.EVT_BUTTON,self.OnClick_exit,self.sysexit)
self.Bind(wx.EVT_BUTTON,self.add_data,self.add)
self.Bind(wx.EVT_LIST_ITEM_SELECTED,self.OnSelectAdd,self.ComboUp)
self.add.SetDefault()
######################### COMBO BOX SELECTION ######################################################################
def OnSelectAdd(self,e):
item_select = self.ComboUp.GetFocusedItem()
if item_select != -1:
textprint = self.ComboUp.GetItem(item_select).GetItemText()
print(textprint)
############################################# EXIT BUTTON ######################################################################
def OnClick_exit(self,e):
self.Close()
############################################ ADD RECORD BUTTON ############################################################
def add_data(self,e,db: sqlalchemy.engine.base.Engine, query: str,parameters: dict):
log_headline: str = "insert_data() ::"
"""
:param db:
:param query: INSERT INTO DATABASE.TABLE(PN) VALUES(:PN)
:param parameters: {self.PNAdd.GetValue():PN}
:return:
"""
stmt = sqalchemy.text(query)
try:
print("Testing...")
with engine.connect() as conn:
conn.execute(stmt, parameters=parameters)
conn.commit()
except Exception as e:
print("Error!")
if __name__ == '__main__':
app = wx.App()
frame = compwin()
frame.Show()
app.MainLoop()
Here is one way of getting the details of an item that has been selected in the wx.ListCtrl.
You put the data in, in columns, so you need to quiz the columns that you require.
The index of the selected item, is contained in the event.
import wx
dflist = [
["123","Widget 1", "w101"],["456","Widget 2", "w2091"],["789","Widget 3", "w3000"],["132","Widget 4", "w404"],
["465","Widget X", "x555"]
]
class compwin(wx.Frame):
def __init__(self):
wx.Frame.__init__(self,None,-1,'Shortage Sheet Compiler v0.1',
size=(600,300))
panel = wx.Panel(self,-1)
#textbox printout
#Prompts for Update Field
wx.TextCtrl(panel,value = "Update What Part?",size=(150,20),pos=(25,20),style=wx.TE_READONLY|wx.BORDER_NONE)
#Text Boxes for Update Field
self.Combobox = wx.ListCtrl(panel, wx.ID_ANY, size=(400, -1),pos=(25, 50), style=wx.LC_REPORT)
self.Combobox.InsertColumn(0, "Item", wx.LIST_FORMAT_RIGHT)
self.Combobox.InsertColumn(1, "Name", wx.LIST_FORMAT_RIGHT)
self.Combobox.InsertColumn(2, "Part Number", wx.LIST_FORMAT_RIGHT)
self.Combobox.SetColumnWidth(1, 150)
self.Combobox.SetColumnWidth(2, 150)
for item in dflist:
self.Combobox.Append((item[0],item[1],item[2]))
self.Combobox.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected)
def OnItemSelected(self, event):
ind = event.GetIndex()
item = self.Combobox.GetItem(ind, 0).GetText()
name = self.Combobox.GetItem(ind, 1).GetText()
part = self.Combobox.GetItem(ind, 2).GetText()
print("item", item, "name", name, "part number", part)
if __name__ == '__main__':
app = wx.App()
frame = compwin()
frame.Show()
app.MainLoop()
Regarding the Add function:
You have bound the button event to a routine. So it will be called with self and the event.
By way of magical thinking, you've included in it's parameters: the database definition, the query and the input data. None of which you have defined or passed - witness the error.
Define those things within the add_data routine and remove them from the parameter list, which should consist of (self, event)
Happy trails!