Python sqlite3 row keys

How to get column names from Sqlite database table in Python

To get the names of column form the sqlite table in Python use the sqlite3 module in python. sqlite3 module is a python built-in module. sqlite3 is a helpful python module if you are working database related work in python.

Do you want to get a list of colum names form a table in python? In this article I will show you how we can get the name of Columns in a Sqlite database table using python.

Prerequistes

We will be working with Sqlite table if you donot know how to create a sqlite database table and how to insert data in it. Please follow the follwoing tutorials.

• create a sqlite database table
• insert data in sqlite database table

Get column names from Sqlite database table in Python

In Python sqlite3 module do not have any direct method to get the name of column names present in a SQLite database. Instead you have to follow the following steps to get the name of Columns in a Sqlite database.

1. import sqlite3 module in Python using import statement
2. use the sqlite.connect() function to get connection to database
3. use the connection.cursor() function to get the cursor
4. use the cursor.description attribute of the cursor object
5. the description attributes contain all the informations about table
6. you need to filter the table names out of all the inofrmation

with the help of following above steps you can very easily get the column name of a sqlite database.Python code to get the database column names using sqlite3 module.

Python code to get the database column names using sqlite3 module.

 import sqlite3 connection = sqlite3.connect('sqlite.db') cursor = connection.execute('select * from Student') names = list(map(lambda x: x[0], cursor.description)) connection.close() print(names)

Output of the above code

I have had already a table in my slqite database with the name ‘sqlite.db’ you name it whatevery you want.

Using list comprehension to get the name of the columns present in Sqlite table

Alternatively we can also use the list comprehension to get the list of column names from a sqlite table in python.

check out the following python code that return the column names available in a sqlite database. We have used the python sqlite3 module and the list comprehension technique.

 import sqlite3 connection = sqlite3.connect('sqlite.db') cursor = connection.execute('select * from Student') names = [description[0] for description in cursor.description] connection.close() print(names)

Output of the above code

The output of the above code is similar to the first one. It also do the same task just doing it with another method.Output

Print List of column names from a sqlite database in Python

Another way To get the list of column names from a sqlite database table is to use the cursor.fetchone() mehtod. fetchone() is a funciton available in sqlite3 module in pythonv. sqlite3 module is a python built in module and helps us in working with SQLite databases.Steps To get the list of columns using the fecthone() funciton.

1. import the sqlite3 module
2. create connecton using sqlite3.connect() funciton
3. use the sqlite3.Row attribute of the sqlite3 module.It will help us change the connection.row_factory default row style into Sqlitet3 style.
4. use the fecthone() funciton
5. get the keys from the row using keys() funciton

Follwoing is a python program that use the cursor.fetchone() python funciton to get the colun names out of a sqlite3 database table in python.

 import sqlite3 connection = sqlite3.connect('sqlite.db') connection.row_factory = sqlite3.Row cursor = connection.execute('select * from Student') # instead of cursor.description: row = cursor.fetchone() names = row.keys() connection.close() print(names)

Output of the code

in the student table we have only 3 columns. SO the output will be a lis containing the names of theese columns.Output

Get the informations of a Sqlite database table

To get the complete Informationo of a sqlite database in python, use the sqlite.description funciton. It will return the complete description of the sqlite database table.Steps To get the complte informationo of a table

1. Connect to the database using sqlite.connect() funciton
2. create a cursor using the connection.cursor() funciton
3. use the cursor.description to get all information of a sqlite table

Follwoing is a python code that extract all the information of a sqlite datbase including columns names.

 import sqlite3 connection = sqlite3.connect('sqlite.db') connection.row_factory = sqlite3.Row cursor = connection.execute('select * from Student') # cursor.description: desc = cursor.description connection.close() print(desc) 

Get columns using the table_info () funciton

We can also execute the SQLite command that returns all the information of the SQlite database. To get the name of the columns from a sqlite database table we can use the standard Sql query PRAGMA table_info(table_name) . This is the Sql statement that returns all the information of the sqlite database. We just need to use the curosr.execute() funcitoon of the sqlite3 module to get the name of the columns of sqlite datbase.Steps to get the columns name using the standard sql statement

1. import the sqlite3 module in python
2. use the connect() funciton for connection
3. use the cursor() funciton for cursor
4. execute the PRAGMA table_info(table_name) statement using the cursor.execute() funciton
5. use fetchall() funciton and select the first index items

Below is the python code that simplify the process of getting the names of the colums using the python sqlite3 module

 import sqlite3 connection = sqlite3.connect('sqlite.db') connection.row_factory = sqlite3.Row cursor = connection.execute('PRAGMA table_info(Student)') desc = cursor.fetchall() # getting names using list comprehension names = [fields[1] for fields in desc] connection.close() print(names) 

Output of the above code

The output of the code is just the name of the columns present in the Stedent table in sqlite database.

Conclusion

Use one way or the other, you will finally get the names of the columns available in the sqlite database using any of the above method. Try one which you can understand and can use it in your benefits. If you still have any doubt, make sure you reach me out using my social media.

I am a software Engineer having 4+ Years of Experience in Building full-stack applications.

Источник

Using SQLite’s Row object with Python

Figure 1 below shows a simple customer table in SQLite:

Figure 1. The table this article uses.

Consider the Python code below to connect to an SQLite database an iterate over a result set.

Figure 2a. By default, SQLite cursor.execute returns a list of tuples.

By default, the cursor.execute method resolves to a list of tuples where each tuple contains field values. Field columns queried this way cannot be read by field name—you must use the ordinal position of the column in the tuple to get its value. However, if you add this line

after establishing the connection, rows resolves to list of SQLite Row objects. Data columns represented by Row objects can be fetched by either ordinal position (like a list) or by using the field name as a key (like a dictionary). Like a dictionary you can also uses its keys() method to get the list of field names.

Figure 2b. Using the SQLite Row factory object gets you row results fetchable by field name.

The Row object is more forgiving than a simple Python dictionary because its key-based access isn’t case-sensitive (as shown above).

The SQLite docs recommend you use the built-in Row factory object because of the case-insensitivity and minimal memory overhad (as shown above). But, if you want result rows as simple Python dictionaries, you can provide your own dictionary factory like this:

Источник