Most direct solution
Can't get any simpler than a one-liner solution using list comprehension. It also is the most direct method:
[col for col in result.keys()]
# return: ['id', 'name']
@Saul's answer also works, but you'll need to be careful about iterating over only the first element through each cursor.description
, lest you get a bunch of None
in each tuple of the returned list.
It also is less-efficient, because you need to iterate through the ResultProxy
, access the cursor.description
attribute and for each of them only retrieve the element at index 0.
Using timeit
in Python with 500,000 iterations showed the speed difference (0.016 vs 0.011):
connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")
def cursfunc():
return [ i[0] for i in result.cursor.description ]
print(timeit.timeit("cursfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01606178
While the proposed solution completes in ~30% less time:
connection = create_engine('sqlite:///rcsample.db').connect()
result = connection.execute("select * from response")
def keysfunc():
return [col for col in result.keys()]
print(timeit.timeit("keysfunc()", setup="from __main__ import cursfunc", number=500000))
# return: 0.01097001
In fact, my suspicion is that the time difference could be greater on a table with more columns than the obviously simplified example above.
In Practice: keys and values
In practice, you'd probably want to print both the key and values dynamically. There are two ways you can go about it. The first:
results = conn.execute('SELECT * FROM salesperson')
[{column:value for column, value in result.items()} for result in results]
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name':
# 'Margaret'}, {'id': 3, 'name': 'Anna'}]
Alternatively using unpacking:
rows = conn.execute('SELECT * FROM salesperson LIMIT 2').fetchall()
print([{**row} for row in rows])
# returns: [{'id': 1, 'name': 'John Doe'}, {'id': 2, 'name': 'Margaret'}]
Both of these methods are direct and pythonic, while also exempt the programmer from having to specify (or know beforehand) the columns names explicitly.