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

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

Output:

SQLite: