Proper system.db.runNamedQuery usage in python

#1

I frequently have troubles where I want to run a named query within a python script, but when I try to iterate through that dataset to format the returns I end up crashing my designer. I don’t think I am doing anything that crazy, but I am wondering if there is a best practices for handling the returned datasets in the designer?

image

This is the result of the Query in MSSMS, and I just want to manipulate those two results into a singular string formatted how I want. The command I’m currently using in my script is:

roles = system.dataset.toPyDataSet(system.db.runNamedQuery(self.session.custom.ProjectName, “UserManagement/Get Roles for a UserName”, {‘UserName’:row.get(‘username’)}))

and I want the output to be “Supervisor, test4”.

0 Likes

#2
# Not a great example, but (something very much like) this works for me
roles = system.dataset.toPyDataSet(system.db.runNamedQuery(“UserManagement/Get Roles for a UserName”, {‘UserName’:row.get(‘username’)}))
entries = []
for i in range (len(roles)):
    entries.append(roles[i][0])
return ', '.join(map(str, entries))

You should note that in my example I’m using 8.0.6, so runNamedQuery no longer requires a project name argument. I have no idea how you’re acquiring the row variable you’re using to get the user’s name, and so I didn’t use arguments in my query. My result came back as
"Supervisor, OtherRole, Third".

If this isn’t helpful, could you provide any stacktrace you’ve come across when your Designer crashes?

0 Likes

#3

I was using

for role in roles:
    entries.append(role)

to loop through and create my string, and it kept crashing.

0 Likes

#4

Right, so the roles list was a list of PyDataSet rows, not Strings, so it was attempting to join a bunch of items which weren’t strings with an actual string, and I’m not sure if PyDataSet rows have any way to toString themselves because they could contain anything.

The solution I provided is pretty fragile, but hopefully it got you to a spot where you could continue…

0 Likes