Python

Python RSS

sqlite3-- how to see column names for table

•

Join Date: May 2008

Posts: 6

Reputation: jmroach is an unknown quantity at this point

Solved Threads: 0

jmroach

Offline

Newbie Poster

sqlite3-- how to see column names for table

May 15th, 2008

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:

 Help with Code Tags 
 python Syntax (Toggle Plain Text) 
con = sqlite3.connect( ":memory:" )
cur = con.cursor()
cur.execute( "CREATE TABLE tablename ( d1 varchar, d2, varchar )" )
cur.execute( "INSERT INTO tablename values (?,?)", tuple(datarow) )
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

•

Join Date: Jun 2005

Posts: 146

Reputation: G-Do is an unknown quantity at this point

Solved Threads: 28

G-Do

Offline

Junior Poster

Re: sqlite3-- how to see column names for table

May 15th, 2008

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:

 Help with Code Tags 
 python Syntax (Toggle Plain Text) 
cursor.execute("PRAGMA table_info(tablename)")
print cursor.fetchall()
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!

Vi veri veniversum vivus vici

•

Join Date: May 2008

Posts: 6

Reputation: jmroach is an unknown quantity at this point

Solved Threads: 0

jmroach

Offline

Newbie Poster

Re: sqlite3-- how to see column names for table

May 15th, 2008

Perfect. Thanks a ton, G-Do!

•

Join Date: Nov 2006

Posts: 224

Reputation: bugmenot is an unknown quantity at this point

Solved Threads: 31

bugmenot

Offline

Posting Whiz in Training

Re: sqlite3-- how to see column names for table

May 28th, 2008

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.

 Help with Code Tags 
 Python Syntax (Toggle Plain Text) 
from sqlite3 import dbapi2 as sqlite
cur.execute("SELECT * FROM SomeTable")
col_name_list = [tuple[0] for tuple in cur.description]
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)