#! /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()
