How to Get a List of Column Names on Sqlite3 Database

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.

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 create a table in sqlite3 with column names taken from a list?

You can do something like this:

import contextlib
import sqlite3

fields = ["field1", "field2", "field3"]

with contextlib.closing(sqlite3.connect(':memory:')) as conn:
    cursor = conn.cursor()
    columns = ", ".join("{field} TEXT".format(field=field) for field in fields)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS your_table(
         id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
         {columns}
    )
    """.format(columns=columns))
    conn.commit()

But, you should consider using an ORM, like SqlAlchemy. Here is a tutorial for that: https://www.pythoncentral.io/introductory-tutorial-python-sqlalchemy/

Edit

To go further, your insert statement could be built like this:

stmt = ("INSERT INTO mytable ({fields}) VALUES ({marks})"
.format(fields=", ".join(fields), marks=", ".join("?" * len(fields))))

For instance:

INSERT INTO mytable (field1, field2, field3) VALUES (?, ?, ?)

Issue with replacing NULL sqlite3 database column values with other types in Python 3?

The NULL value in SQL is special, and to compare values against it you need to use the IS and IS NOT operators. So your query should be this:

UPDATE table
SET animal = 'cat'
WHERE animal IS NULL AND id = 32;

NULL by definition means "unknown" in SQL, and so comparing a column directly against it with = also produces an unknown result.

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;

How to get a list of column names with Sqlite.swift?

Assuming you already have a database connection called db set up, to get a list of the column names, you can use the following code:

do {

    let tableInfo = Array(try db.prepare("PRAGMA table_info(table_name)"))
    for line in tableInfo {
        print(line[1]!, terminator: " ")
    }
    print()

} catch _ { }

where table_name is replaced with the literal string of your table's name.

You can also add

print(tableInfo)

to see more pragma info about your table.

Credits

Thanks to this answer for clues of how to do this.

Example Function

Tested routine from Joe Blow to save a little typing:

func findColumns(_ tableName:String) {

    var asAnArray:[String] = []
    do {
        let s = try db!.prepare("PRAGMA table_info(" + tableName + ")" )
        for row in s { asAnArray.append(row[1]! as! String) }
    }
    catch { print("some woe in findColumns for \(tableName) \(error)") }

    let asAString = asAnArray.joined(separator: ",")

    print(asAnArray)
    print(asAString)
}

---

Related Topics