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:
