Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get list of column names from an empty table [duplicate]

Tags:

python

sqlite

I'm using Python's sqlite3 module and would like to get a list of all columns in a table when the table doesn't have any rows.

Normally, if I create a database like

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, if the table is initially empty, c.fetchone() returns None. If there are rows committed, then I am able to get a list of the column names.

Is there another way to do it? I went through the official sqlite3 module documentation, but couldn't find anything useful in that regard.

I guess I could put some dummy data in the table, then retrieve the column names and then delete the row, but I was hoping there's a more elegant way to do it.

Edit:

Seems there are a few ways to do it:

  1. Get the SQL that was used to create the table:

    c.execute("""SELECT sql FROM sqlite_master 
    WHERE tbl_name = 'stocks' AND type = 'table'""")
    
  2. Use the PRAGMA statement from sqlite3:

    c.execute("PRAGMA table_info(stocks)")
    
  3. Use the .description field of the Cursor object

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

Of these, No.2 seems the simplest and most straight-forward. Thanks all for the help.

like image 359
mindcorrosive Avatar asked May 13 '11 14:05

mindcorrosive


1 Answers

try with :

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 items of each 7-tuple is usefull.

like image 129
Cédric Julien Avatar answered Oct 02 '22 17:10

Cédric Julien