Serializing Thin AXL SQL Query Responses with python-zeep

I have a buddy who asked for some help in constructing an SQL query to get all phones from CUCM that used a VPN Profile, which were configured in his system in one of several Common Phone Configuration elements.  I helped him construct this:

SELECT FROM device d
INNER JOIN commonphoneconfig cpc ON d.fkcommonphoneconfig=cpc.pkid
INNER JOIN vpnprofile vp ON cpc.fkvpnprofile = vp.pkid
WHERE = ‘vpn-profile-name’

However, he wanted then to be able to run this using python, as he wanted to match this up against registered phones on the system to analyse active IP registrations to determine which phones were remotely registered.

Although he’d done a rudimentary setup with suds, he wanted to migrate this to use python-zeep.

from collections import OrderedDict

from zeep import Client
from zeep.cache import SqliteCache
from zeep.transports import Transport
from zeep.plugins import HistoryPlugin
from zeep.exceptions import Fault
from zeep.helpers import serialize_object

from requests import Session
from requests.auth import HTTPBasicAuth
from urllib3 import disable_warnings
from urllib3.exceptions import InsecureRequestWarning
from lxml import etree


USERNAME = 'administrator'
PASSWORD = 'ciscopsdt'
FQDN = ''
WSDL = 'file://C://path//to//AXLAPI.wsdl'
ADDRESS = "https://{fqdn}:8443/axl/".format(fqdn=FQDN)

def element_list_to_ordered_dict(elements):
    return [OrderedDict((element.tag, element.text) for element in row) for row in elements]

def sql_query(service, sql_statement):
    axl_resp = service.executeSQLQuery(sql=sql_statement)
        return element_list_to_ordered_dict(serialize_object(axl_resp)["return"]["rows"])
    except KeyError:
        # single tuple response
        return element_list_to_ordered_dict(
    except TypeError:
        # no SQL tuples
        return serialize_object(axl_resp)["return"]

def sql_update(service, sql_statement):
    axl_resp = service.executeSQLUpdate(sql=sql_statement)
    return serialize_object(axl_resp)["return"]["rowsUpdated"]

def main():
    session = Session()
    session.verify = False
    session.auth = HTTPBasicAuth(USERNAME, PASSWORD)
    transport = Transport(cache=SqliteCache(), session=session, timeout=60)
    history = HistoryPlugin()
    client = Client(wsdl=WSDL, transport=transport, plugins=[history])
    axl = client.create_service(BINDING_NAME, ADDRESS)

    sql_statement = "SELECT " \
                    "FROM device d " \
                    "INNER JOIN commonphoneconfig cpc ON d.fkcommonphoneconfig=cpc.pkid " \
                    "INNER JOIN vpnprofile vp ON cpc.fkvpnprofile = vp.pkid " \
                    "WHERE = 'vpn-profile-name'"
        print(sql_query(service=axl, sql_statement=sql_statement))
    except Fault as thin_axl_error:
            for hist in [history.last_sent, history.last_received]:
                print(etree.tostring(hist["envelope"], encoding="unicode", pretty_print=True))
        except (IndexError, TypeError):
            # catch cases where it fails before being put on the wire

if __name__ == '__main__':

I’ve included some helper functions for parsing the xml and for handling the AXL output for both query and update cases.  I’ve also included a helpful xml dump for troubleshooting.

Happy coding!


12 thoughts on “Serializing Thin AXL SQL Query Responses with python-zeep

  1. hi, thanks for this. just wonder on line 35 return element_list_to_ordered_dict(serialize_object(axl_resp)[“return”][“rows”])
    shouldnt be row without s ?

    Liked by 1 person

      1. Hello Jonathan,
        I am studying the program. I don’t understand about “element_list_to_ordered_dict(serialize_object(axl_resp)[“return”][“rows”])”. [“return”] and [“rows”] what do these two parameters do?


      2. Hi Yue,
        The response changes depending on what you get back from AXL. If there’s one SQL row returned, the XML response is of the form . If multiples, its . There’s also a case were there’s no row returned at all.

        The try/except code parses this low-level stuff.


  2. Awesome, I remember having to deal with this with Ruby, checking the type of the serialized return to be either hash, or array of hashes, etc. Moving to Python I see this is still the case but it’s pretty simple to handle. Great post.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.