sqlite3-- how to see column names for table

[jmroach]

So this should be a really easy one, but i've spent a few hours searching and can't find anything that explicitly states how to do this. All i want to do is see the column names for a particular table. I created the tables like:

con = sqlite3.connect( ":memory:" )
cur = con.cursor()
cur.execute( "CREATE TABLE tablename ( d1 varchar, d2, varchar )" )
cur.execute( "INSERT INTO tablename values (?,?)", tuple(datarow) )

since the tables are created dynamically i want be able to see the column headers; 'd1' and 'd2' in this case. isn't there an easy way to do this???

Thanks in advance.

-john

[G-Do]

Hi jmroach,

You're going to want to use an sqlite pragma statement. A pragma statement lets you query database meta-data or in some cases alter the behavior of the database. The pragma that gives you column headers is called "table_info." In the example you gave below, you would use it by saying:

cursor.execute("PRAGMA table_info(tablename)")
print cursor.fetchall()

And you should get back a printed list of tuples, where each tuple describes a column header.

Hope this helps!

[jmroach]

Perfect. Thanks a ton, G-Do!

[bugmenot]

This seemed inefficient to me, so I looked around for another way. Here is a simpler way to do it, using the sqlite3.Cursor.description attribute.

from sqlite3 import dbapi2 as sqlite
cur.execute("SELECT * FROM SomeTable")
col_name_list = [tuple[0] for tuple in cur.description]

cur.description returns a tuple of information about each table. The entire tuple is : (name, type_code, display_size, internal_size, precision, scale, null_ok)

[weisburd]

bugmenot - thank you! That saved me a bunch of time.