Get a list of column names from an empty table

Question

I am using the sqlite3 Python sqlite3 and would like to get a list of all the columns in the table when the table has no rows.

Usually if I create a database, for example

 import sqlite3 conn = sqlite3.connect(":memory:") c = conn.cursor() # create the table schema c.execute('''create table stocks (date text, trans text, symbol text, qty real, price real)''') conn.commit() c.close() 

Then I can get the column names with something like

 conn.row_factory = sqlite3.Row c = conn.cursor() c.execute('select * from stocks') r = c.fetchone() print r.keys() 

The problem is that if the table is initially empty, c.fetchone() returns None . If there are rows, then I can get a list of column names.

Is there any other way to do this? I went through the official sqlite3 documentation , but could not find anything useful in this regard.

I think I could add some dummy data to the table, then get the column names and then delete the row, but I was hoping there would be a more elegant way to do this.

Edit:

There seem to be several ways to do this:

• Get the SQL that was used to create the table:

   c.execute("""SELECT sql FROM sqlite_master WHERE tbl_name = 'stocks' AND type = 'table'""") 

• Use the PRAGMA from sqlite3:

   c.execute("PRAGMA table_info(stocks)") 

• Use the .description field of a .description object

   c.execute('select * from stocks') r=c.fetchone() print c.description 

Of these, No.2 seems the simplest and most direct. Thank you all for your help.

Answer 1

try:

 conn.row_factory = sqlite3.Row c = conn.cursor() c.execute('select * from stocks') r = c.fetchone() print c.description # This will print the columns names >>> (('date', None, None, None, None, None, None), ('trans', None, None, None, None, None, None), ('symbol', None, None, None, None, None, None), ('qty', None, None, None, None, None, None), ('price', None, None, None, None, None, None)) 

As explained here , only the first elements of each 7-tuple are useful.

Answer 2

 import sqlite3 con=sqlite3.connect(":memory:") c=con.cursor() c.execute("select * from stocks") fieldnames=[f[0] for f in c.description]