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

Question

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

Answer 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]

Answer 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.

Answer 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

Answer 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

Answer 5

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]

Answer 6

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

Answer 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

Answer 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')

Answer 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)

Answer 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)

Answer 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.

Answer 12

Another way of using pragma:

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