CODEWITHSUNDEEP
pythonsqlsql-server-2008ormsqlalchemy
Dr. John A Zoidberg
Dr. John A Zoidberg

Reputation: 1248

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

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?

Upvotes: 35

Views: 36595

Answers (3)

Mauricio Perilla
Mauricio Perilla

Reputation: 131

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

Upvotes: 13

r-m-n
r-m-n

Reputation: 15090

columns have name and type attributes

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

Upvotes: 46

Monofuse
Monofuse

Reputation: 827

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.

Upvotes: -2

Related Questions