~launchpad-p-s/sofastatistics/main

« back to all changes in this revision

Viewing changes to dbe_plugins/dbe_ms_access.py

  • Committer: Grant Paton-Simpson
  • Date: 2009-05-19 04:21:43 UTC
  • Revision ID: g@ubuntu-20090519042143-p561mbokz3inefvd
Initial import

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
#Must run makepy once - 
 
2
#see http://www.thescripts.com/forum/thread482449.html e.g. the following 
 
3
#way - run PYTHON\Lib\site-packages\pythonwin\pythonwin.exe (replace 
 
4
#PYTHON with folder python is in).  Tools>COM Makepy utility - select 
 
5
#appropriate library - e.g. for ADO it would be 
 
6
# Microsoft ActiveX Data Objects 2.8 Library (2.8) - and 
 
7
#select OK.  NB DAO has to be done separately from ADO etc.
 
8
 
 
9
#"""
 
10
import adodbapi
 
11
import win32com.client
 
12
#"""
 
13
import wx
 
14
import pprint
 
15
 
 
16
import getdata
 
17
import table_entry
 
18
 
 
19
# numeric
 
20
BYTE = 'Byte - 1-byte unsigned integer'
 
21
INTEGER = 'Integer - 2-byte signed integer'
 
22
LONGINT = 'Long Integer - 4-byte signed integer'
 
23
DECIMAL = 'Decimal'
 
24
SINGLE = 'Single'
 
25
DOUBLE = 'Double'
 
26
CURRENCY = 'Currency'
 
27
# other
 
28
DATE = 'date'
 
29
BOOLEAN = 'boolean'
 
30
TIMESTAMP = 'timestamp'
 
31
VARCHAR = 'varchar'
 
32
LONGVARCHAR = 'longvarchar'
 
33
 
 
34
AD_OPEN_KEYSET = 1
 
35
AD_LOCK_OPTIMISTIC = 3
 
36
AD_SCHEMA_COLUMNS = 4
 
37
 
 
38
def quote_identifier(raw_val):
 
39
    return "[%s]" % raw_val
 
40
 
 
41
def DbeSyntaxElements():
 
42
    if_clause = "IIF(%s, %s, %s)"
 
43
    # True is -1, not 1, in Access, thus the ABS before summing
 
44
    abs_wrapper_l = " ABS("
 
45
    abs_wrapper_r = ")"
 
46
    return if_clause, abs_wrapper_l, abs_wrapper_r
 
47
 
 
48
    
 
49
class DbDets(getdata.DbDets):
 
50
 
 
51
    def getDbTbls(self, cur, db):
 
52
        "Get table names given database and cursor. NB not system tables"
 
53
        tbls = []
 
54
        cat = win32com.client.Dispatch(r'ADOX.Catalog')
 
55
        cat.ActiveConnection = cur.adoconn
 
56
        alltables = cat.Tables
 
57
        tbls = []
 
58
        for tab in alltables:
 
59
            if tab.Type == 'TABLE':
 
60
                tbls.append(tab.Name)
 
61
        cat = None
 
62
        return tbls
 
63
 
 
64
    def getFldType(self, adotype):
 
65
        """
 
66
        http://www.devguru.com/Technologies/ado/quickref/field_type.html
 
67
        http://www.databasedev.co.uk/fields_datatypes.html
 
68
        """
 
69
        if adotype == win32com.client.constants.adUnsignedTinyInt:
 
70
            fld_type = BYTE # 1-byte unsigned integer
 
71
        elif adotype == win32com.client.constants.adSmallInt:
 
72
            fld_type = INTEGER # 2-byte signed integer
 
73
        elif adotype == win32com.client.constants.adInteger:
 
74
            fld_type = LONGINT # 4-byte signed integer
 
75
        elif adotype == win32com.client.constants.adSingle:
 
76
            fld_type = SINGLE # Single-pCURRENCYrecision floating-point value
 
