MySQL-Simple-Query-Browser-0.1.10

I’ve write a simle script to manage MySQL database, write in Python and use wx module for GUI and MySQLdb to connect to the database

below is the source

http://docs.google.com/Doc?id=dhr2c3jq_8g3vv2t

and if you want to download it

http://www.hotscripts.com/Detailed/55679.html

#!/usr/bin/env python

"""
    for my lovely parent
    
    MySQL Simple Query Browser
"""

__scriptname__      = "MySQL Simple Query Browser"
__author__          = "Triyadhi Surahman"
__email__           = "<triyadhi.surahman@gmail.com> <justrelay@yahoo.com>"
__date__            = "20051224 04:57:29 PM"
__version__         = "0.1.10"


import wx
import wx.grid as gridlib
import wx.lib.dialogs as dlg


import MySQLdb

import string


# Constant
# Menu File
ID_FILE_QUIT                            = 10001
# Menu Database
ID_DATABASE_CONNECT                     = 10201
ID_DATABASE_DISCONNECT                  = 10202
ID_DATABASE_CREATE                      = 10203
ID_DATABASE_DROP                        = 10204
ID_DATABASE_REFRESH                     = 10205
# Menu Table
ID_TABLE_CREATE                         = 10301
ID_TABLE_DROP                           = 10302
# Menu Query
ID_QUERY_EXECUTE                        = 10401
# Menu Help
ID_HELP_MANUAL                          = 10501
ID_HELP_ABOUT                           = 10502


# Misc
ID_BUTTON_ADD_TABLE_NAME_TYPE           = 20001
ID_BUTTON_ADD_CREATE_TABLE              = 20002
ID_COMBOBOX_DATABASE                    = 20003
ID_COMBOBOX_TABLE                       = 20004

ERROR_MESSAGE                           = True

# Tested MySQL command, 1st word only
has_result = ['ANALYZE', 'BACKUP', 'CHECK', 'CHECKSUM', 'DESCRIBE', 'OPTIMIZE', 'REPAIR', 'RESTORE', 'SELECT', 'SHOW']
no_result  = ['ALTER', 'CREATE', 'DELETE', 'DROP', 'GRANT', 'INSERT', 'SET', 'RENAME', 'REVOKE', 'TRUNCATE', 'UPDATE', 'USE']



