How to select a column by its name in Python

In Python, when we are working with databases, the SELECT statement is what we are using most of the time to get the data from the SQLite database.

We retrieve the data in different ways, but the most popular way of retrieving data is using the column name of the table to retrieve the data from a table in python.

Select a Column by its name in Python

In python, to select a column by a name in the database you have to use the column name explicitly. Use the [SELECT] query with the column name to select a column by its name.

import sqlite3
import pandas as pd
# Create your connection.
con = sqlite3.connect('sqlite.db')
result =con.execute("SELECT Name FROM Student")
for row in result.fetchall():
    print(row[0] )       
con.commit()
con.close()

Output

The output of the code is the name of the students available in the student database table.

AlixaProDev
Alixawebdev
AskALixa

Print the data of all database columns in python

If you want to get all data for each column. you can do something like this.

import sqlite3
import pandas as pd
# Create your connection.
con = sqlite3.connect('sqlite.db')
result =con.execute("SELECT * FROM Student")
for row in result.fetchall():
    print(row)       
con.commit()
con.close()

output of the code

('AlixaProDev', 'CS', 19.0)
('Alixawebdev', 'BBa', 21.0)
('AskALixa', 'Software', 22.0)

Get column by its name even if you do not know column names in Python

There are cases where you do not actually remember the column name of the table. In this case, you can query the master database to get the column names.

Check the following code to get the column names of the database in python. you should still remember the table name of the database.

import sqlite3
import pandas as pd
# Create your connection.
con = sqlite3.connect('sqlite.db')
sql = "select * from Student where 1=0;"
d=con.execute(sql)
column= [r[0] for r in d.description]
print(column)      
con.commit()
con.close()

Output of the code

The output of the code is the list of the columns available in the database.

['Name', 'Course', 'Age']

Summary and Conclusion

In this python article, we have seen how you can use the column name to get the data from the database in python. you can also find the column’s name if you do not know about it. but you should know the table name at least.