77
        elif adotype == win32com.client.constants.adDouble:
 
78
            fld_type = DOUBLE # Double precision floating-point
 
79
        elif adotype == win32com.client.constants.adNumeric:
 
80
            fld_type = DECIMAL
 
81
        elif adotype == win32com.client.constants.adCurrency:
 
82
            fld_type = CURRENCY
 
83
        else:
 
84
            raise Exception, "Not an MS Access ADO field type %d" % adotype
 
85
        return fld_type
 
86
 
 
87
    def GetMinMax(self, fld_type, num_prec, dec_pts):
 
88
        """
 
89
        Returns minimum and maximum allowable numeric values.
 
90
        NB even though a floating point type will not store values closer 
 
91
            to zero than a certain level, such values will be accepted here.
 
92
            The database will store these as zero.
 
93
        http://www.databasedev.co.uk/fields_datatypes.html38 
 
94
        """
 
95
        if fld_type == BYTE:
 
96
            min = 0
 
97
            max = (2**8)-1 # 255
 
98
        elif fld_type == INTEGER:
 
99
            min = -(2**15)
 
100
            max = (2**15)-1            
 
101
        elif fld_type == LONGINT:
 
102
            min = -(2**31)
 
103
            max = (2**31)-1            
 
104
        elif fld_type == DECIMAL:
 
105
            # (+- 38 if .adp as opposed to .mdb)
 
106
            min = -((10**28)-1)
 
107
            max = (10**28)-1
 
108
        elif fld_type == SINGLE: # signed by default
 
109
            min = -3.402823466E+38
 
110
            max = 3.402823466E+38
 
111
        elif fld_type == DOUBLE:
 
112
            min = -1.79769313486231E308
 
113
            max = 1.79769313486231E308
 
114
        elif fld_type == CURRENCY:
 
115
            """
 
116
            Accurate to 15 digits to the left of the decimal point and 
 
117
                4 digits to the right.
 
118
            e.g. 19,4 -> 999999999999999.9999
 
119
            """
 
120
            dec_pts = 4
 
121
            num_prec = 15 + dec_pts
 
122
            abs_max = ((10**(num_prec + 1))-1)/(10**dec_pts)
 
123
            min = -abs_max
 
124
            max = abs_max
 
125
        else:
 
126
            raise Exception, "Not a valid MS Access (ADO) numeric type"
 
127
        return min, max
 
128
 
 
129
    def getTblFlds(self, cur, db, tbl):
 
130
        """
 
131
        Returns details for set of fields given database, table, and cursor.
 
132
        NUMERIC_SCALE - number of significant digits to right of decimal point.
 
133
        NUMERIC_SCALE should be Null if not numeric (but is in fact 255 so 
 
134
            I must set to None!).
 
135
        """
 
136
        #http://msdn.microsoft.com/en-us/library/aa155430(office.10).aspx
 
137
        cat = win32com.client.Dispatch(r'ADOX.Catalog') # has everything I 
 
138
            # need but pos and charset
 
139
        cat.ActiveConnection = cur.adoconn
 
140
        # extra properties which can't be obtained from cat.Tables.Columns
 
141
        # viz ordinal position and charset
 
142
        # Do not add fourth constraint(None, None, "tbltest", None) will not work!
 
143
        # It should (see http://www.w3schools.com/ADO/met_conn_openschema.asp) but ...
 
144
        extras = {}
 
145
        rs = cur.adoconn.OpenSchema(AD_SCHEMA_COLUMNS, (None, None, tbl)) 
 
146
        while not rs.EOF:
 
147
            fld_name = rs.Fields("COLUMN_NAME").Value
 
148
            ord_pos = rs.Fields("ORDINAL_POSITION").Value
 
149
            char_set = rs.Fields("CHARACTER_SET_NAME").Value
 
150
            extras[fld_name] = (ord_pos, char_set)
 
151
            rs.MoveNext()
 
152
        flds = {}
 
