How to create a SQLite database easily (Phoenix)

Keywords : Grid, SQLite, Database, Data tables, Datetime.


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

img_sample_one.png

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

source.zip


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

https://docs.wxpython.org/


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....

How to create a SQLite database easily (Phoenix) (last edited 2020-12-13 16:58:38 by Ecco)

NOTE: To edit pages in this wiki you must be a member of the TrustedEditorsGroup.