How to get column names from SQLAlchemy result (declarative syntax)
Solution 1
The difference is between ORM and non-ORM, not declarative, which is just a helper for the ORM.
Query has a method column_descriptions()
that was added for this purpose::
http://www.sqlalchemy.org/docs/orm/query.html#sqlalchemy.orm.query.Query.column_descriptions
the example there seems like it has a typo, says q.columns
but it should be q.column_descriptions
(edit: just fixed it).
Solution 2
You can do something similar to Foo Stack's answer without resorting to private fields by doing:
conn.execute(query).keys()
Solution 3
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import (Column, Index, Date, DateTime, Numeric, BigInteger, String, ForeignKey, Boolean)
Base = declarative_base()
class Project(Base):
"""sqlalchemy ORM for my table."""
__tablename__ = "table1"
id = Column("id", BigIntegerID, primary_key=True, autoincrement=True)
date = Column("date", Date, nullable=False)
value = Column("value", Numeric(20, 8))
...
...
Then this will return the columns names ['id', 'date', 'value', ...]:
Project.__table__.columns.keys()
Or this
Project.metadata.tables['table1'].columns.keys()
Solution 4
Just playing around, this syntax will give you all the columns (so to solve your problem, set query to look at one table/object only):
conn.execute(query)._metadata.keys
Solution 5
This link shows how to get all the metadata you could ever need about a table, column and more.
Many of the answers above are based on the info on this page. Suppose we have declared a table.
employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('employee_name', String(60), nullable=False),
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)
Here are some examples of getting metadata about the table.
# access the column "EMPLOYEE_ID":
employees.columns.employee_id
# or just
employees.c.employee_id
# via string
employees.c['employee_id']
# iterate through all columns
for c in employees.c:
print(c)
# get the table's primary key columns
for primary_key in employees.primary_key:
print(primary_key)
# get the table's foreign key objects:
for fkey in employees.foreign_keys:
print(fkey)
# access the table's MetaData:
employees.metadata
# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind
# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys
# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key
# access a column's table:
employees.c.employee_id.table is employees
# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table
Comments
-
Sukumar almost 2 years
I am working in a pyramid project and I've the table in SQLAlchemy in declarative syntax
"""models.py""" class Projects(Base): __tablename__ = 'projects' __table_args__ = {'autoload': True}
I get the results by using
""""views.py""" session = DBSession() row_data = session.query(Projects).filter_by(id=1).one()
How can I get the column names from this result.
PS: I am unable to use this method since I am using the declarative syntax.
-
Sukumar almost 13 yearsThanks for the answer! Unfortunately, this returns [{'aliased': False, 'expr': <class 'metrics.models.Projects'>, 'type': <class 'metrics.models.Projects'>, 'name': 'Projects'}] I am trying to use reflection so that I don't have to specify the column name and with this method I'll still have to specify the column names. Or am I missing something?
-
Sukumar almost 13 yearsI think I found the solution. I was able to get the column list by using Projects.__table__.columns.keys()
-
CubeBot88 almost 7 yearsNote that 'columns' is interchangeable with 'c', so for above Project.metadata.tables[Project.__tablename__].c.keys() would also work. As would Project.__table__.c.keys()
-
Grail Finder almost 6 yearsits still there 5 years later. Only way you can make something reliable is to rely on that.
-
Jcc.Sanabria over 5 yearsNice! It'll be useful in a property (setter) method
-
medley56 over 4 yearsI think this only works if you are specifying which columns you want back in the query (which means you already know the column names). What @Sukumar and I need is the columns for a query constructed like
q=sess.query(MyTableObject).all()
-
wolfmanx almost 4 years
column_descriptions
is an attribute, not a method. -
Tom over 3 yearsI know this is an old answer, but hoping someone knows: if the query is for more than one entity, how do you get unambiguous column names? For a query like
Query(DataClass1).join(DataClass2).add_entity(DataClass2)
, ifDataClass1
andDataClass2
have fields with the same names, thenkeys()
returns a list with duplicate entries. -
Kots over 2 yearsbravo! i learned something and didn't just copy