Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 837
Dr. John A Zoidberg Avatar asked Aug 14 '16 09:08

Dr. John A Zoidberg


People also ask

How do I get the column names of a table?

We can verify the data in the table using the SELECT query as below. We will be using sys. columns to get the column names in a table. It is a system table and used for maintaining column information.

How do I select a column in SQLAlchemy?

SQLAlchemy Core The already created students table is referred which contains 4 columns, namely, first_name, last_name, course, score. But we will be only selecting a specific column. In the example, we have referred to the first_name and last_name columns. Other columns can also be provided in the entities list.

How can I get all column names from a table in SQL Server?

USE db_name; DESCRIBE table_name; it'll give you column names with the type.

What is subquery in SQLAlchemy?

The grouping is done with the group_by() query method, which takes the column to use for the grouping as an argument, same as the GROUP BY counterpart in SQL. The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.


2 Answers

columns have name and type attributes

for c in columns:
    print c.name, c.type
like image 101
r-m-n Avatar answered Oct 10 '22 23:10

r-m-n


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'])
like image 21
Mauricio Perilla Avatar answered Oct 11 '22 01:10

Mauricio Perilla