Lets say I have this code:
sql_query="select actor.actor_id from actor where actor='%s'"
cursor.execute=(sql_query,(actorID))
result=cursor.fetchall()
return(result)
What should I do to my code so the results are in the form of a list of tuples?Also I want the first tuple to be the name of the columns of my query.
For example: [(“Name”, “Id”,),(“Jim”,7,),(“Tom”,13,)]
Here is another answer that simple grabs the column names and stores them in a nested tuple:
cur.execute('''DESCRIBE patient_login''')
results = cur.fetchall()
nested_tuple_list = []
nested_tuple_list_2 = []
for result in results:
result = ((result[0]))
nested_tuple_list.append(result)
nested_tuple_list = tuple(nested_tuple_list)
nested_tuple_list_2.append(nested_tuple_list)
print(nested_tuple_list_2)
The describe command in SQL will Describe the table by telling you which columns exist within the table, and various characteristics of the table such as primary key, datatype ect. This command will return a tuple of the described data. Here we search each result and grab the first index in the results for each of the nested tuples. The first index corresponds with the column name in the for loop. We can append this to the first empty list. After we append all of the column names, we change the list to a tuple. We then append that tuple to the list and have all of the column names in the list within a tuple. Here is the output:
[('ID', 'username', 'password')]
If you want each element in the list to be a tuple in it of itself, here is the code:
nested_tuple_list = tuple(nested_tuple_list)
nested_tuple_list_2 = [(x,) for x in nested_tuple_list]
print(nested_tuple_list_2)
We can do a list comprehension with x representing each column, and here is our output for each of my columns in the DataFrame:
[('ID',), ('username',), ('password',)]