python pyodbc query loop only returns one result
Question: I've included the code below. Basically, I have a table (called `forumPosts`)
that has the columns `user_id`,`children`,`content`,`id`. `children` is a
string of ids for `forumPosts` that are follow up forum posts (I'm not in
charge of organizing children posts into the same table as parent posts). This
string is formatted like `'["id1","id2",...]'`.

My goal is to produce a data set that looks like:

    
    
    parent_user_id,child_user_id_1,child_user_id_2,...
    

The issue here is that the below code only prints a single output. Why does it
do this? Thanks for your help.

    
    
    import pyodbc
    
    
    #connect to database, create db cursor
    cnxn = pyodbc.connect('cnxnstring', autocommit=True)
    cursor = cnxn.cursor()
    
    for row in cursor.execute("select user_id,children from myTable where children!='[]' and user_id!='null'"):
        children=row[1].replace('"','').replace('[','').replace(']','').split(',')
        userid=row[0]
        for child in children:
            print cursor.execute("select user_id from myTable where id = ?",child).fetchone()
    

sample data for the columns requested:

    
    
      id           |  user_id    |  children
    hgjegh4pjbr44p | gd6v7134AUa | ["asdf34dfg3sdfq", "asdegh4pjbrxx3"]
    asdegh4pjbrxx3 | xzf7134AUax | ["hgjegh4pjbr44p"]
    hgjegh4pjbr44p | NULL        | []
    asdf34dfg3sdfq | adfcv34skax | []
    


Answer: ## Explanation

Using the sample data provided, the output is:

    
    
    ('adfcv34skax', )
    None
    

A row for `asdegh4pjbrxx3` ID is not returned because of leading whitespace.
This can be observed by changing the last `print` line to:

    
    
    print child
    

Which outputs:

    
    
    asdf34dfg3sdfq
     asdegh4pjbrxx3
    hgjegh4pjbr44p
    

`' asdegh4pjbrxx3'` isn't equal to `'asdegh4pjbrxx3'`, which returns a null
result.

## Solution

Replace the `children` assignment line with the following to strip necessary
characters and whitespace:

    
    
    children=[child.strip('"[] ') for child in row[1].split(',')]
    

I think a list comprehension with the single `strip` call is more readable
than nested `replace`.

Alternately,
[ast.literal_eval](http://docs.python.org/2/library/ast.html#ast.literal_eval)
should work as well:

    
    
    children=ast.literal_eval(row[1])
    

