CODEWITHSUNDEEP
pythonmysqldatabaseormsqlalchemy
L. Heinrichs
L. Heinrichs

Reputation: 161

SQLALchemy: Get row elements based on columns key/name

What I am trying to do

Using SQLAlchemy (with ORM) i am currently trying to create a function like:

export_database_table_to_excel_file( database_orm_object )

with a database_orm_object like

class database_orm_object(db.Model):
    id              = db.Column(db.Integer, primary_key=True)
    some_string     = db.Column(db.String(30), unique=True)

The idea is to have an excel table generated that prints the columns keys as a header row, followed by one row for each of the databases entries. As any database table may be given to the function, the column count and names are unknown.

What works

So far, I am able to create a table with a row of headers, for the example above the output would be an excel file containing two cells:

| id | some_string |

This is done by fetching all of the database_orm_object columns and their keys. The keys are then appended to an (empty) excel file in memory.

def create_download_file(database_orm_object):
    book = Workbook()
    sheet = book.active

    col_headers = database_orm_object.__table__.columns.keys()
    sheet.append(col_headers) # add column keys as header row
    book.save("generated.xlsx")

What doesnt work

I dont know how to access the elements of a databases entry. My plan was to create two for loops, an outer loop that iterates through all the rows in a query.all() result and an inner loop that iterates over the database_orm_object's values. The inner loop appends the values to an array, the outer loop then adds this array as a table row on the excel file.

Code

Some of the code was already mentioned in what works. The two for loops have been added and are meant to iterate over the query results (outer) and each results values (inner). col_id and query_result.col_id are different types and are meant as an illustration of what i want to achieve: 

def create_download_file(database_orm_object):
    book = Workbook()
    sheet = book.active

    col_headers = database_orm_object.__table__.columns.keys()
    sheet.append(col_headers) # add column keys as header row

    for item in database_orm_class.query.all():
        row = []                      # create an empty row
        for col_id in col_headers:    # ??? iterate over columns
            row.append(item.col_id)   # ??? add table row's value for this column
        sheet.append(row)             # append all the tables row values to excel file

    book.save("generated.xlsx")

Question

How may I access a query result row's element if I have the column keys stored in an array or list?

Upvotes: 3

Views: 10756

Answers (1)

Son Nguyen
Son Nguyen

Reputation: 181

You can try this in new version of sqlAlchemy:

for item in database_orm_class.query.all():    
    item = dict(item)
    # your code

Or for older version:

for item in database_orm_class.query.all():    
        item = item.__dict__
        # your code

Upvotes: 4

Related Questions