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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can use sqlite3 and pep-249

import sqlite3
connection = sqlite3.connect('~/foo.sqlite')
cursor = connection.execute('select * from bar')

cursor.description is description of columns

names = list(map(lambda x: x[0], cursor.description))

Alternatively you could use a list comprehension:

names = [description[0] for description in cursor.description]

Method 2

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.

Method 3

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

Method 4

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

Method 5

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

Method 6

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]

Method 7

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

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

You will get column name as a list

Method 8

Well, I may be very late to answer this but since people still follow this thread, I just wanted to share how I use to get the list of column names in python sqlite3.

import sqlite3

def getVarList(con, tableName)
    return [fields[1] for fields in con.execute(f"PRAGMA table_info({tableName})").fetchall()]
    
conn = sqlite3.connect('foo.db')
varList = getVarList(conn, 'bar')

Method 9

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)

Method 10

Since the question has a python flag. I feel free to post a python specific answer with pandas:

import sqlite3
import pandas as pd

path_to_db = 'path/to/db'
connect = sqlite3.connect(path_to_db, isolation_level=None)
table = 'table_name'

column_list = list(pd.read_sql_query(f"SELECT * FROM {table} limit 1", connect).columns)

Method 11

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.

Method 12

Another way of using pragma:

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0