Exporting data into CSV file from Flask-SQLAlchemy

Question

I'm looking to generate(export) a csv from a flask-sqlalchemy app i'm developing. But i'm getting some unexpected outcomes in my csv i.e. instead of the actual data from the MySQL DB table populated in the csv file, i get the declarative class model entries (placeholders??). The issue possibly could be the way i structured the query or even, the entire function.

Oddly enough - judging from the csv output (pic) - it would seem i'm on the right track since the row/column count is the same as the DB table but actual data is just not populated. I'm fairly new to SQLAlchemy ORM and Flask, so looking for some guidance here to pull through. Constructive feedback appreciated.

#class declaration with  DB object (divo)
class pearl(divo.Model):
    __tablename__ = 'users'                 
    work_id = divo.Column(divo.Integer, primary_key=True)
    user_fname = divo.Column(divo.String(length=255))
    user_lname = divo.Column(divo.String(length=255))
    user_category = divo.Column(divo.String(length=255))
    user_status = divo.Column(divo.String(length=1))
    login_id = divo.Column(divo.String(length=255))
    login_passwd = divo.Column(divo.String(length=255))

#user report function
@app.route("/reports/users")
def users_report():
    with open(r'C:\Users\Xxxxxxx\Projects\_repository\zzz.csv', 'w') as s_key:
        x15 = pearl.query.all()
        for i in x15:
#        x16 = tuple(x15)
            csv_out = csv.writer(s_key)
            csv_out.writerow(x15)
    flash("Report generated. Please check designated repository.", "green")
    return redirect(url_for('reports_landing'))  # return redirect(url_for('other_tasks'))

#csv outcome (see attached pic)

bas · Accepted Answer · 2021-05-28 20:14:13Z

1

instead of the actual data from the MySQL DB table populated in the csv file, i get the declarative class model entries (placeholders??)

Each object in the list

x15 = pearl.query.all()

represents a row in your users table.

What you're seeing in the spreadsheet are not placeholders, but string representations of each row object (See object.repr).

You could get the value of a column for a particular row object by the column name attribute, for example:

x15[0].work_id # Assumes there is at least one row object in x15

What you could do instead is something like this:

with open(r'C:\Users\Xxxxxxx\Projects\_repository\zzz.csv', 'w') as s_key:
    x15 = divo.session.query(pearl.work_id, pearl.user_fname) # Add columns to query as needed
    for i in x15:
        csv_out = csv.writer(s_key)
        csv_out.writerow(i)

i in the code above is a tuple of the form:

('work_id value', 'user_fname value')

edited May 28, 2021 at 20:14

answered May 28, 2021 at 20:01

bas

15.2k7 gold badges28 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exporting data into CSV file from Flask-SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related