How can I get column name and type from an existing table in SQLAlchemy?

Question

Suppose I have the table users and I want to know what the column names are and what the types are for each column.

I connect like this;

connectstring = ('mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BSQL'
                     '+Server%7D%3B+server%3D.....')

engine = sqlalchemy.create_engine(connectstring).connect()
md = sqlalchemy.MetaData()
table = sqlalchemy.Table('users', md, autoload=True, autoload_with=engine)
columns = table.c

If I call

for c in columns:
            print type(columns)

I get the output

<class 'sqlalchemy.sql.base.ImmutableColumnCollection'>

printed once for each column in the table. Furthermore,

print columns

prints

['users.column_name_1', 'users.column_name_2', 'users.column_name_3'....]

Is it possible to get the column names without the table name being included?

for c in columns: print(type(columns)) will print the type of 'columns', not 'c', for as many times as there are 'c's in 'columns'. — grofte
– grofte, Commented Sep 1, 2020 at 11:58
using this sqlalchemy.Table(... method under SQLAlchemy v 1.4 I was getting a 'will be depricated in v 2.0' warning -> by consulting SQLA meta data docs I found that removing the autoload=True arg clears that warning. in other words that line in this example would be: table = sqlalchemy.Table('users', md, autoload_with=engine) — Samuel Freeman
– Samuel Freeman, Commented Mar 19, 2023 at 11:52

r-m-n · Accepted Answer · 2016-08-14 09:35:56Z

46

columns have name and type attributes

for c in columns:
    print c.name, c.type

answered Aug 14, 2016 at 9:35

r-m-n

15.3k6 gold badges74 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Engensmax Over a year ago

If I may add here, you can also get the python type of the column by calling c.type.python_type. (See also here)

Mauricio Perilla · Accepted Answer · 2021-06-16 16:33:12Z

14

Better use "inspect" to obtain only information from the columns, with that you do not reflect the table.

import sqlalchemy
from sqlalchemy import inspect

engine = sqlalchemy.create_engine(<url>)
insp = inspect(engine)

columns_table = insp.get_columns(<table_name>, <schema>) #schema is optional


for c in columns_table :
    print(c['name'], c['type'])

edited Jun 16, 2021 at 16:33

answered Jun 16, 2021 at 5:22

Mauricio Perilla

1411 silver badge3 bronze badges

1 Comment

Arnab Mukherjee Over a year ago

Approach is encouraging, reflecting from table is more readable. Apart from that, once you have the table object you can write queries as well. Not sure inspections is lazy or not. If it's not lazy you are reading the complete database in one shot.

Monofuse · Accepted Answer · 2016-08-14 09:35:26Z

-2

You could call the column names from the Information Schema:

SELECT * FROM information_schema.columns WHERE TABLE_NAME = 'users'

Then sort the result as an array, and loop through them.

answered Aug 14, 2016 at 9:35

Monofuse

8379 silver badges15 bronze badges

Collectives™ on Stack Overflow

How can I get column name and type from an existing table in SQLAlchemy?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related