How to create a SQLite database easily (Phoenix)
Keywords : Grid, SQLite, Database, Data tables, Datetime.
Contents
Demonstrating :
Tested py3.x, wx4.x and Win10.
Are you ready to use some samples ?
Test, modify, correct, complete, improve and share your discoveries !
A wxPython GUI to create SQLite 3 databases
ICON file : icon_wxWidgets.ico
1 # sample_one.py
2
3 import wx
4 import wx.grid
5 import gettext
6 import os
7 import sqlite3
8 import datetime
9
10 # class MyFrame
11 # class MyDialog1
12 # class MyApp
13
14 cwd = os.path.abspath(os.curdir)
15
16 #---------------------------------------------------------------------------
17
18 class MyFrame(wx.Frame):
19 def __init__(self, *args, **kwds):
20 kwds["style"] = wx.DEFAULT_FRAME_STYLE
21 wx.Frame.__init__(self, *args, **kwds)
22
23 self.SetIcon(wx.Icon('icon_wxWidgets.ico'))
24
25 self.frame_1_menubar = wx.MenuBar()
26 wxglade_tmp_menu = wx.Menu()
27 wxglade_tmp_menu.Append(1, _("Create sqlite3 DataBase"), "", wx.ITEM_NORMAL)
28 self.frame_1_menubar.Append(wxglade_tmp_menu, _("DataBase Configuration"))
29
30 wxglade_tmp_menu = wx.Menu()
31 wxglade_tmp_menu.Append(2, _("Message"), "", wx.ITEM_NORMAL)
32 self.frame_1_menubar.Append(wxglade_tmp_menu, _("About"))
33
34 self.SetMenuBar(self.frame_1_menubar)
35 self.__set_properties()
36 self.__do_layout()
37
38 self.Bind(wx.EVT_MENU, self.open_dialog, id=1)
39 self.Bind(wx.EVT_MENU, self.open_dialog1, id =2)
40
41
42 def __set_properties(self):
43 self.SetTitle(_("Sqlite3 Creator"))
44 self.SetSize((400, 250))
45 self.SetBackgroundColour(wx.Colour(255, 255, 255))
46
47
48 def __do_layout(self):
49 sizer_1 = wx.BoxSizer(wx.VERTICAL)
50 self.SetSizer(sizer_1)
51 self.Layout()
52
53
54 def open_dialog(self, event):
55 MyDialog1(self).Show()
56
57
58 def open_dialog1(self,event):
59 wx.MessageBox("This App is made for you and for all developpers who use sqlite3 and want to create fast databases and data tables\n\nEnjoy...!")
60
61 #---------------------------------------------------------------------------
62
63 class MyDialog1(wx.Dialog):
64 def __init__(self, *args, **kwds):
65 kwds["style"] = wx.DEFAULT_DIALOG_STYLE
66 wx.Dialog.__init__(self, *args, **kwds)
67
68 self.SetIcon(wx.Icon('icon_wxWidgets.ico'))
69
70 self.label_27 = wx.StaticText(self, -1, _(" Create Your Data Tables :"))
71 self.label_25 = wx.StaticText(self, -1, _(" Folder/File/Table"))
72 self.txtFileName = wx.TextCtrl(self, -1, "")
73 self.txtDataName = wx.TextCtrl(self, -1, "")
74 self.txtDataTable = wx.TextCtrl(self, -1, "")
75 self.grid_1 = wx.grid.Grid(self, -1, size=(1, 1))
76 self.bnt_add = wx.Button(self, -1, _("Add Column"))
77 self.bnt_remove = wx.Button(self, -1, _("Remove Column"))
78 self.bnt_create = wx.Button(self, -1, _("Create DataBase"))
79 self.bnt_reset = wx.Button(self, -1, _("Reset Grid"))
80 self.txtDataFileOutput = wx.TextCtrl(self, -1, "", style=wx.TE_READONLY)
81
82 self.__set_properties()
83 self.__do_layout()
84
85 self.Bind(wx.EVT_BUTTON, self.cl_add_col, self.bnt_add)
86 self.Bind(wx.EVT_BUTTON, self.clk_remove_col, self.bnt_remove)
87 self.Bind(wx.EVT_BUTTON, self.clk_Create_db, self.bnt_create)
88 self.Bind(wx.EVT_BUTTON, self.clk_reset_grid, self.bnt_reset)
89
90
91 def __set_properties(self):
92 self.SetTitle(_("DataBase Creator"))
93 self.SetSize((555, 444))
94 self.txtFileName.SetMinSize((100, 27))
95 self.txtDataName.SetMinSize((100, 27))
96 self.txtDataTable.SetMinSize((100, 27))
97 self.grid_1.CreateGrid(1, 2)
98 self.grid_1.SetColLabelValue(0, _("Name"))
99 self.grid_1.SetColSize(0, 200)
100 self.grid_1.SetColLabelValue(1, _("Type"))
101 self.grid_1.SetColSize(1, 200)
102 self.bnt_add.SetMinSize((200, 29))
103 self.bnt_remove.SetMinSize((200, 29))
104 self.bnt_create.SetMinSize((200, 29))
105 self.bnt_reset.SetMinSize((200, 29))
106 self.txtDataFileOutput.SetMinSize((400, 30))
107
108
109 def __do_layout(self):
110 sizer_7 = wx.BoxSizer(wx.VERTICAL)
111 grid_sizer_10 = wx.GridSizer(2, 2, 0, 0)
112 grid_sizer_8 = wx.GridSizer(1, 4, 0, 0)
113 sizer_7.Add(self.label_27, 0, 0, 0)
114 grid_sizer_8.Add(self.label_25, 0, wx.ALIGN_CENTER_VERTICAL, 0)
115 grid_sizer_8.Add(self.txtFileName, 0, wx.ALIGN_CENTER_VERTICAL, 0)
116 grid_sizer_8.Add(self.txtDataName, 0, wx.ALIGN_CENTER_VERTICAL, 0)
117 grid_sizer_8.Add(self.txtDataTable, 0, wx.ALIGN_CENTER_VERTICAL, 0)
118 sizer_7.Add(grid_sizer_8, 1, wx.EXPAND, 0)
119 sizer_7.Add(self.grid_1, 1, wx.EXPAND, 0)
120 grid_sizer_10.Add(self.bnt_add, 0, wx.ALIGN_RIGHT | wx.ALIGN_CENTER_VERTICAL, 0)
121 grid_sizer_10.Add(self.bnt_remove, 0, wx.ALIGN_CENTER_VERTICAL, 0)
122 grid_sizer_10.Add(self.bnt_create, 0, wx.ALIGN_RIGHT, 0)
123 grid_sizer_10.Add(self.bnt_reset, 0, 0, 0)
124 sizer_7.Add(grid_sizer_10, 1, wx.EXPAND, 0)
125 sizer_7.Add(self.txtDataFileOutput, 0, wx.EXPAND, 0)
126 self.SetSizer(sizer_7)
127 self.Layout()
128
129
130 def cl_add_col(self, event):
131 self.grid_1.AppendRows(1)
132 event.Skip()
133
134
135 def clk_remove_col(self, event):
136 try:
137 lst = self.grid_1.GetSelectedRows()[0]
138 self.grid_1.DeleteRows(lst, 1)
139 event.Skip()
140 except IndexError:
141 wx.MessageBox("You Did Not Select Any Row To Delete")
142
143
144 def clk_Create_db(self, event):
145 try:
146 DataColumnsList=[]
147 DataTypeList=[]
148 DataString=[]
149 DataTableName=str(self.txtDataTable.Value)
150 ConnectionString=cwd + "/" + (self.txtFileName.Value + "/" + self.txtDataName.Value + ".db")
151 RowNum = self.grid_1.GetNumberRows()
152
153 for i in range(0,RowNum):
154 DataColumnsList.append(str(self.grid_1.GetCellValue(i, 0)))
155 DataTypeList.append(str(self.grid_1.GetCellValue(i, 1)))
156
157 for i in range(len(DataColumnsList)):
158 DataString.append(DataColumnsList[i] + " " + DataTypeList[i])
159
160 elem= ",".join(DataString)
161 CreateQuery = ("""CREATE TABLE """ + DataTableName + """(%s)""" % elem)
162
163 if not os.path.isfile("CreationLog.txt"):
164 f=open("CreationLog.txt", "w")
165
166 with open("CreationLog.txt", "r+") as NewLog:
167 OldLog=NewLog.read()
168 NewLog.seek(0)
169 NewLog.write(OldLog + "\nCreated on " + str(datetime.date.isoformat(datetime.datetime.now())) + " in '" + ConnectionString + "' ,Used Query : " + CreateQuery + "\n")
170
171
172 if not os.path.exists(self.txtFileName.Value):
173 os.makedirs(self.txtFileName.Value)
174 else:
175 wx.MessageBox("The Folder Already Exists, but you can add to it data tables!")
176
177 cnn = sqlite3.connect(ConnectionString)
178 cursor=cnn.cursor()
179 cursor.execute(CreateQuery)
180 cnn.commit
181 cnn.close()
182
183 self.txtDataFileOutput.Value=("A Data File Named " + self.txtDataName.Value + ".db Was Created in " + self.txtFileName.Value)
184
185 except OSError:
186 wx.MessageBox("The Grid Is Empty!")
187 event.Skip()
188
189
190 def clk_reset_grid(self, event):
191 r=self.grid_1.GetNumberRows()
192
193 for i in range(0,r):
194 self.grid_1.DeleteRows(1, i)
195
196 for c in range(0,2):
197 self.grid_1.SetCellValue(0, c, "")
198
199 self.txtFileName.Value=""
200 self.txtDataName.Value=""
201 self.txtDataTable.Value=""
202 self.txtDataFileOutput.Value=""
203 event.Skip()
204
205 #---------------------------------------------------------------------------
206
207 class MyApp(wx.App):
208 def OnInit(self):
209 gettext.install("app")
210
211 # Set Current directory to the one containing this file.
212 os.chdir(os.path.dirname(os.path.abspath(__file__)))
213
214 wx.InitAllImageHandlers()
215
216 self.SetAppName('Sqlite3 Creator')
217
218 # Create the main window.
219 frame_1 = MyFrame(None, wx.ID_ANY, "")
220 self.SetTopWindow(frame_1)
221
222 frame_1.Show()
223 return True
224
225 #---------------------------------------------------------------------------
226
227 if __name__ == '__main__':
228 app = MyApp()
229 app.MainLoop()
Download source
Additional Information
Link :
http://code.activestate.com/recipes/578665-a-wxpython-gui-to-create-sqlite3-databases/
http://code.activestate.com/recipes/578676-a-phone-book-gui-built-in-wxpython-connected-to-da/
https://github.com/ActiveState/code
https://www.blog.pythonlibrary.org/2010/03/18/wxpython-an-introduction-to-grids/
https://www.blog.pythonlibrary.org/2010/04/04/wxpython-grid-tips-and-tricks/
https://www.salstat.com/news/linking-wxgrid-to-sqlite-database-in-python-an-example
https://groups.google.com/forum/#!topic/wx-users/hk2JidYWxIc
https://groups.google.com/forum/#!topic/wxpython-users/HhtKCxPVX_s
https://tutorial101.blogspot.com/2016/05/wxpython-phone-book-using-data-grid.html
https://stackoverflow.com/questions/54500052/how-to-add-the-database-to-wx-grid-of-wxpython
https://stackoverflow.com/questions/24609197/how-to-make-text-fit-in-cells-in-a-wxpython-grid
https://stackoverflow.com/questions/5868280/auto-wrap-and-newlines-in-wxpython-grid
https://stackoverflow.com/questions/20477651/how-to-make-the-cells-of-a-grid-bold
https://www.pythoncentral.io/series/python-sqlite-database-tutorial/
http://www.jacquet80.eu/blog/post/2008/10/31/241-sqlite
http://www.grroups.com/blog/sqlite-working-with-large-data-sets-in-python-effectively
https://www.sqlite.org/download.html
- - - - -
https://wiki.wxpython.org/TitleIndex
Thanks to
Toufic Zaarour (sample_one.py coding), ActiveState, the wxPython community...
About this page
Date (d/m/y) Person (bot) Comments :
25/05/18 - Ecco (Created page for wxPython Phoenix).
Comments
- For any suggestions or improvements : touficmc@gmail.com
- blah, blah, blah....