{"id":125,"date":"2020-02-26T13:13:12","date_gmt":"2020-02-26T12:13:12","guid":{"rendered":"http:\/\/www.w2hp.net\/wp\/?page_id=125"},"modified":"2020-02-26T13:13:12","modified_gmt":"2020-02-26T12:13:12","slug":"gtk-treeview-to-browse-mysql","status":"publish","type":"page","link":"https:\/\/wp.w2hp.net\/?page_id=125","title":{"rendered":"Gtk.TreeView to browse MySql"},"content":{"rendered":"<pre>#! \/usr\/bin\/python3\r\n\r\n###########################################################\r\n#\r\n# Use Gtk.TreeView to browse MySql databases\r\n#\r\n###########################################################\r\n#  GRANT ALL ON *.* TO 'python'@'localhost' identified by \"egowaret\";\r\n\r\nimport gi\r\ngi.require_version('Gtk', '3.0')\r\nfrom gi.repository import Gtk\r\n \r\nimport MySQLdb\r\nimport os\r\n\r\nclass MyWindow(Gtk.Window):\r\n    \r\n    def __init__(self):\r\n\r\n        Gtk.Window.__init__(self, title='My Window Title')\r\n        self.connect('delete-event', Gtk.main_quit)        \r\n        \r\n        self.connection = None\r\n        self.connect_to_database()\r\n        store = Gtk.TreeStore(str, str, str, str)\r\n        self.populate_store(store)\r\n        \r\n        self.treeview = Gtk.TreeView(model=store)\r\n\r\n        renderer = Gtk.CellRendererText()\r\n        column_catalog = Gtk.TreeViewColumn('Name', renderer, text=0)\r\n        self.treeview.append_column(column_catalog)\r\n        \r\n        self.treeview.connect('test-expand-row', self.add_child_items, store)\r\n        \r\n        scrolled_window = Gtk.ScrolledWindow()\r\n        scrolled_window.set_policy(\r\n            Gtk.PolicyType.NEVER, Gtk.PolicyType.AUTOMATIC)\r\n        scrolled_window.add(self.treeview)\r\n        scrolled_window.set_min_content_height(200)\r\n\r\n        self.add(scrolled_window)\r\n        self.show_all()\r\n    \r\n    def connect_to_database(self):\r\n        #try:\r\n        self.connection = MySQLdb.connect(host=\"localhost\", user=\"python\", passwd=\"egowaret\")\r\n        #except MySQLdb.Error, e:\r\n        #    print e.args[1]\r\n    \r\n    # Add databases to TreeStore\r\n    def populate_store(self, store):\r\n        \r\n        root_iter = store.append(None, ['localhost', 'server', '', ''])\r\n        \r\n        #try:\r\n        cursor = self.connection.cursor()\r\n        cursor.execute(\"Select * From `INFORMATION_SCHEMA`.`SCHEMATA`\")\r\n        rows = cursor.fetchall()\r\n        \r\n        for row in rows:\r\n            db_iter = store.append(root_iter, [row[1], 'database', '', ''])\r\n            store.append(db_iter, ['dummy', '', '', ''])\r\n        # except MySQLdb.Error, e:\r\n        # store.append(root_iter, [e.args[1], '', '', ''])    \r\n    \r\n    def add_child_items(self, treeview, iter, path, store):\r\n        \r\n        if iter == None:\r\n            return\r\n        \r\n        if store.iter_n_children(iter) == 1:\r\n            \r\n            node_iter = store.iter_nth_child(iter, 0)\r\n\r\n            if store.get(node_iter, 0)[0] == 'dummy':\r\n                store.remove(node_iter)\r\n\r\n                if store.get(iter, 1)[0] == 'database':\r\n                \r\n                    cursor = self.connection.cursor()\r\n                    db_name = store.get(iter, 0)[0]\r\n                    \r\n                    tables_iter = store.append(iter, ['tables', '', '', ''])\r\n                    \r\n                    sql = \"Select `TABLE_NAME` From `INFORMATION_SCHEMA`.`TABLES`\" \\\r\n                    \"Where `TABLE_SCHEMA` = '{0}' \" \\\r\n                    \"And `TABLE_TYPE` = 'BASE TABLE'\".format(db_name)\r\n                    print (sql)\r\n                    cursor.execute(sql)\r\n                    rows = cursor.fetchall()\r\n                    \r\n                    for row in rows:\r\n                        table_iter = store.append(tables_iter, [row[0], 'table', db_name, ''])\r\n                        store.append(table_iter, ['dummy', '', '', ''])\r\n                        \r\n                    views_iter = store.append(iter, ['views', '', '', ''])\r\n                    \r\n                    sql = \"Select `TABLE_NAME` From `INFORMATION_SCHEMA`.`TABLES`\" \\\r\n                    \"Where `TABLE_SCHEMA` = '{0}'\" \\\r\n                    \"And `TABLE_TYPE` Like '%VIEW%'\".format(db_name)\r\n                    print (sql)\r\n                    cursor.execute(sql)\r\n                    rows = cursor.fetchall()\r\n                    \r\n                    for row in rows:\r\n                        view_iter = store.append(views_iter, [row[0], 'table', db_name, ''])\r\n                        store.append(view_iter, ['dummy', '', '', ''])\r\n                        \r\n                if store.get(iter, 1)[0] == 'table':\r\n                    \r\n                    columns_iter = store.append(iter, ['columns', '', '', ''])\r\n                    \r\n                    db_name = store.get(iter, 2)[0]\r\n                    table_name = store.get(iter, 0)[0]\r\n                    \r\n                    sql = \"Select `COLUMN_NAME` From `INFORMATION_SCHEMA`.`COLUMNS` \" \\\r\n                    \"Where `TABLE_SCHEMA` = '{0}' \" \\\r\n                    \"And `TABLE_NAME` = '{1}'\" \\\r\n                    \"Order By `ORDINAL_POSITION`\".format(db_name, table_name)\r\n                    \r\n                    cursor = self.connection.cursor()\r\n                    cursor.execute(sql)\r\n                       \r\n                    rows = cursor.fetchall()\r\n                    \r\n                    for row in rows:\r\n                        store.append(columns_iter, [row[0], 'column', '', ''])\r\n                    \r\n            \r\n            \r\nwin = MyWindow()\r\nGtk.main()\r\n<\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"<p>#! \/usr\/bin\/python3 ########################################################### # # Use Gtk.TreeView to browse MySql databases # ########################################################### # GRANT ALL ON *.* TO &#8218;python&#8217;@&#8217;localhost&#8216; identified by &#8222;egowaret&#8220;; import gi gi.require_version(&#8218;Gtk&#8216;, &#8218;3.0&#8216;) from gi.repository import Gtk import MySQLdb import os class MyWindow(Gtk.Window): def __init__(self): Gtk.Window.__init__(self, title=&#8217;My Window Title&#8216;) self.connect(&#8218;delete-event&#8216;, Gtk.main_quit) self.connection = None self.connect_to_database() store = Gtk.TreeStore(str, str, str, str) &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/wp.w2hp.net\/?page_id=125\" class=\"more-link\"><span class=\"screen-reader-text\">\u201eGtk.TreeView to browse MySql\u201c<\/span> weiterlesen<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"ngg_post_thumbnail":0,"footnotes":""},"class_list":["post-125","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/pages\/125","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=125"}],"version-history":[{"count":1,"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/pages\/125\/revisions"}],"predecessor-version":[{"id":126,"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=\/wp\/v2\/pages\/125\/revisions\/126"}],"wp:attachment":[{"href":"https:\/\/wp.w2hp.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}