153
        for col in cat.Tables(tbl).Columns:
 
154
            # build dic of fields, each with dic of characteristics
 
155
            fld_name = col.Name
 
156
            fld_type = self.getFldType(col.Type)
 
157
            bolautonum = col.Properties("AutoIncrement").Value
 
158
            boldata_entry_ok = False if bolautonum else True
 
159
            bolnumeric = fld_type in [BYTE, INTEGER, LONGINT, DECIMAL, 
 
160
                                      SINGLE, DOUBLE, CURRENCY]
 
161
            dec_pts = col.NumericScale if col.NumericScale < 18 else 0
 
162
            boldatetime = fld_type in [DATE, TIMESTAMP]
 
163
            fld_txt = not bolnumeric and not boldatetime
 
164
            num_prec = col.Precision
 
165
            min_val, max_val = self.GetMinMax(fld_type, num_prec, dec_pts)
 
166
            dets_dic = {
 
167
                getdata.FLD_SEQ: extras[fld_name][0],
 
168
                getdata.FLD_BOLNULLABLE: col.Properties("Nullable").Value,
 
169
                getdata.FLD_DATA_ENTRY_OK: boldata_entry_ok,
 
170
                getdata.FLD_COLUMN_DEFAULT: col.Properties("Default").Value,
 
171
                getdata.FLD_BOLTEXT: fld_txt,
 
172
                getdata.FLD_TEXT_LENGTH: col.DefinedSize,
 
173
                getdata.FLD_CHARSET: extras[fld_name][1],
 
174
                getdata.FLD_BOLNUMERIC: bolnumeric,
 
175
                getdata.FLD_BOLAUTONUMBER: bolautonum,
 
176
                getdata.FLD_DECPTS: dec_pts,
 
177
                getdata.FLD_NUM_WIDTH: num_prec,
 
178
                getdata.FLD_BOL_NUM_SIGNED: True,
 
179
                getdata.FLD_NUM_MIN_VAL: min_val,
 
180
                getdata.FLD_NUM_MAX_VAL: max_val,
 
181
                getdata.FLD_BOLDATETIME: boldatetime, 
 
182
            }
 
183
            flds[fld_name] = dets_dic
 
184
        debug = False 
 
185
        if debug:
 
186
            pprint.pprint(flds)
 
187
        cat = None
 
188
        return flds  
 
189
 
 
190
    def getIndexDets(self, cur, db, tbl):
 
191
        """
 
192
        has_unique - boolean
 
193
        idxs = [idx0, idx1, ...]
 
194
        each idx is a dict name, is_unique, flds
 
195
        """
 
196
        cat = win32com.client.Dispatch(r'ADOX.Catalog')
 
197
        cat.ActiveConnection = cur.adoconn
 
198
        index_coll = cat.Tables(tbl).Indexes
 
199
        # initialise
 
200
        has_unique = False
 
201
        idxs = []
 
202
        for index in index_coll:
 
203
            if index.Unique:
 
204
                has_unique = True
 
205
            fld_names = [x.Name for x in index.Columns]
 
206
            idx_dic = {getdata.IDX_NAME: index.Name, 
 
207
                       getdata.IDX_IS_UNIQUE: index.Unique, 
 
208
                       getdata.IDX_FLDS: fld_names}
 
209
            idxs.append(idx_dic)
 
210
        cat = None
 
211
        debug = False
 
212
        if debug:
 
213
            pprint.pprint(idxs)
 
214
            print has_unique
 
215
        return has_unique, idxs
 
216
 
 
217
    def getDbDets(self):
 
218
        """
 
219
        Return connection, cursor, and get lists of 
 
220
            databases, tables, and fields 
 
221
            based on the MySQL database connection details provided.
 
222
        Connection string as per the ADO documentation.
 
223
        The database used will be the first if none provided.
 
224
        The table used will be the first if none provided.
 
225
        The field dets will be taken from the table used.
 
226
        Returns conn, cur, dbs, tbls, flds, has_unique, idxs.
 
227
        """
 
