sqlite3 gui

NighterNet darkneter at gmail.com
Thu Aug 6 02:39:52 CEST 2009


This took a while to get a gui working. I am new at this coding.

This work with sqlite3 Python Version: 3.1.0. The older version
doesn't work.

#!/usr/local/bin/python
'''
    This is for basic database access to view the information.
Python version: 3.1.0

Features:
-view database.
-run script
-update row
-delete row
-insert row

To do list:
-delete table
-create table
-export database
-import database
-primary id need to able to insert to the table
-delete table row
-refresh data

Note:
-It doesn't remove the GUI
-It bit buggy.
-Python version: 3.0.1 -Doesn't support the gui

execute script without '"' ->  INSERT INTO member (user, password)
VALUES( "admin", "admin")
'''

from tkinter import *
from tkinter import ttk
from tkinter import tix
import sqlite3 as sqlite
import re

DATABASE = 'nameofdatabasefile.db'
title = "Sqlite3 Database";

db = sqlite.connect(DATABASE);
cur = db.cursor();

print("#==============================#")
print("# Python Version: 3.1.0        #")
print("# sqlite3                      #")
print("#==============================#")

class Application(Frame):
    #label_serverstatus = Label()
    table = []
    tablename = ''
    tablerowid = ''
    tablecolid = '0'
    tablerowname = ''
    tablevarinput = []
    tablevartext = []
    tableentrywidgetrow = []
    tablelabelwidgetrow = []

    def __init__(self, master=None):
        Frame.__init__(self, master)
        self.master.grid_columnconfigure(0, weight=1)
        self.master.grid_rowconfigure(0, weight=1)
        self.master.title(title)
        self.listtable()#Make a list table
        self.createWidgets()
        self.master.geometry("800x600")
        self.pack()
    def listtable(self):
        self.table = []
        cur.execute("select * from sqlite_master WHERE type='table'");
        for t in cur:
            #print (t[1])
            self.table.append(t[1])

    #Create Widgets
    def createWidgets(self):
        #Schema
        self.Schemaframe = ttk.Labelframe(self, text='Schema',
width=600, height=100)
        self.Schemaframe.grid(column=1, row=1, sticky=W)
        self.text_database = Label(self.Schemaframe)
        self.text_database["text"] = "Schema Database:" + DATABASE
        self.text_database.grid(column=1, row=1, sticky=W)

        #Table
        self.tableframe = ttk.Labelframe(self, text='Schema Table',
width=600, height=100)
        self.tableframe.grid(column=1, row=2, sticky=W)
        self.text_table = Label(self.tableframe)
        self.text_table["text"] = "table:"
        self.text_table.grid(column=1, row=1, sticky=W)

        self.combobox = ttk.Combobox(self.tableframe)
        self.combobox.grid(column=2, row=1, sticky=W)
        self.combobox["values"] = self.table
        self.combobox.bind('<<ComboboxSelected>>', self.table_click)
        self.combobox.current(1)

        self.button_deletetable = Button(self.tableframe)
        self.button_deletetable["text"] = "Delete Table"
        self.button_deletetable.grid(column=3, row=1, sticky=W)

        self.button_createtable = Button(self.tableframe)
        self.button_createtable["text"] = "Create Table"
        self.button_createtable.grid(column=4, row=1, sticky=W)

        #Table
        self.scriptframe = ttk.Labelframe(self, text='Schema Script',
width=600, height=100)
        self.scriptframe.grid(column=1, row=3, sticky=W)

        self.button_script = Button(self.scriptframe)
        self.button_script["text"] = "Exeute script"
        self.button_script["command"] = self.executescript
        self.button_script.grid(column=1, row=1, sticky=W)

        self.button_commit = Button(self.scriptframe)
        self.button_commit["text"] = "Commit"
        self.button_commit["command"] = self.commit_save
        self.button_commit.grid(column=2, row=1, sticky=W)

        #TEXT BOX
        self.textbox = Text(self.scriptframe, width=40, height=10)
        self.textbox.grid(column=1, row=2, sticky=W,columnspan=6)

        #TABLE FRAME LIST
        self.tablerowframe = ttk.Labelframe(self, text='Table Rows',
width=600, height=100)
        self.tablerowframe.grid(column=2, row=1, sticky=W,rowspan=3)

        #TABLE ROW LIST
        self.listbox = Listbox(self.tablerowframe, exportselection=0,
background="white")
        self.listbox.bind("<<ListboxSelect>>",self.listselect_click)
        self.listbox.grid(column=1, row=1, sticky=W)

        #vbar
        self.vbar = Scrollbar(self.tablerowframe, name="vbar")
        self.vbar.grid(column=1, row=1, sticky=E)
        self.vbar.configure(orient=VERTICAL,
command=self.listbox.yview)
        self.listbox.configure(yscrollcommand=self.vbar.set)

        self.button_createrow = Button(self.tablerowframe)
        self.button_createrow["text"] = "Create Table Row"
        self.button_createrow["command"] = self.commit_save
        self.button_createrow.grid(column=1, row=2, sticky=W)

        #TABLE FRAME ROW EDIT
        self.tablerowframe = ttk.Labelframe(self, text='Table Rows
EDIT', width=256, height=480)
        self.tablerowframe.grid(column=1, row=4, sticky=W)

    #SELECT TABLE FROM COMBOBOX
    def table_click(self,event):
        #print("click")
        self.tablename = str(self.combobox.selection_get())
        print(self.tablename)
        self.tableselected(self.tablename);
        return "break"

    #SHOW TABLE ROWS IN THE LIST BOX
    def tableselected(self,name):
        #print("TABLE SELECT ->" + str(name))
        cur.execute("select * from " + name);
        self.listbox.delete(0, "end")
        for t in cur:
            #print (t)
            #print (t[1])#table
            #spreadsheet.append(t[1])
            self.listbox.insert("end", str(t[0]))
        #self.listbox["listvariable"] = spreadsheet

    #CLICK TABLE ROW SELECTED ID OR ROW NAME
    def listselect_click(self,event):
        rowid = self.listbox.curselection()
        rowid = self.listbox.index(rowid)
        #print(self.listbox.get(rowid))
        self.tablerowid = rowid
        self.tablerowname = self.listbox.get(rowid)
        self.selecttablerow()
        return "break"

    #TABLE ROW INFORMATION
    def selecttablerow(self):
        #print ("TABLE ROW ->")
        self.tablevartext = []
        self.tablevarinput = []

        cur.execute("select * from sqlite_master WHERE type='table'");
        for t in cur:
            if self.tablename == t[1]:
                #print (t)
                tablestr = t[4].replace("CREATE TABLE "
+self.tablename + " ",'')
                tablestr = tablestr.replace("(","")
                tablestr = tablestr.replace(")","")
                comumndata = tablestr.split(',')

                for col in comumndata:
                    col = col.strip()
                    m = re.match(r"(^(\w+))",col)
                    #print (m.group(0))#column name of the table
current selected.
                    self.tablevartext.append(m.group(0))
                break

        query = str('SELECT * FROM ' + self.tablename + " WHERE id=\'"
+ self.tablerowname + "\'")
        #print(query)
        cur.execute(query);
        #table row out put
        for t in cur:
            #print (t)
            for vardata in t:
                #print(vardata)
                self.tablevarinput.append(vardata)
        self.deletetablerowinput
        self.createtablerowinput()

    def createtablerowinput(self):
        self.deletetablerowinput()
        rowcount = 0
        #self.button_destroy = Button(self.tablerowframe)
        #self.button_destroy["text"] = "delete rows"
        #self.button_destroy["command"] = self.deletetablerowinput
        #self.button_destroy.grid(column=1, row=1, sticky=W)

        self.button_deleterowtable = Button(self.tablerowframe)
        self.button_deleterowtable["text"] = "Delete Table Row"
        self.button_deleterowtable["command"] = self.deletetablerow
        self.button_deleterowtable.grid(column=1, row=1, sticky=W)

        self.button_updatetablerow = Button(self.tablerowframe)
        self.button_updatetablerow["text"] = "Update Table Row"
        self.button_updatetablerow["command"] = self.updatetablerow
        self.button_updatetablerow.grid(column=2, row=1, sticky=W)

        self.button_inserttablerow = Button(self.tablerowframe)
        self.button_inserttablerow["text"] = "Insert Table Row"
        self.button_inserttablerow["command"] = self.inserttablerow
        self.button_inserttablerow.grid(column=3, row=1, sticky=W)

        for columnname in self.tablevartext:
            #print(columnname)
            self.entry_tablevarrow = Entry(self.tablerowframe,
width=30, textvariable=columnname)
            self.entry_tablevarrow.delete(0,"end")
            self.entry_tablevarrow.insert(0,str(self.tablevarinput
[rowcount]))
            self.entry_tablevarrow.grid(column=2, row=rowcount+2,
sticky=W)
            self.tableentrywidgetrow.append(self.entry_tablevarrow)

            self.label_var = Label(self.tablerowframe, width=16)
            self.label_var['text'] = columnname
            self.label_var.grid(column=1, row=rowcount+2, sticky=W)
            self.tablelabelwidgetrow.append(self.label_var)
            #print(rowcount)
            rowcount += 1
    def updatetablerow(self):
        count = 0;
        #mysql_query("UPDATE  $db_table SET imageid='{$hashname}'
WHERE idhash='{$IDIMAGE}'")
        query = "UPDATE " + self.tablename
        set = " SET "
        where = "WHERE"
        for x in self.tableentrywidgetrow:
            if count == 0:
                where = where + " " + x["textvariable"] + "=\'" + x.get
() + "\' "
            else:
                if count == len(self.tableentrywidgetrow)-1:
                    set = set + "" + x["textvariable"] + "=\'" + x.get
() + "\' "
                else:
                    set = set + "" + x["textvariable"] + "=\'" + x.get
() + "\', "

            print ( x["textvariable"] + "->" +  x.get())
            count += 1
        print (query + set + where)
        querystring = query + set + where
        self.applydata(querystring)
        #print (len(self.tableentrywidgetrow))
    def deletetablerow(self):
        count = 0;
        query = "DELETE FROM " + self.tablename + " WHERE "
        delete = ""
        for x in self.tableentrywidgetrow:
            if count == len(self.tableentrywidgetrow)-1:
                delete = delete + "" + x["textvariable"] + "=\'" +
x.get() + "\' "
            else:
                delete = delete + "" + x["textvariable"] + "=\'" +
x.get() + "\' AND "
            count += 1
        print (query + delete)
        querystring = query + delete
        self.applydata(querystring)
        #print (len(self.tableentrywidgetrow))
    def inserttablerow(self):
        #cur.execute('INSERT INTO member (user, password) VALUES
( "admin", "admin")')
        count = 0;
        query = "INSERT INTO " + self.tablename + "("
        values = ""
        insert = ""
        #be sure it not the primary key else it will give error
        for x in self.tableentrywidgetrow:
            if count == len(self.tableentrywidgetrow)-1:
                values = values + x["textvariable"] + ''
                insert = insert + '\"' + x.get() + '\"'
            else:
                if count == 0:
                    print ("PRIMARY KEY")
                else:
                    values = values + x["textvariable"] + ','
                    insert = insert + '\"' + x.get() + '\",'

            count += 1

        querystring = query + values + ") VALUES(" + insert + ")"
        print (querystring)
        self.applydata(querystring)
        #print (len(self.tableentrywidgetrow))

    def deletetablerowinput(self):
        for x in self.tableentrywidgetrow:
            x.destroy()
        self.tableentrywidgetrow = []
        for y in self.tablelabelwidgetrow:
            x.destroy()
        self.tablelabelwidgetrow = []

    #SCRIPTS ++++++
    def executescript(self):
        print("Execute Script...")
        scripttext = self.textbox.get('1.0', 'end')
        self.applydata(scripttext)
        print(scripttext)
    #COMMIT +++++++
    def commit_save(self):
        print("Sqlite3 Commit")
        db.commit()
    def applydata(self,querystr):
        print("Sqlite3 Commit")
        db = sqlite.connect(DATABASE);
        cur = db.cursor()
        cur.execute(querystr)
        db.commit()

root = Tk()
app = Application(master=root)
app.mainloop()
root.destroy()

Not sure what you guys think about this. Still working on it. I can't
hard code this well. Still learning it. >.<



More information about the Python-list mailing list