How to Get a List of Column Names in SQLite

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

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]

How can I get the list of a columns in a table for a SQLite database?

What you're looking for is called the data dictionary. In sqlite a list of all tables can be found by querying sqlite_master table (or view?)

sqlite> create table people (first_name varchar, last_name varchar, email_address varchar);
sqlite> select * from sqlite_master;
table|people|people|2|CREATE TABLE people (first_name varchar, last_name varchar, email_address varchar)

To get column information you can use the pragma table_info(table_name) statement:

sqlite> pragma table_info(people);
0|first_name|varchar|0||0
1|last_name|varchar|0||0
2|email_address|varchar|0||0

For more information on the pragma statements, see the documentation.

How to find information on all columns in a SQLite database?

You can query the table sqlite_master to get all the tables of the database and then use the table valued function pragma_table_info() to get for each table its columns:

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT at.name table_name, pti.*
FROM all_tables at INNER JOIN pragma_table_info(at.name) pti
ORDER BY table_name;

Retrieve column names from sqlite3 database with php

Refer to SQLite.org - PRAGMA Statements:

PRAGMA table_info(sqlite_master);

To fit it into your PHP implementation:

<?php // Display all sqlite tables
    $db = new SQLite3('data.db');
    $tablesquery = $db->query("PRAGMA table_info(sqlite_master);");

    while ($table = $tablesquery->fetchArray(SQLITE3_ASSOC)) {
        echo $table['name'] . '<br />';
} ?>

How to get column names with query data in sqlite3?

Yes, after logged into sql prompt mode, execute the following each at a time:

.header on
.mode column

How to get list of column names of a sqlite db file

use the pragma table_info(spamtable) command. The table names will be index 1 of the returned tuples.

---

Related Topics