How to get column names from SQLAlchemy result (declarative syntax)

I am working in a pyramid project and I’ve the table in SQLAlchemy in declarative syntax

class Projects(Base):
    __tablename__ = 'projects'
    __table_args__ = {'autoload': True}

I get the results by using

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.

Enquirer: Sukumar


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::

the example there seems like it has a typo, says q.columns but it should be q.column_descriptions (edit: just fixed it).

Respondent: zzzeek

Solution #2:

You can do something similar to Foo Stack’s answer without resorting to private fields by doing:

Respondent: prolibertas

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’, …]:


Or this

Respondent: Lydia

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):

Respondent: Foo Stack

Solution #5:

This link shows how to get all the metadata you could ever need about a table, column and more.

SQLAlchemy Metadata

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":

# or just

# via string

# iterate through all columns
for c in employees.c:

# get the table's primary key columns
for primary_key in employees.primary_key:

# get the table's foreign key objects:
for fkey in employees.foreign_keys:

# access the table's MetaData:

# access the table's bound Engine or Connection, if its MetaData is bound:

# access a column's name, type, nullable, primary key, foreign key

# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:

# access a column's table:
employees.c.employee_id.table is employees

# get the table related by a foreign key
Respondent: Mark Kortink

Solution #6:


>>> q[0].keys()


row_data = session.query(Projects).filter_by(id=1).one()

Example :

>>> q = session.query(,users_user.first_name).filter('79267548577').limit(1).all()
>>> columns_names = q[0].keys

Result :

>>> q[0].keys()
['phone', 'first_name']
Respondent: ??????? ???????

Solution #7:

Would like to extend @zzzeek’s answer. Indeed Query has column_descriptions attribute but it’s not available for all the methods.

Consider the following two queries:

1. query = session.query(Projects).filter_by(<filter_condition>)
2. query = session.query(Projects).all() <-- This query does not have column_descriptions.

So if you come across this situation where you need to use column_descriptions attribute but using ...query(...).all() then you can change it to ...query(...).filter_by() i.e. filter_by() without any filter condition.

Respondent: Abu Shumon

The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.