[python] Is there a way to get a list of column names in sqlite?

I want to get a list of column names from a table in a database. Using pragma I get a list of tuples with a lot of unneeded information. Is there a way to get only the column names? So I might end up with something like this:

[Column1, Column2, Column3, Column4]

The reason why I absolutely need this list is because I want to search for a column name in the list and get the index because the index is used in a lot of my code.

Is there a way of getting a list like this?

Thanks

Assuming that you know the table name, and want the names of the data columns you can use the listed code will do it in a simple and elegant way to my taste:

import sqlite3

def get_col_names():
#this works beautifully given that you know the table name
    conn = sqlite3.connect("t.db")
    c = conn.cursor()
    c.execute("select * from tablename")
    return [member[0] for member in c.description]

I'm not a sqlite user, so take this with a grain of salt; most RDBM's support the ANSI standard of the INFORMATION_SCHEMA views. If you run the following query:

SELECT *
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'your table'

You should get a table which lists all of the columns in your specified table. IT may take some tweaking to get the output you want, but hopefully its a start.

You can get a list of column names by running:

SELECT name FROM PRAGMA_TABLE_INFO('your_table');
name      
tbl_name  
rootpage  
sql

You can check if a certain column exists by running:

SELECT 1 FROM PRAGMA_TABLE_INFO('your_table') WHERE name='sql';
1

Reference:

https://www.sqlite.org/pragma.html#pragfunc

Another way of using pragma:

> table = "foo"
> cur.execute("SELECT group_concat(name, ', ') FROM pragma_table_info(?)", (table,))
> cur.fetchone()
('foo', 'bar', ...,)

Quick, interactive way to see column names

If you're working interactively in Python and just want to quickly 'see' the column names, I found cursor.description to work.

import sqlite3
conn = sqlite3.connect('test-db.db')
cursor = conn.execute('select * from mytable')
cursor.description

Outputs something like this:

(('Date', None, None, None, None, None, None),
 ('Object-Name', None, None, None, None, None, None),
 ('Object-Count', None, None, None, None, None, None))

Or, quick way to access and print them out.

colnames = cursor.description
for row in colnames:
    print row[0]

Outputs something like this:

Date
Object-Name
Object-Count

I like the answer by @thebeancounter, but prefer to parameterize the unknowns, the only problem being a vulnerability to exploits on the table name. If you're sure it's okay, then this works:

def get_col_names(cursor, tablename):
    """Get column names of a table, given its name and a cursor
       (or connection) to the database.
    """
    reader=cursor.execute("SELECT * FROM {}".format(tablename))
    return [x[0] for x in reader.description] 

If it's a problem, you could add code to sanitize the tablename.

I use this:

import sqlite3

    db = sqlite3.connect('~/foo.sqlite')
    dbc = db.cursor()
    dbc.execute("PRAGMA table_info('bar')"
    ciao = dbc.fetchall()

    HeaderList=[]
    for i in ciao:
        counter=0
        for a in i:
            counter+=1
            if( counter==2):
                HeaderList.append(a)

print(HeaderList)

It is very easy.
First create a connection , lets name it, con. Then run the following code.

get_column_names=con.execute("select * from table_name limit 1")
col_name=[i[0] for i in get_column_names.description]
print(col_name)

You will get column name as a list

As far as I can tell Sqlite doesn't support INFORMATION_SCHEMA. Instead it has sqlite_master.

I don't think you can get the list you want in just one command. You can get the information you need using sql or pragma, then use regex to split it into the format you need

SELECT sql FROM sqlite_master WHERE name='tablename';

gives you something like

CREATE TABLE tablename(
        col1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        col2 NVARCHAR(100) NOT NULL,
        col3 NVARCHAR(100) NOT NULL,
)

Or using pragma

PRAGMA table_info(tablename);

gives you something like

0|col1|INTEGER|1||1
1|col2|NVARCHAR(100)|1||0
2|col3|NVARCHAR(100)|1||0

An alternative to the cursor.description solution from smallredstone could be to use row.keys():

import sqlite3
connection = sqlite3.connect('~/foo.sqlite')
connection.row_factory = sqlite3.Row
cursor = connection.execute('select * from bar')
# instead of cursor.description:
row = cursor.fetchone()
names = row.keys()

The drawback: it only works if there is at least a row returned from the query.

The benefit: you can access the columns by their name (row['your_column_name'])

Read more about the Row objects in the python documentation.