How to get a list of column names on Sqlite3 database?

When working with SQLite3 databases, retrieving the list of column names is a highly practical operation, especially when you are unfamiliar with the database structure. Several methods can achieve this, and I will introduce two commonly used approaches below:

Method 1: Using PRAGMA table_info()

PRAGMA is a powerful command in SQLite for retrieving database metadata. To obtain the column names for a specific table, use PRAGMA table_info(table_name);. This command returns detailed information about each column in the table, including column names and data types.

Example Code (assuming the table name is employees):

sql
PRAGMA table_info(employees);

Here is an example of handling this command using the sqlite3 library in Python:

python
import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute("PRAGMA table_info(employees);")
columns = cursor.fetchall()
column_names = [column[1] for column in columns]  # Extract column names

print(column_names)

Method 2: Using cursor.description

When using the sqlite3 library in Python, after executing any query, you can retrieve the column names of the result set using the description attribute of the cursor. This method is particularly convenient when you have already executed some queries.

Example Code:

python
import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM employees LIMIT 1;")
# Retrieve column names
column_names = [description[0] for description in cursor.description]

print(column_names)

This code first executes a query on the employees table but limits the result to one row (for efficiency). It then retrieves the column names using cursor.description.

Summary

These two methods have their pros and cons. PRAGMA table_info() provides more metadata beyond column names, while cursor.description is ideal for quickly retrieving column names when query results are already available. Choose the appropriate method based on your specific needs. These skills are particularly valuable when dealing with unknown database structures, as they help developers quickly understand and manipulate data.