Get column names from SQLite with Python

Photo by Kevin Ku on Unsplash

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!

THAT’s it!!!

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

    print("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!