Menu
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?
837
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.
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.
USE db_name; DESCRIBE table_name; it'll give you column names with the type.
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.
columns have name and type attributes
for c in columns:
print c.name, c.type
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'])
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
