How to retrieve table column names in a Sqlite database using python and SQLAlchemy ?

Published: April 27, 2023

Tags: Python; SQLAlchemy;

DMCA.com Protection Status

When working with databases using Python and SQLAlchemy, a common first step is to retrieve the names of columns in a table in a Sqlite database.. There are several ways to accomplish this task, but this article will focus on using Python and SQLAlchemy.

Import SQLAlchemy

First, import the necessary modules: ‘SQLAlchemy’. This will allow you to use the SQLAlchemy library and connect to a Sqlite database.

import sqlalchemy
import pkg_resources

pkg_resources.get_distribution("sqlalchemy").version

Create an instance of the SQLAlchemy Engine class

Next, create an instance of the SQLAlchemy Engine class, which will serve as the interface between your Python code and the database. Provide the name of the Sqlite database as the argument.

engine = sqlalchemy.create_engine('sqlite:///database_name.db')

For example using the chinook.db databse:

engine = sqlalchemy.create_engine('sqlite:///chinook.db')

Note: to get database table names

engine = sqlalchemy.create_engine('sqlite:///chinook.db')

print(engine.table_names())

returns

['CUSTOMER', 'PRODUCT', 'albums', 'artists', 'customers', 'employees', 'genres', 'invoice_items', 'invoices', 'media_types', 'playlist_track', 'playlists', 'sqlite_sequence', 'sqlite_stat1', 'tracks']

Get column names of a table

To get column names of a table, for example the table called 'employees' a solution is to do:

query = engine.execute('PRAGMA table_info(employees);')

for row in query:
    print(row)

returns here

(0, 'EmployeeId', 'INTEGER', 1, None, 1)
(1, 'LastName', 'NVARCHAR(20)', 1, None, 0)
(2, 'FirstName', 'NVARCHAR(20)', 1, None, 0)
(3, 'Title', 'NVARCHAR(30)', 0, None, 0)
(4, 'ReportsTo', 'INTEGER', 0, None, 0)
(5, 'BirthDate', 'DATETIME', 0, None, 0)
(6, 'HireDate', 'DATETIME', 0, None, 0)
(7, 'Address', 'NVARCHAR(70)', 0, None, 0)
(8, 'City', 'NVARCHAR(40)', 0, None, 0)
(9, 'State', 'NVARCHAR(40)', 0, None, 0)
(10, 'Country', 'NVARCHAR(40)', 0, None, 0)
(11, 'PostalCode', 'NVARCHAR(10)', 0, None, 0)
(12, 'Phone', 'NVARCHAR(24)', 0, None, 0)
(13, 'Fax', 'NVARCHAR(24)', 0, None, 0)
(14, 'Email', 'NVARCHAR(60)', 0, None, 0)

References

Links Site
sqlalchemy sqlalchemy.org
SQLite wikipedia.org
sqlite sqlite.org