Python sqlalchemy getting column names dynamically?

Question

#!/usr/bin/env python # -*- coding: utf-8 -*- from sqlalchemy import create_engine connection = create_engine('mysql://user:passwd@localhost:3306/db').connect() result = connection.execute("select * from table") for v in result: print v['id'] print v['name'] connection.close() 

How can I dynamically use COLUMNS column names? in this case id and name

Answer 1

You can find columns by calling result.keys() , or you can access them by calling v.keys() inside a for loop.

Here's an example using items() :

 for v in result: for column, value in v.items(): print('{0}: {1}'.format(column, value)) 

Answer 2

something like that

headers=[ i[0] for i in result.cursor.description ]

same question here return column names from pyodbc execute () statement

Answer 3

The most direct solution

It could not be simpler than a one-line solution using list comprehension. This is the most direct method:

 [col for col in result.keys()] # return: ['id', 'name'] 

@Saul's answer also works, but you need to be careful when cursor.description over only the first element for each cursor.description so that you don't get a bunch of None in every tuple of the returned list.

It is also less efficient because you need ResultProxy to access cursor.description and for each of them get an element with index 0 only.

Using timeit in Python with 500,000 iterations showed a difference in speed (0.016 versus 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 

Although the proposed solution is completed 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, I suspect that the time difference in a table with a large number of columns may be greater than in the explicitly simplified example above.

In practice: keys and values

In practice, you probably want to print the key and values ​​dynamically. There are two ways to do this. 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, but also free the programmer from having to explicitly specify (or know in advance) the column names.