hallo zusammen,
hier ist
update 3
alles läuft jetzt auf der idee von tabellars tablewrapper, und einem entsprechenden dbapi.
ich habe nur mysql getest.
zuerst das mysqlDAO, das mir auf grund der vielen codewiederholungen noch nicht gefällt, v.a. muss man gucken, ob das nun dem db-api 2.0 entspricht, bez. des execute usw.
änderungen diesbez. sollten allerdings eine kleinigkeit sein. glaube ich
die namen der attribute möchte ich gern bis zum abschluss der zusammenarbeit beibehalten.
Code: Alles auswählen
#!/usr/bin/env python
import MySQLdb
from _mysql_exceptions import *
class MySqlDAO:
def __init__(self):
"""..."""
def connect(self,host,user,db,passwd=''):
self.db=db
try:
self.conn=MySQLdb.connect(host=host,
user=user,
db=db,
passwd=passwd)
except OperationalError, msg:
print msg[1]
def getTableNameList(self):
cur=self.conn.cursor()
cur.execute("show tables from %s"%self.db)
result=cur.fetchall()
tableNameList=[]
for i in result:
tableNameList.append(i[0])
return tableNameList
def getTableColumnsList(self,db_table,option):
cur=self.conn.cursor()
cur.execute("show columns from %s"%db_table)
result=cur.fetchall()
tableColList=[]
if option==1:
for i in result:
if 'auto_increment' not in i:
tableColList.append(i[0])
else:
for i in result:
tableColList.append(i[0])
return tableColList
def getTableColumnsData(self,s_cols_names,db_table):
cursor=self.conn.cursor()
cursor.execute("select %s from %s"%(s_cols_names,db_table))
result=cursor.fetchall()
cursor.close()
return result
def getTableColumnsDataSort(self,s_cols_names,db_table,choice):
cursor=self.conn.cursor()
cursor.execute("select %s from %s order by %s"%(s_cols_names,db_table,choice))
result=cursor.fetchall()
cursor.close()
return result
def getTableColumnsDataSelect(self,db_table,target):
cursor=self.conn.cursor()
cursor.execute("select * from %s where %s"%(db_table,target))
result=cursor.fetchall()
cursor.close()
return result
def getTableColumnsDataSearch(self,s_cols_names,db_table,choice,entry):
cursor=self.conn.cursor()
cursor.execute("select %s from %s where %s= '%s'"%(s_cols_names,db_table,choice,entry))
result=cursor.fetchall()
cursor.close()
return result
def delTableColumnsData(self,db_table,db_table_cols,check):
cursor=self.conn.cursor()
cursor.execute("delete from %s where %s = %s"%(db_table,db_table_cols,check))
result=cursor.fetchall()
cursor.close()
return result
def insertTableColumnsData(self,db_table,s_cols_names,entries):
cursor=self.conn.cursor()
cursor.execute("insert into %s (%s) values (%s)"%(db_table,s_cols_names,entries))
result=cursor.fetchall()
cursor.close()
def updateTableColumnsData(self,db_table,result,db_table_col,check):
cursor=self.conn.cursor()
cursor.execute("update %s set %s where %s=%s"%(db_table,result,db_table_col,check))
result=cursor.fetchall()
cursor.close()
def close(self):
self.conn.close()
nun die neue fassung des tableWrapper moduls
Code: Alles auswählen
#!/usr/bin/env python
#Modul tableWrapper
class TableWrapper:
"""dbwrapper"""
def __init__(self):
"""init"""
def connect(self,DbType,Host,User,Db,Passwd):
if DbType=='PgSQL':
import pgsqlDAO
self.dbWrapper=pgsqlDAO.PgSqlDAO()
self.dbWrapper.connect(Host,Db,User)
if DbType=='MySQL':
import mysqlDAO
self.dbWrapper=mysqlDAO.MySqlDAO()
self.dbWrapper.connect(Host,User,Db,Passwd)
if DbType=='XML':
import xmlDAO
self.dbWrapper=xmlDAO.XmlDAO()
def getTableNameList(self):
return self.dbWrapper.getTableNameList()
def getTableColumnsList(self,db_table,option):
return self.dbWrapper.getTableColumnsList(db_table,option)
def getTableColumnsData(self,s_cols_names,db_table):
return self.dbWrapper.getTableColumnsData(s_cols_names,db_table)
def getTableColumnsDataSort(self,s_cols_names,db_table,choice):
return self.dbWrapper.getTableColumnsDataSort(s_cols_names,db_table,choice)
def getTableColumnsDataSelect(self,db_table,target):
return self.dbWrapper.getTableColumnsDataSelect(db_table,target)
def getTableColumnsDataSearch(self,s_cols_names,db_table,choice,entry):
return self.dbWrapper.getTableColumnsDataSearch(s_cols_names,db_table,choice,entry)
def delTableColumnsData(self,db_table,db_table_cols,check):
return self.dbWrapper.delTableColumnsData(db_table,db_table_cols,check)
def insertTableColumnsData(self,db_table,s_cols_names,entries):
self.dbWrapper.insertTableColumnsData(db_table,s_cols_names,entries)
def updateTableColumnsData(self,db_table,result,db_table_col,check):
self.dbWrapper.updateTableColumnsData(db_table,result,db_table_col,check)
def close(self):
self.dbWrapper.close()
nun der client,...übrigens es ist bereits die fassung, in der tabellen ausgewählt werden können. achtung, bis auf eine ausnahme, legen sich alle fenster noch auf die selbe position am bildschirm, auch das auswahlfenster mit dem hauptfenster, also einfach drunter gucken,
beim test mit einer mysql datenbank mit 2 tabellen, gab es keine probleme.
Code: Alles auswählen
#!/usr/bin/env python
from Tkinter import*
from ScrolledText import*
import tkMessageBox
import sys
import tableWrapper
class Fenster:
#Abstand der Elemente
a_x=5 #Abstand horizontal
a_y=5 #Abstand vertikal
#Die Buttons bekommen alle dieselbe Breite
b=20
#Farbe wird auch von la verwendet
f="#00ff00"
lt=""
def __init__(self):
raise NotImplementedError("Abstract class")
def prepare(self):
self.root.resizable(0,0)
self.root.protocol("WM_DELETE_WINDOW",self.mainexit)
la=Label(self.root,bg=self.f,text="///GARO_GUI")
la.grid(row=0,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
la_1=Label(self.root,bg=self.f,text=self.lt)
la_1.grid(row=1,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
button=Button(self.root,
text="Prog. Beenden",
width=10,command=self.ende)
button.grid(row=15,column=0,
columnspan=2,
padx=10,pady=10)
def ende(self):
antwort=tkMessageBox.askyesno\
("Warnung","Sicher beenden?")
if antwort==1:
sys.exit(0)
def mainexit(self):
self.root.withdraw()
class AuswahlFenster(Fenster):
lt="Auswahl"
def __init__(self,hf,root,tablewrapper,db_tables):
self.hf=hf
self.root=Toplevel(root)
self.root.title("Auswahlfenster")
self.prepare()
self.tablewrapper=tablewrapper
scb_v=Scrollbar(self.root, orient="vertical")
scb_h=Scrollbar(self.root, orient="horizontal")
self.li_1=Listbox(self.root,
width=20,height=8,
yscrollcommand=scb_v.set,
xscrollcommand=scb_h.set)
scb_v["command"]=self.li_1.yview
scb_h["command"]=self.li_1.xview
self.li_1.grid(row=2,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
scb_v.grid(row=2,column=2,
padx=self.a_x,pady=self.a_y)
scb_h.grid(row=3,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
b_1=Button(self.root,width=self.b,text="Auswahl",
command=lambda:
self.change_table(table=self.get_selected()))
b_1.grid(row=4,column=0,
padx=self.a_x,pady=self.a_y)
for table in db_tables:
self.li_1.insert("end",table)
def get_selected(self):
selected=self.li_1.get("active")
return selected
def change_table(self,table):
for inst in EingabeFenster.instancelist:
inst.root.destroy()
for inst in SuchFenster.instancelist:
inst.root.destroy()
hf.db_table=table
hf.cols_names=self.tablewrapper.getTableColumnsList(hf.db_table,option=1)
hf.li_1width=len(hf.cols_names)*12
hf.s_cols_names=", ".join(hf.cols_names)
for widget in hf.frame_1.winfo_children():
widget.destroy()
hf.set_frame_1()
for widget in hf.frame_3.winfo_children():
widget.destroy()
hf.set_frame_3()
class HauptFenster(Fenster):
lt="Ausgabe"
def __init__(self):
self.root=Tk()
self.root.wm_geometry('+20+20')
self.root.title("Hauptfenster")
self.prepare()
self.choice=StringVar()
self.tablewrapper=tableWrapper.TableWrapper()
self.tablewrapper.connect('MySQL','ein_host','ein_user','eine_db','ein_pw')
self.db_tables=self.tablewrapper.getTableNameList()
if len(self.db_tables)>1:
auswahl=AuswahlFenster(self,self.root,self.tablewrapper,self.db_tables)
print self.db_tables
self.db_table=self.db_tables[0]
self.cols_names=self.tablewrapper.getTableColumnsList(self.db_table,option=1)
self.s_cols_names=", ".join(self.cols_names)
self.li_1width=len(self.cols_names)*12
self.frame_1=Frame(self.root)
self.frame_1.grid(row=2,column=0,columnspan=2)
self.set_frame_1()
self.frame_2=Frame(self.root)
self.frame_2.grid(row=5,column=0,columnspan=2)
self.set_frame_2()
self.frame_3=Frame(self.root)
self.frame_3.grid(row=9,column=1)
self.set_frame_3()
def set_frame_1(self):
self.scb_v=Scrollbar(self.frame_1, orient="vertical")
self.scb_h=Scrollbar(self.frame_1, orient="horizontal")
self.li_1=Listbox(self.frame_1,
width=self.li_1width,height=8,
yscrollcommand=self.scb_v.set,
xscrollcommand=self.scb_h.set)
self.scb_v["command"]=self.li_1.yview
self.scb_h["command"]=self.li_1.xview
self.li_1.grid(row=3,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
self.scb_v.grid(row=3,column=2,
padx=self.a_x,pady=self.a_y)
self.scb_h.grid(row=4,column=0,
columnspan=2,
padx=self.a_x,pady=self.a_y)
def set_frame_2(self):
b_1=Button(self.frame_2,width=self.b,text="Alle Daten",
command=lambda:
self.list_data
(result=self.tablewrapper.getTableColumnsData(self.s_cols_names,self.db_table)))
b_1.grid(row=6,column=0,
padx=self.a_x,pady=self.a_y)
b_2=Button(self.frame_2,width=self.b,
text="Loeschen",command=self.ask)
b_2.grid(row=6,column=1,
padx=self.a_x,pady=self.a_y)
b_3=Button(self.frame_2,width=self.b,text="Sortieren",
command=lambda:
self.list_data(result=self.tablewrapper.getTableColumnsDataSort(self.s_cols_names,
self.db_table,
self.choice.get())))
b_3.grid(row=7,column=0,
padx=self.a_x,pady=self.a_y)
b_4=Button(self.frame_2,width=self.b,text="Eingabe",
command=lambda:
EingabeFenster
(self.frame_2,self.tablewrapper,
self.db_table,self.cols_names,
self.root.winfo_width()))
b_4.grid(row=7,column=1,
padx=self.a_x,pady=self.a_y)
b_5=Button(self.frame_2,width=self.b,text="Suchen",
command=lambda:
SuchFenster
(self,self.root,self.db_table))
b_5.grid(row=8,column=0,
padx=self.a_x,pady=self.a_y)
b_6=Button(self.frame_2,width=self.b,text="Bearbeiten",
command=lambda:
BearbeitungsFenster
(self.root,self.tablewrapper,
self.db_table,self.cols_names,
self.li_1.get("active"),
self.root.winfo_width()))
b_6.grid(row=8,column=1,padx=self.a_x,pady=self.a_y)
def set_frame_3(self):
for i in range(len(self.cols_names)):
self.rb=Radiobutton(self.frame_3,text=self.cols_names[i],
value=self.cols_names[i],
variable=self.choice)
self.rb.grid(row=i+9,column=1,
padx=self.a_x,pady=self.a_y)
if i==0:
self.rb.select()
self.list_data(result=self.tablewrapper.getTableColumnsData(self.s_cols_names,self.db_table))
def list_data(self,result):
self.li_1.delete(0,END)
result=[list(element) for element in result]
for element in result:
for i in range(len(element)):
element[i]=str(element[i])
result=[" ".join(element) for element in result]
for elements in result:
self.li_1.insert("end",elements)
def get_selected(self):
selected=self.li_1.get("active")
selected=selected.split()
selected=[str(selected[i]).replace(str(selected[i]),'"'+str(selected[i])+'"')
for i in range(len(selected))]
target=[self.cols_names[i]+"="+selected[i]
for i in range(len(selected))]
target=" and ".join(target)
check=self.tablewrapper.getTableColumnsDataSelect(self.db_table,target)
check=check[0][0]
db_table_cols=self.tablewrapper.getTableColumnsList(self.db_table,option=0)
self.tablewrapper.delTableColumnsData(self.db_table,db_table_cols[0],check)
def ask(self):
answer=tkMessageBox.askyesno("Warnung",
"Wollen Sie den gewaehlten Datensatz sicher loeschen ?")
if answer==1:
self.get_selected()
else:
pass
class EingabeFenster(Fenster):
lt="Eingabe/Bearbeitung"
instancelist=[]
def __init__(self,root,tablewrapper,db_table,cols_names,hfwidth):
self.root=Toplevel(root)
self.root.wm_geometry('%s+20'%('+'+str(hfwidth+40)))
self.root.title("Eingabefenster")
self.prepare()
self.tablewrapper=tablewrapper
self.db_table=db_table
self.cols_names=cols_names
self.s_cols_names=", ".join(self.cols_names)
self.entrylist=[]
for i in range(len(self.cols_names)):
la=Label(self.root,
text=self.cols_names[i])
la.grid(row=i+2,column=0)
entry=Entry(self.root)
entry.grid(row=i+2,column=1,
padx=self.a_x,pady=self.a_y)
self.entrylist.append(entry)
b_1=Button(self.root,width=self.b,
text="Eintragen",
command=lambda:
self.get_entries())
b_1.grid(row=10,column=1,
columnspan=2,
padx=self.a_x,pady=self.a_y)
EingabeFenster.instancelist.append(self)
def get_entries(self):
entries=[element.get() for element in self.entrylist]
for i in range(len(entries)):
test=entries[i].find(" ")
if test and test !=-1:
entries[i]=entries[i].replace(entries[i][test],"/")
for element in self.entrylist: element.delete(0,"end")
entries=[entries[i].replace(entries[i],'"'+entries[i]+'"')
for i in range(len(entries))]
s_entries=", ".join(entries)
self.tablewrapper.insertTableColumnsData(self.db_table,self.s_cols_names,s_entries)
class BearbeitungsFenster(Fenster,EingabeFenster):
lt="Bearbeitung"
def __init__(self,root,tablewrapper,db_table,cols_names,selected,hfwidth):
self.selected=selected
self.selected=self.selected.split()
self.cols_names=cols_names
EingabeFenster.__init__(self,root,tablewrapper,db_table,cols_names,hfwidth)
self.root.title("Bearbeitungsfenster")
for i in range(len(self.entrylist)):
self.entrylist[i].insert(0,self.selected[i])
b_1=Button(self.root,width=self.b,
text="Eintragen",
command=lambda:
self.get_entries())
b_1.grid(row=10,column=1,
columnspan=2,
padx=self.a_x,pady=self.a_y)
def get_entries(self):
entries=[element.get() for element in self.entrylist]
for i in range(len(entries)):
test=entries[i].find(" ")
if test and test !=-1:
entries[i]=entries[i].replace(entries[i][test],"/")
for element in self.entrylist: element.delete(0,"end")
entries=[entries[i].replace(entries[i],'"'+entries[i]+'"')
for i in range(len(entries))]
s_entries=", ".join(entries)
result=[self.cols_names[i]+"="+entries[i]
for i in range(len(entries))]
result=", ".join(result)
self.selected=list(self.selected)
self.selected=[str(self.selected[i]).replace(str(self.selected[i]),'"'+str(self.selected[i])+'"')
for i in range(len(self.selected))]
target=[self.cols_names[i]+"="+self.selected[i]
for i in range(len(self.selected))]
target=" and ".join(target)
check=self.tablewrapper.getTableColumnsDataSelect(self.db_table,target)
check=check[0][0]
db_table_cols=self.tablewrapper.getTableColumnsList(self.db_table,option=0)
self.tablewrapper.updateTableColumnsData(self.db_table,result,db_table_cols[0],check)
class SuchFenster(Fenster):
lt="Suche"
instancelist=[]
def __init__(self,hf,root,db_table):
self.root=Toplevel(root)
self.root.title("Suchfenster")
self.prepare()
choice=StringVar()
self.db_table=db_table
entry=Entry(self.root)
entry.grid(row=3,column=0)
for i in range(len(hf.cols_names)):
rb=Radiobutton(self.root,text=hf.cols_names[i],
value=hf.cols_names[i],
variable=choice)
rb.grid(row=i+2,column=1,
padx=self.a_x,pady=self.a_y)
if i==0:
rb.select()
b_1=Button(self.root,text="Suche",
command=lambda:
hf.list_data
(result=hf.tablewrapper.getTableColumnsDataSearch
(hf.s_cols_names,self.db_table,choice.get(),entry.get())))
b_1.grid(row=9,column=0)
SuchFenster.instancelist.append(self)
if __name__ == "__main__":
hf=HauptFenster()
mainloop()
liebe grüße
rolgal
Ein nächster schöner Schritt wäre es, im DB-Client per Dialog die entsprechende Datenbank und die Tabellen auszuwählen...
Das sollten wir doch schaffen, oder 
!!! Ich hab auf die Schnelle die Anpassungen gemacht und es hat dann mit PostgreSQL gleich funktioniert (Tabellenanzeige, Daten anzeigen...)