How to print all columns in SQLAlchemy ORM

Question

Using SQLAlchemy, I am trying to print out all of the attributes of each model that I have in a manner similar to:

SELECT * from table;

However, I would like to do something with each models instance information as I get it. So far the best that I've been able to come up with is:

for m in session.query(model).all():
    print [getattr(m, x.__str__().split('.')[1]) for x in model.__table__.columns]
    # additional code

And this will give me what I'm looking for, but it's a fairly roundabout way of getting it. I was kind of hoping for an attribute along the lines of:

m.attributes 
# or 
m.columns.values

I feel I'm missing something and there is a much better way of doing this. I'm doing this because I'll be printing everything to .CSV files, and I don't want to have to specify the columns/attributes that I'm interested in, I want everything (there's a lot of columns in a lot of models to be printed).

score 42 · Answer 1 · answered Feb 27 '15 at 22:29

42

This is an old post, but I ran into a problem with the actual database column names not matching the mapped attribute names on the instance. We ended up going with this:

from sqlalchemy import inspect
inst = inspect(model)
attr_names = [c_attr.key for c_attr in inst.mapper.column_attrs]

Hope that helps somebody with the same problem!

answered Feb 27 '15 at 22:29

Starktron

551
1
4
5

Very useful. Thank you! – clime May 23 '16 at 07:41
1

Likewise, if you're looking for relationships: `rel_names = [rel.key for rel in inst.mapper.relationships]`. You can also find synonyms and composites: https://docs.sqlalchemy.org/en/13/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.attrs – Nathaniel Jones Aug 22 '19 at 21:34
It would be nice if there was a way to access the attribute names from the `column` object. As it stands I have to access both in the same comprehension: `unique_columns = [c_attr.key for c_attr in inst.column_attrs if inst.columns[c_attr.key].unique]` – Jonathan Biemond Aug 23 '23 at 21:50

score 31 · Answer 2 · edited Dec 26 '20 at 16:18

31

Probably the shortest solution (see the recent documentation):

from sqlalchemy.inspection import inspect
columns = [column.name for column in inspect(model).c]

The last line might look more readable, if rewrite it in three lines:

table = inspect(model)
for column in table.c:
    print column.name

edited Dec 26 '20 at 16:18

monojohnny

5,894
16
59
83

answered Oct 19 '15 at 16:23

Kostiantyn Ivashchenko

941
1
10
15

2

nice solution, this fixed my problem – Han.Oliver Nov 29 '18 at 09:05
1

This also works with flask-sqlalchemy set up – Doptima Aug 06 '21 at 04:01

score 23 · Answer 3 · answered Mar 17 '14 at 21:47

23

Building on Rodney L's answer:

model = MYMODEL
columns = [m.key for m in model.__table__.columns]

answered Mar 17 '14 at 21:47

KurtB

604
7
8

score 11 · Accepted Answer · edited May 07 '20 at 19:42

11

Take a look at SQLAchemy's metadata reflection feature.

A Table object can be instructed to load information about itself from the corresponding database schema object already existing within the database. This process is called reflection.

edited May 07 '20 at 19:42

G. Shand

388
3
12

answered May 18 '11 at 04:12

Alexander Kiselev

309
2
7

2

Using flask's sqlalchemy ORM I was able to get the column names via the following: data_table.__table__.columns – simoes Aug 21 '13 at 21:27
9

`{i.name: getattr(m, i.name) for i in m.__table__.columns}` this worked fine for me. – Waket Zheng Apr 12 '19 at 09:00
Link seems broken. Can you paste some sample code, how "metadata reflection" should work? Does this link point at the right inspection, note the `get_columns` function? https://docs.sqlalchemy.org/en/13/core/reflection.html#fine-grained-reflection-with-inspector – Nate Anderson Nov 26 '19 at 01:47

Fahad Yousuf · Answer 5 · 2018-11-30T15:15:04.740

6

I believe this is the easiest way:

print [cname for cname in m.__dict__.keys()]

EDIT: The answer above me using sqlalchemy.inspection.inspect() seems to be a better solution.

edited Nov 30 '18 at 15:15

answered Sep 20 '12 at 22:05

Fahad Yousuf

181
1
10

__dict__ not always can help. I use keys = self._sa_instance_state.attrs.items() – n0nSmoker Jan 30 '13 at 11:16
not help..if we have multiple function defined under the model, this will get these function – Han.Oliver Nov 30 '18 at 02:00

score 6 · Answer 6 · answered Sep 28 '17 at 19:29

6

print repr(model.__table__)

Or just the columns:

print str(list(model.__table__.columns))

