Myujth

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?

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

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)