{SPAM?} SQL Qeries via XMLRPC

S

Simon Wittber

In a recent project, we came across a problem using the FreeTDS (MS SQL /
Sybase DB API) libraries on a Linux box.

To cut a long story short, we ending up exposing the functions we needed
from a Win32 machine, via XMLRPC, in Python, so that we could run ad-hoc
queries on the SQL Server which was running on that same machine.

Side point: The timeliness at which I was able to achieve this impressed the
boss. Python code is now springing up everywhere :)

I am now considering implementing persistent connections, authentication,
and perhaps even "stored procedures" in this tiny XMLRPC Server. This would
provide a consistent database interface to all our applications (written in
various languages) and allow us to consider things like load balancing and
query caching. It would also make a move from the proprietary SQL Server to
a Free RDBMS much easier (portable stored procedures, same API etc).

The problem is, I have a tiny little nag in my mind about the use of XMLRPC
(and therefore HTTP) to implement this.

Has anyone done this sort of thing before?

Are there any problems with this strategy that I have not considered?

Should I be considering the standard python XMLRPC / HTTP / SocketServer
modules for mission critical production systems?

Regards,

Simon Wittber.
 
R

Roger Binns

Simon said:
The problem is, I have a tiny little nag in my mind about the use of
XMLRPC (and therefore HTTP) to implement this.

Has anyone done this sort of thing before?

Are there any problems with this strategy that I have not considered?

Using XML-RPC is a great way of exposing services and data.
You do however want the API exposed to be higher level
rather than lower level (for example retrieve an entire
record in one go rather than a field at a time).

Using XML-RPC does not prevent also adding other service
mechanisms such as SOAP, CORBA or whatever is flavour
of next year. I would suggest looking at the Cookbook
to get a feel for it, and also to see the limitations.
For example XML-RPC doesn't support None and SOAP
can't handle empty dictionaries.

Note that using XML-RPC does not mean HTTP. The most
common transport for XML-RPC is HTTP, but you can use
it over anything. In one of my projects I use it over
SSH (using the Paramiko library to do the SSH part)

The existing standard Python library XML-RPC works
well, but has the limitation that it doesn't fully
support HTTP authentication and it uses one connection
per request. The authentication issue can be solved
by making authentication part of your exposed APIs,
not the underlying XML-RPC transport.

The one connection per request will presumably be
solved in a future release of the Python library
and generally is not an issue on a LAN.

The great thing about using XML-RPC is the cross
language portability. You can get bindings for any
language. One of my projects was a web site with
the front end done in PHP (Smarty) and the backend
all being Python exposed via XML-RPC over HTTP.

Roger
 
F

Fredrik Lundh

Roger said:
The existing standard Python library XML-RPC works
well, but has the limitation that it doesn't fully
support HTTP authentication and it uses one connection
per request. The authentication issue can be solved
by making authentication part of your exposed APIs,
not the underlying XML-RPC transport.

The one connection per request will presumably be
solved in a future release of the Python library
and generally is not an issue on a LAN.

the standard transport does this, yes.

it's fairly easy to plug in your own transports (just subclass Transport,
override as necessary, and pass an instance of your custom transport
to the ServerProxy class).

</F>
 
R

Roger Binns

Fredrik said:
it's fairly easy to plug in your own transports (just subclass
Transport, override as necessary, and pass an instance of your custom
transport
to the ServerProxy class).

I did actually try for two weeks. In my case I wanted both the
persistent connections, and also to work over HTTPS (I had
to use m2crypto for the SSL not the stuff builtin to Python
in order to deal with certificate issues).

I had to give up in the end. The thread on "useless destructors"
was particularly appropriate as a lot of the code was manually
closing things and most of the work was to try and circumvent
that. I ended up just using SSH (paramiko) which took me about
two hours :)

Someone as illustrious as yourself may be able to fix it,
but I do believe it will be harder than it initially appears :)

Roger
 
L

Lawrence Oluyede

I did something (as an excercise) like you were asking for with a friend,
it's based on twisted and send queries against a sqlite db (for testing,
but you can plug into it easily another db). The source code is LGPL and
the package ships with a couple of (quite-working and untested) web
interfaces (cgi and webware). It ships also with a test.

Its primary interfaces are XML-RPC and SOAP ones

So, have a look and tell me if some code suits your needs. To execute it
type: twistd -noy pydbserver.py (as root)

bye!

http://www26.brinkster.com/rhymes/pydbserver.tar.gz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top