-
Notifications
You must be signed in to change notification settings - Fork 5
/
Copy pathtableserver.py
168 lines (148 loc) · 6.25 KB
/
tableserver.py
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
#
# A simple CherryPy based RESTful web server for paged access to a table in a sqlite database
# Developed for csvview but should work for any sqlite table
import cherrypy
from mako.template import Template
from mako.lookup import TemplateLookup
import os
import os.path
import json
import sqlite3
import string
import sys
import tempfile
import threading
import webbrowser
lookup = TemplateLookup(directories=['html'])
threadLocal = threading.local()
def getDbConn( dbName ):
"""Get a thread-local database connection.
"""
varName = "sqlite3-dbConn-" + dbName
v = getattr( threadLocal, varName, None )
if v is None:
v = sqlite3.connect( dbName )
setattr( threadLocal, varName, v )
return v
def getTableInfo( dbConn, tableName ):
"""Use sqlite tableinfo pragma to retrieve metadata on the given table
"""
query = "pragma table_info(%s)" % tableName
c = dbConn.execute( query )
r = c.fetchall()
return r
def viewFormat( columnType, cellVal ):
""" format cellVal suitable for client-side rendering
"""
if cellVal==None:
return None
intFormatStr = "{:,d}"
realFormatStr = "{:,.2f}"
if columnType=="integer":
ret = intFormatStr.format( cellVal )
elif columnType=="real":
ret = realFormatStr.format( cellVal )
else:
ret = cellVal
return ret
class PagedDbTable(object):
def __init__( self, dbName, dbTableName):
super( PagedDbTable, self ).__init__()
self.dbName = dbName
self.dbTableName = dbTableName
query = "select count(*) from " + dbTableName
dbConn = getDbConn( dbName )
c = dbConn.execute( query );
self.totalRowCount = c.fetchone()[0]
self.baseQuery = "select * from " + dbTableName
self.tableInfo = getTableInfo( dbConn, dbTableName )
self.columnNames = map( lambda ti: ti[1], self.tableInfo )
self.columnTypes = map( lambda ti: ti[2], self.tableInfo )
# extract human-friendly descriptions from columnInfo companion table
cinfoTable = dbTableName + "_columnInfo"
c = dbConn.execute( "select description from " + cinfoTable)
rows = c.fetchall()
self.columnDescs = map( lambda r:r[0], rows )
self.columnInfo = []
for (cn,cd) in zip(self.columnNames,self.columnDescs):
cmap = { 'id': cn, 'field': cn, 'name': cd }
self.columnInfo.append( cmap )
# print self.columnInfo
def getColumnInfo( self ):
return self.columnInfo
def getDataPage( self, sortCol, sortDir, startRow, rowLimit ):
dbConn = getDbConn( self.dbName )
if( sortCol != None and sortCol in self.columnNames and sortDir in ['asc','desc']):
orderStr = ' order by "' + sortCol + '" ' + sortDir
else:
orderStr = ""
query = self.baseQuery + orderStr + " limit " + str( startRow ) + ", " + str( rowLimit )
# print query
c = dbConn.execute( query )
rows = c.fetchall()
# print " ==> ", len( rows ), " rows"
# now prepare rows for sending to view:
viewRows = []
for row in rows:
mappedRow = { columnName : viewFormat( columnType, cellVal ) for
(columnName, columnType, cellVal) in zip( self.columnNames, self.columnTypes, row ) }
viewRows.append( mappedRow )
# namedRows = map( lambda r: dict( zip( self.columnNames, r)), rows )
return viewRows
#
# N.B.: We're still using a REST-ful (stateless) approach here, but doing so via the default() method.
# We do this because it appears that CherryPy's MethodDispatcher() doesn't allow default() or index()
# methods, which in turn would force us to reify the table hierarchy from the sqllite database as a
# tree of Python objects, which we don't want to do.
# So we just stick with the standard Dispatcher() but this means using default() to provide RESTful
# URIs.
# Provide RESTful paged access to named table
class TableResource(object):
def __init__(self, dbName):
super( TableResource, self ).__init__()
self.dbName = dbName
@cherrypy.expose
def default( self, tableName, startRow = 0, rowLimit = 10, sortby = '' ):
dbTable = PagedDbTable( self.dbName, tableName )
# print "startRow = ", startRow, ", rowLimit = ", rowLimit, ", sortby = '", sortby, "'"
startRow = int( startRow )
rowLimit = int( rowLimit )
sortstr = sortby.strip();
if( len(sortstr) > 0 ):
[ sortcol, sortdir ] = sortstr.split('+')
else:
[ sortcol, sortdir ] = [ None, None ]
cherrypy.response.headers['Content-Type'] = 'application/json'
# rowData = self.dataFile['data'][ startRow : startRow + rowLimit ]
columnInfo = dbTable.getColumnInfo()
rowData = dbTable.getDataPage( sortcol, sortdir, startRow, rowLimit )
request = { 'startRow': startRow, 'rowLimit': rowLimit }
response = { 'request': request, 'columnInfo': columnInfo,
'totalRowCount': dbTable.totalRowCount, 'results': rowData }
return json.dumps( response )
# Use simple templating to inject table name extracted from request params back in to HTML on client side:
class TableViewerResource(object):
@cherrypy.expose
def default(self, table_name=''):
return self.to_html( table_name )
def to_html(self, table_name):
tmpl = lookup.get_template("table_viewer.html")
return tmpl.render(table_name=table_name)
APP_DIR = os.path.abspath(".")
config = {'/':
{'tools.staticdir.on': True,
'tools.staticdir.dir': APP_DIR
},
}
def open_page(tableName):
webbrowser.open("http://127.0.0.1:8080/table_viewer?table_name=" + tableName )
class Root(object):
def __init__( self, tables, table_viewer ):
self.tables = tables
self.table_viewer = table_viewer
def startWebServer( dbName, tableName ):
root = Root( TableResource( dbName ), TableViewerResource() )
dbTable = PagedDbTable( dbName, tableName )
cherrypy.config.update( {'log.screen': False })
cherrypy.engine.subscribe('start', lambda : open_page( tableName ) )
cherrypy.quickstart( root, '/', config)