Use Gtk.TreeView to browse MySql database structure from database to tables and views and to columns
Python, 130 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | #! /usr/bin/python
###########################################################
#
# Use Gtk.TreeView to browse MySql databases
#
###########################################################
from gi.repository import Gtk
import MySQLdb
import os
class MyWindow(Gtk.Window):
def __init__(self):
Gtk.Window.__init__(self, title='My Window Title')
self.connect('delete-event', Gtk.main_quit)
self.connection = None
self.connect_to_database()
store = Gtk.TreeStore(str, str, str, str)
self.populate_store(store)
self.treeview = Gtk.TreeView(model=store)
renderer = Gtk.CellRendererText()
column_catalog = Gtk.TreeViewColumn('Name', renderer, text=0)
self.treeview.append_column(column_catalog)
self.treeview.connect('test-expand-row', self.add_child_items, store)
scrolled_window = Gtk.ScrolledWindow()
scrolled_window.set_policy(
Gtk.PolicyType.NEVER, Gtk.PolicyType.AUTOMATIC)
scrolled_window.add(self.treeview)
scrolled_window.set_min_content_height(200)
self.add(scrolled_window)
self.show_all()
def connect_to_database(self):
try:
self.connection = MySQLdb.connect('localhost', 'anon', 'pass')
except MySQLdb.Error, e:
print e.args[1]
# Add databases to TreeStore
def populate_store(self, store):
root_iter = store.append(None, ['localhost', 'server', '', ''])
try:
cursor = self.connection.cursor()
cursor.execute("Select * From `INFORMATION_SCHEMA`.`SCHEMATA`")
rows = cursor.fetchall()
for row in rows:
db_iter = store.append(root_iter, [row[1], 'database', '', ''])
store.append(db_iter, ['dummy', '', '', ''])
except MySQLdb.Error, e:
store.append(root_iter, [e.args[1], '', '', ''])
def add_child_items(self, treeview, iter, path, store):
if iter == None:
return
if store.iter_n_children(iter) == 1:
node_iter = store.iter_nth_child(iter, 0)
if store.get(node_iter, 0)[0] == 'dummy':
store.remove(node_iter)
if store.get(iter, 1)[0] == 'database':
cursor = self.connection.cursor()
db_name = store.get(iter, 0)[0]
tables_iter = store.append(iter, ['tables', '', '', ''])
sql = "Select `TABLE_NAME` From `INFORMATION_SCHEMA`.`TABLES`" \
"Where `TABLE_SCHEMA` = '{0}' " \
"And `TABLE_TYPE` = 'BASE TABLE'".format(db_name)
print sql
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
table_iter = store.append(tables_iter, [row[0], 'table', db_name, ''])
store.append(table_iter, ['dummy', '', '', ''])
views_iter = store.append(iter, ['views', '', '', ''])
sql = "Select `TABLE_NAME` From `INFORMATION_SCHEMA`.`TABLES`" \
"Where `TABLE_SCHEMA` = '{0}'" \
"And `TABLE_TYPE` Like '%VIEW%'".format(db_name)
print sql
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
view_iter = store.append(views_iter, [row[0], 'table', db_name, ''])
store.append(view_iter, ['dummy', '', '', ''])
if store.get(iter, 1)[0] == 'table':
columns_iter = store.append(iter, ['columns', '', '', ''])
db_name = store.get(iter, 2)[0]
table_name = store.get(iter, 0)[0]
sql = "Select `COLUMN_NAME` From `INFORMATION_SCHEMA`.`COLUMNS` " \
"Where `TABLE_SCHEMA` = '{0}' " \
"And `TABLE_NAME` = '{1}'" \
"Order By `ORDINAL_POSITION`".format(db_name, table_name)
cursor = self.connection.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
store.append(columns_iter, [row[0], 'column', '', ''])
win = MyWindow()
Gtk.main()
|
