Re: [sqlalchemy] Web API Wrapper for Informix Database

[Brian Van Klaveren]

I think what you actually would need to do is implement your own version of the 
DBAPI (effectively a parodying DBAPI). Then you would just configure a 
SQLAlchemy engine to use that DBAPI.

In this scenario, a requests session would be started on the acquisition of a 
cursor (or maybe on connect()). Execute could lazily set the query up and fetch 
would execute the SOAP request, download all rows (unless the SOAP service 
supports pagination) and cache them locally.

Brian

> On Apr 3, 2016, at 1:27 PM, Nathan Nelson  wrote:
> 
> hood.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Web API Wrapper for Informix Database

[Nathan Nelson]

I have a Cisco CallManager server that runs Informix under the hood. 
 Unfortunately, Cisco does not allow direct ODBC connectivity into the 
underlying database server.  With that being said, they do provide an AXL 
(administrative XML) SOAP based web API that allows you to query the 
underlying database using a standard SQL query by simply wrapping the query 
in the necessary XML and POSTing the data to the web service.

My thought was to somehow write an adapter that would encapsulate or format 
any raw SQL queries that SQLAlchemy would issue to the underlying database 
and handle formatting them accordingly, POSTing them and then returning the 
result set in the expected format.

Realistically, even if this is feasible and doable, I can say with near 
certainty it is above my current Python skill level, though, if given some 
initial pointers or advice I might be able to hack through it and pick up a 
few things along the way.  I is there any other SQLAlchemy related project 
that anyone knows of anywhere (Github, etc.) that might be similar to what 
I am trying to accomplish here?  I thought maybe the Amazon Redshift stuff 
for SQLAlchemy I found on Github would be helpful to poke through the code 
but I don't think it's really what I am looking to do.  My effort might be 
further complicated as it seems Informix uses ibm_db instead of a built-in 
dialect that comes with SQLAlchemy, correct?

Here's a very basic example of code I use to convert a raw SQL query 
("SELECT * FROM devices") into a web request (using Requests) to 
demonstrate the XML wrapper around the raw SQL query I mentioned above and 
showing how it's POSTed for a simple select:

import requests

from lxml import etree, objectify


url = 'https://192.168.1.200:8443/axl/'
sess = requests.Session()
sess.auth = ('username', 'password')
req_headers = {'Content-type': 'text/xml', 'SOAPAction': 'CUCM:DB ver=8.5'}


def _parse_unicode_xml(unicode_xml):
"""
Converts unicode XML to an object tree using the lxml parser.
"""
utf8_parser = etree.XMLParser(encoding='utf-8')
s = unicode_xml.encode('utf-8')
result = objectify.fromstring(s, parser=utf8_parser)
return result


def ccm_execute(sql_query):
"""
Queries Cisco CallManager via the AXL SOAP web service via an XML 
request.
"""
wrapper = '''
http://schemas.xmlsoap.org/soap/envelope/; 
xmlns:ns="http://www.cisco.com/AXL/API/8.5;>



{sql_query}


'''
xml_request = wrapper.format(sql_query=sql_query)
r = sess.post(url, headers=req_headers, data=xml_request, verify=False)
xml_root = _parse_unicode_xml(r.text)
result = [{y.tag: y.text for y in x.iterchildren()} for x in xml_root.
iter('row')]
return result


devices_query = 'SELECT * FROM device'

devices = ccm_execute(devices_query)

for device in devices:
print(device)

Thanks,

Nathan

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.