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 |