Getting column data from result set with column name as a string.

[Dale Preston]

I'm using sqlalchemy 1.3.18.  I'm trying to write an app that looks at data from an ORM declarative table without necessarily knowing the table definition.

What I am looking for is a way to get a single object (row in resultSet), having the name of column[1] is "lastname", and having "lastname" as a string in memory, how can I get the value of the "lastname" field from the row in resultSet?

It's easy if I know in code that the row has a lastname property and I can use row.lastname but I want to do something like row["lastname"] or row.columns["lastname"] if there's a way.

I'm using reflection to get the columns for the table.  Here's some code I tried:

class Users(Base):

    __tablename__ = 'users'

    userid = Column(String(80), primary_key=True)

    lastname = Column(String(40), nullable=False)

    firstname = Column(String(40), nullable=False)

    emailaddress = Column(String(80), nullable=False)

def ReflectTableColumns(DbEngine, meta, targetTable):

    tableschema = Table(targetTable, meta, autoload=True, autoload_with=DbEngine)

    cols = dict()

    for c in tableschema.columns:

        print("{0}\t|\t{1}".format(c.name, c.type))

        cols[c.name] = c.type

    

    return cols

def GetUsers():

    DBSession = sessionmaker(bind=Engine)

    session = DBSession()

    ShowTableData(session.query(Users).all(), 'users')

def ShowTableData(resultSet, tablename):

    columns = ReflectTableColumns(Engine, Base.metadata, tablename)

    columnNames = list(columns.keys())

    print (type(resultSet))

    for row in resultSet:

        print (row.items[columnNames[1]])

        print (row.columns[columnNames[1]])

        print (row[columnNames[1]])

        

GetUsers()

[Mike Bayer]

On Tue, Aug 18, 2020, at 5:20 PM, Dale Preston wrote:

I'm using sqlalchemy 1.3.18.  I'm trying to write an app that looks at data from an ORM declarative table without necessarily knowing the table definition.

What I am looking for is a way to get a single object (row in resultSet), having the name of column[1] is "lastname", and having "lastname" as a string in memory, how can I get the value of the "lastname" field from the row in resultSet?

It's easy if I know in code that the row has a lastname property and I can use row.lastname but I want to do something like row["lastname"] or row.columns["lastname"] if there's a way.

to get individual columns in the row you query for those columns directly:

row = sess.query(User.lastname).first()

print(row.lastname)

otherwise you can always label a column if you need:

row = sess.query(User.anything.label("lastname")).first()

print(row.lastname)

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f11bf60e-e872-489e-9a9b-03998440bbb1n%40googlegroups.com.

[Dale Preston]

Thanks.  The label is an interesting option; I'll look into that.

On a StackOverflow thread, I got row.__table__.columns which I can iterate over and test the key, allowing me to get the column I need but I have to loop through all the columns until I find the one I want for each row because columns doesn't have an index either.  I also don't like using a private property but I guess (hope) __table__ would always be there.

[Mike Bayer]

__table__ is public (private would be a single or double underscore prefix only), but also you could use inspect(cls).column_attrs:

https://docs.sqlalchemy.org/en/13/orm/mapping_api.html?highlight=column_attrs#sqlalchemy.orm.Mapper.column_attrs

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/975d8626-39cf-439f-b5ed-df1e9680f7bfn%40googlegroups.com.

[Simon King]

If you want to get an attribute of an object where the name of the
attribute is variable, you can use the getattr function:

attrname = "lastname"
column = getattr(User, attrname)
for item in session.query(column):
print(item)

or:

attrname = "lastname"
for user in session.query(User):
value = getattr(user, attrname)
print(value)

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9bfe5942-6161-455e-845a-924b2e9f4f12%40www.fastmail.com.