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?

• grofte: 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'.
• Samuel Freeman: 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)

Answer 1

columns have name and type attributes

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

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

Answer 2

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'])

• Arnab Mukherjee: 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.

Answer 3

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.