228
        
 
229
        conn_dets_access = self.conn_dets.get(getdata.DBE_MS_ACCESS)
 
230
        if not conn_dets_access:
 
231
            raise Exception, "No connection details available for MS Access"
 
232
        if not conn_dets_access.get(self.db):
 
233
            raise Exception, "No connections for MS Access database %s" % \
 
234
                self.db
 
235
        conn_dets_access_db = conn_dets_access[self.db]
 
236
        """DSN syntax - http://support.microsoft.com/kb/193332 and 
 
237
        http://www.codeproject.com/database/connectionstrings.asp ...
 
238
        ... ?df=100&forumid=3917&exp=0&select=1598401"""
 
239
        database = conn_dets_access_db["database"]
 
240
        user = conn_dets_access_db["user"]
 
241
        pwd = conn_dets_access_db["pwd"]
 
242
        mdw = conn_dets_access_db["mdw"]
 
243
        DSN = """PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;
 
244
            USER ID=%s;PASSWORD=%s;Jet OLEDB:System Database=%s;""" % \
 
245
            (database, user, pwd, mdw)
 
246
        conn = adodbapi.connect(connstr=DSN)
 
247
        #wk = conn.adoConn.CreateWorkspace("", user, pwd)
 
248
        #dbobj = wk.OpenDatabase(database) # quite useful
 
249
        cur = conn.cursor() # must return tuples not dics
 
250
        cur.adoconn = conn.adoConn # (need to be able to access from just the cursor)
 
251
        # get database name
 
252
        dbs = [self.db]
 
253
        tbls = self.getDbTbls(cur, self.db)
 
254
        # get table names (from first db if none provided)
 
255
        db_to_use = self.db if self.db else dbs[0]
 
256
        tbls = self.getDbTbls(cur, db_to_use)
 
257
        # get field names (from first table if none provided)
 
258
        tbl_to_use = self.tbl if self.tbl else tbls[0]
 
259
        flds = self.getTblFlds(cur, db_to_use, tbl_to_use)
 
260
        has_unique, idxs = self.getIndexDets(cur, db_to_use, tbl_to_use)
 
261
        debug = False
 
262
        if debug:
 
263
            print self.db
 
264
            print self.tbl
 
265
            pprint.pprint(tbls)
 
266
            pprint.pprint(flds)
 
267
            pprint.pprint(idxs)
 
268
        return conn, cur, dbs, tbls, flds, has_unique, idxs
 
269
 
 
270
 
 
271
def InsertRow(conn, cur, tbl_name, data):
 
272
    """
 
273
    data = [(value as string, fld_name, fld_dets), ...]
 
274
    Modify any values (according to field details) to be ready for insertion.
 
275
    Use placeholders in execute statement.
 
276
    Commit insert statement.
 
277
    
 
278
    TODO - test this when can use WebKit in Windows.
 
279
    """
 
280
    debug = False
 
281
    if debug: pprint.pprint(data)
 
282
    fld_dics = [x[2] for x in data]
 
283
    fld_names = [x[1] for x in data]
 
284
    fld_names_clause = " ([" + "], [".join(fld_names) + "]) "
 
285
    # e.g. (`fname`, `lname`, `dob` ...)
 
286
    fld_placeholders_clause = " (" + \
 
287
        ", ".join(["%s" for x in range(len(data))]) + ") "
 
288
    # e.g. " (%s, %s, %s ...) "
 
289
    SQL_insert = "INSERT INTO `%s` " % tbl_name + fld_names_clause + \
 
290
        "VALUES %s" % fld_placeholders_clause
 
291
    if debug: print SQL_insert
 
292
    data_lst = []
 
293
    for i, data_dets in enumerate(data):
 
294
        if debug: pprint.pprint(data_dets)
 
295
        str_val, fld_name, fld_dic = data_dets
 
296
        if fld_dic[getdata.FLD_BOLDATETIME]:
 
