Hello folks I am a beginning to intermediate python programmer. This project IS NOT FOR PRODUCTION OR COMMERCIAL USE. It was just and idea that I had to give myself practice and experience in programming.
First I made the backend in DOS/Commandline python just so I could determine the logic of what I was trying to do. Here is that program.
https://drive.google.com/open?id=13auJt5bL4vm85ifBYcTjvkUpmmgRDyWZ
#*****************************************************************
#
# Name:Command line prototype of wxpython date entry from.
#
# Source: Self
#
# URL:Self
#
# Date: 2019-01-25 16:02:32
#*****************************************************************
import sys
import os
import sqlite3
from sqlite3 ``import Error
def main():
``print sys.argv[``1``]
``db_file ``= sys.argv[``1``]
``return db_file
def create_connection(db_file):
``""" create a database connection to a SQLite database """
``choice ``= 0
``tablenames ``= []
``foreignkeyexist ``= "PRAGMA foreign_key_list"
``foreignkeyon ``= "PRAGMA foreign_key = 'ON'"
``try``:
``conn ``= sqlite3.connect(db_file)
``c ``= conn.cursor()
``conn.text_factory ``= bytes
``c.execute(foreignkeyon)
``print``(sqlite3.version)
``row ``= c.execute(``"SELECT name FROM sqlite_master WHERE type = 'table'"``)
``print``(``type``(row))
``for line ``in row:
``choice ``+``=``1
``print``(``str``(choice) ``+ ")" + " " + line[``0``])
``tablenames ``+``= [line[``0``]]
``print``(tablenames)
``tablechoice ``= raw_input``(``"Enter the number of the table that you want to edit "``)
``tableindex ``= int``(tablechoice)
``chosentable ``= tablenames[tableindex``-``1``]
``print chosentable
``tablestmt ``= "PRAGMA table_info(" + chosentable ``+ ")"
``tablecolumns ``= c.execute(tablestmt )
``for index ``in tablecolumns:
``if not index[``5``]:
``if "BLOB" not in index[``2``]:
``print index
# get the tables in the database
#SELECT name FROM sqlite_master WHERE type = “table”``
# First string in a sqlite database file. This identifys the file
# as a sqlite database file "SQLite format 3"
``except Error as e:
``print``(e)
``finally``:
``conn.close()
if __name__ ``=``= '__main__'``:
``filename ``= main()
``create_connection(filename)
Here is a link to a sample sqlite database in case you do not have one
https://drive.google.com/open?id=1CKKFC1q121aPZZT6wEwN81RdjdejfT20
This command line version of the program does work as expected.
I used wxglade to make the front end interface and then tried to integrate the two programs.
Here is the latest version of that integrated program.
https://drive.google.com/open?id=1mXY8ChwwgRXyAp_GXtmAftSNEn0842bg
Enter code hereimport wx
import wx.grid
import os,sys
import sqlite3 as sq
from sqlite3 import Error
filetypewildcard = “Sqlite Database (.db)|.db|”“sqz files(.sqz)|.sqz|”“sqlfiles(.sql)|.sql”
currentDirectory = os.getcwd()
chosentable = “”
def create_connection(db_file):
“”" create a database connection to a SQLite database “”"
First string in a sqlite database file. This identies the file
as a sqlite database file “SQLite format 3”
choice = 0
tablenames = []
databasefileheader = "SQLite format 3"
foreignkeyexist = "PRAGMA foreign_key_list"
foreignkeyon = "PRAGMA foreign_key = 'ON'"
try:
conn = sq.connect(db_file)
c = conn.cursor()
conn.text_factory = bytes
c.execute(foreignkeyon)
print(sq.version)
get the tables in the database
tables = c.execute('SELECT name FROM sqlite_master WHERE type = "table"')
row = c.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
for line in row:
#How to get the table name into the listbox and dynamically adjust
#the number of items in the listbox.
tablenames += [line[0]]
# print tablenames
displaytables = TableChoice(None,wx.ID_ANY)
displaytables.listtables.SetItems(tablenames)
displaytables.Show(True)
Determine if table has foreign key or BLOB column and exclude them from listing
of columns to be displayed
print chosentable
tablestmt = "PRAGMA table_info(" + chosentable + ")"
print tablestmt
tablecolumns = c.execute(tablestmt)
print tablecolumns
for index in tablecolumns:
if not index[5]:
if "BLOB" not in index[2]:
print index
except Error as e:
print(e)
finally:
conn.close()
begin wxGlade: dependencies
end wxGlade
begin wxGlade: extracode
end wxGlade
begin wxGlade: dependencies
end wxGlade
begin wxGlade: extracode
end wxGlade
class ActorList(wx.Frame):
def init(self, *args, **kwds):
# begin wxGlade: ActorList.init
kwds[“style”] = kwds.get(“style”, 0) | wx.DEFAULT_FRAME_STYLE
wx.Frame.init(self, *args, **kwds)
self.SetSize((800, 600))
# Menu Bar
self.ActiorListMenu = wx.MenuBar()
wxglade_tmp_menu = wx.Menu()
item = wxglade_tmp_menu.Append(wx.ID_ANY, "Open Database File", "")
self.Bind(wx.EVT_MENU, self.OpenFile, id=item.GetId())
wxglade_tmp_menu.Append(wx.ID_ANY, "Save Database", "")
wxglade_tmp_menu.Append(wx.ID_ANY, "Close Database", "")
item = wxglade_tmp_menu.Append(wx.ID_ANY, "Exit", "")
self.Bind(wx.EVT_MENU, self.Quit, id=item.GetId())
self.ActiorListMenu.Append(wxglade_tmp_menu, "File")
wxglade_tmp_menu = wx.Menu()
item = wxglade_tmp_menu.Append(wx.ID_ANY, "Help", "")
self.Bind(wx.EVT_MENU, self.DisplayHelpFrame, id=item.GetId())
item = wxglade_tmp_menu.Append(wx.ID_ANY, "About", "")
self.Bind(wx.EVT_MENU, self.DisplauSplashScreen, id=item.GetId())
self.ActiorListMenu.Append(wxglade_tmp_menu, "Help")
self.SetMenuBar(self.ActiorListMenu)
# Menu Bar end
self.frame_statusbar = self.CreateStatusBar(2)
self.panel_1 = wx.Panel(self, wx.ID_ANY)
self.panel_2 = wx.ScrolledWindow(self.panel_1, wx.ID_ANY, style=wx.BORDER_RAISED)
self.displaygrid = wx.grid.Grid(self.panel_2, wx.ID_ANY, size=(1, 1))
self.button_3 = wx.Button(self.panel_1, wx.ID_ANY, "CLEAR")
self.button_1 = wx.Button(self.panel_1, wx.ID_ANY, "ADD")
self.button_2 = wx.Button(self.panel_1, wx.ID_ANY, "OPEN")
self.__set_properties()
self.__do_layout()
self.Bind(wx.EVT_BUTTON, self.ClearForm, self.button_3)
self.Bind(wx.EVT_BUTTON, self.AddData, self.button_1)
self.Bind(wx.EVT_BUTTON, self.OpenFile, self.button_2)
# end wxGlade
def __set_properties(self):
# begin wxGlade: ActorList.__set_properties
self.SetTitle("frame")
self.frame_statusbar.SetStatusWidths([-1, 0])
# statusbar fields
frame_statusbar_fields = ["frame_statusbar", ""]
for i in range(len(frame_statusbar_fields)):
self.frame_statusbar.SetStatusText(frame_statusbar_fields[i], i)
self.displaygrid.CreateGrid(2, 1)
self.displaygrid.SetFocus()
self.panel_2.SetScrollRate(10, 10)
self.button_1.SetToolTipString("Add data to table")
self.button_2.SetToolTipString("Open a database table")
# end wxGlade
def __do_layout(self):
# begin wxGlade: ActorList.__do_layout
sizer_1 = wx.BoxSizer(wx.VERTICAL)
sizer_2 = wx.BoxSizer(wx.VERTICAL)
sizer_3 = wx.BoxSizer(wx.HORIZONTAL)
sizer_4 = wx.BoxSizer(wx.VERTICAL)
sizer_4.Add(self.displaygrid, 1, wx.ALIGN_CENTER | wx.EXPAND, 0)
self.panel_2.SetSizer(sizer_4)
sizer_2.Add(self.panel_2, 1, wx.EXPAND, 0)
sizer_3.Add(self.button_3, 1, wx.EXPAND, 0)
sizer_3.Add(self.button_1, 1, wx.EXPAND, 0)
sizer_3.Add(self.button_2, 1, wx.EXPAND, 0)
sizer_2.Add(sizer_3, 1, wx.ALIGN_BOTTOM | wx.ALL | wx.EXPAND | wx.FIXED_MINSIZE | wx.SHAPED, 10)
self.panel_1.SetSizer(sizer_2)
sizer_1.Add(self.panel_1, 3, wx.ALL | wx.EXPAND, 0)
self.SetSizer(sizer_1)
self.Layout()
# end wxGlade
def OpenFile(self, event): # wxGlade: ActorList.<event_handler>
dlg = wx.FileDialog(self, message="Choose a file", defaultDir=currentDirectory, defaultFile="", wildcard=filetypewildcard, style=wx.OPEN | wx.FD_CHANGE_DIR)
if dlg.ShowModal() == wx.ID_OK:
filename = dlg.GetPath()
self.frame_statusbar.SetStatusText(filename)
create_connection(filename)
for path in db_file:
print path
dlg.Destroy()
#print("Event handler 'OpenFile' not implemented!")
#event.Skip()
def Quit(self, event): # wxGlade: ActorList.<event_handler>
print("Event handler 'Quit' not implemented!")
event.Skip()
def DisplayHelpFrame(self, event): # wxGlade: ActorList.<event_handler>
print("Event handler 'DisplayHelpFrame' not implemented!")
event.Skip()
def DisplauSplashScreen(self, event): # wxGlade: ActorList.<event_handler>
print("Event handler 'DisplauSplashScreen' not implemented!")
event.Skip()
def ClearForm(self, event): # wxGlade: ActorList.<event_handler>
print("Event handler 'ClearForm' not implemented!")
event.Skip()
def AddData(self, event): # wxGlade: ActorList.<event_handler>
print("Event handler 'AddData' not implemented!")
event.Skip()
end of class ActorList
class OpenDialog(wx.Dialog):
def init(self, *args, **kwds):
# Content of this block not found. Did you rename this class?
pass
def __set_properties(self):
# Content of this block not found. Did you rename this class?
pass
def __do_layout(self):
# Content of this block not found. Did you rename this class?
pass
end of class OpenDialog
class SaveDialog(wx.Dialog):
def init(self, *args, **kwds):
# Content of this block not found. Did you rename this class?
pass
def __set_properties(self):
# Content of this block not found. Did you rename this class?
pass
def __do_layout(self):
# Content of this block not found. Did you rename this class?
pass
end of class SaveDialog
class MyDialog(wx.Dialog):
def init(self, *args, **kwds):
# Content of this block not found. Did you rename this class?
pass
def __set_properties(self):
# Content of this block not found. Did you rename this class?
pass
def __do_layout(self):
# Content of this block not found. Did you rename this class?
pass
end of class MyDialog
class TableChoice(wx.Dialog):
def init(self, *args, **kwds):
# begin wxGlade: TableChoice.init
kwds[“style”] = kwds.get(“style”, 0) | wx.DEFAULT_DIALOG_STYLE | wx.MAXIMIZE_BOX | wx.MINIMIZE_BOX | wx.RESIZE_BORDER
wx.Dialog.init(self, *args, **kwds)
self.panel_3 = wx.Panel(self, wx.ID_ANY)
self.listtables = TableList(self.panel_3, wx.ID_ANY, choices=[""], style=wx.LB_NEEDED_SB | wx.LB_SINGLE)
self.__set_properties()
self.__do_layout()
self.Bind(wx.EVT_LISTBOX_DCLICK, self.get_selected_table, self.listtables)
# end wxGlade
def __set_properties(self):
# begin wxGlade: TableChoice.__set_properties
self.SetTitle("TableChoice")
self.listtables.SetFont(wx.Font(12, wx.SWISS, wx.NORMAL, wx.BOLD, 0, "Courier"))
# end wxGlade
def __do_layout(self):
# begin wxGlade: TableChoice.__do_layout
fsizer = wx.BoxSizer(wx.VERTICAL)
mainsizer = wx.BoxSizer(wx.VERTICAL)
widgetsizer = wx.BoxSizer(wx.VERTICAL)
label_1 = wx.StaticText(self.panel_3, wx.ID_ANY, "Chooe which table you would like to add data to", style=wx.ALIGN_CENTER)
label_1.SetFont(wx.Font(14, wx.SWISS, wx.NORMAL, wx.BOLD, 0, "Courier"))
widgetsizer.Add(label_1, 0, wx.ALIGN_RIGHT | wx.EXPAND, 0)
widgetsizer.Add(self.listtables, 1, wx.EXPAND, 0)
mainsizer.Add(widgetsizer, 1, wx.EXPAND, 0)
self.panel_3.SetSizer(mainsizer)
fsizer.Add(self.panel_3, 1, wx.EXPAND, 0)
self.SetSizer(fsizer)
fsizer.Fit(self)
self.Layout()
self.Centre()
# end wxGlade
def get_selected_table(self, event): # wxGlade: TableChoice.<event_handler>
global chosentable
self.indexc = self.listtables.GetSelection()
print self.indexc
chosentable = self.listtables.GetString(self.indexc)
print chosentable
end of class TableChoice
class TableList(wx.ListBox):
def __init__(self, parent, id=wx.ID_ANY, size="", choices=[], style=wx.LB_NEEDED_SB | wx.LB_SINGLE, name=""):
wx.ListBox.__init__(self,parent,id)
def setchoicelist(self,choicelist):
self.choices = choicelist
class MyApp(wx.App):
def OnInit(self):
#image = wx.Image(“bope-logo.png”, wx.BITMAP_TYPE_PNG)
#bmp = image.ConvertToBitmap()
#wx.SplashScreen(bmp, wx.SPLASH_CENTRE_ON_PARENT | wx.SPLASH_TIMEOUT, 5000, None, -1)
#wx.Yield()
self.frame = ActorList(None, wx.ID_ANY, “”)
self.SetTopWindow(self.frame)
self.frame.Show()
return True
end of class MyApp
if name == “main”:
app = MyApp(0)
app.MainLoop()…
``
when I run it and choose the customer table or any table I get this error.
PRAGMA table_info()
near “)”: syntax error
2
customers
Which indicates to me that the PRAGMA statement is being formed correctly into a string in line 42 but that the string variable ‘chosentable’ is never being populated with any value.
I decalre chosentable at that start of the command line/ non wxpython part of the program at line 9 then the event handeler get_selected_table at line 267 declares it global so it should be changed in that function and that change should be read at line 42 where I concatenate it to the PRAGMA string pieces and then execute it.
the two print statements in the event handler at lines 270 and 272 are just there as a old style debugging print statement to ensure that I am actually getting the right values in that function. But for some reason it is not being returned back to the main program.
Can anyone tell me what I am doing wrongly.
Thanks in advance.
cmdlinedbase1_1_7.py (1.83 KB)
testdataentry7g.py (10.6 KB)