answered Sep 28 '17 at 19:29

Curtis Yallop

6,696
3
46
36

jmunsch · Answer 7 · 2015-08-03T20:28:44.917

Put this together and found it helpful:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql+pymysql://testuser:password@localhost:3306/testdb')
DeclarativeBase = declarative_base()
metadata = DeclarativeBase.metadata
metadata.bind = engine


# configure Session class with desired options
Session = sessionmaker()

# associate it with our custom Session class
Session.configure(bind=engine)

# work with the session
session = Session()

And then:

d = {k: metadata.tables[k].columns.keys() for k in metadata.tables.keys()}

Example output print(d):

{'orderdetails': ['orderNumber', 'productCode', 'quantityOrdered', 'priceEach', 'orderLineNumber'], 
'offices': ['addressLine1', 'addressLine2', 'city', 'country', 'officeCode', 'phone', 'postalCode', 'state', 'territory'],
'orders': ['comments', 'customerNumber', 'orderDate', 'orderNumber', 'requiredDate', 'shippedDate', 'status'],
'products': ['MSRP', 'buyPrice', 'productCode', 'productDescription', 'productLine', 'productName', 'productScale', 'productVendor', 'quantityInStock'],
'employees': ['employeeNumber', 'lastName', 'firstName', 'extension', 'email', 'officeCode', 'reportsTo', 'jobTitle'], 
'customers': ['addressLine1', 'addressLine2', 'city', 'contactFirstName', 'contactLastName', 'country', 'creditLimit', 'customerName', 'customerNumber', 'phone', 'postalCode', 'salesRepEmployeeNumber', 'state'],
'productlines': ['htmlDescription', 'image', 'productLine', 'textDescription'],
'payments': ['amount', 'checkNumber', 'customerNumber', 'paymentDate']}

OR and then:

from sqlalchemy.sql import text
cmd = "SELECT * FROM information_schema.columns WHERE table_schema = :db ORDER BY table_name,ordinal_position"
result = session.execute(
            text(cmd),
            {"db": "classicmodels"}
        )
result.fetchall()

FistOfFury · Answer 8 · 2016-08-26T21:28:16.723

I'm using SQL Alchemy v 1.0.14 on Python 3.5.2

Assuming you can connect to an engine with create_engine(), I was able to display all columns using the following code. Replace "my connection string" and "my table name" with the appropriate values.

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine('my connection string')

conn = engine.connect()
metadata = MetaData(conn)
t = Table("my table name", metadata, autoload=True)
columns = [m.key for m in t.columns]
columns

the last row just displays the column names from the previous statement.

score 1 · Answer 9 · answered May 18 '11 at 05:34

You may be interested in what I came up with to do this.

from sqlalchemy.orm import class_mapper
import collections

# structure returned by get_metadata function.
MetaDataTuple = collections.namedtuple("MetaDataTuple", 
        "coltype, colname, default, m2m, nullable, uselist, collection")


def get_metadata_iterator(class_):
    for prop in class_mapper(class_).iterate_properties:
        name = prop.key
        if name.startswith("_") or name == "id" or name.endswith("_id"):
            continue
        md = _get_column_metadata(prop)
        if md is None:
            continue
        yield md


def get_column_metadata(class_, colname):
    prop = class_mapper(class_).get_property(colname)
    md = _get_column_metadata(prop)
    if md is None:
        raise ValueError("Not a column name: %r." % (colname,))
    return md


def _get_column_metadata(prop):
    name = prop.key
    m2m = False
    default = None
    nullable = None
    uselist = False
    collection = None
    proptype = type(prop)
    if proptype is ColumnProperty:
        coltype = type(prop.columns[0].type).__name__
        try:
            default = prop.columns[0].default
        except AttributeError:
            default = None
        else:
            if default is not None:
                default = default.arg(None)
        nullable = prop.columns[0].nullable
    elif proptype is RelationshipProperty:
        coltype = RelationshipProperty.__name__
        m2m = prop.secondary is not None
        nullable = prop.local_side[0].nullable
        uselist = prop.uselist
        if prop.collection_class is not None:
            collection = type(prop.collection_class()).__name__
        else:
            collection = "list"
    else:
        return None
    return MetaDataTuple(coltype, str(name), default, m2m, nullable, uselist, collection)

score 0 · Answer 10 · answered Oct 03 '13 at 14:02

0

I use this because it's slightly shorter:

for m in session.query(*model.__table__.columns).all():
    print m

answered Oct 03 '13 at 14:02

rvl

81
5

How to print all columns in SQLAlchemy ORM

10 Answers10

Linked