Sqlite3 get column names python

Get column names from SQLite with Python

Get column names with Python script from a SQLite database with this example is simple and possible! The Python solution is actually very easy.

Get the column names with python from a SQLite database is possible! The solution is actually very easy. To get a list of column names from a table in a SQLite database or getting the row or rows back as a dictionary solution is just one extra line of code when setting the connection. So yes, there is a simple way of getting the column names.

The solution is in one single line! conn.row_factory = sqlite3.Row is as easy as this!

The SLQite Row instance serves as a highly optimized row_factory for Connection objects. This tries to mimic a tuple in most of its features.

The Row instance supports mapping access by column name and index, iteration, representation, equality testing and len().

If two SLQite Row objects have exactly the same columns and their members are equal, they compare equal.

Get Column Names from SQLite with Python

Let’s initialize a table with the power of columns 🙂 :

 conn = sqlite3.connect("mydatabase.db") conn.row_factory = sqlite3.Row #this for getting the column names! 

When you now do a normal query on the SQLite database with Python you will get the column-names back with the values.

 conn = sqlite3.connect(self.sqlite_file) conn.row_factory = sqlite3.Row field = "Rotterdam" sql = '''SELECT `name`, `address`, `city` FROM `addresses` WHERE `city` = ?''' cur = conn.cursor() cur.execute(sql, field) rows = cur.fetchall() for row in rows: print(dict(row)) 

The dict(row) is telling the row data that it is of a dictionary type. So you will get the data back in dictionary style.

 'name': 'Jansen', 'address': 'Blaak 928', 'city': 'Rotterdam'> 'name': 'Klaasen', 'address': 'Maasberglaan 23', 'city': 'Rotterdam'> 'name': 'de Vos', 'address': 'Meent 198', 'city': 'Rotterdam'> 

You can also get the values with key-names like:

 for row in rows: r = dict(row)\['name'\] print(r) 

or a maybe somewhat nicer way to get the data from the SQLite table with python, is:

 for row in rows: r = dict(row) print(r\['name'\]) 

If you want a complete example look below!

### The whole SLQlite column names python script """ author: Theo van der Sluijs / PurePython url: https://itheo.tech copyright: CC BY-NC 4.0 creation date: 25-12-2018 Small tutorial how to get the column names with your row of values You need the sqlite3 for this to work. It's installed together with your python. """ import sqlite3 from sqlite3 import Error class RowsKeys: def __init__(self): self.sqlite_file = "address.db" self.conn = None # set the placeholder for the connection self.create_connection() # create the connection self.drop_table() # drop the table if it exists self.create_table() # creation the dummy table self.create_data() # for filling up the database with dummy data def create_connection(self): """ create a database connection to the SQLite database specified by db_file :db_file: self.sqlite_file :creates : self.conn Connection object """ try: self.conn = sqlite3.connect(self.sqlite_file) self.conn.row_factory = sqlite3.Row #this for getting the column names! except Error as e: print("create_connection: <>".format(e)) else: print("Database connection created!") def drop_table(self): """ small function to drop the dummy table """ sql = '''DROP TABLE IF EXISTS `addresses` ''' try: self.conn.execute(sql) except Error as e: print("create_table: <>".format(e)) else: print("Table dropped") def create_table(self): """ small function to create a dummy table """ sql = '''CREATE TABLE IF NOT EXISTS `addresses` (`id` integer PRIMARY KEY, `name` TEXT, `address` TEXT, `city` TEXT)''' try: self.conn.execute(sql) except Error as e: print("create_table: <>".format(e)) else: print("Table created!") def create_data(self): addresses = [("Jansen", "Blaak 928", "Rotterdam"), ("Klaasen", "Maasberglaan 23", "Rotterdam"), ("Sluijsen", "Maasstraat 25", "Barendrecht"), ("de Vos", "Meent 198", "Rotterdam"), ("De Notenkraker", "Pennylane 15", "Amsterdam")] sql = """INSERT INTO `addresses` (`name`, `address`, `city`) VALUES (?, ?, ?)""" try: cur = self.conn.cursor() cur.executemany(sql, addresses) self.conn.commit() except Error as e: print("create_table: <>".format(e)) else: print("Insert of fake data!") def get_rows(self, fields): """ Small function for getting multiple rows :param fields: :return: rows """ try: sql = '''SELECT `name`, `address`, `city` FROM `addresses` WHERE `city` = ?''' cur = self.conn.cursor() cur.execute(sql, fields) return cur.fetchall() except Error as e: print("get_row: <>".format(e)) def get_row(self, fields): try: sql = '''SELECT `name`, `address`, `city` FROM `addresses` WHERE `city` = ?''' cur = self.conn.cursor() cur.execute(sql, fields) return cur.fetchone() except Error as e: print("get_row: <>".format(e)) def close_conn(self): try: self.conn.close() except Error as e: print("close_conn: <>".format(e)) else: print("Connection closed!") if __name__ == "__main__": s = RowsKeys() # get one row and print as dictionary print("Return one Row") fields = ["Barendrecht"] data = s.get_row(fields) print(dict(data)) print("============= hljs-string">"Return multiple Rows") # get multiple rows and print as dictionary fields = ["Rotterdam"] rows = s.get_rows(fields) for row in rows: print(dict(row)) print() s.close_conn() 

Questions? Shoot! Let me know in the comments below.

Did you find this article valuable?

Support Theo van der Sluijs by becoming a sponsor. Any amount is appreciated!

Источник

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.

Источник