Get SQL headers from Numpy Array in python

Question

With below, I can get the row and col data from SQL:
How would I get the table headers as part of the result set or array.?

    top = csr.execute("Select * from bigtop")
    d=list(top)
    a = np.asarray(d, dtype='object')
    print a

Just like I asked here: How do I create a CSV file from database in Python?

Answer 1

This is a self contained example that illustrates the general idea. numpy.recarray is your friend,

from sqlite3 import connect
from numpy import asarray

db = connect(":memory:")
c = db.cursor()
c.execute('create table bigtop (a int, b int, c int)')

for v in [(1,2,3),(4,5,6),(7,8,9)]:
    c.execute('insert into bigtop values (?,?,?)',v)

s = c.execute('select * from bigtop')

h = [(i[0],int) for i in c.description]

# You can also use 'object' for your type
# h = [(i[0],object) for i in c.description]

a = asarray(list(s),dtype=h)

print a['a']

gives the first column,

[1 4 7]

and,

print a.dtype

gives the name and type of each column,

[('a', '<i4'), ('b', '<i4'), ('c', '<i4')]

alternatively, if you used object as your type, you get,

[('a', '|O4'), ('b', '|O4'), ('c', '|O4')]

Answer 2

csr.description should have the headers

Answer 3

If you wanted the column names as the first line in your array, you would do

top = csr.execute("Select * from bigtop")
d=list(top)
a = np.asarray([[x[0] for x in top.description]] + d, dtype='object')

and get something like

array([[heading1, heading2, heading3, ...],
       [val1, val2, val3, ...],
           ...
           , dtype=object)