class DatabaseConnectDialog(wx.Dialog):
    def __init__(self, parent, id, title):
        wx.Dialog.__init__(self, parent, id, title)
        
        self.boxvertical        = wx.BoxSizer(wx.VERTICAL)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Connect To MySQL Server")
        font                    = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
        statictext.SetFont(font)
        statictext.SetSize(statictext.GetBestSize())
        boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
        self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        gridsizer               = wx.GridSizer(3, 2, 5, 5)
        
        statictexthost          = wx.StaticText(self, -1, "Host")
        self.textctrlhost       = wx.TextCtrl(self, -1, value="localhost")
        
        statictextusername      = wx.StaticText(self, -1, "Username")

        #self.textctrlusername   = wx.TextCtrl(self, -1)
        self.textctrlusername   = wx.TextCtrl(self, -1, value="root")

        self.textctrlusername.SetFocus()
        
        statictextpassword      = wx.StaticText(self, -1, "Password")
        self.textctrlpassword   = wx.TextCtrl(self, -1, style=wx.TE_PASSWORD)
        
        gridsizer.AddMany([
                            (statictexthost, 0, wx.EXPAND),
                            (self.textctrlhost, 0, wx.EXPAND),
                            (statictextusername, 0, wx.EXPAND),
                            (self.textctrlusername, 0, wx.EXPAND),
                            (statictextpassword, 0, wx.EXPAND),
                            (self.textctrlpassword, 0, wx.EXPAND)
                            ])
        
        self.boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        boxhorizontal       = wx.BoxSizer(wx.HORIZONTAL)
        self.checkbox            = wx.CheckBox(self, -1, "&amp;Use Default Port (3306)")
        self.checkbox.SetValue(True)
        self.Bind(wx.EVT_CHECKBOX, self.CheckBoxChangePort, self.checkbox)
        boxhorizontal.Add(self.checkbox, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_LEFT | wx.ALL, 5)
        
        self.gridsizerport   = wx.GridSizer(1, 2, 5, 5)
        statictextport  = wx.StaticText(self, -1, "Port")
        self.textctrlport    = wx.TextCtrl(self, -1)
        self.gridsizerport.AddMany([
                                    (statictextport, 0, wx.EXPAND),
                                    (self.textctrlport, 0, wx.EXPAND)
                                    ])
        self.boxhorizontalport  = wx.BoxSizer(wx.HORIZONTAL)
        self.boxhorizontalport.Add(self.gridsizerport, 0, wx.ALIGN_CENTER | wx.ALL, 0)
        self.boxhorizontalport.Show(self.gridsizerport, 0)
            
        boxhorizontal       = wx.BoxSizer(wx.HORIZONTAL)
        ok                  = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        cancel              = wx.Button(self, wx.ID_CANCEL, " &amp;Cancel ")
        boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        self.boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(self.boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        self.boxvertical.Fit(self)
        
        
    def CheckBoxChangePort(self, event):
        """
            If you want to use another port instead the default port
            Alt+U or click on *Use Default Port* checkbox
        """
        if event.IsChecked() == 0:
            self.boxvertical.Insert(3, self.boxhorizontalport, 0, wx.ALIGN_CENTER | wx.ALL, 5)
            self.boxvertical.Show(self.boxhorizontalport)
        elif event.IsChecked() == 1:
            self.boxvertical.Detach(self.gridsizerport)
            # hidding the box
            self.boxvertical.Show(self.boxhorizontalport, 0)

        self.boxvertical.RecalcSizes()
        self.boxvertical.Fit(self)



class DatabaseCreateDialog(wx.Dialog) :
    def __init__(self, parent, id, title) :
        wx.Dialog.__init__(self, parent, id, title)
        boxvertical             = wx.BoxSizer(wx.VERTICAL)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Create Database")
        font                    = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
        statictext.SetFont(font)
        statictext.SetSize(statictext.GetBestSize())
        boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        gridsizer               = wx.GridSizer(1, 2, 5, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Database Name")
        self.textctrl           = wx.TextCtrl(self, -1)
        
        gridsizer.AddMany([
                            (statictext, 0, wx.ALIGN_LEFT),
                            (self.textctrl, 0, wx.ALIGN_LEFT)
                            ])
                         
        boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal       = wx.BoxSizer(wx.HORIZONTAL)
        ok              = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        cancel              = wx.Button(self, wx.ID_CANCEL, " &amp;Cancel ")
        boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        boxvertical.Fit(self)


            
class DatabaseDropDialog(wx.Dialog) :
    def __init__(self, parent, id, title, databases) :
        wx.Dialog.__init__(self, parent, id, title)
        boxvertical             = wx.BoxSizer(wx.VERTICAL)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Drop Database")
        font                    = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
        statictext.SetFont(font)
        statictext.SetSize(statictext.GetBestSize())
        boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        gridsizer               = wx.GridSizer(1, 2, 5, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Database Name")
        self.combobox           = wx.ComboBox(self, -1, style=wx.CB_READONLY)
        
        dbnamelist              = databases
        
        for d in dbnamelist :
            self.combobox.Append(d)
        
        gridsizer.AddMany([
                            (statictext, 0, wx.ALIGN_LEFT),
                            (self.combobox, 0, wx.ALIGN_LEFT)
                            ])
                         
        boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal       = wx.BoxSizer(wx.HORIZONTAL)
        ok              = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        cancel              = wx.Button(self, wx.ID_CANCEL, " &amp;Cancel ")
        boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        boxvertical.Fit(self)



class TableCreateDialog(wx.Dialog) :
    def __init__(self, parent, id, title, databases) :
        wx.Dialog.__init__(self, parent, id, title)
        
        boxvertical             = wx.BoxSizer(wx.VERTICAL)
        
        notebook                = wx.Notebook(self, -1)
        paneltablename          = wx.Panel(notebook, -1)
        paneladd                = wx.Panel(notebook, -1)
        panelsyntax             = wx.Panel(notebook, -1)
        notebook.AddPage(paneltablename, "Name and Type")
        notebook.AddPage(paneladd, "Add Fields")
        notebook.AddPage(panelsyntax, "Syntax")
        
        # BoxSizer vertical for paneltablename
        boxptv                  = wx.BoxSizer(wx.VERTICAL)
        gridsizer               = wx.GridSizer(3, 2, 5, 5)
        statictextdatabases     = wx.StaticText(paneltablename, -1, "Database")
        statictexttablename     = wx.StaticText(paneltablename, -1, "Table Name")
        statictexttabletype     = wx.StaticText(paneltablename, -1, "Table Type")
        self.comboboxdatabases  = wx.ComboBox(paneltablename, -1)
        self.textctrltablename  = wx.TextCtrl(paneltablename, -1, size=self.comboboxdatabases.GetSize())
        self.comboboxtabletype  = wx.ComboBox(paneltablename, -1, style=wx.CB_READONLY)
        
        dbnamelist              = databases
        
        for d in dbnamelist :
            self.comboboxdatabases.Append(d)
        
        tabletypelist           = ['MyISAM', 'InnoDB', 'ISAM']
        
        for t in tabletypelist :
            self.comboboxtabletype.Append(t)
        
        self.comboboxtabletype.SetValue(tabletypelist[0])
        
        gridsizer.AddMany([
                            (statictextdatabases, 0, wx.ALIGN_LEFT),
                            (self.comboboxdatabases, 0, wx.ALIGN_LEFT),
                            (statictexttablename, 0, wx.ALIGN_LEFT),
                            (self.textctrltablename, 0, wx.ALIGN_LEFT),
                            (statictexttabletype, 0, wx.ALIGN_LEFT),
                            (self.comboboxtabletype, 0, wx.ALIGN_LEFT)
                            ])
        
        boxptv.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        button                  = wx.Button(paneltablename, ID_BUTTON_ADD_TABLE_NAME_TYPE, " &amp;Add ")
        boxhorizontal.Add(button, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxptv.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        # event
        self.Bind(wx.EVT_BUTTON, self.AddTableNameType, id=ID_BUTTON_ADD_TABLE_NAME_TYPE)
        
        paneltablename.SetSizer(boxptv)
        paneltablename.SetAutoLayout(True)
        boxptv.Fit(paneltablename)
        
        # BoxSizer vertical for paneladd
        boxpav                  = wx.BoxSizer(wx.VERTICAL)
        gridsizer               = wx.GridSizer(9, 2, 5, 5)
        statictextfieldname     = wx.StaticText(paneladd, -1, "Field Name")
        statictextdatatype      = wx.StaticText(paneladd, -1, "Data Type")
        statictextlengthvalues  = wx.StaticText(paneladd, -1, "Length/Values")
        statictextattributes    = wx.StaticText(paneladd, -1, "Attributes")
        statictextnull          = wx.StaticText(paneladd, -1, "NULL")
        statictextdefault       = wx.StaticText(paneladd, -1, "Default")
        statictextextra         = wx.StaticText(paneladd, -1, "Extra")
        statictextcolumndefinition  = wx.StaticText(paneladd, -1, "Column Definition")
        statictextfulltext      = wx.StaticText(paneladd, -1, "Fulltext")
        
        
        self.textctrlfieldname  = wx.TextCtrl(paneladd, -1)
        self.textctrlfieldname.SetFocus()
        
        self.comboboxdatatype   = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)
        
        datatypelist            = [
                                # numeric types
                                'TINYINT', 'BIT', 'BOOL', 'BOOLEAN', 'SMALLINT',
                                'MEDIUMINT', 'INT', 'INTEGER', 'BIGINT', 'FLOAT',
                                'DOUBLE', 'DOUBLE PRECISION','REAL', 'DECIMAL', 'DEC',
                                'NUMERIC', 'FIXED',
                                # date and time types
                                'DATE', 'DATETIME', 'TIMESTAMP', 'TIME', 'YEAR',
                                # string types
                                'CHAR', 'VARCHAR', 'BINARY', 'VARBINARY', 'TINYBLOB',
                                'TINYTEXT', 'BLOB', 'TEXT', 'MEDIUMBLOB', 'MEDIUMTEXT',
                                'LONGBLOB', 'LONGTEXT', 'ENUM', 'SET'
                                ]
        for d in datatypelist :
            self.comboboxdatatype.Append(d)

        self.textctrllengthvalues = wx.TextCtrl(paneladd, -1)
        
        self.comboboxattributes = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)
        
        attributeslist          = ['', 'BINARY', 'UNSIGNED', 'UNSIGNED ZEROFILL']
        for u in attributeslist :
            self.comboboxattributes.Append(u)
        
        self.comboboxnull       = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)
        
        nulllist                = ['', 'NULL', 'NOT NULL']
        for n in nulllist :
            self.comboboxnull.Append(n)
        
        self.textctrldefault    = wx.TextCtrl(paneladd, -1)
        
        self.comboboxextra      = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)
        
        extralist               = ['', 'AUTO_INCREMENT']
        for e in extralist :
            self.comboboxextra.Append(e)
        
        
        self.comboboxcolumndefinition = wx.ComboBox(paneladd, -1, style=wx.CB_READONLY)
        
        columndefinitionlist    = ['', 'PRIMARY KEY', 'INDEX', 'UNIQUE']
        for p in columndefinitionlist :
            self.comboboxcolumndefinition.Append(p)
        
        self.checkboxfulltext   = wx.CheckBox(paneladd, -1)
        
        gridsizer.AddMany([
                            (statictextfieldname, 0, wx.ALIGN_LEFT),
                            (self.textctrlfieldname, 0, wx.ALIGN_LEFT),
                            (statictextdatatype, 0, wx.ALIGN_LEFT),
                            (self.comboboxdatatype, 0, wx.ALIGN_LEFT),
                            (statictextlengthvalues, 0, wx.ALIGN_LEFT),
                            (self.textctrllengthvalues, 0, wx.ALIGN_LEFT),
                            (statictextattributes, 0, wx.ALIGN_LEFT),
                            (self.comboboxattributes, 0, wx.ALIGN_LEFT),
                            (statictextnull, 0, wx.ALIGN_LEFT),
                            (self.comboboxnull, 0, wx.ALIGN_LEFT),
                            (statictextdefault, 0, wx.ALIGN_LEFT),
                            (self.textctrldefault, 0, wx.ALIGN_LEFT),
                            (statictextextra, 0, wx.ALIGN_LEFT),
                            (self.comboboxextra, 0, wx.ALIGN_LEFT),
                            (statictextcolumndefinition, 0, wx.ALIGN_LEFT),
                            (self.comboboxcolumndefinition, 0, wx.ALIGN_LEFT),
                            (statictextfulltext, 0, wx.ALIGN_LEFT),
                            (self.checkboxfulltext, 0, wx.ALIGN_LEFT)
                            ])
        boxpav.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        button                  = wx.Button(paneladd, ID_BUTTON_ADD_CREATE_TABLE, " &amp;Add ")
        #self.Bind(wx.EVT_BUTTON, self.OnAddCreateTable, id=ID_BUTTON_ADD_CREATE_TABLE)
        boxhorizontal.Add(button, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxpav.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        # event
        self.Bind(wx.EVT_BUTTON, self.AddField, id=ID_BUTTON_ADD_CREATE_TABLE)
        
        paneladd.SetSizer(boxpav)
        paneladd.SetAutoLayout(True)
        boxpav.Fit(paneladd)
        
        # BoxSizer vertical for panelsyntax
        boxpsv                  = wx.BoxSizer(wx.VERTICAL)
        self.textctrlsyntax     = wx.TextCtrl(panelsyntax, -1, 
                                            style=wx.TE_MULTILINE | wx.HSCROLL | wx.TE_RICH, 
                                            size=paneladd.GetSize())
        boxpsv.Add(self.textctrlsyntax, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        panelsyntax.SetSizer(boxpsv)
        panelsyntax.SetAutoLayout(True)
        boxpsv.Fit(panelsyntax)
        
        boxvertical.Add(notebook, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        ok                  = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        cancel                  = wx.Button(self, wx.ID_CANCEL, " &amp;Cancel ")
        boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        boxvertical.Fit(self)
        
        self.ResetValue()
    
    def ResetValue(self) :
        self.syntaxvalue = ""
        self.tabledetails = []
        self.databasename = ""
        self.tablename = ""
        self.tabletype = ""
        self.primarykey = ""
        self.unique = ""
        self.index = ""
        self.fulltext = ""
    
    def AddTableNameType(self, event) :
        self.databasename   = self.comboboxdatabases.GetValue()
        self.tablename      = self.textctrltablename.GetValue()
        self.tabletype      = self.comboboxtabletype.GetValue() 
        self.PanelSyntaxValue()
    
    def AddField(self, event) :
        item = ""
        if len(self.tabledetails) > 0 :
            item = ",\\n"
        
        item += string.strip(self.textctrlfieldname.GetValue())
        item += " " + self.comboboxdatatype.GetValue()
        
        if len(string.strip(self.textctrllengthvalues.GetValue())) > 0 :
            item += "(" + self.textctrllengthvalues.GetValue() + ")"
            
        item += " " + self.comboboxattributes.GetValue()
        item += " " + self.comboboxnull.GetValue()
        
        if len(string.strip(self.textctrldefault.GetValue())) > 0 :
            item += " DEFAULT '" + self.textctrldefault.GetValue() + "'"
            
        item += " " + self.comboboxextra.GetValue()
        
        if self.comboboxcolumndefinition.GetValue() == 'PRIMARY KEY' :
            if len(self.primarykey) == 0 :
                self.primarykey = self.textctrlfieldname.GetValue()
            else :
                self.primarykey += ", " + self.textctrlfieldname.GetValue()
        elif self.comboboxcolumndefinition.GetValue() == 'INDEX' :
            if len(self.index) == 0 :
                self.index = self.textctrlfieldname.GetValue()
            else :
                self.index += ", " + self.textctrlfieldname.GetValue()
        elif self.comboboxcolumndefinition.GetValue() == 'UNIQUE' :
            if len(self.unique) == 0 :
                self.unique = self.textctrlfieldname.GetValue()
            else :
                self.unique += ", " + self.textctrlfieldname.GetValue()
        
        if self.checkboxfulltext.IsChecked() == 1 :
            if len(self.fulltext) == 0 :
                self.fulltext = self.textctrlfieldname.GetValue()
            else :
                self.fulltext += ", " + self.textctrlfieldname.GetValue()
        
        self.tabledetails.append(item)
        
        self.PanelSyntaxValue()
        
    def PanelSyntaxValue(self) :
        self.syntaxvalue = "CREATE TABLE " + self.databasename + "." + self.tablename + " (\\n"
        
        for i in range(len(self.tabledetails)) :
            self.syntaxvalue += self.tabledetails[i]
                
        if len(self.primarykey) > 0 :
            self.syntaxvalue += ",\\n PRIMARY KEY (" + self.primarykey + ")"
            
        if len(self.index) > 0 :
            self.syntaxvalue += ",\\n INDEX (" + self.index + ")"
            
        if len(self.unique) > 0 :
            self.syntaxvalue += ",\\n UNIQUE (" + self.unique + ")"
                
        if len(self.fulltext) > 0 :
            self.syntaxvalue += ",\\n FULLTEXT (" + self.fulltext + ")"
                
        self.syntaxvalue += "\\n) TYPE=" + self.tabletype
                
        self.textctrlsyntax.SetValue(self.syntaxvalue)
        


class TableDropDialog(wx.Dialog) :
    def __init__(self, parent, id, title, connection, databases) :
        wx.Dialog.__init__(self, parent, id, title)
        
        self.connection = connection
        
        boxvertical             = wx.BoxSizer(wx.VERTICAL)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictext              = wx.StaticText(self, -1, "Drop Table")
        font                    = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
        statictext.SetFont(font)
        statictext.SetSize(statictext.GetBestSize())
        boxhorizontal.Add(statictext, 0, wx.ALIGN_CENTER|wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        gridsizer               = wx.GridSizer(2, 2, 5, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        statictextdb            = wx.StaticText(self, -1, "Database Name")
        self.comboboxdb         = wx.ComboBox(self, ID_COMBOBOX_DATABASE, style=wx.CB_READONLY)
        statictexttable         = wx.StaticText(self, -1, "Table Name")
        self.comboboxtable      = wx.ComboBox(self, ID_COMBOBOX_TABLE, style=wx.CB_READONLY)
        
        self.Bind(wx.EVT_COMBOBOX, self.DatabaseSelected, id=ID_COMBOBOX_DATABASE)
        self.Bind(wx.EVT_COMBOBOX, self.TableSelected, id=ID_COMBOBOX_TABLE)
        
        dbnamelist              = databases
        
        for d in dbnamelist :
            self.comboboxdb.Append(d)
        
        gridsizer.AddMany([
                            (statictextdb, 0, wx.ALIGN_LEFT),
                            (self.comboboxdb, 0, wx.ALIGN_LEFT),
                            (statictexttable, 0, wx.ALIGN_LEFT),
                            (self.comboboxtable, 0, wx.ALIGN_LEFT)
                            ])
                         
        boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal       = wx.BoxSizer(wx.HORIZONTAL)
        ok              = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        cancel              = wx.Button(self, wx.ID_CANCEL, " &amp;Cancel ")
        boxhorizontal.Add(cancel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        boxvertical.Fit(self)
        
    def DatabaseSelected(self, event) :
        self.comboboxtable.Clear()
        self.GetTable(self.connection, self.comboboxdb.GetValue())
        
        
    def GetTable(self, connection, dbname) :
        connection.query("""USE """ + dbname)
        connection.query("""SHOW TABLES""")
        r = connection.store_result()
        f = r.fetch_row(maxrows=0)
            
        for i in range(len(f)) :
            self.comboboxtable.Append(f[i][0])
        
    def TableSelected(self, event) :
        self.dropsyntax = "DROP TABLE " + self.comboboxdb.GetValue() + "." + self.comboboxtable.GetValue()



class HelpAbout(wx.Dialog) :
    def __init__(self, parent, id, title) :
        wx.Dialog.__init__(self, parent, id, title)
        
        boxvertical             = wx.BoxSizer(wx.VERTICAL)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        namelabel               = wx.StaticText(self, -1, __scriptname__)
        font                    = wx.Font(12, wx.DEFAULT, wx.NORMAL, wx.NORMAL)
        namelabel.SetFont(font)
        namelabel.SetSize(namelabel.GetBestSize())
        namelabel.SetSize(namelabel.GetBestSize())
        boxhorizontal.Add(namelabel, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        gridsizer               = wx.GridSizer(3, 2, 5, 5)
        
        authorlabel             = wx.StaticText(self, -1, "Author")
        authorvalue             = wx.StaticText(self, -1, __author__)
        emaillabel              = wx.StaticText(self, -1, "Email")
        emailvalue              = wx.StaticText(self, -1, __email__)
        versionlabel            = wx.StaticText(self, -1, "Version")
        versionvalue            = wx.StaticText(self, -1, __version__)
        datelabel               = wx.StaticText(self, -1, "Date")
        datevalue               = wx.StaticText(self, -1, __date__)
        
        gridsizer.AddMany([
                            (authorlabel, 0, wx.ALIGN_LEFT),
                            (authorvalue, 0, wx.ALIGN_LEFT),
                            (emaillabel, 0, wx.ALIGN_LEFT),
                            (emailvalue, 0, wx.ALIGN_LEFT),
                            (versionlabel, 0, wx.ALIGN_LEFT),
                            (versionvalue, 0, wx.ALIGN_LEFT),
                            (datelabel, 0, wx.ALIGN_LEFT),
                            (datevalue, 0, wx.ALIGN_LEFT)
                            ])
                         
        boxvertical.Add(gridsizer, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        
        boxhorizontal           = wx.BoxSizer(wx.HORIZONTAL)
        ok                  = wx.Button(self, wx.ID_OK, " &amp;OK ")
        ok.SetDefault()
        boxhorizontal.Add(ok, 0, wx.ALIGN_CENTRE | wx.ALL, 5)
        boxvertical.Add(boxhorizontal, 0, wx.ALIGN_CENTER | wx.ALL, 5)
        
        self.SetSizer(boxvertical)
        # We need it if we want to change the layout automatically 
        # see CheckBoxChangePort
        self.SetAutoLayout(True)
        boxvertical.Fit(self)



class HelpManual(dlg.ScrolledMessageDialog) :
    def __init__(self, parent) :
        f = open("manual.txt", "r")
        msg = f.read()
        f.close()
        
        dlg.ScrolledMessageDialog.__init__(self, parent, msg, caption="Manual")
        
    def OnOK(self, event) :
        self.Destroy()
        event.Skip()
    


class ErrorMessageDialog(dlg.ScrolledMessageDialog) :
    def __init__(self, parent, msg) :
        dlg.ScrolledMessageDialog.__init__(self, parent, msg, caption="Query Error")
        
    def OnOK(self, event) :
        self.Destroy()
        event.Skip()
         
        
class MainFrame(wx.Frame):
    
    # Style
    style = {
            'frame': wx.DEFAULT_FRAME_STYLE,
            'splitter': wx.SUNKEN_BORDER | wx.CLIP_CHILDREN,
            'textctrl': wx.TE_MULTILINE | wx.HSCROLL | wx.TE_RICH
            }
            
    def __init__(self, parent, title):
        wx.Frame.__init__(self, parent, -1, title,
                            size=wx.Size(750, 550),
                            style=self.style['frame'])
                            
        self.CentreOnScreen()
        
        # Menu Bar
        menubar = wx.MenuBar()
        
        # Menu : File
        menufile                = wx.Menu()
        menufile.Append(ID_FILE_QUIT, "&amp;Quit\\tCtrl+Q")
        menubar.Append(menufile, "&amp;File")
        # Event
        self.Bind(wx.EVT_MENU, self.MenuFileQuit, id=ID_FILE_QUIT)
        
        # Menu : Database
        menudatabase            = wx.Menu()
        menudatabase.Append(ID_DATABASE_CONNECT, "C&amp;onnect\\tF11")
        menudatabase.Append(ID_DATABASE_DISCONNECT, "D&amp;isconnect\\tF12")        
        menudatabase.AppendSeparator()
        menudatabase.Append(ID_DATABASE_CREATE, "&amp;Create")
        menudatabase.Append(ID_DATABASE_DROP, "&amp;Drop")        
        menudatabase.AppendSeparator()
        menudatabase.Append(ID_DATABASE_REFRESH, "&amp;Refresh\\tCtrl+R")
        
        menubar.Append(menudatabase, "&amp;Database")
        # Event
        self.Bind(wx.EVT_MENU, self.MenuDatabaseConnect, id=ID_DATABASE_CONNECT)
        self.Bind(wx.EVT_MENU, self.MenuDatabaseDisconnect, id=ID_DATABASE_DISCONNECT)
        self.Bind(wx.EVT_MENU, self.MenuDatabaseCreate, id=ID_DATABASE_CREATE)
        self.Bind(wx.EVT_MENU, self.MenuDatabaseDrop, id=ID_DATABASE_DROP)
        self.Bind(wx.EVT_MENU, self.MenuDatabaseRefresh, id=ID_DATABASE_REFRESH)
        
        # Menu : Table
        menutable               = wx.Menu()
        menutable.Append(ID_TABLE_CREATE, "&amp;Create")
        menutable.Append(ID_TABLE_DROP, "&amp;Drop")

        menubar.Append(menutable, "&amp;Table")
        
        # Event
        self.Bind(wx.EVT_MENU, self.MenuTableCreate, id=ID_TABLE_CREATE)
        self.Bind(wx.EVT_MENU, self.MenuTableDrop, id=ID_TABLE_DROP)
        
        # Menu : Query
        menuquery               = wx.Menu()
        menuquery.Append(ID_QUERY_EXECUTE, "&amp;Execute\\tF5")
        
        menubar.Append(menuquery, "&amp;Query")
        # Event
        self.Bind(wx.EVT_MENU, self.MenuQueryExecute, id=ID_QUERY_EXECUTE)
        
        # Menu : Help
        menuhelp                = wx.Menu()
        menuhelp.Append(ID_HELP_MANUAL, "&amp;Manual\\tF1")
        menuhelp.AppendSeparator()
        menuhelp.Append(ID_HELP_ABOUT, "&amp;About")
        menubar.Append(menuhelp, "&amp;Help")
        # Event
        self.Bind(wx.EVT_MENU, self.MenuHelpManual, id=ID_HELP_MANUAL)
        self.Bind(wx.EVT_MENU, self.MenuHelpAbout, id=ID_HELP_ABOUT)
        
        self.SetMenuBar(menubar)
        
        # Status Bar
        self.statusbar          = wx.StatusBar(self)
        self.statusbar.SetFieldsCount(4)
        widths                  = [-2, -1, 50, 50]
        self.statusbar.SetStatusWidths(widths)
        self.SetStatusBar(self.statusbar)
        
        self.statusbar.SetStatusText("No Database Selected", 1)
        
        # Split the Window
        splitter1               = wx.SplitterWindow(self, style=self.style['splitter'])
        splitter2               = wx.SplitterWindow(splitter1, style=self.style['splitter'])
        
        # TextCtrl
        self.textctrl           = wx.TextCtrl(splitter2, -1, style=self.style['textctrl'])
        self.textctrl.SetFocus()
        
        # Grid
        self.grid               = gridlib.Grid(splitter2, -1)
        self.grid.CreateGrid(1, 1)
        self.grid.SetColLabelValue(0, "")
        self.grid.SetRowLabelValue(0, "")
        self.grid.EnableEditing(0)
        self.grid.SetColLabelSize(20)
        self.grid.SetRowLabelSize(0)
        self.gridcols           = 0
        self.gridrows           = 0
        
        # TreeCtrl
        self.treectrl           = wx.TreeCtrl(splitter1, -1)
        
        self.Bind(wx.EVT_TREE_SEL_CHANGED, self.OnTreeSelChanged, self.treectrl)
        
        splitter2.SplitHorizontally(self.textctrl, self.grid, -275)
        splitter1.SplitVertically(self.treectrl, splitter2, 150)
        splitter1.SetMinimumPaneSize(20)
        splitter2.SetMinimumPaneSize(20)        


    def MenuFileQuit(self, event) :
        """
            Alt+F+Q or Ctrl+Q
        """
        self.Close()
        
        event.Skip()
    

    def MenuDatabaseConnect(self, event) :
        """
            Show the database connect dialog, Alt+D+O or F11
        """
        
        dcd                     = DatabaseConnectDialog(self, -1, "Connect")
        dcd.CenterOnScreen()
        dcd.Show()
        
        if dcd.ShowModal() == wx.ID_OK :
            _host = dcd.textctrlhost.GetValue()
            _user = dcd.textctrlusername.GetValue()
            _passwd = dcd.textctrlpassword.GetValue()
            
            if dcd.checkbox.IsChecked() == 0 :
                # we need port as an integer
                _port = int(dcd.textctrlport.GetValue())
                self.DBConnect(_host, _user, _passwd, _port)          
            else :
                self.DBConnect(_host, _user, _passwd)

        dcd.Destroy()
        
        event.Skip()


    def MenuDatabaseDisconnect(self, event) :
        """
            Alt+D+I or F11
        """
        
        try :
            self.DBClose()
        except :
            pass
            
        event.Skip()
    
    
    def MenuDatabaseCreate(self, event) :
        """
            Show the database create dialog, Alt+D+C or F11
        """
        
        databasecreate = DatabaseCreateDialog(self, -1, "Create")
        databasecreate.CenterOnScreen()
        databasecreate.Show()
        
        if databasecreate.ShowModal() == wx.ID_OK :
            self.ExecuteSQL(self.SplitSQL("CREATE DATABASE " + databasecreate.textctrl.GetValue()))
        
        databasecreate.Destroy()        
        
        event.Skip()


    def MenuDatabaseDrop(self, event) :
        """
            Show the database drop dialog, Alt+D+D or F11
        """
        
        databasedrop = DatabaseDropDialog(self, -1, "Drop", self.databases)
        databasedrop.CenterOnScreen()
        databasedrop.Show()
        
        if databasedrop.ShowModal() == wx.ID_OK :
            self.ExecuteSQL(self.SplitSQL("DROP DATABASE " + databasedrop.combobox.GetValue()))
            
        databasedrop.Destroy()
        
        event.Skip()
            
    
    def MenuDatabaseRefresh(self, event) :
        """
            Refresh database, get host, databases, and tables, Alt+D+R or Ctrl+R
        """
        
        try :
            rootitem = self.treectrl.GetItemText(self.treectrl.GetRootItem())
            self.treectrl.DeleteAllItems()
            self.DBGetDatabasesAndTables(rootitem)
        except :
            pass
            
        event.Skip()


    def MenuTableCreate(self, event) :
        """
            Show the table create dialog, Alt+T+C
        """
        
        tablecreatedialog       = TableCreateDialog(self, -1, "Create Table", self.databases)
        tablecreatedialog.CenterOnScreen()
        tablecreatedialog.Show()
        
        if tablecreatedialog.ShowModal() == wx.ID_OK :
            self.ExecuteSQL(self.SplitSQL(tablecreatedialog.textctrlsyntax.GetValue()))
        
        tablecreatedialog.ResetValue()
        tablecreatedialog.Destroy()
        
        event.Skip()
            
        
    def MenuTableDrop(self, event) :
        """
            Show the table drop dialog, Alt+T+D
        """
        
        tabledrop = TableDropDialog(self, -1, "Drop Table", self.db, self.databases)
        tabledrop.CenterOnScreen()
        tabledrop.Show()
        
        if tabledrop.ShowModal() == wx.ID_OK :
            self.ExecuteSQL(self.SplitSQL(tabledrop.dropsyntax))
            
        tabledrop.Destroy()
        
        event.Skip()
    
    
    def MenuQueryExecute(self, event) :
        """
            Execute query, Alt+Q+E or F5
        """
        
        self.ExecuteSQL(self.SplitSQL(self.textctrl.GetValue()))
        
        event.Skip()    
        
        
    def MenuHelpManual(self, event) :
        """
            Show the Manual, Alt+H+M or F1
        """
        helpmanual = HelpManual(self)
        helpmanual.Show()
        
        
    def MenuHelpAbout(self, event) :
        """
            Show the About dialog, Alt+H+A
        """
        
        helpabout               = HelpAbout(self, -1, "About")
        helpabout.CenterOnScreen()
        helpabout.Show()
        
        event.Skip()


    def DBConnect(self, _host, _user, _passwd, _port=3306) :
        try :
            self.db = MySQLdb.connect(host=_host, user=_user, passwd=_passwd, port=_port)
            self.DBGetDatabasesAndTables(_host)
        except MySQLdb.Error, details:
            print "Error ", details
            
            if ERROR_MESSAGE :
                err = ErrorMessageDialog(self, ("Error " + str(details)))
                err.Show()
    

    def DBGetDatabasesAndTables(self, _host) :            
        try :
            self.treeroot = self.treectrl.AddRoot(_host)
            self.db.query("""SHOW DATABASES""")
            r = self.db.store_result()
            fr = r.fetch_row(maxrows=0)
        
            self.databases = []
            self.dbmap = {}
            for i in fr :
                self.databases.append(i[0])
            
            for i in self.databases :
                d = self.treectrl.AppendItem(self.treeroot, i)
                self.dbmap[i] = d
                self.db.query("""USE """ + i)
                self.db.query("""SHOW TABLES""")
                rt = self.db.store_result()
                frt = rt.fetch_row(maxrows=0)
            
                tables = []
                for j in frt :
                    tables.append(j[0])
                
                for j in tables :
                    t = self.treectrl.AppendItem(d, j)
                
                    self.db.query("""DESCRIBE """ + j)
                    rf = self.db.store_result()
                    frf = rf.fetch_row(maxrows=0)
                    field = []
                
                    for k in frf :
                        field.append(k[0])
                
                    for k in field :
                        fi = self.treectrl.AppendItem(t, k)
            self.treectrl.Expand(self.treeroot)
        except MySQLdb.Error, details :
            print "Error ", details
            
            if ERROR_MESSAGE :
                err = ErrorMessageDialog(self, ("Error " + str(details)))
                err.Show()
    
    
    def DBClose(self) :
        self.db.close()
        self.treectrl.DeleteAllItems()


    def SplitSQL(self, sql) :
        """ 
            currently only supported the '#' comment
        """
        
        sql = string.strip(sql)
               
        tup = []
        b       = False
        
        # get the # and \\n location and place it in 'tup' list
        for i in range(len(sql)) :
            if sql[i] == "#" :
                b = True
                index = i
                
                while b :
                    if index < len(sql) :
                        if sql[index] == "\\n" :
                            b = False
                            front   = i
                            end     = index
                        index += 1
                    else :
                        b = False
                        front   = i
                        end     = index
                    
                tup.append((front, end))
                index = 0

        tup2 = []
        
        # get char >= tup[i][0] and <= tup[i][1] and place it in tup2
        for i in range(len(tup)) :
            tup2.append("")
            
            for j in range(tup[i][0], tup[i][1]) :
                tup2[i] += sql[j]
                
        # replace sql char with "" when it's found in tup2
        for i in range(len(tup2)) :
            try :
                sql = string.replace(sql, tup2[i], "")
            except :
                pass
            
        # replace, split, strip, and return
        sql = string.replace(sql, "\\n", "")
        ret = sql.split(";")
        for i in range(len(ret)) :
            ret[i] = string.strip(ret[i])
        
        return ret
        
        
    def ExecuteSQL(self, sql=[]) :
        query   = []
        query   = sql
        
        success         = False
        errormessage    = ""
        
        for q in range(len(query)) :
            # MySQL command
            command = query[q].split(" ", 1)
            
            if len(query[q]) > 0 :
                # Set up the grid
                self.grid.DeleteCols(0, self.gridcols, 1)
                self.grid.DeleteRows(0, self.gridrows, 1)
                self.grid.SetColLabelValue(0, "")
                self.grid.SetColSize(0, self.grid.GetDefaultColSize())
                self.grid.SetRowSize(0, self.grid.GetDefaultRowSize())
                self.grid.ClearGrid()
            
                try :
                    self.db.query(query[q])
                    r = self.db.store_result()
                    success = True
                    
                except MySQLdb.Error, detail :
                    success = False
                    self.gridcols   = 0
                    self.gridrows   = 0
                    print "Error", detail
                    
                    errormessage += query[q] + "\\nError " + str(detail) + "\\n\\n\\n"
                    
                
                if success :
                    if command[0].upper() in has_result :
                        f = r.fetch_row(maxrows=0, how=1)
                        if len(f) >= 1 :
                            self.gridcols = len(f[0].keys())
                            self.gridrows = len(f)
                
                            self.grid.InsertCols(0, (self.gridcols - 1))
                            for i in range(len(f[0].keys())) :
                                self.grid.SetColLabelValue(i, f[0].keys()[i])
                    
                            self.grid.InsertRows(1, len(f) - 1, 0)
                            for i in range(len(f)) :
                                for j in range(len(f[i].values())) :
                                    if f[i].values()[j] == None :
                                        self.grid.SetCellValue(i, j, "NULL")
                                    else :
                                        self.grid.SetCellValue(i, j, str(f[i].values()[j]))
                            
                            self.grid.AutoSizeColumns(1)
                            self.grid.AutoSizeRows(1)
                            self.gridcols = j
                            self.gridrows = i
                            self.statusbar.SetStatusText(str(self.gridrows + 1), 2)
                    
                        else :
                            self.gridcols   = 0
                            self.gridrows   = 0
                    
                    elif command[0].upper() in no_result :
                        self.gridcols = 0
                        self.gridrows = 0
                        self.statusbar.SetStatusText("0", 2)
                
                        if command[0].upper() == 'USE' :
                            try :
                                item = self.dbmap[command[1].strip()]
                                self.treectrl.SelectItem(item)
                                self.treectrl.EnsureVisible(item)
                                self.treectrl.Expand(item)
                            except :
                                pass
                    else :
                        self.gridcols   = 0
                        self.gridrows   = 0
                        m = "Sorry, not supported or not tested yet."
                        print "Sorry, not supported or not tested yet."
                        
                        errormessage += query[q] + "\\n" + m

                else :
                    pass
                    
        if ERROR_MESSAGE :
            if len(errormessage) > 0 :
                err = ErrorMessageDialog(self, errormessage)
                err.Show()
        


    def OnTreeSelChanged(self, event) :
        dbname = self.treectrl.GetItemText(event.GetItem())
        
        if dbname in self.databases :
            self.db.select_db(dbname)
            self.statusbar.SetStatusText(str(dbname), 1)
            
        event.Skip()

        
    def OnCloseWindow(self, event):
        try :
            self.DBClose()
        except :
            pass
             
        self.Destroy()


        
class MyApp(wx.App):
    """
        We must have it
    """
    def OnInit(self):
        frame                   = MainFrame(None, __scriptname__)
        frame.Show()
        self.SetTopWindow(frame)
        return True


        
def main():
    app                         = MyApp(False)
    app.MainLoop()


    
if __name__ == "__main__":
    main()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s