Skip to content
Related Articles

Related Articles

Improve Article

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

Output:

SQLite:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :