How to Show all Columns in the SQLite Database using Python ?

Last Updated : 30 Apr, 2021

In this article, we will discuss how we can show all columns of a table in the SQLite database from Python using the sqlite3 module.

Approach:

Connect to a database using the connect() method.
Create a cursor object and use that cursor object created to execute queries in order to create a table and insert values into it.
Use the description keyword of the cursor object to get the column names. The description keyword specifies only the columns of a table in a two-dimensional tuple consisting of none values and the column names only.

data=cursor.execute('''SELECT * FROM table_name''')
print(data.description)

The above code displays all the columns of a given table in a two-dimensional tuple.

Display the data in the table by executing the below query using the cursor object.

SELECT * FROM table_name

Finally, commit the changes in the database and close the connection.

Below is the Implementation:

Creating the table

In the below program we connect to a database named gfg.db, then we create an EMPLOYEE table and insert values into it. Finally, we commit the changes in the database and terminate the connection.

Python3

# Import module
import sqlite3

# Connecting to sqlite
conn = sqlite3.connect('gfg1.db')

# Creating a cursor object using the cursor() method
cursor = conn.cursor()

# Creating table
table ="""CREATE TABLE EMPLOYEE(FIRST_NAME VARCHAR(255), 
                                LAST_NAME VARCHAR(255),
                                AGE int, 
                                SEX CHAR(1), 
                                INCOME int);"""
cursor.execute(table)
print('Table Created!')

# Queries to INSERT records.
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
                       VALUES ('Anand', 'Choubey', 25, 'M', 10000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
                    VALUES ('Mukesh', 'Sharma', 20, 'M', 9000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
                    VALUES ('Ankit', 'Pandey', 24, 'M', 6300)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
                    VALUES ('Subhdra ', 'Singh', 26, 'F', 8000)''')
cursor.execute('''INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) 
                    VALUES ('Tanu', 'Mishra', 24, 'F', 6500)''')

print('Data inserted into the table')

# Commit your changes in the database    
conn.commit()

# Closing the connection
conn.close()

Output:

Table Created!
Data inserted into the table

Retrieving columns from the table:

Now as we have already created a table and inserted values into the table in a database. We will connect to the previous database where the EMPLOYEE table is created. Then we will first display all the columns and then the data values in the column.

Python3

# Import module
import sqlite3

# Connecting to sqlite
conn = sqlite3.connect('gfg.db')

# Creating a cursor object using the cursor() method
cursor = conn.cursor()


# Display columns
print('\nColumns in EMPLOYEE table:')
data=cursor.execute('''SELECT * FROM EMPLOYEE''')
for column in data.description:
    print(column[0])
    
# Display data
print('\nData in EMPLOYEE table:')
data=cursor.execute('''SELECT * FROM EMPLOYEE''')
for row in data:
    print(row)
    
# Commit your changes in the database    
conn.commit()

# Closing the connection
conn.close()