How i can get data from DB with columns.names(keys)?

Question

I am using SQLAlchemy to pull data from my database. More specifically, I use the db.select method. So I manage to pull out only the values from the columns or only the names of the columns, but I need to pull out in the format NAME: VALUE. Help how to do this?

 connection = engine.connect()
    metadata = db.MetaData()
    report = db.Table('report', metadata, autoload=True, autoload_with=engine)
    query = db.select([report])
    ResultProxy = connection.execute(query)
    ResultSet = ResultProxy.fetchall()

Does this help? https://stackoverflow.com/questions/6455560/how-to-get-column-names-from-sqlalchemy-result-declarative-syntax — MSH, Oct 26 '21 at 08:31

score 0 · Answer 1 · answered Oct 26 '21 at 10:48

As the docs state, ResultProxy.fetchall() returns a list of RowProxy objects. These behave like namedtuples, but can also be used like dictionaries:

>>> ResultSet[0]['column_name']
column_value

For more info, see https://docs.sqlalchemy.org/en/13/core/tutorial.html#coretutorial-selecting

Gord Thompson · Accepted Answer · 2021-10-26T14:58:24.570

With SQLAlchemy 1.4+ we can use .mappings() to return results in a dictionary-like format:

import sqlalchemy as sa

# …

t = sa.Table(
    "t",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=False),
    sa.Column("txt", sa.String),
)
t.create(engine)

# insert some sample data
with engine.begin() as conn:
    conn.exec_driver_sql(
        "INSERT INTO t (id, txt) VALUES (1, 'foo'), (2, 'bar')"
    )

# test code
with engine.begin() as conn:
    results = conn.execute(select(t)).mappings().fetchall()
    pprint(results)
    # [{'id': 1, 'txt': 'foo'}, {'id': 2, 'txt': 'bar'}]

How i can get data from DB with columns.names(keys)?

2 Answers2