297
            valid_datetime, t = util.datetime_str_valid(str_val)
 
298
            if not valid_datetime:
 
299
                return False # should have been tested already but ...
 
300
            else:
 
301
                if debug: print t
 
302
            val2add = "%s-%s-%s %s:%s:%s" % (t[0], t[1], t[2], 
 
303
                                             t[3], t[4], t[5])
 
304
            if debug: print val2add 
 
305
        else:
 
306
            val2add = str_val
 
307
        data_lst.append(val2add)
 
308
    data_tup = tuple(data_lst)
 
309
    try:
 
310
        cur.execute(SQL_insert, data_tup)
 
311
        conn.commit()
 
312
        return True
 
313
    except Exception, e:
 
314
        if debug: print "Failed to insert row.  SQL: %s, Data: %s" % \
 
315
            (SQL_insert, str(data_tup)) + "\n\nOriginal error: %s" % e
 
316
        return False
 
317
 
 
318
def setDataConnGui(parent, read_only, scroll, szr, lblfont):
 
319
    ""
 
320
    # default database
 
321
    parent.lblMsaccessDefaultDb = wx.StaticText(scroll, -1, 
 
322
                                                "Default Database:")
 
323
    parent.lblMsaccessDefaultDb.SetFont(lblfont)
 
324
    msaccess_default_db = parent.msaccess_default_db \
 
325
        if parent.msaccess_default_db else ""
 
326
    parent.txtMsaccessDefaultDb = wx.TextCtrl(scroll, -1, 
 
327
                                              msaccess_default_db, 
 
328
                                              size=(250,-1))
 
329
    parent.txtMsaccessDefaultDb.Enable(not read_only)
 
330
    # default table
 
331
    parent.lblMsaccessDefaultTbl = wx.StaticText(scroll, -1, 
 
332
                                                 "Default Table:")
 
333
    parent.lblMsaccessDefaultTbl.SetFont(lblfont)
 
334
    msaccess_default_tbl = parent.msaccess_default_tbl \
 
335
        if parent.msaccess_default_tbl else ""
 
336
    parent.txtMsaccessDefaultTbl = wx.TextCtrl(scroll, -1, 
 
337
                                               msaccess_default_tbl, 
 
338
                                               size=(250,-1))
 
339
    parent.txtMsaccessDefaultTbl.Enable(not read_only)
 
340
    bxMsaccess= wx.StaticBox(scroll, -1, "MS Access")
 
341
    parent.szrMsaccess = wx.StaticBoxSizer(bxMsaccess, wx.VERTICAL)
 
342
    #3 MS ACCESS INNER
 
343
    szrMsaccessInner = wx.BoxSizer(wx.HORIZONTAL)
 
344
    szrMsaccessInner.Add(parent.lblMsaccessDefaultDb, 0, 
 
345
                         wx.LEFT|wx.RIGHT, 5)
 
346
    szrMsaccessInner.Add(parent.txtMsaccessDefaultDb, 1, 
 
347
                         wx.GROW|wx.RIGHT, 10)
 
348
    szrMsaccessInner.Add(parent.lblMsaccessDefaultTbl, 0, 
 
349
                         wx.LEFT|wx.RIGHT, 5)
 
350
    szrMsaccessInner.Add(parent.txtMsaccessDefaultTbl, 1, 
 
351
                         wx.GROW|wx.RIGHT, 10)
 
352
    parent.szrMsaccess.Add(szrMsaccessInner, 0)
 
353
    msaccess_col_dets = [
 
354
        ("Database(s)", table_entry.COL_TEXT_BROWSE, 300, 
 
355
            "Choose an MS Access database file", 
 
356
            "MS Access databases (*.mdb)|*.mdb"),
 
357
        ("Security File (*.mdw)", table_entry.COL_TEXT_BROWSE, 300,
 
358
            "Choose an MS Access security file",
 
359
            "MS Access security files (*.mdw)|*.mdw"),
 
360
        ("User Name (opt)", table_entry.COL_STR, 140),
 
361
        ("Password (opt)", table_entry.COL_STR, 140),
 
362
        ]
 
