1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
#!/usr/bin/python
"""
Module Name: metakitBrowse
Description: Plug in for PythonCard application dbBrowse to provide MetaKit specific functionality
Constant/configuration values are currently maintained in the source code. If we are to optimise this application they should be split into seperate configuration files (as per PythonCard/Webware style guidelines)
The structure of this module should be replicated for different RDBMS so that they can be interchanged by dbBrowse - hopefully.
To Do;
Because metakit isn't a relational database we probably don't need to support the getQueryString method in this class. Its only really an internal utility method for the other, public, classes anyway.
"""
#jm - marked changes 030218
__version__="$Revision $"[11:-2]
__date__="$Date $"
__author__="Andy Todd <andy47@halfcooked.com>"
import metakit
import os
class browse:
# Connection should be a dictionary with at least two keys,
# 'databasename' and 'directory'
# This is wildly different to other database modules
def __init__(self, connection):
"Setup the database connection"
self._system_tables=[]
# Not providing a db name is guaranteed to ruin our connection
if not connection['databasename']:
raise ValueError
filename = os.path.join(connection['directory'], connection['databasename'])
# not sure about the mode to use, I assume we just want read-only
self._db = metakit.storage(filename, 0)
self._cursor='placeholder'
# This one is used in getRow
self._tableName=''
def getTables(self):
"Return a list of all of the non-system tables in <database>"
return [p.name for p in self._db.contents().structure() if p.type == 'V'] #jm
def getColumns(self, tableName):
"Get the definition of the columns in tableName"
columnDefs = []
for column in self._db.view(tableName).structure(): #jm - this block
columnName, dataType = column.name,column.type
if dataType in ['S','V']:
dataType == 'varchar'
elif dataType == 'I':
dataType == 'int'
elif dataType in ["F","D"]:
dataType == 'float'
nullable, key, default = "", "", ""
# Dodgy default, but if works for me
precision = 25
columnDefs.append((columnName, dataType, precision, nullable, key, default))
return columnDefs
def getQueryString(self, tableName):
"Return a SQL statement which queries all of the columns in tableName"
tableStructure=self.getColumns(tableName)
# Construct and return the string
stmt='SELECT '
for columnList in tableStructure:
stmt+=columnList[0]+', '
stmt=stmt[:-2]+' FROM '+tableName
return stmt
def getRow(self, tableName):
"Get a row from tableName"
# When we upgrade to 2.2 this will be a great candidate for a
# generator/iterator. In the meantime we use self._tableName to keep
# track of what we are doing
if tableName!=self._tableName:
self._tableName=tableName
self._cursor = self._db.view(tableName)
self._currentPos = -1
self._currentPos += 1
if self._currentPos == len(self._cursor):
self._currentPos -= 1
return None
# could cache these
# apparently we don't need all the junk info
# from getColumns nor do we need to use getQueryString
columnNames = [i.name for i in self._cursor.structure()] #jm
row = self._cursor[self._currentPos]
result = []
for c in columnNames:
try:
result.append(getattr(row, c))
except AttributeError:
result.append('')
#print result
return result
def getRows(self, tableName):
"Get all of the rows from tableName"
# columnNames = [i[0] for i in self.getColumns(tableName)]
self._cursor=self._db.view(tableName)
columnNames = [i.name for i in self._cursor.structure()] #jm
rows=[]
for row in range(0, len(self._cursor)):
currentRow=[]
for column in columnNames:
currentRow.append(getattr(self._cursor[row], column))
rows.append(currentRow)
return rows
if __name__ == '__main__':
# We are in an interactive session so run our test routines
# Connect to the database
##connection={ 'databasename':'andy'
## ,'directory':'E:\Gadfly'}
connection={'databasename':'calflora.db', 'directory':'.'}
dbHolder = browse(connection)
# Return all of our table names into user_tables
user_tables = dbHolder.getTables()
# Print out the structure of each table and its first row
print "--------------------------------------------------"
for table in user_tables:
print "table:", table
print dbHolder.getQueryString(table)
print dbHolder.getRow(table)
print "--------------------------------------------------"
|
