Getting a whole row's data without knowing the column names in sqlalchemy

Question

My question is how does one get all data in a row (in a printable format, not just the object) where the user can specify the names of the columns of data they want. Here's my current code:

#!/usr/bin/python3
from sqlalchemy import create_engine, MetaData, Table, Column, String, Integer
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

class SQL_Engine:

    def __init__(self, dbpath, username, password, dbtype='sqlite', tableName='content_blocks', connector=None):
            self.dbpath = dbpath
            self.username = username
            self.password = password
            self.dbtype = dbtype
            self.tableName = tableName
            self.connector = connector

    def Create_Session(self):
            if self.connector == None:
                    engine = create_engine(self.dbtype+"://"+self.username+":"+self.password+"@"+self.dbpath)
            else:
                    engine = create_engine(self.dbtype+"+"+self.connector+"://"+self.username+":"+self.password+"@"+self.dbpath)
            Base = declarative_base(engine)
            class ContentBlocks(Base):
                    __tablename__ = self.tableName
                    __table_args__ = {'autoload':True}

            metadata = Base.metadata
            Session = sessionmaker(bind=engine)
            session = Session()
            return session, ContentBlocks, Base

    def ViewTable(self):
            session, ContentBlocks, Base = self.Create_Session()
            request = session.query(ContentBlocks).all()
            columns = ContentBlocks.__table__.columns.keys()
            table = " ".join(columns)
            for entry in request:
                    for column in columns:
                            #print entry.column; can't do this as of yet

My initial attempt, as you can see in Create_Session() was to retrieve all the column names and pass them as a variable into entry object, however this just results in an error:

Traceback (most recent call last):
   File "SQLDeduplication-copy.py", line 131, in <module>
      no.ViewTable()
   File "SQLDeduplication-copy.py", line 64, in ViewTable
      print(entry.column)
   AttributeError: 'ContentBlocks' object has no attribute 'column'

Thus, I need to make python interpret column as a pointer, not literally. And this is where I hit a wall because all the other StackOverflow answers I've seen include using the column name.

That said, if anyone has a better idea, I am completely open to that. Please correct my code if there are any mistakes in it

Possible duplicate of [How to access object attribute given string corresponding to name of that attribute](https://stackoverflow.com/questions/2612610/how-to-access-object-attribute-given-string-corresponding-to-name-of-that-attrib), [How to retrieve model column value dynamically in python](https://stackoverflow.com/questions/19576978/how-to-retrieve-model-column-value-dynamically-in-python) — Ilja Everilä, Dec 14 '17 at 09:26
Note that if you're going to be accessing the model attributes, perhaps inspect the mapper and its [attrs](http://docs.sqlalchemy.org/en/latest/orm/mapping_api.html#sqlalchemy.orm.mapper.Mapper.attrs) instead of the table columns. In your case they'd seem to be one and the same, but need not be. — Ilja Everilä, Dec 14 '17 at 09:29
@IljaEverilä How would you suggest implementing it? I inserted `for k in inspect(ContentBlocks).attrs`, but the individual columns still aren't iterable. Is there a way to access the data from the mapper instance? — Arcsector, Dec 15 '17 at 23:38

Arcsector · Accepted Answer · 2020-10-06T04:30:09.307

0

IljaEverilä had the correct solution. Here it is:

def ViewTable(self):
    session, ContentBlocks, Base = self.Create_Session()
    request = session.query(ContentBlocks).all()
    columns = ContentBlocks.__table__.columns.keys()
    num = 1
    for entry in request:
        print(num, '\\', end=' ')
        for column in columns:
            print(column, ':', getattr(entry, column), '|',  end=' ')

edited Oct 06 '20 at 04:30

answered Jan 02 '18 at 18:26

Arcsector

1,153
9
14

Getting a whole row's data without knowing the column names in sqlalchemy

1 Answers1