363
    parent.msaccess_new_grid_data = []
 
364
    parent.msaccess_grid = table_entry.TableEntry(frame=parent, 
 
365
        panel=scroll, szr=parent.szrMsaccess, read_only=read_only, 
 
366
        grid_size=(1000, 100), col_dets=msaccess_col_dets, 
 
367
        data=parent.msaccess_data, 
 
368
        new_grid_data=parent.msaccess_new_grid_data)
 
369
    szr.Add(parent.szrMsaccess, 0, wx.GROW|wx.ALL, 10)
 
370
 
 
371
def getProjSettings(parent, proj_dic):
 
372
    ""
 
373
    parent.msaccess_default_tbl = \
 
374
        proj_dic["default_tbls"][getdata.DBE_MS_ACCESS]
 
375
    if proj_dic["conn_dets"].get(getdata.DBE_MS_ACCESS):
 
376
        parent.msaccess_data = [(x["database"], x["mdw"], x["user"], 
 
377
                                 x["pwd"]) \
 
378
            for x in proj_dic["conn_dets"][getdata.DBE_MS_ACCESS].values()]
 
379
    else:
 
380
        parent.msaccess_data = []
 
381
 
 
382
def setConnDetDefaults(parent):
 
383
    try:
 
384
        parent.msaccess_default_db
 
385
    except AttributeError:
 
386
        parent.msaccess_default_db = ""
 
387
    try:
 
388
        parent.msaccess_default_tbl
 
389
    except AttributeError:
 
390
        parent.msaccess_default_tbl = ""
 
391
    try:
 
392
        parent.msaccess_data
 
393
    except AttributeError:
 
394
        parent.msaccess_data = []
 
395
 
 
396
def processConnDets(parent, default_dbs, default_tbls, conn_dets):
 
397
    parent.msaccess_grid.UpdateNewGridData()
 
398
    msaccess_default_db = parent.txtMsaccessDefaultDb.GetValue()
 
399
    msaccess_default_tbl = parent.txtMsaccessDefaultTbl.GetValue()
 
400
    has_msaccess_conn = msaccess_default_db and msaccess_default_tbl
 
401
    incomplete_msaccess = (msaccess_default_db or msaccess_default_tbl) \
 
402
        and not has_msaccess_conn
 
403
    if incomplete_msaccess:
 
404
        wx.MessageBox("The MS Access details are incomplete")
 
405
        parent.txtMsaccessDefaultDb.SetFocus()
 
406
    default_dbs[getdata.DBE_MS_ACCESS] = msaccess_default_db \
 
407
        if msaccess_default_db else None            
 
408
    default_tbls[getdata.DBE_MS_ACCESS] = msaccess_default_tbl \
 
409
        if msaccess_default_tbl else None
 
410
    #pprint.pprint(parent.msaccess_new_grid_data) # debug
 
411
    msaccess_settings = parent.msaccess_new_grid_data
 
412
    if msaccess_settings:
 
413
        conn_dets_msaccess = {}
 
414
        for msaccess_setting in msaccess_settings:
 
415
            db_path = msaccess_setting[0]
 
416
            db_name = parent.getFileName(db_path)
 
417
            new_msaccess_dic = {}
 
418
            new_msaccess_dic["database"] = db_path
 
419
            new_msaccess_dic["mdw"] = msaccess_setting[1]
 
420
            new_msaccess_dic["user"] = msaccess_setting[2]
 
421
            new_msaccess_dic["pwd"] = msaccess_setting[3]
 
422
            conn_dets_msaccess[db_name] = new_msaccess_dic
 
423
        conn_dets[getdata.DBE_MS_ACCESS] = conn_dets_msaccess
 
424
    return incomplete_msaccess, has_msaccess_conn