{SPAM?} SQL Qeries via XMLRPC

Discussion in 'Python' started by Simon Wittber, Aug 10, 2004.

  1. 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?


    Simon Wittber.
    Simon Wittber, Aug 10, 2004
    1. Advertisements

  2. Simon Wittber

    Roger Binns Guest

    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 Binns, Aug 10, 2004
    1. Advertisements

  3. 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).

    Fredrik Lundh, Aug 11, 2004
  4. Simon Wittber

    Roger Binns Guest

    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 Binns, Aug 11, 2004
  5. 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)


    Lawrence Oluyede, Aug 12, 2004
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.