Skip to content
Related Articles

Related Articles

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

Improve Article
Save Article
Like Article
  • 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()

Output:

SQLite:


My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!