wiz_odbc.py :  » Business-Application » hylaPEx » hylapex » library » wizard » Python Open Source

Home
Python Open Source
1.3.1.2 Python
2.Ajax
3.Aspect Oriented
4.Blog
5.Build
6.Business Application
7.Chart Report
8.Content Management Systems
9.Cryptographic
10.Database
11.Development
12.Editor
13.Email
14.ERP
15.Game 2D 3D
16.GIS
17.GUI
18.IDE
19.Installer
20.IRC
21.Issue Tracker
22.Language Interface
23.Log
24.Math
25.Media Sound Audio
26.Mobile
27.Network
28.Parser
29.PDF
30.Project Management
31.RSS
32.Search
33.Security
34.Template Engines
35.Test
36.UML
37.USB Serial
38.Web Frameworks
39.Web Server
40.Web Services
41.Web Unit
42.Wiki
43.Windows
44.XML
Python Open Source » Business Application » hylaPEx 
hylaPEx » hylapex » library » wizard » wiz_odbc.py
#Boa:FramePanel:pnl
# -*- coding: utf-8 -*-
#
# hylaPEx, an hylafax client written in python
#
# www.unipex.it/hylapex/
#
# License:
#         GNU General Public License (GPL)
#         For more info see LICENSE.txt and LICENSE-GPL.txt
#
# Copyright (C) 2004-2006  Unipex s.r.l.,  All Rights Reserved.
# Via Vittorio Veneto 83/A
# 33050 Gonars (UD) - Italy
# phone 0039 0432 931511 - fax 0039 0432 931378
# www.unipex.it - michele.petrazzo@unipex.it

from library import utils

import wx, wx.grid
import db_lib, os, string
import wx_util
from twisted.internet import reactor

[wxID_PNL, wxID_PNLBR_SAVE, wxID_PNLBT_CONNECT, wxID_PNLBT_MAN_QUERY,
 wxID_PNLCHK_MAN_QUERY, wxID_PNLCHO_DSN, wxID_PNLCHO_TABLE, wxID_PNLGR_FILED, 
 wxID_PNLLBL_DSN, wxID_PNLLBL_FAX_BOOK_NAME, wxID_PNLLBL_FILEDS, 
 wxID_PNLLBL_MAN_QUERY, wxID_PNLLBL_PASS, wxID_PNLLBL_TABLE, wxID_PNLLBL_USER, 
 wxID_PNLTXT_FB_NAME, wxID_PNLTXT_MAN_QUERY, wxID_PNLTXT_PASS, 
 wxID_PNLTXT_USER, 
] = [wx.NewId() for _init_ctrls in range(19)]


class editQuery(wx.MiniFrame):
    def __init__(self, *args, **kargs):
        super(editQuery, self).__init__(*args, **kargs)
        self.SetBackgroundColour(wx.Colour(245, 245, 245))
        sz   = wx.BoxSizer(wx.VERTICAL)
        szBt = wx.BoxSizer(wx.HORIZONTAL)
        self.txt = wx.TextCtrl(self, style=wx.TE_MULTILINE)
        btSave = wx.Button(self, label="Save and close")
        btSave.Bind(wx.EVT_BUTTON, self.onBtSave)
        btQuery = wx.Button(self, label="Test Query")
        btQuery.Bind(wx.EVT_BUTTON, self.onBtQuery)
        
        szBt.Add(btQuery, 0, wx.ALL | wx.ALIGN_CENTER_HORIZONTAL, 5)
        szBt.Add(btSave, 0, wx.ALL | wx.ALIGN_CENTER_HORIZONTAL, 5)
        
        sz.Add(self.txt, 1, wx.ALL | wx.EXPAND, 5)
        sz.Add(szBt, 0, wx.EXPAND)
        sz.SetMinSize(wx.Size(200,100))
        
        self.SetSizerAndFit(sz)
        self.Layout()
        self.CenterOnParent()
        
        self.Show()
        
    def setValues(self, functReturn, text=''):
        """
        """
        self.txt.SetValue(text)
        self.functReturn = functReturn
    
    def onBtQuery(self, event):
        """
        """
        self.functReturn(self.txt.GetValue())
    
    def onBtSave(self, event):
        """
        """
        self.functReturn(self.txt.GetValue())
        self.Destroy()
        
class pnl(wx.Panel):
    def _init_ctrls(self, prnt):
        # generated method, don't edit
        wx.Panel.__init__(self, id=wxID_PNL, name='pnl_1', parent=prnt,
              pos=wx.Point(10, 55), size=wx.Size(468, 277),
              style=wx.TAB_TRAVERSAL)
        self.SetClientSize(wx.Size(460, 250))
        self.SetToolTipString('')
        self.SetBackgroundColour(wx.Colour(245, 245, 245))

        self.gr_filed = wx.grid.Grid(id=wxID_PNLGR_FILED, name='gr_filed',
              parent=self, pos=wx.Point(248, 56), size=wx.Size(192, 168),
              style=0)
        self.gr_filed.SetToolTipString('')
        self.gr_filed.SetColLabelSize(20)
        self.gr_filed.SetColMinimalAcceptableWidth(25)
        self.gr_filed.SetRowLabelSize(24)

        self.br_save = wx.Button(id=wxID_PNLBR_SAVE, label='Save',
              name='br_save', parent=self, pos=wx.Point(160, 208),
              size=wx.Size(59, 23), style=0)
        self.br_save.SetToolTipString('')
        self.br_save.Bind(wx.EVT_BUTTON, self.OnBr_saveButton,
              id=wxID_PNLBR_SAVE)

        self.lbl_fileds = wx.StaticText(id=wxID_PNLLBL_FILEDS,
              label='List of fields - select with 1 or y', name='lbl_fileds',
              parent=self, pos=wx.Point(248, 40), size=wx.Size(168, 13),
              style=0)

        self.txt_fb_name = wx.TextCtrl(id=wxID_PNLTXT_FB_NAME,
              name='txt_fb_name', parent=self, pos=wx.Point(104, 16),
              size=wx.Size(144, 21), style=0, value='')
        self.txt_fb_name.SetToolTipString('')

        self.lbl_fax_book_name = wx.StaticText(id=wxID_PNLLBL_FAX_BOOK_NAME,
              label='Faxbook name', name='lbl_fax_book_name', parent=self,
              pos=wx.Point(8, 16), size=wx.Size(70, 13), style=0)
        self.lbl_fax_book_name.SetToolTipString('')

        self.txt_user = wx.TextCtrl(id=wxID_PNLTXT_USER, name='txt_user',
              parent=self, pos=wx.Point(72, 88), size=wx.Size(160, 21), style=0,
              value='')
        self.txt_user.SetToolTipString('')

        self.txt_pass = wx.TextCtrl(id=wxID_PNLTXT_PASS, name='txt_pass',
              parent=self, pos=wx.Point(72, 112), size=wx.Size(160, 21),
              style=wx.TE_PASSWORD, value='')
        self.txt_pass.SetToolTipString('')

        self.lbl_dsn = wx.StaticText(id=wxID_PNLLBL_DSN, label='Dsn',
              name='lbl_dsn', parent=self, pos=wx.Point(8, 64), size=wx.Size(19,
              13), style=0)

        self.lbl_pass = wx.StaticText(id=wxID_PNLLBL_PASS, label='Password',
              name='lbl_pass', parent=self, pos=wx.Point(8, 112),
              size=wx.Size(46, 13), style=0)
        self.lbl_pass.SetToolTipString('')

        self.lbl_user = wx.StaticText(id=wxID_PNLLBL_USER, label='Username',
              name='lbl_user', parent=self, pos=wx.Point(8, 88),
              size=wx.Size(48, 13), style=0)
        self.lbl_user.SetToolTipString('')

        self.bt_connect = wx.Button(id=wxID_PNLBT_CONNECT, label='Connect',
              name='bt_connect', parent=self, pos=wx.Point(72, 208),
              size=wx.Size(75, 23), style=0)
        self.bt_connect.SetToolTipString('')
        self.bt_connect.Bind(wx.EVT_BUTTON, self.OnBt_connectButton,
              id=wxID_PNLBT_CONNECT)

        self.lbl_table = wx.StaticText(id=wxID_PNLLBL_TABLE, label='Table',
              name='lbl_table', parent=self, pos=wx.Point(8, 136),
              size=wx.Size(27, 13), style=0)
        self.lbl_table.SetToolTipString('')

        self.cho_dsn = wx.Choice(choices=[], id=wxID_PNLCHO_DSN, name='cho_dsn',
              parent=self, pos=wx.Point(72, 64), size=wx.Size(160, 21),
              style=0)
        self.cho_dsn.SetToolTipString('')
        self.cho_dsn.Bind(wx.EVT_CHOICE, self.OnCho_dsnChoice,
              id=wxID_PNLCHO_DSN)

        self.cho_table = wx.Choice(choices=[], id=wxID_PNLCHO_TABLE,
              name='cho_table', parent=self, pos=wx.Point(72, 136),
              size=wx.Size(136, 21), style=0)
        self.cho_table.SetToolTipString('')
        self.cho_table.Bind(wx.EVT_CHOICE, self.OnCho_tableChoice,
              id=wxID_PNLCHO_TABLE)

        self.bt_man_query = wx.Button(id=wxID_PNLBT_MAN_QUERY, label='Go',
              name='bt_man_query', parent=self, pos=wx.Point(216, 160),
              size=wx.Size(24, 21), style=0)
        self.bt_man_query.SetToolTipString('')
        self.bt_man_query.Show(False)
        self.bt_man_query.Bind(wx.EVT_BUTTON, self.OnBt_man_queryButton,
              id=wxID_PNLBT_MAN_QUERY)

        self.chk_man_query = wx.CheckBox(id=wxID_PNLCHK_MAN_QUERY,
              label='Use manual query', name='chk_man_query', parent=self,
              pos=wx.Point(272, 16), size=wx.Size(160, 13), style=0)
        self.chk_man_query.SetValue(False)
        self.chk_man_query.SetToolTipString('')
        self.chk_man_query.Bind(wx.EVT_CHECKBOX, self.OnChk_man_queryCheckbox,
              id=wxID_PNLCHK_MAN_QUERY)

        self.txt_man_query = wx.TextCtrl(id=wxID_PNLTXT_MAN_QUERY,
              name='txt_man_query', parent=self, pos=wx.Point(72, 160),
              size=wx.Size(136, 21), style=0, value='')
        self.txt_man_query.SetToolTipString('')
        self.txt_man_query.Show(False)

        self.lbl_man_query = wx.StaticText(id=wxID_PNLLBL_MAN_QUERY,
              label='Query', name='lbl_man_query', parent=self, pos=wx.Point(8,
              160), size=wx.Size(30, 13), style=0)
        self.lbl_man_query.Show(False)

    def __init__(self, parent, paths, db_inst, modify):
        self._init_ctrls(parent)
        self.parent = parent
        self.db_inst = db_inst
        self.paths = paths
        self.gr_filed.CreateGrid(0, 3)
        self.gr_filed.SetColSize(0, 65)
        self.gr_filed.SetColSize(1, 45)
        self.gr_filed.SetColSize(2, 60)
        #self.gr_filed.EnableEditing(True)
        self.init_odbc()
        #For i18n
        ren = wx_util.rename_ctrls(self, 'wiz_odbc', self.paths.my_path,
            lang = self.paths.language)
        ren.rename()
        ren.SetGridFieldNames('gr_filed')
        self.ren = ren
        
        self.modify = modify[0]
        self.name = modify[1]
        self.useTableLikeQuery = 0
        if self.modify:
            self.txt_fb_name.Enable(False)
            self.txt_fb_name.SetValue(self.name)
            self.__setPar()
        
        self.gr_filed.EnableEditing(True)
        #
        self._lstGoodChar = ('Y', 'y', '1', 's', 'S')

    def init_odbc(self):
        self.db = db_lib.Odbc(debug=utils.do_debug)
        
        #Get dsn
        ret, dsn = self.db.enum_dsn()
        if ret:
            wx.MessageBox(dsn, "Error", wx.ICON_ERROR)
            return
        
        self.cho_dsn.AppendItems( [d[0] for d in dsn])

    def OnBr_saveButton(self, event):
        """ Save the value
        """
        txt_fb_name = self.txt_fb_name.GetValue().strip()
        
        #Name control
        if not txt_fb_name:
            wx.MessageBox(self.ren.get("insert_pb_name", 1), 
                self.ren.get("error", 1), wx.ICON_ERROR)
            return
        
        #Number of fields control
        field_lst4db = [self.gr_filed.GetCellValue(row,0)
                        for row in range(self.gr_filed.GetNumberRows()) 
                        if self.gr_filed.GetCellValue(row, 1) in self._lstGoodChar ]
        
        if len(field_lst4db) < 2:
            dlg = wx.MessageDialog(self, "Devi selzionare almeno due campi\n" +  \
                "segna la colonna field con Y o y o 1" ,"Errore", wx.ICON_ERROR)
            dlg.ShowModal()
            dlg.Destroy()
            return
        
        if not self.modify:
            status, ret_order = self.db_inst.queryReturn('SELECT fb_order FROM fb ORDER BY fb_order')
            if ret_order:   last_number = ret_order[len(ret_order) -1][0]
            else:           last_number = 0
        
        val_other = ['dsn=%s' % self.dsn, 'pass=%s' % self.passw, 
                     'user=%s'% self.user, "use_table_like_query=%s" % self.useTableLikeQuery]
        
        if not self.modify:
            q ='INSERT INTO fb (name, fb_type, fb_order, fb_fields, fb_db_name,'+ \
                'fb_other) VALUES ("%s","odbc", %s, "%s", "%s", "%s")' % \
                (txt_fb_name, last_number +1 , string.join(field_lst4db, ','), 
                    self.table, string.join(val_other, ',') )
        else:
            q = 'UPDATE fb SET fb_fields="%s", fb_db_name="%s", fb_other="%s" '% \
                ( ",".join(field_lst4db), self.table , ",".join(val_other)) + \
                ' WHERE name = "%s"' % txt_fb_name
        #select a00001, a00006 from identifi
        #select a00001 as ragsoc, a00006 as fax from identifi
        self.db_inst.queryReturn(q)
        
        self.parent.update_parent(txt_fb_name)
        try:    del self.db
        except: pass
        
    def OnBt_connectButton(self, event):
        """ Connect to the data source
        """
        self.dsn = self.cho_dsn.GetStringSelection()
        self.user = self.txt_user.GetValue()
        self.passw = self.txt_pass.GetValue()
        
        if not self.dsn:
            wx.MessageBox("Insert the dsn data", 
                "Error", wx.ICON_ERROR)
            return
        if not self.user: self.user = "None"
        
        ret, val = self.db.conn(self.dsn, self.user, self.passw)
        
        if ret:
            wx.MessageBox(val, "Error", wx.ICON_ERROR)
            return
        
        ret, cols = self.db.get_tables()

        if ret:
            wx.MessageBox(str(cols), "Error", wx.ICON_ERROR)
            return
        
        for c in cols:
            self.cho_table.Append(c[0])

        self.bt_connect.Disable()

    def OnCho_tableChoice(self, event = None, fields = None):
        if event:
            self.table = event.GetString()
        
        ret, rows = self.db.get_cols(self.table)
        
        if ret:
            wx.MessageBox(str(rows), "Error", wx.ICON_ERROR)
            return
        
        if not rows: return
        
        status, ret = self.db.queryReturn('SELECT * from %s' % (self.table), nor=1)
        if status:
            wx.MessageBox(str(ret), "Error", wx.ICON_ERROR)
            return

        if ret:
            ret = ret[0]
        if self.gr_filed.GetNumberRows() > 0:
            self.gr_filed.DeleteRows(0,self.gr_filed.GetNumberRows())
        
        if not ret:
           #we have no data on the db, create empty fields
           ret = ["" for x in rows]

        rowsVal = [ ( row[0], f_data ) for row, f_data in zip(rows, ret) ]

        
        self.__setValueToGrid(rowsVal, fields)

    def OnCho_dsnChoice(self, event):
        if self.gr_filed.GetNumberRows() > 0:
            self.gr_filed.DeleteRows(0,self.gr_filed.GetNumberRows())

        self.cho_table.Clear()
        
    def OnChk_man_queryCheckbox(self, event):
        self._showHideCtrls( event.GetInt() )

    def OnBt_man_queryButton(self, event):
        """ Show the query frame
        """
        frame = editQuery(self.parent, title="Edit query")
        frame.setValues(self.__setFromModifyFrame, self.txt_man_query.GetValue())

    def __setValueToGrid(self, rows, fields=None, all=None):
        """ Pass me a list with [ (field, its_value ), ]
        """
        
        if self.gr_filed.GetNumberRows() > 0:
            self.gr_filed.DeleteRows(0, self.gr_filed.GetNumberRows())
        
        self.gr_filed.ClearGrid()
        self.gr_filed.AppendRows(len(rows))
        
        for num, row in enumerate(rows):
            field, value = row
      if not isinstance(value, basestring): value = str(value)
            self.gr_filed.SetCellValue(num, 0, field)
            if fields and (field in fields) or all:
                self.gr_filed.SetCellValue(num, 1, 'y')
            self.gr_filed.SetCellValue(num, 2, str( value.decode('utf-8','ignore') ) )
            
    def __setPar(self):
        """ Used for set parameters on modify
        """ 
        ret, vals = self.db_inst.queryReturn('SELECT fb_other, fb_db_name, fb_fields from fb WHERE name = "%s"' % self.name)
        if ret: return
        db_args, table, fields = vals[0]
        
        #Need because odbc doesn't like unicode!!!
        table = str(table)
        args = {}
        for val in db_args.split(','):
            tmp_val = string.split(val, '=')
            k = tmp_val[0]; v = ""
            if len(tmp_val) > 1: v = tmp_val[1]
            args[k] = v
        
        if args['dsn'] in self.cho_dsn.GetStrings():
            self.cho_dsn.SetStringSelection(args['dsn'])
        else: 
            self.paths.log4write.write("No DSN found on init odbc: %s" % args['dsn'])
            self.paths.log4write.write( self.cho_dsn.GetStrings() )
            print "No DSN found on init odbc: %s" % args['dsn']
            return
        self.txt_user.SetValue(args['user'])
        self.txt_pass.SetValue(args['pass'])
        
        #Load the dsn list
        self.OnBt_connectButton(None)
        
        ULQ = 'use_table_like_query'
        if args.has_key(ULQ) and args[ULQ] == 'True':
            self.chk_man_query.SetValue(True)
            self._showHideCtrls(1)
            self.useTableLikeQuery = True
            #Wait some time that the connection is done
            reactor.callLater(0.5, self.__setFromModifyFrame, table)
        else:
            if table in self.cho_table.GetStrings():
                self.cho_table.SetStringSelection( table )
                self.table = str(table)
            else:
                self.paths.log4write.write("No TABLE found on init odbc: %s" % table)
                self.paths.log4write.write( self.cho_table.GetStrings() )
                print self.cho_table.GetStrings()
                return
            self.OnCho_tableChoice(fields=fields)
        
    def __setFromModifyFrame(self, text):
        """
        """
        q = text.replace('\n', ' ')
        self.table = q
        self.txt_man_query.SetValue(q)
        
        status, retQueryData = self.db.queryReturn(str(q), nor=1)
        
        if status:
            wx.MessageBox("Error on query: %s \nError:\n%s" % 
                (q, str(retQueryData)), "Error", wx.ICON_ERROR)
            return
        
        #Get the column description
        #SELECT * FROM IDENTIFI
        status, retQueryCols = self.db.get_cols(query=q)
        
        if status:
            wx.MessageBox("Error on query: %s \nError:\n%s" % 
                (q, str(retQueryCols)), "Error", wx.ICON_ERROR)
            return

        colDescr = [ x[0] for x in retQueryCols ]
        retQueryData = retQueryData[0]
        
        print colDescr
        print retQueryData
        
        valueToDisplay = [(dCol.strip(), dData.strip()) for dCol, dData in zip(colDescr, retQueryData) ]
        print valueToDisplay
        self.__setValueToGrid(valueToDisplay, all = 1)
        
        """
        select a00001 as a, a00005 from identifi
        """
        

    def _showHideCtrls(self, val):
        if val: #show controls -> query request
            valShow = True
        else: valShow = False
        
        self.bt_man_query.Show(valShow)
        self.txt_man_query.Show(valShow)
        self.lbl_man_query.Show(valShow)
        
        self.cho_table.Show(not valShow)
        self.lbl_table.Show(not valShow)
        self.useTableLikeQuery = valShow
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.