How to concatenate structs in a loop in python

user_10028126 0
Viewed 336

I am trying to search for all users in an sql database whose first names are "blah" and return that data to my html through an ajax call. I have this functioning with a single user like this: 

user = db.execute(
            'SELECT * FROM user WHERE genres LIKE ?', (str,)
        ).fetchone()
        user_details = {
            'first': user['first'],
            'last': user['last'],
            'email': user['email']
        }
        y = json.dumps(user_details)
        return jsonify(y)

Now for multiple users I want the struct to look something like this:

users{
    user1_details = {
        'first': user['first'],
        'last': user['last'],
        'email': user['email']
    }
    user2_details = {
        'first': user2['first'],
        'last': user2['last'],
        'email': user2['email']
    }
    user3_details = {
        'first': user3['first'],
        'last': user3['last'],
        'email': user3['email']
    }
}

generating each user_details in a loop. I know I can use fetchall() to find all the users, but how do I concatenate the details?

sql python
2 Answers

Fetch all the rows after the query, then structure the results as you'd like.

Example:

   db = mysql.connection.cursor()

   # query
   db.execute('SELECT * FROM user')

   # returned columns
   header = [x[0] for x in db.description]

   # returned rows
   results = db.fetchall()

   #data to be returned
   users_object = {}

   #structure results 
   for result in results:
        users_object[result["user_id"]] = dict(zip(header,result))

   return jsonify(users_object)

As you can see in under "#structure results", you just loop through the results and insert the data for each row into the users_object with key equal to "user_id" for example.

If you want the results in an array just convert users_object into an array e.g. users_array and append the dict to the array within the loop instead

user_38636600

The keys in the desired users dictionary do not seem particularly useful so you could instead build a list of user dicts. It's easy to go directly from fetchall() to such a list:

result = db.execute('SELECT * FROM user WHERE genres LIKE ?', (str,))
users = [{'first': first, 'last': last, 'email': email} for first, last, email in result.fetchall()]
return jsonify(users)

To return a dict containing the user list:

return jsonify({'users': users})
mhawke_219450
Related