Python Script for multiple ZSQL Methods

Zope
,
1

I need to run a select across two different databases so I've created two zsql methods, one for each database. The problem is that if I use statements to call the zsql methods, I get duplicate results due to dtml-in iterating.

<dtml-in blog_author_select>
<dtml-in "intranet_user_select(intranet_username)">
<option value="&dtml-id;">&dtml-fullname;</option>
</dtml-in>
</dtml-in>

That code is what I initially tried but of course due to dtml-in iterating over results, it's looping through over and over and creating a ton of duplicate data.

To resolve this, I am wanting to use a python script to call the first zsql method and then use the results of that against the second zsql method. Then I can use dtml-in to call the python script and populate my select list with the correct data.

While I can get a python script to run the zsql method and return the results, I cannot seem to figure out how to pass those results to the second zsql method in the same python script and return the correct data set.

Here's what I have thus far (and yes, I am aware it's wrong).

recordset = context.blog_author_select().dictionaries()
rownum = 1
for record in recordset:
   rownum = rownum + 1
   username = [record["intranet_username"] for record in recordset]

   mainselect = context.intranet_user_select(intranet_username=username).dictionaries()
   print mainselect
   return printed

There are two things happening here that aren't working (and possibly more):

  1. The results being returned are not correct. The intranet_user_select zsql method says (select * from table where !=username and yet those results are not being filtered out.

  2. As part of #1 the reason for this, at least I believe, is that the results are in a list and therefore not being interpreted correctly.

I think what needs to happen is that the first query (blog_author_select) is run, and those results are held row by row instead of a list. Then, a loop needs to be run on the second query, and run however many times as there are results from the first query (i.e. if there are 5 results from the first query, the loop would run 5 times on the second query).

My plan, once the correct data is returned, is to call this script via and use the variables (column names) just as I do with any other zsql method.

I hope that explained everything in detail, and I am appreciative of anyone who can help me solve this.

2

Again, you have a nested loop: at the outer level the for record in recordset, at the inner level [... for record in recordset]. Why not just use username = record["intranet_username"]? You could collect the results from your mainselect in a list you have created outside the loop.

3

I modified the username = line per your suggestion but the second query is not looping and is instead returning everything except the initial result from the first query.

I'm not entirely sure of the complete solution you are suggesting here. Can you please provide what should be a python script and then how to run that via a dtml-in so I can populate the select list with the correct data?

4

I do not solve your problems (unless you pay me); I give only hints that you can solve your problems yourself:

result = []
for author in search_authors(...):
  ...
  for main in search_main(...):
    result.append(...main...)
return result

Details for you.

5

Fair enough. Here's what I have now.

result = []
for author in context.blog_author_select():
  username = author["intranet_username"]
  for main in context.intranet_user_select(intranet_username=username):
    result.append(main)
return result

This gives me the exact same results as the nested dtml-in statements I described above. It seems to be looping blog_author_select x amount of times over intranet_user_select which gives duplicative results.

For additional context, my two zsql methods are as follows:

blog_author_select:

select intranet_username
from blog_author

intranet_user_select (param=intranet_username):

select id,
last_name || ', ' || first_name as fullname
from intranet_accounts
where (<dtml-sqlvar intranet_username type=string>!=username and enabled='t') and (account_type='4' or account_type='5')
order by last_name asc
6

Sure -- but each run should have a different username. If different "username"s should give the same result, you would need to filter them out.

7

I solved it but did it in a different way. If there's a better way to do this, I'd love to know.

Also, by looping over the results just like a dtml-in, it duplicates because it is only checking one result at a time against the other table. That was the problem in the first place, and why I cannot just have a python script doing the same thing as nested dtml-in statements.

Here's what I ended up with:

  1. I modified the intranet_user_select zsql method to

     select id,
 last_name || ', ' || first_name as fullname
 from intranet_accounts
 where (username not in(<dtml-var username>) and enabled='t') and (account_type='4' or     account_type='5')
 order by last_name asc

  2. The python script is now:

     result = []
 author = context.blog_author_select()
 username = str([record["intranet_username"] for record in author]).strip("[]")

 for main in context.intranet_user_select(username=username):
      result.append(main)
 return result