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 = '''
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/";
xmlns:ns="http://www.cisco.com/AXL/API/8.5";>
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLQuery>
<sql>{sql_query}</sql>
</ns:executeSQLQuery>
</soapenv:Body>
</soapenv:Envelope>'''
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.
