Shows how to remotely access a database via a web service supporting XML-RPC and SOAP protocols. The example uses the MySQLdb module, but should be easily customised to any database with Python module adhering to the Python DB API. The web service interface provides the ability to make a self contained query, or allows the creation of a distinct database cursor to service a series of queries or updates without other clients interfering. Cursors automatically expire and delete themselves after a set period of inactivity if not explicitly closed.
Download
Copy to clipboard1 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 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 | # The actual web service.
import MySQLdb
import signal
import netsvc
import netsvc.xmlrpc
import netsvc.soap
class Cursor(netsvc.Service):
def __init__(self,name,cursor,timeout):
netsvc.Service.__init__(self,name)
self.joinGroup("database-services")
self._cursor = cursor
self._timeout = timeout
self._restart()
self.exportMethod(self.execute)
self.exportMethod(self.executemany)
self.exportMethod(self.description)
self.exportMethod(self.rowcount)
self.exportMethod(self.fetchone)
self.exportMethod(self.fetchmany)
self.exportMethod(self.fetchall)
self.exportMethod(self.arraysize)
self.exportMethod(self.close)
def encodeObject(self,object):
if hasattr(MySQLdb,"DateTime"):
if type(object) == MySQLdb.DateTimeType:
return ("xsd:string",object.strftime())
elif type(object) == MySQLdb.DateTimeDeltaType:
return ("xsd:string",str(object))
return netsvc.Service.encodeObject(self,object)
def executeMethod(self,name,method,params):
try:
return netsvc.Service.executeMethod(self,name,method,params)
except MySQLdb.ProgrammingError,exception:
self.abortResponse(1,"Programming Error","db",str(exception))
except MySQLdb.Error,(error,description):
self.abortResponse(error,description,"mysql")
def _restart(self):
self.cancelTimer("idle")
self.startTimer(self._expire,self._timeout,"idle")
def _expire(self,name):
if name == "idle":
self.close()
def execute(self,query,args=None):
result = self._cursor.execute(query,args)
self._restart()
return result
def executemany(self,query,args=None):
result = self._cursor.executemany(query,args)
self._restart()
return result
def description(self):
self._restart()
return self._cursor.description
def rowcount(self):
self._restart()
return self._cursor.rowcount
def fetchone(self):
result = self._cursor.fetchone()
self._restart()
return result
def fetchmany(self,size=None):
if size == None:
size = self._cursor.arraysize
result = self._cursor.fetchmany(size)
self._restart()
return result
def fetchall(self):
result = self._cursor.fetchall()
self._restart()
return result
def arraysize(self):
self._restart()
return self._cursor.arraysize
def close(self):
self._cursor.close()
self.cancelTimer("idle")
self.destroyReferences()
return 0
class Database(netsvc.Service):
def __init__(self,name,**kw):
netsvc.Service.__init__(self,name)
self._name = name
self.joinGroup("database-services")
self._database = MySQLdb.connect(**kw)
self._cursors = 0
self.exportMethod(self.execute)
self.exportMethod(self.executemany)
self.exportMethod(self.cursor)
def encodeObject(self,object):
if hasattr(MySQLdb,"DateTime"):
if type(object) == MySQLdb.DateTimeType:
return ("xsd:string",object.strftime())
elif type(object) == MySQLdb.DateTimeDeltaType:
return ("xsd:string",str(object))
return netsvc.Service.encodeObject(self,object)
def executeMethod(self,name,method,params):
try:
return netsvc.Service.executeMethod(self,name,method,params)
except MySQLdb.ProgrammingError,exception:
self.abortResponse(1,"Programming Error","db",str(exception))
except MySQLdb.Error,(error,description):
self.abortResponse(error,description,"mysql")
def execute(self,query,args=None):
cursor = self._database.cursor()
cursor.execute(query,args)
if cursor.description == None:
return cursor.rowcount
return cursor.fetchall()
def executemany(self,query,args=None):
cursor = self._database.cursor()
cursor.executemany(query,args)
if cursor.description == None:
return cursor.rowcount
return cursor.fetchall()
def cursor(self,timeout):
self._cursors = self._cursors + 1
name = "%s/%d" % (self._name,self._cursors)
cursor = self._database.cursor()
Cursor(name,cursor,timeout)
child = "%d" % self._cursors
return child
dispatcher = netsvc.Dispatcher()
dispatcher.monitor(signal.SIGINT)
httpd = netsvc.HttpDaemon(8000)
server = Database("test",db="test")
rpcgw1 = netsvc.xmlrpc.RpcGateway("database-services")
httpd.attach("/xmlrpc/database",rpcgw1)
rpcgw2 = netsvc.soap.RpcGateway("database-services")
httpd.attach("/soap/database",rpcgw2)
httpd.start()
dispatcher.run()
# Example of XML-RPC client using PythonWare "xmlrpclib" module.
import xmlrpclib
url = "http://localhost:8000/xmlrpc/database/test"
service = xmlrpclib.Server(url)
tables = service.execute("show tables")
if tables == []:
print "no tables"
else:
for entry in tables:
table = entry[0]
print "table: " + table
# create cursor specific to queries
name = service.cursor(30)
print "cursor: " + url + "/" + name
cursor = xmlrpclib.Server(url+"/"+name)
cursor.execute("select * from "+table)
desc = cursor.description()
print "desc: " + str(desc)
data = cursor.fetchall()
print "data: " + str(data)
cursor.close()
# Example of SOAP client using pywebsvcs "SOAP" module.
import SOAP
url = "http://localhost:8000/soap/database/test"
service = SOAP.SOAPProxy(url)
tables = service.execute("show tables")
if tables == []:
print "no tables"
else:
for entry in tables:
table = entry[0]
print "table: " + table
# create cursor specific to queries
name = service.cursor(30)
print "cursor: " + url + "/" + name
cursor = SOAP.SOAPProxy(url+"/"+name)
cursor.execute("select * from "+table)
desc = cursor.description()
print "desc: " + str(desc)
data = cursor.fetchall()
print "data: " + str(data)
cursor.close()
|
This style of interface is very useful for debugging, or where you don't much care who can do what to your database. It is recommended that in production systems you not provide full SQL access like this, but hide your database behind interfaces which restrict what can be done.
Alternatively, what you can do is restrict client access to specific hosts, or require user authorisation using the basic HTTP authentication mechanism. Both approaches are supported by the underlying framework the example makes use of.
A good approach is to restrict host access to the machine your main corporate web server is on and in your web pages using PHP or Python, hard code queries against the backend database service using the XML-RPC protocol. Your main web server can then be used to restrict who can access what, and the pages on the web server restrict what queries can be run as well as present it in a more palatable form.
The "netsvc" module is provided as part of OSE, which is available from:
The "MySQLdb" module is available from:
http://sourceforge.net/projects/mysql-python
The "ZSI" package from "pywebsvcs" is needed by OSE if using the SOAP gateway. The "SOAP" client module is also part of "pywebsvcs". These are from:
